postgresql-database-engineering

SKILL.md

PostgreSQL Database Engineering

A comprehensive skill for professional PostgreSQL database engineering, covering everything from query optimization and indexing strategies to high availability, replication, and production database management. This skill enables you to design, optimize, and maintain high-performance PostgreSQL databases at scale.

When to Use This Skill

Use this skill when:

Designing database schemas for high-performance applications

Optimizing slow queries and improving database performance

Implementing indexing strategies for complex query patterns

Setting up partitioning for large tables (100M+ rows)

Configuring streaming replication and high availability

Tuning PostgreSQL configuration for production workloads

Implementing backup and recovery procedures

Debugging performance issues and query bottlenecks

Setting up connection pooling with pgBouncer or PgPool

Monitoring database health and performance metrics

Planning database migrations and schema changes

Implementing database security and access controls

Scaling PostgreSQL databases horizontally or vertically

Managing VACUUM operations and database maintenance

Setting up logical replication for data distribution

Core Concepts

PostgreSQL Architecture

PostgreSQL uses a process-based architecture with several key components:

Postmaster Process: Main server process that manages connections

Backend Processes: One per client connection, handles queries

Shared Memory: Shared buffers, WAL buffers, lock tables

Background Workers: Autovacuum, checkpointer, WAL writer, statistics collector

Write-Ahead Log (WAL): Transaction log for durability and replication

Storage Layer: TOAST for large values, FSM for free space, VM for visibility

MVCC (Multi-Version Concurrency Control)

PostgreSQL's foundational concurrency mechanism:

Snapshots: Each transaction sees a consistent snapshot of data

Tuple Versions: Multiple row versions coexist for concurrent access

Transaction IDs: xmin (creating transaction), xmax (deleting transaction)

Visibility Rules: Determines which row versions are visible to transactions

VACUUM: Reclaims space from dead tuples and prevents transaction wraparound

FREEZE: Marks old rows as visible to all transactions

Key Implications:

No read locks - readers never block writers

Writers never block readers

Updates create new row versions

Regular VACUUM is essential

Dead tuples accumulate until vacuumed

Transaction Isolation Levels

PostgreSQL supports four isolation levels:

Read Uncommitted: Treated as Read Committed in PostgreSQL

Read Committed (default): Sees committed data at statement start

Repeatable Read: Sees snapshot from transaction start

Serializable: True serializable isolation with SSI

Choosing Isolation:

Read Committed: Most applications, best performance

Repeatable Read: Reports, analytics needing consistency

Serializable: Financial transactions, critical consistency needs

Index Types

PostgreSQL offers multiple index types for different use cases:

1. B-Tree (Default)

Use for: Equality, range queries, sorting

Supports: <, <=, =, >=, >, BETWEEN, IN, IS NULL

Best for: Most general-purpose indexing

Example: Primary keys, foreign keys, timestamps

2. Hash

Use for: Equality comparisons only

Supports: = operator

Best for: Large tables with equality lookups

Limitation: Not WAL-logged before PG 10, no range queries

3. GiST (Generalized Search Tree)

Use for: Geometric data, full-text search, custom types

Supports: Overlaps, contains, nearest neighbor

Best for: Spatial data, ranges, full-text search

Example: PostGIS geometries, tsvector, ranges

4. GIN (Generalized Inverted Index)

Use for: Multi-valued columns (arrays, JSONB, full-text)

Supports: Contains, exists operators

Best for: JSONB queries, array operations, full-text search

Tradeoff: Slower updates, faster queries

5. BRIN (Block Range Index)

Use for: Very large tables with natural ordering

Supports: Range queries on sorted data

Best for: Time-series data, append-only tables

Advantage: Tiny index size, scales to billions of rows

6. SP-GiST (Space-Partitioned GiST)

Use for: Non-balanced data structures

Supports: Points, ranges, IP addresses

Best for: Quadtrees, k-d trees, radix trees

Query Planning and Optimization

PostgreSQL's query planner determines execution strategies:

Planner Components:

Statistics: Table and column statistics for cardinality estimation

Cost Model: CPU, I/O, and memory cost estimation

