PostgreSQL
The world's most advanced open-source relational database - ACID, MVCC, JSONB, and pgvector for AI applications, all in one engine your team already knows how to operate
---
name: postgresql
description: >
You are a PostgreSQL developer. You design normalized schemas, select correct index types, read EXPLAIN ANALYZE output, tune autovacuum, and use pgvector for AI similarity search.
---PostgreSQL is the default relational database - ACID, MVCC, JSONB, rich index types, and pgvector for vector similarity search in one engine. Choose it over MySQL for complex queries, JSONB, and full-text search; choose it over DynamoDB when your access patterns require JOINs, aggregations, or ad-hoc queries. The most expensive PostgreSQL mistakes are not using connection pooling, skipping EXPLAIN before adding indexes, and leaving autovacuum undertuned on high-write tables.
Workflow
- Run EXPLAIN (ANALYZE, BUFFERS) on every query that takes more than 10ms before adding an indexEXPLAIN shows which indexes are used or missed, actual vs estimated row counts, and buffer hits vs disk reads - adding indexes without reading EXPLAIN leads to duplicate indexes, wrong index types, and queries that still do sequential scans
- Use TIMESTAMPTZ (timestamp with time zone) for every timestamp columnTIMESTAMP without timezone stores wall-clock time and produces wrong comparisons across DST transitions or when the database server timezone changes; TIMESTAMPTZ stores in UTC and converts correctly on every read
- Set up PgBouncer or Supabase connection pooling before the first load testPostgreSQL allocates ~5 MB of memory per connection; 200 unpoooled connections from a Node.js app in production consumes 1 GB just for connection overhead before a single query runs
- Tune autovacuum thresholds for tables with high UPDATE or DELETE volumeMVCC stores old row versions; without frequent vacuuming, dead tuples accumulate, bloat the table, slow sequential scans, and eventually cause transaction ID wraparound - a database-halting event
- Design composite indexes with the most selective column first and match column order to query WHERE clausesAn index on (user_id, created_at) supports WHERE user_id = $1 ORDER BY created_at but not WHERE created_at > $1 alone - wrong column order produces a sequential scan despite the index existing
- Use the expand-contract pattern for schema migrations that would lock the tableALTER TABLE ADD COLUMN NOT NULL DEFAULT on a large table takes an exclusive lock that blocks all reads and writes for minutes; expand-contract adds the column nullable, backfills in batches, adds the constraint, then drops the old column - zero downtime
Output Format
A production query analysis has three parts: the EXPLAIN ANALYZE output (actual rows, loops, buffers), the diagnosis (sequential scan, wrong index, bad estimate), and the fix (index type, statistics update, query rewrite). Document each slow query in this format.
-- Slow query
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
-- EXPLAIN: Seq Scan on orders (cost=0.00..8432.00 rows=12 actual rows=8 loops=1)
-- Diagnosis: sequential scan; status has low cardinality but created_at filter is selective
-- Fix: partial index on pending orders only
CREATE INDEX CONCURRENTLY idx_orders_pending_created
ON orders (created_at) WHERE status = 'pending';Examples
pgvector semantic search with SQL filter
Find the 5 most similar product descriptions to a query embedding, restricted to in-stock items
SELECT id, name, embedding <=> $1 AS distance FROM products WHERE in_stock = true ORDER BY embedding <=> $1 LIMIT 5; - add an IVFFlat or HNSW index on the embedding column for datasets over 100k rows
Wrong: index on a low-cardinality boolean column
CREATE INDEX idx_users_active ON users (is_active);
PostgreSQL ignores this index on large tables because scanning half the table for is_active=true is faster than an index scan - the query planner estimates the index cost correctly and chooses a sequential scan
Right: partial index on the minority case
Most users are active; only 2% are suspended. Queries filter on is_suspended=true.
CREATE INDEX idx_users_suspended ON users (id) WHERE is_suspended = true; - indexes only the 2% of rows that match, making the index small and fast; the planner uses it because the selectivity is high
Edge Cases & Gotchas
- Long-running transaction blocking VACUUM - a forgotten transaction holds a snapshot that prevents MVCC cleanup; dead tuples accumulate until the transaction ends. Fix: set statement_timeout and idle_in_transaction_session_timeout in postgresql.conf to kill abandoned transactions
- N+1 queries from an ORM - fetching 100 users then fetching each user's orders in a loop produces 101 queries. Fix: use an ORM eager load (Prisma include, Sequelize include) or write a JOIN query explicitly; check with pg_stat_statements or query logs
- Index bloat after bulk DELETE - deleting 50% of a table's rows does not reclaim index space automatically. Fix: run REINDEX CONCURRENTLY after large deletes to rebuild the index without locking; schedule regular VACUUM on high-churn tables
- Sequence exhaustion on INTEGER primary key - a 32-bit SERIAL column wraps at 2.1 billion; a table receiving 1000 inserts/second exhausts it in 24 days. Fix: use BIGSERIAL (64-bit) or UUID for all primary keys at schema design time
When to Break These Rules
- Use Redis for session storage, distributed locking, rate limiting, and pub/sub - PostgreSQL LISTEN/NOTIFY works but adds connection overhead and has limited throughput at scale
- Use DynamoDB or Firestore when your access patterns are strictly key-value lookups at sub-millisecond latency globally and you have no need for JOINs, aggregations, or ad-hoc queries
Reference Files
src/data/skills/index.js- add skill entries heresrc/components/Skills/DocRenderer.js- renders this doc format