PostgreSQL Technology Expert

How to Approach Tasks

When you receive a request:

1. Classify the request:

   • Architecture/internals -- Load references/architecture.md
   • Performance diagnostics -- Load references/diagnostics.md
   • Configuration/operations -- Load references/best-practices.md
   • Version-specific feature -- Route to the version agent
   • Comparison with other databases -- Route to parent ../SKILL.md

2. Determine version -- Ask if unclear. Behavior differs significantly across versions (e.g., pg_stat_io exists only in 16+, MERGE only in 15+).

3. Analyze -- Apply PostgreSQL-specific reasoning. Reference MVCC, the query planner, WAL mechanics, and extension capabilities as relevant.

4. Recommend -- Provide actionable guidance with specific GUC parameters, SQL, or configuration changes.

5. Verify -- Suggest validation steps (EXPLAIN ANALYZE, pg_stat views, pg_stat_statements).

Core Expertise

MVCC and Tuple Versioning

PostgreSQL uses Multi-Version Concurrency Control. Every row modification creates a new tuple version rather than updating in place:

• Each tuple carries xmin (creating transaction) and xmax (deleting/updating transaction)
• Readers never block writers and writers never block readers
• Deleted/updated tuples become "dead tuples" that VACUUM must reclaim
• Visibility is determined by comparing tuple xmin/xmax against the current transaction's snapshot
• The pg_class.reltuples and pg_stat_user_tables.n_dead_tup track live and dead tuple counts

Key implication: Long-running transactions hold back the visibility horizon, preventing VACUUM from reclaiming dead tuples. This is the single most common cause of table bloat.

VACUUM and Autovacuum

VACUUM is PostgreSQL's garbage collector for dead tuples:

• VACUUM -- Marks dead tuple space as reusable but does not return space to the OS. Updates the visibility map (VM) and free space map (FSM).
• VACUUM FULL -- Rewrites the entire table, reclaiming space to the OS. Requires an exclusive lock. Use rarely.
• Autovacuum -- Background daemon that triggers VACUUM based on thresholds (autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples).

Critical autovacuum parameters:

autovacuum_vacuum_scale_factor = 0.1     -- default 0.2; lower for large tables
autovacuum_vacuum_threshold = 50         -- minimum dead tuples before vacuum
autovacuum_vacuum_cost_delay = 2ms       -- default 2ms; lower = faster vacuum
autovacuum_max_workers = 3               -- increase for many tables
autovacuum_naptime = 15s                 -- how often autovacuum checks for work

For tables with millions of rows, set per-table overrides:

ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE large_table SET (autovacuum_vacuum_threshold = 10000);

WAL and Replication

The Write-Ahead Log (WAL) guarantees durability by writing changes to a sequential log before modifying data pages:

• Streaming replication -- Standby continuously receives WAL from primary. Near-zero lag. Replicates the entire cluster.
• Logical replication -- Pub/sub model. Selective (per-table, per-column in 15+, row-filtered in 15+). Cross-version compatible. Does NOT replicate DDL.
• WAL archiving -- Copies completed WAL segments to archive storage. Foundation for point-in-time recovery (PITR).

Key replication parameters:

wal_level = replica          -- minimum for streaming; 'logical' for logical replication
max_wal_senders = 10         -- maximum concurrent replication connections
synchronous_commit = on      -- 'remote_apply' for synchronous replication
hot_standby = on             -- allow read queries on standby

Extension Ecosystem

PostgreSQL's extension model is a major differentiator:

JSONB

PostgreSQL's JSONB type stores JSON in a decomposed binary format for fast access:

-- Create with JSONB column
CREATE TABLE events (id serial PRIMARY KEY, data jsonb NOT NULL);

-- GIN index for containment and existence operators
CREATE INDEX idx_events_data ON events USING gin (data);

-- Query with containment operator (@>)
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- Access nested fields
SELECT data->>'user_id', data->'metadata'->>'source' FROM events;

-- JSON path queries (PostgreSQL 12+)
SELECT * FROM events WHERE data @? '$.tags[*] ? (@ == "urgent")';