Plan Types: Sequential scan, index scan, bitmap scan, joins

Join Methods: Nested loop, hash join, merge join

Optimization: Query rewriting, predicate pushdown, join reordering

Key Statistics:

n_distinct: Number of distinct values (for selectivity)

correlation: Physical row ordering correlation

most_common_vals: MCV list for skewed distributions

histogram_bounds: Value distribution histogram

Understanding EXPLAIN:

Cost: Startup cost .. total cost (arbitrary units)

Rows: Estimated row count

Width: Average row size in bytes

Actual Time: Real execution time (with ANALYZE)

Loops: Number of times node executed

Partitioning Strategies

Table partitioning for managing large datasets:

Range Partitioning

Use for: Time-series data, sequential values

Example: Partition by date ranges (daily, monthly, yearly)

Benefit: Easy data lifecycle management, faster queries

List Partitioning

Use for: Discrete categorical values

Example: Partition by country, region, status

Benefit: Logical data separation, partition pruning

Hash Partitioning

Use for: Even data distribution

Example: Partition by hash(user_id)

Benefit: Balanced partition sizes, parallel queries

Partition Pruning:

Planner eliminates irrelevant partitions

Drastically reduces query scope

Essential for partition performance

Partition-Wise Operations:

Partition-wise joins: Join matching partitions directly

Partition-wise aggregation: Aggregate within partitions

Parallel partition processing

Replication and High Availability

PostgreSQL replication options:

Streaming Replication (Physical)

Type: Binary WAL streaming to standby servers

Modes: Asynchronous, synchronous, quorum-based

Use for: High availability, read scalability

Failover: Automatic with tools like Patroni, repmgr

Synchronous vs Asynchronous:

Synchronous: Zero data loss, higher latency

Asynchronous: Low latency, potential data loss

Quorum: Balance between safety and performance

Logical Replication

Type: Row-level change stream

Use for: Selective replication, upgrades, multi-master

Benefit: Replicate specific tables, cross-version

Limitation: No DDL replication, overhead

Cascading Replication

Standbys replicate from other standbys

Reduces load on primary

Geographic distribution

Connection Pooling

Managing database connections efficiently:

pgBouncer

Type: Lightweight connection pooler

Modes: Session, transaction, statement pooling

Use for: High connection count applications

Benefit: Reduced connection overhead, resource limits

Pooling Modes:

Session: Client connects for entire session

Transaction: Connection per transaction

Statement: Connection per statement (rarely used)

PgPool-II

Type: Feature-rich middleware

Features: Connection pooling, load balancing, query caching

Use for: Read/write splitting, connection management

Benefit: Advanced routing, in-memory cache

VACUUM and Maintenance

Critical maintenance operations:

VACUUM

Purpose: Reclaim dead tuple space, update statistics

Types: Regular VACUUM, VACUUM FULL

When: After large updates/deletes, regularly via autovacuum

Impact: Regular VACUUM is non-blocking

ANALYZE

Purpose: Update planner statistics

When: After data changes, schema modifications

Impact: Minimal, fast on most tables

REINDEX

Purpose: Rebuild indexes, fix bloat

When: Index corruption, significant bloat

Impact: Locks table, use REINDEX CONCURRENTLY (PG 12+)

Autovacuum

Purpose: Automated VACUUM and ANALYZE

Configuration: Threshold-based triggering

Tuning: Balance resource usage vs. responsiveness

Monitoring: Track autovacuum runs, prevent wraparound

Performance Tuning

Key configuration parameters:

Memory Settings

shared_buffers: 25% of RAM (start point)
effective_cache_size: 50-75% of RAM
work_mem: Per-operation memory (sort, hash)
maintenance_work_mem: VACUUM, CREATE INDEX memory

Checkpoint and WAL

checkpoint_timeout: How often to checkpoint
max_wal_size: WAL size before checkpoint
checkpoint_completion_target: Spread checkpoint I/O
wal_buffers: WAL write buffer size

Query Planner

random_page_cost: Relative cost of random I/O
effective_io_concurrency: Concurrent I/O operations
default_statistics_target: Histogram detail level

