|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/postgresql

$cat docs/postgresql-deep-dive.md

updated Recently·50 min read·published

PostgreSQL Deep Dive

◆PostgreSQL◆SQL◆Databases◆Intermediate to Advanced🎯Free Tools

Introduction

PostgreSQL is the world's most advanced open-source relational database. It has been in active development for over 35 years and offers features that rival commercial databases like Oracle and SQL Server — all under a liberal open-source license.

What sets PostgreSQL apart is its extensibility, standards compliance, and the breadth of its feature set. It natively supports JSONB, full-text search, geospatial data (PostGIS), vector embeddings (pgvector), time-series (TimescaleDB), and much more — often eliminating the need for separate specialized databases.

This guide goes beyond basic SQL to explore the features that make PostgreSQL uniquely powerful for modern application development.

📝

note

PostgreSQL 17 (released 2024) introduced incremental backup, logical replication improvements, and new JSON_TABLE functionality. This guide covers features available in PostgreSQL 15+.

JSONB — Document Storage in SQL

JSONB (binary JSON) is PostgreSQL's high-performance JSON data type. Unlike the text-based JSON type, JSONB is stored in a decomposed binary format that supports indexing, efficient querying, and operators for path-based access. This effectively gives you document database capabilities inside a relational database.

jsonb-basics.sql

SQL

1	-- Creating a table with JSONB columns
2	CREATE TABLE products (
3	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4	name VARCHAR(255) NOT NULL,
5	metadata JSONB NOT NULL DEFAULT '{}',
6	tags TEXT[] DEFAULT '{}',
7	created_at TIMESTAMPTZ DEFAULT NOW()
8	);
9
10	-- Inserting JSONB data
11	INSERT INTO products (name, metadata, tags) VALUES (
12	'Wireless Headphones',
13	'{
14	"brand": "Sony",
15	"specs": {
16	"weight": 250,
17	"battery_life": 30,
18	"bluetooth_version": 5.2,
19	"noise_canceling": true
20	},
21	"colors": ["black", "silver", "midnight blue"],
22	"reviews": {
23	"avg_rating": 4.7,
24	"count": 2847
25	}
26	}',
27	ARRAY['audio', 'wireless', 'bluetooth']
28	);
29
30	-- Accessing JSONB values
31	SELECT
32	name,
33	metadata->>'brand' AS brand, -- text extraction
34	metadata->'specs'->>'weight' AS weight, -- nested access
35	(metadata->'specs'->>'battery_life')::int AS battery,
36	metadata->'colors' AS all_colors, -- raw JSONB
37	jsonb_array_length(metadata->'colors') AS color_count
38	FROM products;
39
40	-- JSONB operators
41	SELECT name FROM products WHERE metadata @> '{"brand": "Sony"}';
42	SELECT name FROM products WHERE metadata->'specs'->>'noise_canceling' = 'true';
43	SELECT name FROM products WHERE metadata @? '$.specs.battery_life ? (@ > 20)';
44	SELECT * FROM products WHERE metadata @@ 'keys(@) ? (@ == "brand")';

jsonb-advanced.sql

SQL

1	-- GIN index for fast JSONB queries
2	CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
3	CREATE INDEX idx_products_metadata_path ON products USING GIN (metadata jsonb_path_ops);
4
5	-- jsonb_path_ops is smaller and faster for containment (@>) queries
6	-- but does not support key-existence (?) or all-keys (??) operators
7
8	-- JSONB aggregation
9	SELECT
10	jsonb_object_agg(
11	name,
12	metadata->>'brand'
13	) AS product_brands
14	FROM products;
15
16	-- JSONB in GROUP BY (unnest keys)
17	SELECT
18	jsonb_array_elements_text(metadata->'colors') AS color,
19	COUNT(*) AS product_count
20	FROM products
21	GROUP BY color
22	ORDER BY product_count DESC;
23
24	-- Update specific JSONB path
25	UPDATE products
26	SET metadata = jsonb_set(
27	metadata,
28	'{specs, battery_life}',
29	'35'
30	)
31	WHERE id = $1;
32
33	-- Remove a key
34	UPDATE products
35	SET metadata = metadata - 'reviews';
36
37	-- Merge JSONB objects
38	UPDATE products
39	SET metadata = metadata \|\| '{"warranty": "2 years"}'::jsonb;
40
41	-- JSONB to/from relational
42	-- Shred JSONB into rows
43	SELECT
44	p.name,
45	elem AS color
46	FROM products p,
47	jsonb_array_elements_text(p.metadata->'colors') AS elem;
48
49	-- Aggregate rows into JSONB
50	SELECT
51	jsonb_agg(
52	jsonb_build_object('name', name, 'brand', metadata->>'brand')
53	) AS all_products
54	FROM products;

