Postgresql

---
name: postgresql
description: "PostgreSQL technology expert covering ALL versions. Deep expertise in MVCC, VACUUM, WAL, replication, extensions, query optimization, and operational tuning. WHEN: \"PostgreSQL\", \"Postgres\", \"psql\", \"pg_stat\", \"VACUUM\", \"MVCC\", \"WAL\", \"pgAdmin\", \"pg_dump\", \"autovacuum\", \"PgBouncer\", \"PostGIS\", \"pgvector\", \"pg_cron\", \"JSONB\", \"EXPLAIN ANALYZE\", \"shared_buffers\", \"work_mem\", \"streaming replication\", \"logical replication\", \"TOAST\", \"pg_basebackup\"."
license: MIT
---

# PostgreSQL

This skill covers PostgreSQL across all supported versions (14 through 18). It covers PostgreSQL internals, query optimization, operational tuning, and the extension ecosystem. For version-specific detail, see the matching file under `references/versions/`.

## When to Use This Skill vs. Version-Specific Guidance

**Use this agent when the question spans versions or is version-agnostic:**
- "How does MVCC work in PostgreSQL?"
- "Tune autovacuum for a write-heavy workload"
- "Set up streaming replication"
- "Compare GIN vs GiST indexes"
- "Best practices for postgresql.conf tuning"

**See the matching version reference when the question is version-specific:**
- "PostgreSQL 18 virtual generated columns" --> `references/versions/18.md`
- "PostgreSQL 17 incremental backup" --> `references/versions/17.md`
- "PostgreSQL 16 logical replication from standby" --> `references/versions/16.md`
- "PostgreSQL 15 MERGE command" --> `references/versions/15.md`
- "PostgreSQL 14 multirange types" --> `references/versions/14.md`

## 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** -- See the matching `references/versions/<v>.md` file
   - **Comparison with other databases** -- see the `overview` skill

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:
```sql
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:

| Extension | Purpose | Key Use Case |
|---|---|---|
| **PostGIS** | Geospatial data types and functions | Location queries, GIS applications |
| **pgvector** | Vector similarity search | AI/ML embeddings, semantic search |
| **pg_stat_statements** | Query performance statistics | Identifying slow queries |
| **pg_cron** | Job scheduling inside PostgreSQL | Periodic maintenance, ETL |
| **pg_trgm** | Trigram-based text similarity | Fuzzy text search, LIKE optimization |
| **hstore** | Key-value pairs in a column | Simple key-value storage |
| **uuid-ossp** / **pgcrypto** | UUID generation | Primary key generation |
| **pg_partman** | Automated partition management | Time-series partitioning |
| **pgBackRest** | Advanced backup/restore | Enterprise backup strategy |
| **pg_repack** | Online table repack (no locks) | Bloat removal without VACUUM FULL |

### JSONB

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

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:
```ini
[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:

```sql
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

| Index Type | Best For | Limitations |
|---|---|---|
| **B-tree** | Equality, range, sorting, LIKE 'prefix%' | Default; most versatile |
| **Hash** | Equality only | No range scans; WAL-logged since PG 10 |
| **GIN** | JSONB containment, arrays, full-text search | Slow to build/update; fast to query |
| **GiST** | Geospatial (PostGIS), range types, nearest-neighbor | Lossy for some data types |
| **BRIN** | Large tables with natural ordering (timestamps) | Very small index; works only on correlated data |
| **SP-GiST** | Non-balanced data structures (quad-trees, k-d trees) | Specialized use cases |

## pg_stat Views Overview

| View | What It Shows | When to Check |
|---|---|---|
| `pg_stat_activity` | Current sessions, queries, wait events | Blocking, long queries, connection count |
| `pg_stat_user_tables` | Table-level seq scans, index scans, dead tuples, vacuum times | Missing indexes, vacuum health |
| `pg_stat_user_indexes` | Index usage (scans, tuples read/fetched) | Unused indexes (candidates for removal) |
| `pg_stat_bgwriter` | Checkpoint and background writer statistics | Checkpoint frequency tuning |
| `pg_stat_io` (16+) | I/O statistics by backend type and object | I/O bottleneck analysis |
| `pg_stat_wal` (14+) | WAL generation statistics | WAL volume analysis |
| `pg_stat_statements` | Query-level statistics (calls, time, rows) | Top-N slow queries, query patterns |

## 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-specific guidance

| Version | Status | Key Feature | Reference |
|---|---|---|---|
| **PostgreSQL 18** | Current (Sep 2025) | Async I/O, UUIDv7, virtual generated columns, OAuth | `references/versions/18.md` |
| **PostgreSQL 17** | Supported | Incremental backup, JSON_TABLE, MERGE RETURNING | `references/versions/17.md` |
| **PostgreSQL 16** | Supported | Logical replication from standby, SQL/JSON constructors | `references/versions/16.md` |
| **PostgreSQL 15** | Supported | MERGE command, PUBLIC schema changes, pg_stat_io | `references/versions/15.md` |
| **PostgreSQL 14** | Supported (EOL Nov 2026) | Multirange types, pg_stat_wal | `references/versions/14.md` |

## 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.

## Diagnostic Scripts

Ready-made SQL in `scripts/` (version-agnostic, PostgreSQL 14+), numbered by investigation order. All read-only; run as a role with pg_read_all_stats.

- `scripts/01-activity-and-blocking.sql` -- Current activity with blocking chains (pg_blocking_pids)
- `scripts/02-slow-queries.sql` -- Top statements from pg_stat_statements with cache hit ratios
- `scripts/03-bloat-and-vacuum.sql` -- Dead-tuple pressure and vacuum recency per table
- `scripts/04-index-usage.sql` -- Never-scanned indexes plus seq-scan-heavy tables
- `scripts/05-connections-and-cache.sql` -- Connection saturation by state, per-database cache/temp/deadlock vitals