Connection Settings

max_connections: Maximum client connections
connection_limit: Per-database/user limits

Index Strategies

Choosing the Right Index

Decision Matrix:

Query Pattern
Index Type
Reason

WHERE id = 5
B-tree
Equality lookup

WHERE created_at > '2024-01-01'
B-tree
Range query

ORDER BY name
B-tree
Sorting support

WHERE tags @> ARRAY['sql']
GIN
Array containment

WHERE data->>'status' = 'active'
GIN (jsonb_path_ops)
JSONB query

WHERE to_tsvector(content) @@ query
GIN
Full-text search

WHERE location <-> point(0,0)
GiST
Nearest neighbor

WHERE timestamp BETWEEN ... (large table)
BRIN
Sequential time-series

WHERE ip_address << '192.168.0.0/16'
GiST or SP-GiST
IP range query

Composite Indexes

Multi-column indexes for complex queries:

Column Ordering Rules:

Equality columns first

Sort/range columns last

High-selectivity columns first

Match query patterns exactly

Example:

-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at
-- Optimal index: (status, created_at)
CREATE INDEX idx_users_status_created ON users(status, created_at);

Partial Indexes

Index subset of rows:

Benefits:

Smaller index size

Faster updates on non-indexed rows

Targeted query optimization

Use Cases:

Index only active records: WHERE deleted_at IS NULL

Index recent data: WHERE created_at > NOW() - INTERVAL '90 days'

Index specific states: WHERE status IN ('pending', 'processing')

Expression Indexes

Index computed values:

Examples:

-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Date truncation
CREATE INDEX idx_events_date ON events(DATE(created_at));

-- JSONB field
CREATE INDEX idx_data_status ON documents((data->>'status'));

Covering Indexes (INCLUDE)

Include non-key columns for index-only scans:

CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE (first_name, last_name, created_at);

Benefit: Query satisfied entirely from index, no table lookup

Index Maintenance

Monitoring Index Usage:

-- Unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Detecting Bloat:

-- Index bloat estimation
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Query Optimization

Using EXPLAIN ANALYZE

Understanding query execution:

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- EXPLAIN ANALYZE (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';

-- Detailed output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

Key Metrics:

Planning Time: Time to generate plan

Execution Time: Actual query runtime

Shared Hit vs Read: Buffer cache hits vs disk reads

Rows: Estimated vs actual row counts

Filter vs Index Cond: Post-scan filtering vs index usage

Common Query Anti-Patterns

1. N+1 Queries

Problem: One query per row in a loop
Solution: JOIN or batch queries

2. SELECT *

Problem: Fetches unnecessary columns
Solution: Select only needed columns

3. Implicit Type Conversions

Problem: Index not used due to type mismatch
Solution: Ensure query types match column types

4. Function on Indexed Column

Problem: WHERE UPPER(email) = 'USER@EXAMPLE.COM'
Solution: Use expression index or compare correctly

5. OR Conditions

Problem: WHERE status = 'A' OR status = 'B'
Solution: Use IN: WHERE status IN ('A', 'B')

Join Optimization

Join Types:

Nested Loop

Best for: Small outer table, indexed inner table

How: For each outer row, scan inner table

When: Small result sets, good indexes

Hash Join

Best for: Large tables, no good indexes

How: Build hash table of smaller table

When: Equality joins, sufficient memory

Merge Join

Best for: Pre-sorted data, equality joins

How: Sort both inputs, merge scan

When: Both inputs sorted or can be sorted cheaply

Join Order Matters:

Planner reorders joins for optimization

Statistics guide join order decisions

Can force order with SET join_collapse_limit

Aggregation Optimization

Techniques:

Partial Aggregates: Partition-wise aggregation

Hash Aggregates: In-memory grouping

Sorted Aggregates: Pre-sorted input

Parallel Aggregation: Multiple workers

Materialized Views:

Pre-compute expensive aggregations

Refresh on schedule or trigger

Trade freshness for query speed

Query Caching

Levels:

Shared Buffers: PostgreSQL page cache

OS Page Cache: Operating system cache

Application Cache: Redis, Memcached

Prepared Statements: Reuse query plans

Partitioning

Implementing Range Partitioning

Time-series example:

-- Create partitioned table
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Default partition for data outside ranges
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Indexes on partitions
CREATE INDEX idx_events_2024_01_user ON events_2024_01(user_id);
CREATE INDEX idx_events_2024_02_user ON events_2024_02(user_id);

Partition Automation

Automated partition management:

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
    base_table TEXT,
    partition_date DATE
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := base_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := DATE_TRUNC('month', partition_date);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, base_table, start_date, end_date
    );

    -- Create indexes
    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I(user_id)',
        'idx_' || partition_name || '_user', partition_name
    );
END;
$$ LANGUAGE plpgsql;

Partition Maintenance

Dropping old partitions:

-- Detach partition (fast, non-blocking)
ALTER TABLE events DETACH PARTITION events_2023_01;

-- Drop detached partition
DROP TABLE events_2023_01;

-- Or archive before dropping
CREATE TABLE archive.events_2023_01 AS SELECT * FROM events_2023_01;
DROP TABLE events_2023_01;

High Availability and Replication

Setting Up Streaming Replication

Primary server configuration (postgresql.conf):

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
synchronous_commit = on  # or off for async
synchronous_standby_names = 'standby1,standby2'  # for sync replication

Create replication user:

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';

pg_hba.conf on primary:

# Allow replication connections
host replication replicator standby_ip/32 md5

Standby server setup:

# Stop standby PostgreSQL
systemctl stop postgresql

# Remove old data directory
rm -rf /var/lib/postgresql/14/main

# Base backup from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/14/main \
              -U replicator -P -v -R -X stream -C -S standby1

# Start standby
systemctl start postgresql

Standby configuration (created by -R flag):

# standby.signal file created automatically
# postgresql.auto.conf contains:
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
primary_slot_name = 'standby1'

Monitoring Replication

On primary:

-- Check replication status
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

-- Check replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

On standby:

-- Check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Check recovery status
SELECT pg_is_in_recovery();

Failover and Switchover

Promoting standby to primary:

# Trigger failover
pg_ctl promote -D /var/lib/postgresql/14/main

# Or using SQL
SELECT pg_promote();

Controlled switchover:

# 1. Stop writes on primary
# 2. Wait for standby to catch up
# 3. Promote standby
# 4. Reconfigure old primary as new standby

Logical Replication Setup

On publisher (source):

-- Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Or all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

On subscriber (destination):

-- Create subscription
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host dbname=mydb user=replicator password=pass'
    PUBLICATION my_publication;

-- Monitor subscription
SELECT * FROM pg_stat_subscription;

Backup and Recovery

Physical Backups

pg_basebackup:

# Full physical backup
pg_basebackup -h localhost -U postgres -D /backup/base \
              -F tar -z -P -v

# With WAL files for point-in-time recovery
pg_basebackup -h localhost -U postgres -D /backup/base \
              -X stream -F tar -z -P

Continuous archiving (WAL archiving):

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

Logical Backups

pg_dump:

# Single database
pg_dump -h localhost -U postgres -F c -b -v -f mydb.dump mydb

# All databases
pg_dumpall -h localhost -U postgres -f all_databases.sql

# Specific tables
pg_dump -h localhost -U postgres -t users -t orders -F c -f tables.dump mydb

# Schema only
pg_dump -h localhost -U postgres --schema-only -F c -f schema.dump mydb

pg_restore:

# Restore database
pg_restore -h localhost -U postgres -d mydb -v mydb.dump

# Parallel restore
pg_restore -h localhost -U postgres -d mydb -j 4 -v mydb.dump

# Restore specific tables
pg_restore -h localhost -U postgres -d mydb -t users -v mydb.dump

Point-in-Time Recovery (PITR)

Setup:

Take base backup

Configure WAL archiving

Store WAL files safely

Recovery:

# 1. Restore base backup
tar -xzf base.tar.gz -C /var/lib/postgresql/14/main

# 2. Create recovery.signal file
touch /var/lib/postgresql/14/main/recovery.signal

# 3. Configure recovery target (postgresql.conf or postgresql.auto.conf)
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
# Or: recovery_target_name = 'before_disaster'
# Or: recovery_target_lsn = '0/3000000'

# 4. Start PostgreSQL
systemctl start postgresql

Backup Strategies

3-2-1 Rule:

3 copies of data

2 different media types

1 offsite backup

Backup Schedule:

Daily: Incremental WAL archiving

Weekly: Full pg_basebackup

Monthly: Long-term retention

Testing Backups:

Regularly restore to test environment

Verify data integrity

Measure restore time

Performance Monitoring

Key Metrics to Monitor

Database Health:

Active connections

Transaction rate

Cache hit ratio

Deadlocks

Checkpoint frequency

Autovacuum runs

Query Performance:

Slow query log

Query execution time

Lock waits

Sequential scans

System Resources:

CPU utilization

Memory usage

Disk I/O

Network bandwidth

Essential Monitoring Queries

Connection stats:

SELECT count(*) as total_connections,
       count(*) FILTER (WHERE state = 'active') as active,
       count(*) FILTER (WHERE state = 'idle') as idle,
       count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity;

Cache hit ratio:

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit) as heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

Table bloat:

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Long-running queries:

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

Lock monitoring:

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

pg_stat_statements

Installation:

CREATE EXTENSION pg_stat_statements;

Configuration (postgresql.conf):

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Top queries by total time:

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       max_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Top queries by average time:

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Best Practices

Schema Design

Normalization:

Normalize to 3NF for transactional systems

Denormalize selectively for read-heavy workloads

Use foreign keys for data integrity

Consider partitioning for very large tables

Data Types:

Use smallest appropriate data type

BIGINT for large IDs, INTEGER for smaller ranges

NUMERIC for exact decimal values

TIMESTAMP WITH TIME ZONE for timestamps

TEXT over VARCHAR unless length constraint needed

UUID for distributed ID generation

JSONB for semi-structured data

Constraints:

Primary keys on all tables

Foreign keys for referential integrity

CHECK constraints for business rules

NOT NULL where appropriate

UNIQUE constraints for uniqueness

Use constraint names for maintainability

Migration Strategies

Zero-Downtime Migrations:

Add new column

ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

Backfill data (in batches)

UPDATE users SET email_verified = false
WHERE email_verified IS NULL
LIMIT 10000;

Add NOT NULL constraint

ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

Index Creation:

Use CREATE INDEX CONCURRENTLY in production

No table locks, allows reads/writes

Takes longer but doesn't block

Monitor progress with pg_stat_progress_create_index

Large Table Modifications:

Use pg_repack for table rewrites

Partition large tables before modifications

Schedule during maintenance windows

Test on production-like datasets

Security Best Practices

Authentication:

Use strong passwords or certificate authentication

SCRAM-SHA-256 for password encryption

Separate users for different applications

Avoid superuser for application connections

Authorization:

Grant minimal required privileges

Use role-based access control

Revoke PUBLIC access

Row-level security for multi-tenant

Network Security:

Configure pg_hba.conf restrictively

Use SSL/TLS for connections

Firewall database ports

VPN or private networks for replication

Audit Logging:

Enable connection logging

Log DDL statements

Use pgAudit extension for detailed auditing

Monitor for suspicious activity

Maintenance Schedule

Daily:

Monitor slow queries

Check replication lag

Review autovacuum activity

Monitor disk space

Weekly:

Analyze top queries

Review index usage

Check for bloat

Backup verification

Monthly:

Full VACUUM on critical tables

REINDEX bloated indexes

Review configuration parameters

Capacity planning

Quarterly:

Review and optimize indexes

Schema optimization opportunities

Upgrade planning

Performance baseline updates

Advanced Topics

Parallel Query Execution

Configuration:

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB

Forcing parallel execution:

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

When parallelism helps:

Large sequential scans

Large aggregations

Hash joins on large tables

Bitmap heap scans

Custom Functions and Procedures

Stored procedures:

CREATE OR REPLACE PROCEDURE update_user_statistics()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET
        order_count = (SELECT COUNT(*) FROM orders WHERE user_id = users.id),
        last_order_date = (SELECT MAX(created_at) FROM orders WHERE user_id = users.id);

    COMMIT;
END;
$$;

Functions with proper error handling:

CREATE OR REPLACE FUNCTION create_user(
    p_email TEXT,
    p_name TEXT
) RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    INSERT INTO users (email, name)
    VALUES (p_email, p_name)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Email already exists: %', p_email;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error creating user: %', SQLERRM;
END;
$$;

Foreign Data Wrappers

Access external data sources:

-- Install postgres_fdw
CREATE EXTENSION postgres_fdw;

-- Create server
CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_database', port '5432');

-- Create user mapping
CREATE USER MAPPING FOR current_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'remote_password');

-- Import foreign schema
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO local_schema;

-- Query foreign table
SELECT * FROM local_schema.remote_table;

JSON and JSONB Operations

Indexing JSONB:

-- GIN index for containment queries
CREATE INDEX idx_data_gin ON documents USING GIN (data);

-- Expression index for specific field
CREATE INDEX idx_data_status ON documents ((data->>'status'));

-- GIN index with jsonb_path_ops (smaller, faster for @> queries)
CREATE INDEX idx_data_path_ops ON documents USING GIN (data jsonb_path_ops);

Efficient JSONB queries:

-- Containment query (uses GIN index)
SELECT * FROM documents WHERE data @> '{"status": "active"}';

-- Existence query
SELECT * FROM documents WHERE data ? 'email';

-- Path query
SELECT * FROM documents WHERE data->'user'->>'email' = 'user@example.com';

-- Array operations
SELECT * FROM documents WHERE data->'tags' @> '["sql", "postgres"]';

Full-Text Search

Basic setup:

-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Generate search vector
UPDATE articles SET search_vector =
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Trigger for automatic updates
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

Search queries:

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Phrase search
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'database engineering');

-- Search with highlighting
SELECT title,
       ts_headline('english', content, query) AS snippet
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;

Troubleshooting

Common Issues

Problem: Slow Queries

Check EXPLAIN ANALYZE output

Verify indexes exist and are used

Update table statistics: ANALYZE table_name

Check for missing indexes on foreign keys

Look for function calls on indexed columns

Problem: High CPU Usage

Identify expensive queries with pg_stat_statements

Check for missing indexes causing sequential scans

Review parallel query settings

Look for inefficient joins or aggregations

Problem: Connection Exhaustion

Increase max_connections (requires restart)

Implement connection pooling (pgBouncer)

Identify connection leaks in application

Monitor with pg_stat_activity

Problem: Autovacuum Not Keeping Up

Increase autovacuum_max_workers

Adjust autovacuum thresholds

Reduce autovacuum_naptime

Increase autovacuum_work_mem

Check for long-running transactions blocking VACUUM

Problem: Replication Lag

Check network bandwidth between primary and standby

Verify standby hardware resources

Check for long-running queries on standby

Monitor WAL generation rate

Consider increasing wal_sender_timeout

Problem: Transaction ID Wraparound

Monitor age of oldest transaction

Run VACUUM FREEZE on old tables

Check autovacuum_freeze_max_age

Increase autovacuum aggressiveness

Run manual VACUUM FREEZE if necessary

Diagnostic Queries

Find missing indexes on foreign keys:

SELECT c.conrelid::regclass AS table,
       c.confrelid::regclass AS referenced_table,
       string_agg(a.attname, ', ') AS foreign_key_columns
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND c.conkey[1:array_length(c.conkey, 1)]
          OPERATOR(pg_catalog.@>) i.indkey[0:array_length(c.conkey, 1) - 1]
  )
GROUP BY c.conrelid, c.confrelid, c.conname;

Identify blocking queries:

SELECT activity.pid,
       activity.usename,
       activity.query,
       blocking.pid AS blocking_id,
       blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Skill Version: 1.0.0
Last Updated: October 2025
Skill Category: Database Engineering, Performance Optimization, Data Architecture
Compatible With: PostgreSQL 12+, 13, 14, 15, 16
Prerequisites: SQL knowledge, basic database concepts, Linux command line