ℹ

info

Use the containment operator @> with a GIN index for the fastest JSONB queries. Thejsonb_path_ops operator class is smaller and faster than the default GIN class, but only supports containment queries.

Advanced CTEs

Common Table Expressions in PostgreSQL support features that go beyond the SQL standard, including data-modifying CTEs that can INSERT, UPDATE, or DELETE rows and return the affected rows in a single query.

advanced-ctes.sql

SQL

1	-- Data-modifying CTE: UPDATE with returning results
2	WITH updated_orders AS (
3	UPDATE orders
4	SET status = 'shipped', shipped_at = NOW()
5	WHERE status = 'confirmed'
6	AND created_at < NOW() - INTERVAL '2 days'
7	RETURNING id, user_id, total
8	)
9	-- Insert shipping notifications for shipped orders
10	INSERT INTO notifications (user_id, type, data, created_at)
11	SELECT
12	user_id,
13	'order_shipped',
14	jsonb_build_object('order_id', id, 'total', total),
15	NOW()
16	FROM updated_orders;
17
18	-- CTE with multiple statements
19	WITH
20	-- Calculate monthly stats
21	monthly AS (
22	SELECT
23	DATE_TRUNC('month', created_at) AS month,
24	SUM(total) AS revenue,
25	COUNT(*) AS orders
26	FROM orders WHERE status = 'completed'
27	GROUP BY DATE_TRUNC('month', created_at)
28	),
29	-- Calculate growth rates
30	growth AS (
31	SELECT
32	month,
33	revenue,
34	orders,
35	LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
36	ROUND(
37	(revenue - LAG(revenue) OVER (ORDER BY month))
38	/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
39	) AS growth_pct
40	FROM monthly
41	),
42	-- Flag anomalies (revenue dropped > 20%)
43	anomalies AS (
44	SELECT * FROM growth WHERE growth_pct < -20
45	)
46	SELECT * FROM anomalies ORDER BY month DESC;

🔥

pro tip

Data-modifying CTEs are PostgreSQL-specific and incredibly useful for complex multi-step operations that need to return intermediate results. They are essentially stored procedures without the overhead.

Window Functions — Advanced

PostgreSQL offers a rich set of window functions that go beyond basic ranking and aggregation. These include statistical functions, percentiles, hypothetical set functions, and frame specifications.

window-advanced.sql

SQL

