postgresql-lab

A practical PostgreSQL performance lab with real EXPLAIN ANALYZE outputs, benchmark comparisons, and production-grade query patterns.

Not a tutorial. This is a working benchmark environment. Each experiment was run on PostgreSQL 17 with realistic data volumes (200k products · 500k orders · 1.5M order items). Numbers in comments are actual measurements.

---

Quick Start

# Start PostgreSQL 17 + pgAdmin
docker compose -f docker/docker-compose.yml up -d

# Connect via psql
psql "postgresql://pglab:pglab@localhost:5433/pglab"

# Run any lab experiment
psql "postgresql://pglab:pglab@localhost:5433/pglab" -f 01-indexing/experiments.sql

pgAdmin: http://localhost:5050 (admin@pglab.local / pglab)

---

Labs

#	Topic	Key Finding
01	Indexing Strategies	Covering index (INCLUDE) achieves 0 heap fetches — 3.5x faster than regular index
02	Query Optimization	Correlated subquery 480ms → aggregate JOIN 18ms (26x). Window functions 175x faster than ranking subqueries
03	Bulk Operations	Individual INSERTs 2,400 rows/sec → UNNEST 125k/sec → COPY binary 556k/sec
04	JSONB	jsonb_path_ops GIN is 30% smaller than default GIN; expression index on (metadata ->> 'key') fastest for single-key filters
05	Pagination	OFFSET degrades to 102ms at page 25k; keyset cursor stays at 0.31ms regardless of depth
06	Partitioning	Partition pruning: 500k-row query on partitioned table ignores 11/12 partitions
07	Full-Text Search	ILIKE 48ms → FTS with GIN 3.1ms (15x); pg_trgm enables fuzzy matching with index
08	Window Functions	Running totals, MoM growth, NTILE bucketing, moving averages in a single query pass
09	Concurrency	SKIP LOCKED enables multiple workers to process a queue with zero blocking
10	Monitoring	pg_stat_statements query patterns for daily/weekly/monthly observability

---

Data Model

categories (80 rows — hierarchical, parent_id self-ref)
    └── products (200,000 rows — price, JSONB metadata, tsvector, tags[])
        └── order_items (1,500,000 rows)

customers (50,000 rows — tier: standard/silver/gold/platinum)
    └── orders (500,000 rows — status, shipping_country, timestamps)
        └── order_items (1,500,000 rows)

---

PostgreSQL Configuration

The Docker setup uses production-friendly settings:

shared_preload_libraries = pg_stat_statements  # query tracking
log_min_duration_statement = 100               # log queries > 100ms
auto_explain.log_min_duration = 200            # auto-capture slow query plans
shared_buffers = 256MB                         # ~25% of RAM (adjust for your host)
work_mem = 16MB                                # per-sort/hash memory
random_page_cost = 1.1                         # tuned for SSD
effective_io_concurrency = 200                 # SSD concurrent I/O

---

Key Patterns by Use Case

High-throughput writes (UNNEST bulk insert)

INSERT INTO table (col1, col2, col3)
SELECT * FROM UNNEST($1::bigint[], $2::text[], $3::numeric[]);

→ See 03-bulk-operations/experiments.sql

Infinite scroll pagination (keyset cursor)

SELECT ... FROM orders
WHERE (created_at, id) < ($cursor_created_at, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

→ See 05-pagination/experiments.sql

Queue processing without blocking

WITH next AS (SELECT id FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED)
UPDATE jobs SET status='processing' FROM next WHERE jobs.id = next.id RETURNING *;

→ See 09-concurrency/experiments.sql

Slow query discovery

SELECT LEFT(query,100), mean_exec_time::int AS avg_ms, calls
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

→ See 10-monitoring/queries.sql

---

Related Repositories

Repo	Relationship
dapper-extensions-pg	Implements UNNEST bulk insert and keyset pagination in .NET
dotnet-enterprise-api	Uses this schema as the production database

---

License

MIT © Erickson López