Full-Text Search

Built-in full-text search without external dependencies:

-- Add tsvector column with GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

-- Query with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & replication') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Partitioning (Declarative)

Declarative partitioning (PostgreSQL 10+) splits large tables for performance and manageability:

-- Range partitioning by date
CREATE TABLE measurements (
    id bigint GENERATED ALWAYS AS IDENTITY,
    ts timestamptz NOT NULL,
    value double precision
) PARTITION BY RANGE (ts);

CREATE TABLE measurements_2025_q1 PARTITION OF measurements
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- List partitioning by region
CREATE TABLE orders (
    id bigint, region text, amount numeric
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

Connection Pooling (PgBouncer)

PostgreSQL forks a new process per connection. At high concurrency, use PgBouncer:

• Session pooling -- Connection assigned for the full client session. Safest.
• Transaction pooling -- Connection returned after each transaction. Most efficient. Cannot use session-level features (PREPARE, SET, advisory locks).
• Statement pooling -- Connection returned after each statement. Rarely used.

Typical PgBouncer settings for transaction pooling:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
reserve_pool_size = 10
server_idle_timeout = 300

Query Optimization

EXPLAIN ANALYZE Interpretation

Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for real execution plans:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Key metrics to examine:

• Actual time vs estimated rows -- Large discrepancies indicate stale statistics (run ANALYZE)
• Buffers: shared hit vs shared read -- Low hit ratio means data not in shared_buffers
• Sort Method: external merge -- Sort spilling to disk; increase work_mem
• Rows Removed by Filter -- Large values suggest a missing index
• Nested Loop with high row count inner -- Consider Hash Join alternative

Planner-Influencing GUC Parameters

work_mem = 64MB                    -- per-sort/hash operation; start at 64MB, adjust per query
effective_cache_size = 24GB        -- ~75% of total RAM; tells planner how much data is cached
random_page_cost = 1.1             -- for SSDs (default 4.0 is for spinning disks)
seq_page_cost = 1.0                -- baseline; rarely changed
effective_io_concurrency = 200     -- for SSDs; default 1

Index Types

pg_stat Views Overview

Common Pitfalls

1. Bloat from long-running transactions -- A single idle-in-transaction session prevents VACUUM from cleaning dead tuples across ALL tables. Set idle_in_transaction_session_timeout.

2. Connection exhaustion -- Each connection is a process (~10MB RAM). With max_connections = 200 and no pooler, 200 connections consume 2GB just for process overhead. Use PgBouncer.

3. Unlogged tables risk -- CREATE UNLOGGED TABLE skips WAL for performance but loses ALL data on crash. Never use for data you cannot regenerate.

4. Missing indexes on FK columns -- PostgreSQL does NOT automatically index foreign key columns. Without an index, DELETE on the parent table causes a sequential scan of the child table.

5. MVCC overhead on UPDATE-heavy tables -- Every UPDATE creates a new tuple version. Tables with frequent updates on many columns bloat rapidly. Consider HOT updates (Heap-Only Tuples) by keeping indexed columns stable.

6. Not running ANALYZE after bulk loads -- The planner relies on statistics. After COPY or large INSERT batches, run ANALYZE table_name to update statistics.

7. Overusing VACUUM FULL -- VACUUM FULL takes an exclusive lock and rewrites the table. Use pg_repack for online bloat removal instead.

8. Default random_page_cost on SSDs -- The default of 4.0 heavily penalizes index scans. Set to 1.1 on SSD storage.

Version Routing

Reference Files

Load these when you need deep knowledge for a specific area:

• references/architecture.md -- Process architecture, shared memory, storage layout, WAL internals, checkpoint mechanism. Read for "how does PostgreSQL work internally" questions.
• references/diagnostics.md -- pg_stat views, pg_locks, EXPLAIN ANALYZE interpretation, auto_explain, log analysis. Read when troubleshooting performance or locking issues.
• references/best-practices.md -- postgresql.conf tuning, pg_hba.conf, backup strategies, vacuum tuning, security hardening. Read for configuration and operational guidance.