1	-- Percentiles and distribution
2	SELECT
3	name,
4	salary,
5	PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank,
6	CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution,
7	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
8	OVER (PARTITION BY department) AS median_salary,
9	PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary)
10	OVER (PARTITION BY department) AS p90_salary
11	FROM employees;
12
13	-- Hypothetical aggregate functions (for "what if" analysis)
14	SELECT
15	product_name,
16	current_rank,
17	hypothetical_rank
18	FROM (
19	SELECT
20	p.name AS product_name,
21	RANK() OVER (ORDER BY p.sales DESC) AS current_rank,
22	RANK() WITHIN GROUP (ORDER BY p.sales DESC)
23	OVER () AS hypothetical_rank
24	FROM products p
25	) sub
26	WHERE hypothetical_rank <= 10;
27
28	-- Frame specifications for precise control
29	SELECT
30	DATE_TRUNC('day', created_at) AS day,
31	revenue,
32	-- Cumulative from start of partition
33	SUM(revenue) OVER w AS cumulative,
34	-- Rolling 7-day average
35	AVG(revenue) OVER w7 AS rolling_7d_avg,
36	-- Rolling 30-day max
37	MAX(revenue) OVER w30 AS rolling_30d_max
38	FROM daily_revenue
39	WINDOW
40	w AS (ORDER BY DATE_TRUNC('day', created_at)),
41	w7 AS (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
42	w30 AS (w ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
43	ORDER BY day DESC;

Full-Text Search

PostgreSQL has built-in full-text search capabilities using tsvector (a sorted list of distinct lexemes) and tsquery (a boolean query language). This eliminates the need for external search engines like Elasticsearch for many use cases.

full-text-search.sql

SQL

1	-- Create a tsvector column and GIN index
2	ALTER TABLE articles
3	ADD COLUMN search_vector tsvector
4	GENERATED ALWAYS AS (
5	setweight(to_tsvector('english', coalesce(title, '')), 'A') \|\|
6	setweight(to_tsvector('english', coalesce(body, '')), 'B') \|\|
7	setweight(to_tsvector('english', coalesce(tags::text, '')), 'C')
8	) STORED;
9
10	CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
11
12	-- Search with ranking
13	SELECT
14	title,
15	ts_rank_cd(search_vector, query) AS rank,
16	ts_headline('english', body, query,
17	'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20'
18	) AS highlighted_body
19	FROM articles, plainto_tsquery('english', 'postgresql indexing performance') query
20	WHERE search_vector @@ query
21	ORDER BY rank DESC
22	LIMIT 20;
23
24	-- Weighted search with phrase matching
25	SELECT title, ts_rank_cd(search_vector, query) AS rank
26	FROM articles, phraseto_tsquery('english', 'database optimization tips') query
27	WHERE search_vector @@ query
28	ORDER BY rank DESC;
29
30	-- Autocomplete with trigram similarity
31	CREATE EXTENSION IF NOT EXISTS pg_trgm;
32
33	SELECT
34	name,
35	similarity(name, 'postgresql') AS sim
36	FROM articles
37	WHERE name % 'postgresql'
38	ORDER BY sim DESC
39	LIMIT 5;
40
41	-- Configure stop words and dictionaries
42	ALTER TEXT SEARCH CONFIGURATION english
43	ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
44	WITH english_stem;

ℹ

info

For most web applications, PostgreSQL full-text search is sufficient and eliminates the operational complexity of running a separate search engine. Use Elasticsearch only when you need advanced features like fuzzy matching, complex faceting, or search across massive datasets (100M+ documents).

Array and Composite Types

PostgreSQL supports arrays as a native column type, allowing you to store lists of values directly in a table. Combined with GIN indexes, array queries can be very efficient.

arrays.sql

SQL

1	-- Array column
2	CREATE TABLE articles (
3	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4	title TEXT NOT NULL,
5	tags TEXT[] DEFAULT '{}',
6	scores INTEGER[] DEFAULT '{}'
7	);
8
9	-- Insert arrays
10	INSERT INTO articles (title, tags, scores) VALUES
11	('PostgreSQL Tips', ARRAY['postgres', 'database', 'sql'], '{95, 87, 92}'),
12	('Redis Caching', ARRAY['redis', 'caching', 'performance'], '{88, 91}');
13
14	-- Array operators
15	SELECT * FROM articles WHERE 'postgres' = ANY(tags); -- contains value
16	SELECT * FROM articles WHERE tags @> ARRAY['database']; -- contains subset
17	SELECT * FROM articles WHERE tags && ARRAY['redis', 'sql'];-- overlaps
18	SELECT * FROM articles WHERE NOT tags @> ARRAY['python']; -- does not contain
19
20	-- Unnest array to rows
21	SELECT title, unnest(tags) AS tag FROM articles;
22
23	-- Aggregate rows to array
24	SELECT array_agg(DISTINCT tag) AS all_tags
25	FROM articles, unnest(tags) AS tag;
26
27	-- Array aggregation and manipulation
28	SELECT
29	title,
30	array_length(tags, 1) AS tag_count,
31	tags[1] AS first_tag,
32	array_remove(tags, 'sql') AS without_sql,
33	array_cat(tags, ARRAY['new-tag']) AS with_added,
34	array_append(tags, 'another') AS appended
35	FROM articles;
36
37	-- GiST and GIN indexes for arrays
38	CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
39	CREATE INDEX idx_articles_tags_gist ON articles USING GiST (tags);

Essential Extensions

PostgreSQL's extension system allows adding functionality without modifying the core database. Extensions are SQL scripts that create functions, types, and operators. Here are the most important ones for modern applications.

extensions.sql

SQL

1	-- Enable extensions
2	CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- UUID generation
3	CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- Encryption functions
4	CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- Trigram similarity search
5	CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";-- Query performance tracking
6	CREATE EXTENSION IF NOT EXISTS "btree_gist"; -- GiST index for B-tree types
7	CREATE EXTENSION IF NOT EXISTS "intarray"; -- Integer array operations
8	CREATE EXTENSION IF NOT EXISTS "hstore"; -- Key-value store type
9	CREATE EXTENSION IF NOT EXISTS "pgvector"; -- Vector similarity search (AI)
10	CREATE EXTENSION IF NOT EXISTS "postgis"; -- Geospatial data
11	CREATE EXTENSION IF NOT EXISTS "pg_partman"; -- Partition management
12	CREATE EXTENSION IF NOT EXISTS "timescaledb"; -- Time-series optimization
13
14	-- pgvector: store and query embeddings
15	CREATE TABLE document_embeddings (
16	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17	content TEXT NOT NULL,
18	embedding vector(1536) NOT NULL
19	);
20
21	CREATE INDEX idx_doc_embedding ON document_embeddings
22	USING ivfflat (embedding vector_cosine_ops)
23	WITH (lists = 100);
24
25	-- Query similar documents
26	SELECT
27	content,
28	1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
29	FROM document_embeddings
30	ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
31	LIMIT 10;
32
33	-- PostGIS: geospatial queries
34	CREATE TABLE locations (
35	id UUID PRIMARY KEY,
36	name TEXT,
37	geom GEOGRAPHY(POINT, 4326)
38	);
39
40	CREATE INDEX idx_locations_geom ON locations USING GIST (geom);
41
42	-- Find all locations within 5km
43	SELECT name, ST_Distance(geom, ST_MakePoint(-73.9857, 40.7484)::geography) AS distance
44	FROM locations
45	WHERE ST_DWithin(
46	geom,
47	ST_MakePoint(-73.9857, 40.7484)::geography,
48	5000 -- 5km in meters
49	)
50	ORDER BY distance;

📝

note

pgvector has become essential for AI applications, enabling similarity search on embeddings directly in PostgreSQL. It powers RAG pipelines, semantic search, and recommendation systems without requiring a separate vector database.

Table Partitioning

Partitioning splits large tables into smaller, more manageable pieces while maintaining a single logical table. PostgreSQL supports declarative partitioning by range, list, and hash.

partitioning.sql

SQL

1	-- Range partitioning by date (most common for time-series)
2	CREATE TABLE events (
3	id UUID DEFAULT gen_random_uuid(),
4	user_id UUID NOT NULL,
5	event_type VARCHAR(50) NOT NULL,
6	data JSONB DEFAULT '{}',
7	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8	) PARTITION BY RANGE (created_at);
9
10	-- Create monthly partitions
11	CREATE TABLE events_2025_01 PARTITION OF events
12	FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
13	CREATE TABLE events_2025_02 PARTITION OF events
14	FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
15	CREATE TABLE events_2025_03 PARTITION OF events
16	FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
17
18	-- Default partition (catches rows that don't match any partition)
19	CREATE TABLE events_default PARTITION OF events DEFAULT;
20
21	-- List partitioning (by discrete values)
22	CREATE TABLE orders (
23	id UUID DEFAULT gen_random_uuid(),
24	user_id UUID NOT NULL,
25	status VARCHAR(20) NOT NULL,
26	total DECIMAL(10, 2)
27	) PARTITION BY LIST (status);
28
29	CREATE TABLE orders_pending PARTITION OF orders FOR VALUES IN ('pending', 'processing');
30	CREATE TABLE orders_completed PARTITION OF orders FOR VALUES IN ('completed', 'delivered');
31	CREATE TABLE orders_cancelled PARTITION OF orders FOR VALUES IN ('cancelled', 'refunded');
32
33	-- Hash partitioning (for even distribution)
34	CREATE TABLE sessions (
35	id UUID DEFAULT gen_random_uuid(),
36	user_id UUID NOT NULL,
37	data JSONB
38	) PARTITION BY HASH (id);
39
40	CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
41	CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
42	CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
43	CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

ℹ

info

Use the pg_partman extension to automate partition creation and maintenance. It can create future partitions on a schedule and drop old ones based on retention policies.

Row-Level Security

Row-Level Security (RLS) restricts which rows a user can see or modify based on policies defined in SQL. It enforces access control at the database level, providing an additional security layer beyond application code.

row-level-security.sql

SQL

1	-- Enable RLS on a table
2	ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
3
4	-- Policy: users can only see their own documents
5	CREATE POLICY user_documents_select ON documents
6	FOR SELECT
7	USING (user_id = current_setting('app.current_user_id')::uuid);
8
9	-- Policy: users can only insert their own documents
10	CREATE POLICY user_documents_insert ON documents
11	FOR INSERT
12	WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);
13
14	-- Policy: users can only update their own documents
15	CREATE POLICY user_documents_update ON documents
16	FOR UPDATE
17	USING (user_id = current_setting('app.current_user_id')::uuid)
18	WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);
19
20	-- Policy: users can only delete their own documents
21	CREATE POLICY user_documents_delete ON documents
22	FOR DELETE
23	USING (user_id = current_setting('app.current_user_id')::uuid);
24
25	-- Admin bypass policy
26	CREATE POLICY admin_all_documents ON documents
27	FOR ALL
28	USING (current_setting('app.current_user_role') = 'admin');
29
30	-- Set session variables (in application code)
31	-- SET app.current_user_id = 'user-uuid-here';
32	-- SET app.current_user_role = 'admin';

Performance Tuning

PostgreSQL performance tuning involves configuration, query optimization, indexing strategy, and monitoring. Here are the key areas to focus on.

performance.sql

SQL

1	-- Analyze query execution plans
2	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
3	SELECT u.name, COUNT(o.id) AS order_count
4	FROM users u
5	JOIN orders o ON o.user_id = u.id
6	WHERE o.created_at >= '2025-01-01'
7	GROUP BY u.id, u.name;
8
9	-- Key EXPLAIN indicators:
10	-- Seq Scan → missing index (bad for large tables)
11	-- Index Scan → using index (good)
12	-- Hash Join → efficient for large joins
13	-- Nested Loop → efficient for small result sets
14	-- Sort → check if sort_key matches index
15
16	-- Vacuum and analyze (essential for performance)
17	VACUUM (VERBOSE, ANALYZE) users;
18
19	-- Check table bloat
20	SELECT
21	schemaname,
22	tablename,
23	pg_size_pretty(pg_total_relation_size(schemaname \|\| '.' \|\| tablename)) AS total_size,
24	pg_size_pretty(pg_relation_size(schemaname \|\| '.' \|\| tablename)) AS table_size,
25	pg_size_pretty(
26	pg_total_relation_size(schemaname \|\| '.' \|\| tablename)
27	- pg_relation_size(schemaname \|\| '.' \|\| tablename)
28	) AS index_size
29	FROM pg_tables
30	WHERE schemaname = 'public'
31	ORDER BY pg_total_relation_size(schemaname \|\| '.' \|\| tablename) DESC;
32
33	-- Monitor slow queries
34	SELECT
35	substring(query, 1, 100) AS short_query,
36	calls,
37	ROUND(total_exec_time::numeric, 2) AS total_ms,
38	ROUND(mean_exec_time::numeric, 2) AS avg_ms,
39	rows
40	FROM pg_stat_statements
41	ORDER BY mean_exec_time DESC
42	LIMIT 20;

✓

best practice

Run ANALYZE after bulk data changes. PostgreSQL uses a query planner that relies on table statistics to choose optimal execution plans. Stale statistics lead to suboptimal plans.

Logical Replication

Logical replication replicates data at the transaction level, allowing you to replicate specific tables, filter rows, or transform data. It is essential for zero-downtime migrations and data distribution.

logical-replication.sql

SQL

1	-- Publisher (source database)
2	ALTER SYSTEM SET wal_level = logical;
3	SELECT pg_reload_conf();
4
5	CREATE PUBLICATION my_publication FOR TABLE users, orders;
6	-- Or replicate all tables:
7	-- CREATE PUBLICATION my_publication FOR ALL TABLES;
8
9	-- Subscriber (target database)
10	CREATE SUBSCRIPTION my_subscription
11	CONNECTION 'postgresql://user:pass@publisher-host:5432/dbname'
12	PUBLICATION my_publication;
13
14	-- Check replication status
15	SELECT * FROM pg_stat_subscription;
16	SELECT * FROM pg_replication_slots;
17
18	-- Refresh a logical replication subscriber after schema changes
19	ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor