DEV Community: Philip McClarence

PostgreSQL Table Partitioning: Range, List & Hash Strategies

Philip McClarence — Sun, 12 Apr 2026 10:00:02 +0000

PostgreSQL Table Partitioning: Range, List & Hash Strategies

Table partitioning splits a large table into smaller physical pieces (partitions) while presenting them as a single logical table. The query planner uses partition pruning to scan only the relevant partitions, making queries on hundreds-of-millions-row tables dramatically faster.

But done wrong, partitioning makes things slower.

When You Need Partitioning

Tables grow. An events table starts at a million rows and works fine. A year later it has 500 million rows and everything hurts:

Sequential scans that took milliseconds now take minutes
VACUUM processes the entire table and takes hours
Index builds lock the table for extended periods
Deleting old data means expensive DELETE statements generating massive WAL

The fundamental issue: PostgreSQL stores all rows in a single table file. Every maintenance operation -- VACUUM, ANALYZE, REINDEX -- operates on the entire table regardless of how much data you actually need.

Partitioning solves this by storing data in independent physical tables. A query for events in March only scans the March partition. VACUUM processes each partition independently. Dropping an old month is DROP TABLE (milliseconds) instead of a multi-hour DELETE.

Finding Partitioning Candidates

-- Large tables that might benefit from partitioning
SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_live_tup AS estimated_rows,
    n_dead_tup AS dead_tuples,
    CASE WHEN n_live_tup > 0
        THEN round(100.0 * n_dead_tup / n_live_tup, 1)
        ELSE 0
    END AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE pg_relation_size(relid) > 1073741824  -- > 1 GB
ORDER BY pg_total_relation_size(relid) DESC;

Range Partitioning (The Most Common)

Range partitioning is the standard choice for time-series data:

CREATE TABLE events (
    event_id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_timestamp TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    user_id BIGINT,
    payload JSONB,
    PRIMARY KEY (event_id, event_timestamp)  -- must include partition key
) PARTITION BY RANGE (event_timestamp);

-- Monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Default partition for data that doesn't match any range
CREATE TABLE events_default PARTITION OF events DEFAULT;

Important: the partition key must be part of any primary key or unique constraint. PostgreSQL can't enforce uniqueness across partitions, so the partition key must be included.

List Partitioning

For data that falls into discrete categories:

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY,
    region TEXT NOT NULL,
    order_date DATE NOT NULL,
    customer_id BIGINT,
    order_total NUMERIC(12, 2),
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('ap-southeast', 'ap-northeast');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Hash Partitioning

Distributes rows evenly when there's no natural range or list key. Useful for reducing contention on high-insert tables:

CREATE TABLE user_sessions (
    session_id UUID DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    started_at TIMESTAMPTZ DEFAULT now(),
    session_data JSONB,
    PRIMARY KEY (session_id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash partitioning only supports pruning on equality conditions (WHERE user_id = 123), not range queries. Use it for I/O distribution, not query performance.

Verifying Partition Pruning

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE event_timestamp >= '2025-06-01' AND event_timestamp < '2025-07-01';

Look for Append nodes listing which partitions are scanned. If all partitions are scanned for a single-month query, pruning is failing -- usually because the WHERE clause doesn't directly reference the partition key, or uses a type/expression that prevents compile-time pruning.

Migrating an Existing Table

You can't convert a table to partitioned in place. The process:

-- 1. Create the new partitioned table
CREATE TABLE events_partitioned (
    LIKE events INCLUDING ALL
) PARTITION BY RANGE (event_timestamp);

-- 2. Create partitions covering the data range
CREATE TABLE events_p_2025_01 PARTITION OF events_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... for each month

-- 3. Copy data in batches
INSERT INTO events_partitioned
SELECT * FROM events
WHERE event_timestamp >= '2025-01-01' AND event_timestamp < '2025-02-01';
-- ... repeat per partition

-- 4. Swap in a transaction
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

-- 5. Verify and drop old table when confident

For zero-downtime migrations, use a trigger or logical replication to capture writes during the copy phase.

Automating Partition Lifecycle

Never rely on manual creation:

-- pg_partman handles creation and retention automatically
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_timestamp',
    p_interval := '1 month',
    p_premake := 3  -- create 3 future partitions in advance
);

-- Run this daily via cron
SELECT partman.run_maintenance();

Dropping Old Data (The Best Part)

-- Instant removal -- no WAL, no dead tuples, no VACUUM needed
ALTER TABLE events DETACH PARTITION events_2024_01;
DROP TABLE events_2024_01;  -- milliseconds

-- Compare: DELETE FROM events WHERE event_timestamp < '2024-02-01'
-- On 100M rows: ~1 hour, massive WAL, dead tuples, VACUUM required

Partition Count: Keep It Manageable

Partition Interval	5 Years	Planning Impact
Monthly	60	Minimal
Weekly	260	Noticeable
Daily	1,825	Significant

The planner evaluates every partition during query planning. More than a few hundred partitions causes measurable slowdown. If you need daily granularity for retention, consider monthly partitions with daily sub-partitions, or use TimescaleDB (optimized for high partition counts).

The Prevention Checklist

Partition key = your most common WHERE clause column. Verify with EXPLAIN.
Automate creation with pg_partman or cron. Missing partitions cause INSERT failures.
Keep partition count under a few hundred. Monthly > daily for most use cases.
Always have a DEFAULT partition. It catches data outside defined ranges.
Review index usage per partition. Hot partitions may need more indexes than cold historical ones.

PostgreSQL Connection Pooling: PgBouncer, Supavisor & Built-In

Philip McClarence — Sat, 11 Apr 2026 10:00:04 +0000

PostgreSQL Connection Pooling: PgBouncer, Supavisor & Built-In

Every PostgreSQL connection creates a new OS process. Each one allocates roughly 5-10 MB of memory. A server with max_connections = 200 might use 2 GB just for connection overhead -- before a single query runs. At 500 connections, you're at 5 GB of memory consumed by connection infrastructure alone.

If you've ever seen "too many connections" errors and responded by increasing max_connections, you've treated the symptom while creating a bigger problem.

Why More Connections Make Things Worse

Modern architectures multiply connections fast. 20 container replicas with 10 connections each = 200 persistent connections. During a rolling deployment, old and new replicas coexist briefly, doubling the count. Serverless functions are worse -- each invocation might open its own connection, creating hundreds of short-lived connections per second.

Each new connection takes 50-100ms to establish (with TLS). PostgreSQL must fork a process, authenticate, load shared catalog data, and negotiate encryption. That's not free.

There are two walls:

max_connections exhaustion: application gets "too many connections" errors
Memory exhaustion: so much RAM consumed by connection overhead that there's nothing left for shared_buffers, work_mem, and the OS page cache

Connection pooling solves both by maintaining a fixed pool of database connections and sharing them among application connections. A pool of 20 database connections can serve hundreds of application connections because most connections are idle at any moment.

Diagnosing Connection Problems

-- Connection count by state and application
SELECT
    state,
    usename AS username,
    application_name,
    count(*) AS connection_count,
    count(*) FILTER (WHERE state = 'idle') AS idle_connections,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, usename, application_name
ORDER BY connection_count DESC;

-- How close are you to max_connections?
SELECT
    count(*) AS active_connections,
    current_setting('max_connections')::int AS max_connections,
    round(100.0 * count(*) / current_setting('max_connections')::int, 1)
        AS utilization_percentage
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Above 70% utilization means you're at risk during traffic spikes. A high idle_connections count relative to total means connections are held open unnecessarily -- a pooler would reclaim these.

-- Find idle-in-transaction connections wasting pool capacity
SELECT
    pid,
    usename,
    application_name,
    state,
    now() - state_change AS idle_duration,
    left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY state_change;

These are the worst offenders. They hold locks, snapshots, and pool slots while doing absolutely nothing.

PgBouncer Setup (The Standard Choice)

PgBouncer is the most widely deployed PostgreSQL pooler -- lightweight, stable, battle-tested:

; /etc/pgbouncer/pgbouncer.ini

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Transaction mode: connection returned after each transaction
pool_mode = transaction

; Pool sizing
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Connection limits
max_client_conn = 1000
max_db_connections = 50

; Timeouts
server_idle_timeout = 600
query_timeout = 300

The critical setting is pool_mode. Transaction mode gives the best connection reuse but means you can't use session-level features (prepared statements, SET commands, LISTEN/NOTIFY, temporary tables) across transactions.

# PgBouncer admin console
psql -p 6432 -U pgbouncer pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW SERVERS;

The Pool Sizing Formula

The optimal pool size depends on your hardware, not your application concurrency:

optimal_pool_size = (CPU_cores * 2) + number_of_disks

For 8 cores with SSD (count as 1 disk): ~17 connections. This seems small, but PostgreSQL can only execute as many queries in parallel as it has CPU cores. Beyond that, connections compete for CPU and context switching reduces throughput.

-- Check how many connections are truly active at any time
SELECT
    count(*) FILTER (WHERE state = 'active') AS truly_active,
    count(*) FILTER (WHERE state = 'idle') AS idle,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
    count(*) AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend';

If truly_active is consistently below 20, a pool of 20-25 is sufficient regardless of your application instance count.

Supavisor (Cloud-Native Alternative)

Supavisor is Supabase's open-source pooler built in Elixir, designed for multi-tenant cloud environments:

tenants:
  - id: "my-tenant"
    db_host: "localhost"
    db_port: 5432
    db_database: "myapp"
    pool_size: 20
    mode: "transaction"

Key advantages over PgBouncer:

Multi-threaded (handles more client connections per instance)
Supports named prepared statements in transaction mode
Per-tenant pool isolation

Application-Level Pooling

Use it alongside (not instead of) an external pooler:

# Python: SQLAlchemy
engine = create_engine(
    "postgresql://user:password@pgbouncer-host:6432/myapp",
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=3600,
    pool_pre_ping=True,
)

// Node.js: pg
const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  max: 5,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

Keep the application pool small (5-10 per instance) when connecting through PgBouncer. The application pool handles lifecycle; PgBouncer handles multiplexing.

Preventing Connection Waste

-- Terminate idle-in-transaction connections after 60 seconds
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- Kill runaway queries after 5 minutes
ALTER SYSTEM SET statement_timeout = '300s';
SELECT pg_reload_conf();

Quick Decision Guide

PgBouncer: battle-tested, lightweight, works for most deployments
Supavisor: multi-tenant, cloud-native, supports prepared statements in transaction mode
pgcat: adds sharding and load balancing
Application pool only: fine for a single instance with low concurrency, insufficient for anything larger

PostgreSQL does not have built-in connection pooling as of version 17. Every client connection creates a new OS process. Until that changes, an external pooler is essential for any production workload with more than a handful of concurrent users.

PostgreSQL GIN Indexes: JSONB, Arrays & Full-Text Search

Philip McClarence — Fri, 10 Apr 2026 10:00:02 +0000

PostgreSQL GIN Indexes: JSONB, Arrays & Full-Text Search

A GIN (Generalized Inverted Index) in PostgreSQL maps individual values inside composite data types -- JSONB keys, array elements, text search lexemes, and trigrams -- to the rows that contain them. If you're searching inside JSONB, arrays, or text, GIN is the only index type that helps. Without one, every query does a full table scan.

Why B-tree Can't Help Here

B-tree indexes work great for scalar comparisons: WHERE user_id = 123, WHERE created_at > '2025-01-01'. One value per row, standard comparison operators.

But PostgreSQL supports rich data types where a single column contains multiple searchable values:

A JSONB column with a document containing dozens of keys
An array column with a list of tags
A tsvector column with lexemes from an entire text document
A text column searched with LIKE '%pattern%'

B-tree indexes can't decompose these into searchable parts. So without a GIN index, queries like WHERE metadata @> '{"status": "active"}' scan every row.

The performance hit is hidden during development. With 1,000 rows in dev, the seq scan is instant. With millions of rows in production, the same query takes seconds.

GIN for JSONB

There are two operator classes, and the choice matters:

-- Default (jsonb_ops): supports @>, ?, ?|, ?& operators
CREATE INDEX CONCURRENTLY idx_products_attributes_gin
    ON products USING gin (attributes);

-- Queries it serves:
SELECT * FROM products WHERE attributes @> '{"color": "blue"}';
SELECT * FROM products WHERE attributes ? 'warranty';
SELECT * FROM products WHERE attributes ?| array['color', 'size'];

-- jsonb_path_ops: supports ONLY @> but is 2-3x smaller and faster
CREATE INDEX CONCURRENTLY idx_products_attributes_path
    ON products USING gin (attributes jsonb_path_ops);

-- Only serves containment queries:
SELECT * FROM products WHERE attributes @> '{"color": "blue"}';
-- Does NOT support ?, ?|, ?& operators

If your queries only use @> (containment), jsonb_path_ops is the clear winner -- smaller index, faster lookups. If you also need key existence checks (?, ?|, ?&), you need the default.

The most common mistake: creating a jsonb_path_ops index then querying with ? (key existence). The index is silently ignored and the query does a seq scan.

GIN for Arrays

CREATE INDEX CONCURRENTLY idx_articles_tags_gin
    ON articles USING gin (tags);

-- Contains all specified elements
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

-- Contains any of the specified elements
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'mysql'];

-- Is contained by
SELECT * FROM articles WHERE tags <@ ARRAY['postgresql', 'performance', 'indexing'];

GIN for Full-Text Search

First, create a generated tsvector column (PG12+), then index it:

ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX CONCURRENTLY idx_articles_search_gin
    ON articles USING gin (search_vector);

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

The setweight function assigns different weights to title vs body matches, so title matches rank higher.

GIN for Trigram Similarity (pg_trgm)

This one is a game-changer for LIKE queries with leading wildcards:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_customers_name_trgm
    ON customers USING gin (customer_name gin_trgm_ops);

-- Now these use the index instead of seq scan:
SELECT * FROM customers WHERE customer_name LIKE '%smith%';
SELECT * FROM customers WHERE customer_name ILIKE '%john%';

-- Similarity searches work too:
SELECT * FROM customers
WHERE customer_name % 'Jon Smith'
ORDER BY similarity(customer_name, 'Jon Smith') DESC;

Without the trigram GIN index, LIKE '%pattern%' always does a sequential scan because B-tree indexes require a fixed prefix.

Detecting Missing GIN Indexes

Find tables with JSONB/array columns that lack GIN indexes:

SELECT
    t.schemaname,
    t.relname AS table_name,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    t.seq_scan,
    t.seq_tup_read,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size
FROM pg_stat_user_tables t
JOIN pg_attribute a ON a.attrelid = t.relid
WHERE a.atttypid IN ('jsonb'::regtype, 'json'::regtype)
   OR pg_catalog.format_type(a.atttypid, a.atttypmod) LIKE '%[]'
   OR a.atttypid = 'tsvector'::regtype
ORDER BY t.seq_tup_read DESC;

Confirm with EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE attributes @> '{"color": "blue", "size": "large"}';

-- If you see "Seq Scan" with "Filter", you need a GIN index
-- If you see "Bitmap Index Scan" on a GIN index, you're good

Tuning fastupdate

GIN indexes use a pending list to batch insertions. This makes inserts fast but can cause unpredictable query latency when the list is flushed:

-- Disable for consistent query latency (slower inserts)
ALTER INDEX idx_products_attributes_gin SET (fastupdate = off);

-- Or tune the pending list size
ALTER INDEX idx_products_attributes_gin SET (gin_pending_list_limit = 256);

If you need predictable query performance over insert throughput, disable fastupdate.

The GIN Index Checklist

Add this to your schema design process:

JSONB column queried with @>, ? operators -> GIN index
Array column queried with @>, && operators -> GIN index
tsvector column -> GIN index
Text column with LIKE '%pattern%' -> trigram GIN index (pg_trgm)
Only need containment (@>)? -> use jsonb_path_ops (2-3x smaller)
Need key existence (?)? -> use default operator class

Don't wait for production performance complaints. If the column type and query pattern match, add the GIN index in the same migration that creates the column.

PostgreSQL Foreign Data Wrappers: Cross-Database Queries Explained

Philip McClarence — Thu, 09 Apr 2026 10:00:02 +0000

PostgreSQL Foreign Data Wrappers: Cross-Database Queries Explained

PostgreSQL foreign data wrappers (FDWs) let you query remote databases, files, and external services as if they were local tables. Using CREATE FOREIGN TABLE, you can JOIN data across PostgreSQL instances, Oracle databases, CSV files, and dozens of other sources directly from SQL.

No ETL pipeline. No data duplication. Just standard SQL against remote data.

When You Need Cross-Database Queries

Applications often need data from multiple databases. A reporting system joins user data from one PostgreSQL instance with order data from another. A migration project reads from Oracle while writing to PostgreSQL. A data pipeline queries a remote analytics database without copying everything locally.

The traditional approach is ETL: extract, transform, load. It works but introduces latency (the local copy is always stale), complexity (pipeline to maintain), and storage costs (data duplication). For many use cases, querying the remote data directly is simpler and more current.

Setting Up postgres_fdw

The postgres_fdw extension is the most commonly used FDW. Here's the full setup:

-- Install the extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create a foreign server
CREATE SERVER remote_analytics
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'analytics-db.example.com',
        port '5432',
        dbname 'analytics',
        fetch_size '10000'  -- critical: default 100 is far too low
    );

-- Create user mapping (credentials for the remote connection)
CREATE USER MAPPING FOR current_user
    SERVER remote_analytics
    OPTIONS (user 'readonly_user', password 'secure_password');

That fetch_size option deserves emphasis. The default of 100 rows per batch means a query returning 1 million rows makes 10,000 round trips to the remote server. Setting it to 10,000 reduces this to 100 round trips. For analytical workloads, 50,000 is even better.

Creating Foreign Tables

You can define them individually or import entire schemas:

-- Individual foreign table
CREATE FOREIGN TABLE remote_orders (
    order_id BIGINT,
    customer_id BIGINT,
    customer_name TEXT,
    order_date DATE,
    order_total_amount NUMERIC(12, 2),
    status TEXT
)
SERVER remote_analytics
OPTIONS (schema_name 'public', table_name 'orders');

-- Or import multiple tables at once (PG9.5+)
IMPORT FOREIGN SCHEMA public
    LIMIT TO (customers, products, categories)
    FROM SERVER remote_analytics
    INTO foreign_data;

IMPORT FOREIGN SCHEMA is a huge time saver. It automatically creates local foreign table definitions matching the remote schema.

The Performance Trap: Pushdown Optimization

Here's where most FDW performance problems hide. PostgreSQL tries to push WHERE clauses, JOINs, sorts, and aggregations to the remote server. When pushdown works, the remote server does the heavy lifting and only sends the filtered results. When it doesn't, PostgreSQL fetches the entire remote table and filters locally.

-- Pushdown works: simple operators
SELECT * FROM remote_orders WHERE order_date >= '2025-01-01';

-- Pushdown FAILS: local function in WHERE
SELECT * FROM remote_orders WHERE my_custom_function(status) = true;
-- PostgreSQL fetches ALL rows, then filters locally

-- Pushdown works: JOINs between foreign tables on the SAME server
SELECT o.order_id, c.customer_name
FROM remote_orders o
JOIN remote_customers c ON o.customer_id = c.customer_id;

-- Pushdown FAILS: JOIN between foreign and local tables
SELECT o.order_id, l.label
FROM remote_orders o
JOIN local_labels l ON o.status = l.status_code;
-- Entire foreign table fetched, then joined locally

Always verify with EXPLAIN VERBOSE:

EXPLAIN VERBOSE
SELECT order_id, customer_name, order_total_amount
FROM remote_orders
WHERE order_date >= '2025-01-01'
  AND status = 'completed'
ORDER BY order_total_amount DESC
LIMIT 100;

Look for Remote SQL in the Foreign Scan node. If your WHERE clause appears in the remote SQL, pushdown is working. If the remote SQL is just SELECT * FROM orders, you have a problem.

The Statistics Gap

This one catches a lot of teams. Foreign tables are not analyzed by autovacuum. Without statistics, the planner assumes a default row estimate (typically 1,000 rows). If the remote table actually has millions of rows, the planner makes terrible decisions -- choosing nested loop joins when hash joins would be 100x faster.

-- Run ANALYZE on foreign tables manually
ANALYZE remote_orders;

-- Verify statistics were collected
SELECT
    relname,
    reltuples AS estimated_rows,
    relpages AS estimated_pages
FROM pg_class
WHERE relname = 'remote_orders';

Schedule this regularly. It's the most overlooked FDW maintenance task.

Checking Your FDW Setup

-- List all foreign servers
SELECT
    s.srvname AS server_name,
    f.fdwname AS wrapper_name,
    s.srvoptions AS server_options
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON s.srvfdw = f.oid;

-- List all foreign tables
SELECT
    ft.foreign_table_schema AS local_schema,
    ft.foreign_table_name AS local_table,
    ft.foreign_server_name AS server_name
FROM information_schema.foreign_tables ft
ORDER BY ft.foreign_table_name;

-- Find foreign tables that haven't been analyzed
SELECT
    relname AS foreign_table_name,
    last_analyze,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE relname IN (
    SELECT foreign_table_name
    FROM information_schema.foreign_tables
)
ORDER BY last_analyze NULLS FIRST;

Beyond PostgreSQL: Other FDW Options

The FDW ecosystem extends well beyond PostgreSQL-to-PostgreSQL:

-- Oracle FDW
CREATE SERVER oracle_prod
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//oracle-host:1521/ORCL');

-- File FDW for CSV files
CREATE EXTENSION file_fdw;
CREATE SERVER csv_files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE import_data (
    id INTEGER,
    name TEXT,
    value NUMERIC
) SERVER csv_files
OPTIONS (filename '/data/import.csv', format 'csv', header 'true');

Same SQL interface regardless of the data source.

Performance Optimization Tips

Materialized views for frequently accessed data

If you query the same remote data repeatedly and can tolerate some staleness:

CREATE MATERIALIZED VIEW mv_recent_orders AS
SELECT order_id, customer_name, order_total_amount, order_date
FROM remote_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Refresh on schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_recent_orders;

Local-speed queries with periodic refreshes, while the foreign table stays available for ad-hoc full access.

Tune fetch_size per table

-- Server-level default
ALTER SERVER remote_analytics OPTIONS (SET fetch_size '10000');

-- Override for a large fact table
ALTER FOREIGN TABLE remote_events OPTIONS (SET fetch_size '50000');

Security considerations

User mappings store passwords in plaintext in pg_user_mappings (visible to superusers). Use a dedicated read-only role on the remote server with minimal privileges. Rotate credentials periodically.

The Decision: FDW vs ETL

Use FDWs when:

You need real-time access to remote data
The remote dataset is small enough that query latency is acceptable
You want to avoid maintaining a sync pipeline

Use ETL when:

You query the same remote data thousands of times per day
Query latency must be sub-millisecond
The remote data needs transformation before use
The remote server cannot handle the additional query load

FDWs and ETL aren't mutually exclusive. Use a foreign table for development and ad-hoc queries, and a materialized view or ETL pipeline for production workloads that need guaranteed performance.

PostgreSQL Logical Replication: Setup, Monitoring & Troubleshooting

Philip McClarence — Wed, 08 Apr 2026 10:00:02 +0000

PostgreSQL Logical Replication: Setup, Monitoring & Troubleshooting

PostgreSQL logical replication decodes WAL changes into a logical format and streams them to subscribers. Unlike physical (streaming) replication that creates byte-for-byte copies of the entire cluster, logical replication lets you replicate a subset of tables, replicate between different PostgreSQL major versions, and feed changes into systems running a different schema.

Sounds great on the surface. The complexity hits after the initial setup.

Why Physical Replication Isn't Always Enough

Streaming replication sends raw WAL bytes to replicas. It's great for HA failover, but falls apart when you need selectivity. You can't replicate just three tables out of a hundred. You can't replicate from PostgreSQL 14 to PostgreSQL 17 during a version upgrade. You can't stream changes into a data warehouse with a different schema.

Logical replication solves all of these by decoding WAL into row-level change events (INSERT, UPDATE, DELETE) and applying them on the subscriber. You define a publication with a set of tables on the source, create a subscription on the target, and PostgreSQL handles the initial data copy and ongoing change streaming.

Setting It Up

On the publisher

First, set wal_level (this requires a restart):

ALTER SYSTEM SET wal_level = 'logical';
-- Restart PostgreSQL after this change

Then create a publication:

-- Publish specific tables
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items, customers;

-- Or publish all tables in a schema (PG15+)
CREATE PUBLICATION analytics_pub FOR TABLES IN SCHEMA analytics;

On the subscriber

Create the subscription. This automatically creates a replication slot on the publisher and copies existing data before streaming changes:

CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=publisher.example.com port=5432 dbname=myapp user=replicator password=secret'
    PUBLICATION orders_pub;

Monitor the initial sync:

SELECT
    srsubid,
    srrelid::regclass AS table_name,
    CASE srsubstate
        WHEN 'i' THEN 'initializing'
        WHEN 'd' THEN 'data_copying'
        WHEN 's' THEN 'synchronized'
        WHEN 'r' THEN 'ready'
    END AS sync_state
FROM pg_subscription_rel;

The Three Things That Bite You in Production

1. DDL is not replicated

This is the big one. An ALTER TABLE ADD COLUMN on the publisher is completely invisible to the subscriber. The subscriber doesn't know the column exists, so when it receives rows with the new column, replication breaks.

The fix requires careful ordering:

-- Step 1: Apply on subscriber FIRST
ALTER TABLE orders ADD COLUMN priority_level TEXT DEFAULT 'normal';

-- Step 2: Apply on publisher
ALTER TABLE orders ADD COLUMN priority_level TEXT DEFAULT 'normal';

-- Step 3: Refresh the subscription
ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION;

The rule is: additive changes (ADD COLUMN, ADD TABLE) go on the subscriber first. Destructive changes (DROP COLUMN, DROP TABLE) go on the publisher first. Get it backwards and replication stops.

2. Replication slots retain WAL indefinitely

A replication slot tells PostgreSQL "do not delete any WAL after this point until I say so." If the subscriber goes offline -- a failed server, a dropped subscription that wasn't cleaned up, a CDC tool that crashed -- the slot keeps retaining WAL segments.

Hours or days later, pg_wal/ has grown from a few GB to hundreds of GB. The disk fills. PostgreSQL can't write new WAL. All transactions stall. The primary is effectively down.

Monitor slot lag constantly:

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS slot_lag_bytes,
    wal_status
FROM pg_replication_slots
WHERE slot_type = 'logical';

And set a safety limit (PG13+):

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

When a slot exceeds this limit, PostgreSQL invalidates it rather than filling the disk. The subscriber will need to be re-synced, but the publisher stays online.

3. Conflict resolution is minimal

If an INSERT on the subscriber violates a unique constraint because the row already exists, replication stops dead. You have to manually intervene:

-- Disable the subscription
ALTER SUBSCRIPTION orders_sub DISABLE;

-- Fix the conflict
DELETE FROM orders WHERE order_id = 12345;

-- Re-enable
ALTER SUBSCRIPTION orders_sub ENABLE;

On PG15+, you can skip conflicts automatically:

ALTER SUBSCRIPTION orders_sub SET (disable_on_error = false);

But that means silently dropping conflicting data, which may not be acceptable.

REPLICA IDENTITY: The Hidden Requirement

For UPDATE and DELETE to work, the subscriber needs to identify which row to modify. By default it uses the primary key. Tables without a primary key need explicit configuration:

-- Option 1: Send all columns (works but increases WAL volume)
ALTER TABLE events REPLICA IDENTITY FULL;

-- Option 2: Use a unique index
CREATE UNIQUE INDEX idx_events_event_id ON events (event_id);
ALTER TABLE events REPLICA IDENTITY USING INDEX idx_events_event_id;

Without a replica identity, UPDATE and DELETE operations on the publisher fail with an error.

Aurora PostgreSQL Setup

For AWS Aurora, logical replication requires a parameter group change and reboot:

-- In the RDS parameter group:
-- rds.logical_replication = 1
-- This automatically sets wal_level = logical

-- After reboot, grant the replication role:
GRANT rds_replication TO replicator_user;

-- Then create publications and subscriptions as normal

Monitoring Replication Health

Check subscription status and lag on the subscriber:

SELECT
    subname AS subscription_name,
    received_lsn,
    latest_end_lsn,
    latest_end_time,
    pg_size_pretty(
        pg_wal_lsn_diff(received_lsn, latest_end_lsn)
    ) AS apply_lag_bytes
FROM pg_stat_subscription
WHERE subname IS NOT NULL;

Check for errors that have stalled replication:

SELECT
    subname,
    worker_type,
    relname,
    last_error_message,
    last_error_time
FROM pg_stat_subscription_stats
WHERE last_error_message IS NOT NULL;

Prevention Checklist

Always set max_slot_wal_keep_size -- 5-20 GB depending on workload. This prevents a single orphaned slot from filling your disk.
Document your schema change procedure -- subscriber-first for additions, publisher-first for removals. Include ALTER SUBSCRIPTION ... REFRESH PUBLICATION in the checklist.
Use UUID primary keys in bidirectional setups to avoid conflicts.
Keep subscriber tables read-only in unidirectional setups (separate role without write privileges).
Monitor both apply lag and slot lag continuously. They indicate different problems.

What Logical Replication Does NOT Replicate

Sequences (sync periodically with pg_dump --data-only -t '*_seq')
DDL changes (manual coordination required)
Large objects
Materialized view refreshes
TRUNCATE (on PostgreSQL versions before 11)

Plan for these gaps in your architecture rather than discovering them in production.

MyDBA.dev vs Percona PMM: Deep PostgreSQL Intelligence vs Multi-Database Monitoring

Philip McClarence — Tue, 07 Apr 2026 10:00:01 +0000

MyDBA.dev vs Percona PMM: Deep PostgreSQL Intelligence vs Multi-Database Monitoring

Percona Monitoring and Management (PMM) is one of the most established open-source database monitoring tools available. It is free, actively maintained, and covers MySQL, MongoDB, and PostgreSQL in a single platform. If you run a polyglot database environment, PMM is a strong choice. This article compares PMM with MyDBA.dev to help you decide which tool fits your PostgreSQL monitoring needs.

Quick Comparison

Capability	Percona PMM	MyDBA.dev
Pricing	Free (open source, self-hosted)	Free tier (hosted) / Pro from GBP 19/mo
Database support	MySQL, MongoDB, PostgreSQL	PostgreSQL only
Deployment	Self-hosted server + agents on every host	SaaS with lightweight collector
Query analytics	QAN with fingerprinting, EXPLAIN	Fingerprinting, EXPLAIN + EXPLAIN ANALYZE, plan regression detection
Health checks	Percona Advisors (security, config, perf)	75+ checks with scored domains and fix scripts
Index advisor	No	Yes -- missing index detection with CREATE INDEX
XID wraparound	Basic metric exposure	Dedicated monitoring with blocker detection
Extension monitoring	No	TimescaleDB, pgvector, PostGIS
Lock visualization	Standard lock metrics	Interactive dependency graphs
Schema comparison	No	Cross-instance schema diff

Where PMM Excels

PMM deserves genuine credit.

It is fully free and open source. Not freemium with paywalled features -- everything is available. As of 2026, all Percona Advisors (security, configuration, and performance checks) are included without any subscription.

Multi-database support is real. If you run MySQL, MongoDB, and PostgreSQL together, PMM gives you one monitoring platform. The MySQL coverage in particular is excellent -- PMM was built on Percona's deep MySQL expertise.

Grafana dashboards are flexible. PMM ships with dozens of pre-built dashboards and you can build your own. If your team already knows Grafana, you are immediately productive.

Advisors catch real issues. The framework checks for default passwords, SSL misconfigurations, memory settings, checkpoint tuning, replication lag, and more. These run automatically and surface findings in the UI.

Backup integration. PMM integrates with Percona's backup tools for MySQL and MongoDB -- useful if backup monitoring is part of your workflow.

Where MyDBA.dev Goes Further

The core difference is specialization. PMM is a multi-database monitoring platform adapted to support PostgreSQL. MyDBA.dev is built exclusively for PostgreSQL.

Health Checks with Fix Scripts

PMM's Advisors cover a solid set of checks. MyDBA.dev runs 75+ checks across 10 scored domains -- Vacuum, Indexes, Storage, Performance, Schema, Security, WAL & Backup, Connections, Replication, and Extensions. Every finding includes a fix script: not just "this index is missing" but the exact CREATE INDEX statement. Domain scores (0-100) let you track improvement over time.

Index Advisor

PMM has no index recommendation engine. MyDBA.dev analyzes query patterns, sequential scan frequency, and table access statistics to recommend missing indexes. Each recommendation includes the CREATE INDEX statement, estimated impact, and the queries that would benefit.

XID Wraparound Protection

XID wraparound is a PostgreSQL-specific risk that can force emergency autovacuum or database shutdown. PMM exposes basic XID age metrics. MyDBA.dev provides dedicated monitoring with age tracking, blocker detection (long-running transactions, prepared transactions, replication slots), trend analysis, and recovery scripts.

Extension Monitoring

TimescaleDB, pgvector, PostGIS -- these extensions have their own performance characteristics and failure modes. MyDBA.dev monitors TimescaleDB (chunk health, compression ratios, continuous aggregate freshness), pgvector (index selection, distance functions, filtered search), and PostGIS (spatial index efficiency, SRID consistency, geometry quality). PMM has no extension awareness.

No Infrastructure to Manage

PMM requires deploying and maintaining PMM Server (Docker/VM) plus agents on every monitored host. You manage storage, upgrades, availability, and resources. MyDBA.dev is SaaS -- install a lightweight collector binary and the monitoring infrastructure is handled for you. No Grafana to upgrade, no Prometheus storage to manage.

Lock Chain Visualization and Schema Diff

MyDBA.dev renders interactive lock chain dependency graphs showing the full blocking tree -- which session holds the lock, which are waiting, what queries are involved. PMM provides standard lock metrics without the visual dependency mapping. MyDBA.dev also includes cross-instance schema comparison for staging vs production or pre/post migration diffs. PMM does not offer schema comparison.

Pricing

PMM: Free (open source). You provide infrastructure for PMM Server and agents. The real cost is your team's time.

MyDBA.dev: Free tier (one connection, 7-day retention). Pro from GBP 19/month (30-day retention, all features). No infrastructure to manage.

When to Choose Which

Choose PMM when: You run MySQL + MongoDB + PostgreSQL together, you are already in the Percona ecosystem, your team has ops capacity for self-hosting, you need backup integration, or budget is the primary constraint.

Choose MyDBA.dev when: Your environment is PostgreSQL-only or PostgreSQL-primary, you want health checks with fix scripts and index recommendations, you use PostgreSQL extensions, you want to avoid managing monitoring infrastructure, or you value remediation guidance alongside detection.

The Honest Take

PMM is a mature, genuinely free monitoring platform with excellent multi-database support. If you run MySQL alongside PostgreSQL, it is hard to beat.

The trade-off is PostgreSQL depth. PMM was built on MySQL heritage and adapted to PostgreSQL. MyDBA.dev was built for PostgreSQL from the ground up. The difference shows in extension monitoring, index recommendations, XID wraparound tooling, lock chain visualization, and the 75+ health checks with fix scripts. If PostgreSQL is your primary database, that specialization matters.

Originally published at mydba.dev/blog/mydba-vs-pmm

MyDBA.dev vs pgwatch: Metrics Collection vs Actionable Intelligence

Philip McClarence — Mon, 06 Apr 2026 10:00:05 +0000

If you are evaluating PostgreSQL monitoring tools, pgwatch is almost certainly on your list. It should be. It is one of the best open-source options available -- mature, lightweight, flexible, and purpose-built for PostgreSQL.

We built myDBA.dev to solve a different problem, and the distinction matters more than you might expect.

What pgwatch Does Well

pgwatch deserves genuine credit. Here is where it excels:

Any SQL query becomes a metric. This is pgwatch's killer feature. Write a SQL query, add it to the configuration, and it shows up in Grafana. Business-specific KPIs, custom health indicators, application-level metrics stored in your database -- if you can SELECT it, pgwatch can track it.

It is free and open source. BSD-3-Clause license, no vendor lock-in, full control. For organizations with strict open-source mandates or zero monitoring budget, this is a decisive advantage.

It scales. The Go-based collector is lightweight enough to monitor hundreds of PostgreSQL instances without becoming a bottleneck. Multiple storage backends -- PostgreSQL, TimescaleDB, Prometheus -- give you flexibility in how you store and query historical data.

Auto-discovery works. Point it at Patroni, PgBouncer, Pgpool2, or AWS RDS and it automatically discovers and monitors all members. Topology changes are handled without manual intervention.

Grafana integration is mature. 30+ predefined dashboards covering database overview, table statistics, index usage, replication, locks, WAL, and more.

The Gap Between Data and Action

Here is the pattern we kept seeing: teams deploy pgwatch, build beautiful Grafana dashboards, and collect comprehensive metrics. Then something goes wrong.

The dashboard shows n_dead_tup at 2.4 million on the orders table. The question is not whether you can see the number -- pgwatch shows it clearly. The question is what you do next.

Is autovacuum disabled on that table? Is a long-running transaction blocking vacuum? Are the per-table vacuum settings misconfigured? What is the exact command to fix it?

-- The fix pgwatch cannot generate for you:
ALTER TABLE orders RESET (autovacuum_enabled);
-- Re-enables autovacuum with default settings

This gap between seeing a metric and knowing what to do about it is where myDBA.dev focuses.

What myDBA.dev Adds

Health Checks with Fix Scripts

pgwatch has no health check system. It collects and displays metrics. Interpretation is left to the operator.

myDBA.dev runs 75+ automated health checks across 12 domains -- Configuration, Performance, Vacuum, Replication, Indexes, Security, Storage, Connections, TimescaleDB, XID Wraparound, pgvector, and Wait Events. Each finding produces three things: what is wrong, why it matters, and a copy-pasteable SQL fix calculated from your actual server configuration.

Index Advisor

pgwatch collects index usage statistics. myDBA.dev goes further: it analyzes your query workload, identifies sequential scans on large tables, detects duplicate and overlapping indexes, and generates CREATE INDEX or DROP INDEX CONCURRENTLY statements with estimated impact.

Automatic EXPLAIN Plans

pgwatch does not collect query execution plans. When a query regresses from 50ms to 12 seconds, you see the timing change in your dashboard but have no plan to explain why.

myDBA.dev automatically collects EXPLAIN plans for your top queries each collection cycle. When performance degrades, you compare old and new plans to see whether the planner switched scan types, whether row estimates drifted, or whether a new join strategy is suboptimal.

Extension Monitoring

pgwatch can store data in TimescaleDB but does not monitor it. It does not track chunk health, compression ratios, continuous aggregate staleness, or job failures. It has no pgvector monitoring for index build progress or recall accuracy. No PostGIS monitoring for spatial index quality or SRID mismatches.

myDBA.dev has dedicated monitoring and health checks for TimescaleDB, pgvector, and PostGIS.

XID Wraparound Protection

pgwatch shows XID age as a metric. myDBA.dev detects wraparound risk, identifies what is blocking autovacuum from making progress (long-running transactions, prepared transactions, replication slots), and generates recovery scripts.

Quick Comparison

Capability	pgwatch	myDBA.dev
Open source	Yes (BSD-3-Clause)	No (SaaS)
Custom SQL metrics	Yes	No
Health checks with fix scripts	No	75+ checks
Index advisor	No	Yes
EXPLAIN plan collection	No	Yes
Extension monitoring	No	TimescaleDB, pgvector, PostGIS
Self-hosting required	Yes	No
Price	Free (self-hosted)	Free tier, Pro from GBP 19/mo

When to Choose pgwatch

Choose pgwatch if your team has deep PostgreSQL expertise and prefers raw data over guided recommendations. If you already run Grafana and Prometheus, pgwatch integrates natively. If you need custom business-domain metrics, pgwatch's SQL-based approach is unmatched. If budget is zero and you have the ops capacity to self-host, pgwatch is the clear winner on cost.

When to Choose myDBA.dev

Choose myDBA.dev if you want the interpretation done for you. If your team does not have a senior DBA who can turn raw metrics into fix scripts, the health check system fills that gap. If you need extension monitoring, EXPLAIN plan collection, or index recommendations, those are capabilities pgwatch does not offer. If you do not want to manage monitoring infrastructure, SaaS means one less thing to operate.

The Bottom Line

pgwatch answers "what is happening?" myDBA.dev answers "what is happening, what does it mean, and what should I do about it?"

Both are good tools solving different problems. The right choice depends on whether your team needs data or direction.

Full comparison with screenshots: MyDBA.dev vs pgwatch

MyDBA.dev vs Datadog Database Monitoring: PostgreSQL-Native vs Full-Stack Observability

Philip McClarence — Sun, 05 Apr 2026 10:00:04 +0000

Datadog Database Monitoring vs MyDBA.dev: What PostgreSQL Teams Actually Need

If you run PostgreSQL in production, you have probably evaluated Datadog Database Monitoring at some point. It is the default choice for many teams because it integrates with everything else in the Datadog ecosystem. But "integrates with everything" and "monitors PostgreSQL deeply" are not the same thing. Let me walk through where each tool excels and where each falls short, from the perspective of someone who spends their days thinking about PostgreSQL performance.

The Core Trade-Off: Breadth vs Depth

Datadog is a full-stack observability platform that happens to include database monitoring. MyDBA.dev is a PostgreSQL monitoring tool that does nothing else. This distinction shapes every difference between them.

Datadog's breadth means you get APM traces correlated with database queries, infrastructure metrics alongside query latency, and a single pane of glass across MySQL, PostgreSQL, MongoDB, and Redis. If your debugging workflow starts at the application layer and drills down to the database, Datadog's trace-to-query correlation is genuinely impressive.

MyDBA.dev's depth means you get 75+ PostgreSQL-specific health checks with SQL remediation scripts, a cluster-aware index advisor, XID wraparound monitoring, and dedicated dashboards for extensions like TimescaleDB, pgvector, and PostGIS. If your debugging workflow starts at the database layer, these capabilities matter more than APM integration.

Five Things Datadog Does Not Cover

1. Extension Monitoring

This is the biggest gap. TimescaleDB, pgvector, and PostGIS are increasingly common in PostgreSQL deployments, and Datadog treats all three as invisible. No chunk size tracking for TimescaleDB, no index recall metrics for pgvector, no spatial index efficiency checks for PostGIS.

MyDBA.dev monitors all three with dedicated dashboards. For pgvector alone there are 13 health checks covering index type selection, recall estimation, quantization settings, and storage efficiency. For TimescaleDB, you get hypertable chunk monitoring, compression ratio tracking, continuous aggregate freshness, and background job health.

If you use any of these extensions, this is likely the deciding factor.

2. XID Wraparound Protection

Transaction ID wraparound is PostgreSQL's most dangerous failure mode. When a database approaches the 2-billion XID limit, PostgreSQL forces an emergency vacuum that blocks all writes -- a production outage that can take hours to resolve.

MyDBA.dev tracks XID age trends over time, alerts on rising age, and identifies which tables are contributing to the problem. Datadog does not monitor XID age at all. For PostgreSQL-specific failure modes, a generalist tool leaves blind spots.

3. Health Scoring with Fix Scripts

MyDBA.dev runs automated health checks across 10 domains -- Connections, Indexes, Performance, Replication, Schema, Security, Storage, Vacuum, WAL & Backup, and Extensions -- and assigns a score from A to F. Each finding includes a severity level, an explanation, and a ready-to-run SQL fix script.

Datadog surfaces some recommendations, but without a structured scoring system or actionable remediation scripts. The difference: "you have a problem" versus "you have a problem, here is why, and here is the SQL to fix it."

4. Cluster-Aware Index Advisor

MyDBA.dev analyzes query workloads and recommends specific indexes with estimated cost savings, write-performance impact, and CREATE INDEX statements. It accounts for replication topology and existing index overlap.

Datadog provides basic missing index detection -- tables with high sequential scan counts that probably need an index. It does not perform workload-based analysis or provide cost/benefit estimates.

5. Plan Regression Detection

MyDBA.dev automatically collects EXPLAIN plans for your top queries and detects when a query's execution plan changes for the worse. If the planner switches from an index scan to a sequential scan after an ANALYZE or a statistics change, you get alerted before users notice the slowdown.

Datadog lets you manually run EXPLAIN ANALYZE through the UI, which is useful but reactive rather than proactive.

Where Datadog Is the Right Choice

Full credit where it is due -- Datadog wins in several important scenarios:

Multi-service architectures where you need to trace latency from the frontend through microservices to the database
Polyglot database environments with MySQL, MongoDB, Redis, and PostgreSQL all in the same stack
Teams already invested in Datadog where adding database monitoring is incremental, not a new tool
Custom dashboarding needs where combining database metrics with application and infrastructure metrics in flexible layouts matters

If your primary debugging workflow is "slow API response -> which service -> which query -> why is it slow," Datadog's end-to-end tracing is hard to replicate with any database-specific tool.

The Pricing Reality

Datadog Database Monitoring costs $70/host/month. It requires the Infrastructure tier ($15/host/month) as a prerequisite. So the effective cost is $85/host/month minimum.

MyDBA.dev pricing is per-organization, not per-host:

Servers	Datadog Annual Cost	MyDBA.dev Pro Annual Cost
1	$1,020	£228
5	$5,100	£228
10	$10,200	£228
25	$25,500	£228

At scale, the difference is not marginal -- it is an order of magnitude. MyDBA.dev also has a free tier with 7-day retention and full health-check access. Datadog does not offer a free database monitoring tier.

Making the Decision

Choose Datadog if PostgreSQL is one of many things you need to monitor and full-stack correlation is critical. Choose MyDBA.dev if PostgreSQL is the thing you need to monitor and depth of PostgreSQL-specific intelligence matters more than breadth.

They are not mutually exclusive. Some teams run Datadog for application-level observability and MyDBA.dev for PostgreSQL-specific depth. The lightweight Go collector adds minimal overhead and does not conflict with the Datadog Agent.

The deciding question: is your bottleneck "I need to connect database performance to application behavior" (Datadog) or "I need to understand what is happening inside PostgreSQL" (MyDBA.dev)?

Originally published at mydba.dev/blog/mydba-vs-datadog

MyDBA.dev vs pganalyze: Which PostgreSQL Monitor Should You Choose?

Philip McClarence — Sat, 04 Apr 2026 10:00:04 +0000

pganalyze vs MyDBA.dev -- A Practical PostgreSQL Monitoring Comparison

I've been running PostgreSQL in production for years, and if there's one thing I've learned about monitoring tools, it's this: the best time to evaluate them is before you need them. Not during a 3am incident when you're staring at a chart that says "something is wrong" but gives you no idea how to fix it.

Both pganalyze and MyDBA.dev are PostgreSQL-focused monitoring tools -- not generic infrastructure platforms that treat Postgres as an afterthought. But they have meaningfully different philosophies about what monitoring should do. Here's a practical comparison.

pganalyze: The Established Player

pganalyze has been around since 2013 and has built genuine depth in several areas.

Their index advisor uses hypothetical index simulation ("What If?" analysis) to recommend new indexes and predict their performance impact before you create them. You can see estimated query cost reduction for candidate indexes, which is valuable when you're weighing the write overhead of a new index against query speed improvements.

Their VACUUM advisor provides per-table autovacuum tuning recommendations, including freeze age analysis. If you're struggling with autovacuum configuration, pganalyze will tell you exactly which tables need what settings.

Log insights parse your PostgreSQL logs to surface connection errors, lock timeouts, checkpoint warnings, and other events that pg_stat_statements alone misses. This is well-executed and fills a real gap.

The tool is mature, stable, and well-documented. It does what it does reliably.

MyDBA.dev: The Opinionated Newcomer

MyDBA.dev (launched 2025) takes a different stance: monitoring should tell you what's wrong AND hand you the fix. Not just a red chart -- a diagnosis, an explanation, and a SQL script you can copy-paste.

75+ Health Checks with Fix Scripts

This is the core differentiator. MyDBA.dev runs 75+ automated health checks across 12 domains (indexes, vacuum, security, WAL, replication, storage, connections, configuration, queries, extensions, locks, XID). Each failing check includes:

A severity score
A plain-English explanation
A ready-to-run SQL fix script

pganalyze surfaces around 20-30 check-style findings, but doesn't generate fix scripts. At 3am, that difference matters.

Extension Monitoring (The Biggest Gap)

If you run TimescaleDB, pgvector, or PostGIS, this is the deciding factor. MyDBA.dev provides dedicated monitoring for all three:

TimescaleDB: chunk health, compression ratios, continuous aggregate staleness, job monitoring, 15+ extension-specific health checks
pgvector: index type analysis (IVFFlat vs HNSW), recall estimation, storage analysis, 13 health checks
PostGIS: spatial index coverage, geometry quality, SRID consistency, 19 health checks

pganalyze has no extension monitoring whatsoever. For teams whose workload is dominated by extension behavior (time-series ingestion, vector similarity search, spatial queries), this is a significant blind spot.

XID Wraparound Protection

Transaction ID wraparound can force your database into read-only emergency mode. MyDBA.dev provides a dedicated dashboard showing current XID age, tables approaching danger, and -- critically -- blocker detection with recovery scripts. It identifies the specific long-running transaction, abandoned replication slot, or prepared transaction preventing XID advancement, and gives you the command to fix it.

pganalyze shows basic xmin horizon data. When you're racing against an emergency wraparound vacuum, the difference between "your XID age is high" and "this replication slot is the blocker, here's the DROP command" is the difference between a quick fix and an hour of investigation.

Cluster-Aware Index Advisor

MyDBA.dev aggregates index usage across the entire replication topology. An index that looks unused on primary might be serving all your read-replica analytics queries. Dropping it based on primary-only stats would break things. pganalyze evaluates each server independently.

Pricing

This is where it gets interesting.

Plan	pganalyze	MyDBA.dev
Free	None	1 server + 1 replica (all features)
Entry	$149/mo (1 server)	$19/mo (1 server)
4 servers	$349/mo	$76/mo

pganalyze has no free tier. MyDBA.dev's free tier includes every feature -- the paid plan adds more servers and longer data retention.

Which Should You Choose?

Choose pganalyze if you need mature VACUUM advisory with per-table recommendations, hypothetical index analysis, or if you're already invested in it and it covers your needs.

Choose MyDBA.dev if you need health checks with remediation scripts, extension monitoring (TimescaleDB/pgvector/PostGIS), XID protection with blocker detection, cluster-aware index analysis, or a free tier to evaluate with production data.

Both are solid, PostgreSQL-focused tools. The right answer depends on your workload. If you run extensions or need remediation guidance, MyDBA.dev. If you need deep VACUUM and hypothetical index analysis, pganalyze. If budget matters, the pricing difference alone may be decisive.

Full comparison with more detail: mydba.dev/blog/mydba-vs-pganalyze

Originally published at mydba.dev/blog/mydba-vs-pganalyze

XID Wraparound Recovery: The Runbook Your Database Needs Before It's Too Late

Philip McClarence — Fri, 03 Apr 2026 10:00:04 +0000

If you have been running PostgreSQL in production for long enough, you have probably seen a blog post or conference talk about XID wraparound. You nodded along, thought "I should set up monitoring for that," and then went back to shipping features. This article is the runbook you will wish you had written before the 3 AM page.

The Problem

Transaction ID wraparound is PostgreSQL's nuclear option. Every transaction gets a 32-bit XID. When the counter approaches 2 billion, PostgreSQL forces itself into single-user mode to prevent data corruption. No reads, no writes, no connections -- until you manually run a vacuum that can take hours on large tables.

The insidious part: it sneaks up. XID age grows slowly, day by day, invisible unless you are actively watching. A database consuming 10 million XIDs per day has 200 days before hitting the 2-billion limit. That feels like plenty of time until you realize that autovacuum has been silently blocked for the last 3 weeks.

Three things block vacuum from advancing the XID horizon:

Long-running transactions -- a single idle in transaction session prevents vacuum from freezing any rows newer than that transaction's snapshot.
Abandoned prepared transactions -- PREPARE TRANSACTION creates a durable transaction that survives server restarts. If nobody commits or rolls it back, it holds the XID horizon indefinitely.
Lagging replication slots -- logical replication slots retain WAL and prevent the server from advancing past the slot's confirmed LSN. A slot that falls behind holds the XID horizon for the entire cluster.

Each of these blockers has a different detection method and a different fix. The danger is that you need to check all three, in sequence, and understand how they interact.

How to Detect It

Start with the database-level XID age, then drill into per-table ages and potential blockers:

-- Database-level XID age
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_wraparound
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY age(datfrozenxid) DESC;

-- Top 10 tables holding back the XID horizon
SELECT schemaname, relname, age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_relation_size(oid)) AS table_size
FROM pg_class JOIN pg_stat_user_tables USING (relname)
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC LIMIT 10;

Now check all three blocker categories:

-- Long-running transactions holding back vacuum
SELECT pid, age(backend_xmin) AS xmin_age, state, query,
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 5;

-- Orphaned prepared transactions
SELECT gid, prepared, owner, database,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

-- Replication slots preventing XID advancement
SELECT slot_name, slot_type, active,
       age(xmin) AS slot_xmin_age,
       age(catalog_xmin) AS slot_catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC;

The problem with manual detection: you need to run all four queries, correlate the results, and determine which specific blocker is the root cause. If a prepared transaction has XID age 1.4 billion and the worst table has XID age 1.4 billion, the prepared transaction is your blocker -- but that correlation is not obvious when you are running queries one at a time under pressure.

Monitoring for XID Wraparound

The key to effective XID monitoring is not just tracking the age number -- it is automatically correlating blockers with the current horizon. A good monitoring setup should:

Track per-database and per-table XID ages as time series, not snapshots
Identify which specific blocker (transaction, prepared transaction, or replication slot) is holding the horizon
Generate actionable recovery steps in the correct order
Alert at meaningful thresholds: 500 million (25%) for early warning, 1 billion (50%) for urgent action

Without automated correlation, you are left running queries one at a time during an emergency and hoping you check the right thing first.

How to Fix It

Follow this sequence. Order matters -- clearing blockers before vacuuming prevents wasted work.

Step 1: Remove prepared transaction blockers

-- List and roll back orphaned prepared transactions
SELECT gid, prepared, owner FROM pg_prepared_xacts;
ROLLBACK PREPARED 'txn_2024_q4';

Step 2: Terminate long-running transaction blockers

-- Kill sessions holding the XID horizon
SELECT pg_terminate_backend(12345);

Step 3: Drop inactive replication slots

-- Check if the slot is genuinely needed before dropping
SELECT slot_name, active, age(xmin) FROM pg_replication_slots;
SELECT pg_drop_replication_slot('stale_subscriber_slot');

Step 4: Run targeted VACUUM FREEZE on the worst tables

-- Freeze the tables with the highest XID age first
SET vacuum_cost_delay = 0;  -- Remove throttling for emergency
VACUUM FREEZE large_events_table;
VACUUM FREEZE user_sessions;
VACUUM FREEZE audit_log;
RESET vacuum_cost_delay;

-- Verify XID age decreased
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database WHERE datname = current_database();

Vacuuming without clearing blockers first is the most common mistake. The vacuum will complete, consuming hours of I/O, but the database XID age will not decrease because the horizon is still pinned by the blocker.

How to Prevent It

Set alerts on XID age at meaningful thresholds. Alert at 500 million (25% of capacity) for early warning, and at 1 billion (50%) for urgent action. The default autovacuum_freeze_max_age of 200 million triggers aggressive vacuum early, but only if autovacuum is not blocked.

Prevent blocker accumulation:

-- Auto-terminate forgotten sessions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
-- Monitor prepared transactions (should normally be empty)
SELECT count(*) FROM pg_prepared_xacts;
-- Check replication slot health daily
SELECT slot_name, active, age(xmin) FROM pg_replication_slots;

The key to preventing wraparound is not running VACUUM FREEZE harder -- it is ensuring nothing blocks autovacuum's freeze cycle from completing on schedule.

WAL and Vacuum Monitoring: The Two Metrics That Predict Every Outage

Philip McClarence — Thu, 02 Apr 2026 10:00:05 +0000

I used to think PostgreSQL outages were unpredictable. Then I started tracking two metrics consistently and realized that every single major outage I had seen was telegraphed days or weeks in advance by one of them. This article covers those two metrics, the SQL to track them, and what to do when they start trending in the wrong direction.

The Problem

The two most common PostgreSQL failure modes share a pattern: they are slow-moving, fully preventable, and invisible until the moment they are not.

Failure mode 1: Disk exhaustion. WAL segments accumulate because archiving falls behind or replication slots retain old WAL. Temporary files pile up from large sorts. The pg_wal directory grows from 1 GB to 50 GB over a weekend. Monday morning, the disk is full, the database cannot write WAL, and all transactions hang.

Failure mode 2: XID wraparound. Dead tuples accumulate because autovacuum is blocked, throttled, or misconfigured. Transaction ID age creeps from 100 million to 500 million to 1.5 billion. Eventually PostgreSQL refuses new transactions to prevent data corruption. The database becomes effectively read-only.

Both failures are predicted by the same two signals: WAL generation rate and vacuum health. If WAL generation is outpacing archive or cleanup, disk will eventually fill. If dead tuples are accumulating faster than vacuum removes them, XID wraparound is approaching. Both problems develop over days or weeks. Both produce no visible symptoms until the final failure -- no slow queries, no error messages, no degraded throughput. Just a sudden, total outage.

The challenge is not that these signals are hard to collect. It is that they require continuous monitoring with trend analysis. A snapshot query showing "WAL rate is 500 MB/hour" is meaningless without knowing whether that is normal for this workload or 5x higher than yesterday.

How to Detect It

WAL generation rate -- compare LSN positions over time:

-- Current WAL position (run twice with interval to calculate rate)
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 AS total_wal_mb;

-- Checkpoint frequency and timing
SELECT checkpoints_timed, checkpoints_req,
       buffers_checkpoint, buffers_backend,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

When checkpoints_req (forced checkpoints) is growing faster than checkpoints_timed (scheduled checkpoints), WAL is being generated faster than the configured checkpoint_timeout expects. This means larger I/O spikes during checkpoints and more WAL retained on disk.

Dead tuple accumulation -- the leading indicator for vacuum health:

-- Tables with the most dead tuples
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       last_autovacuum, last_autoanalyze,
       CASE WHEN n_live_tup > 0
           THEN round(100.0 * n_dead_tup / n_live_tup, 1) ELSE 0
       END AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 10;

-- Active autovacuum workers (saturation check)
SELECT count(*) AS active_workers,
       current_setting('autovacuum_max_workers')::int AS max_workers
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

If active workers consistently equals max workers, tables are queuing for vacuum. Dead tuples accumulate during the wait, and each vacuum run takes longer because there is more work to do -- a feedback loop that gets worse over time.

The fundamental limitation of these queries is that they show the current state, not the trend. You need to run them repeatedly, store the results, and compute deltas. That is monitoring infrastructure work that most teams skip until after the first outage.

Why Trends Matter More Than Snapshots

The real value in WAL and vacuum monitoring is not the current number -- it is the direction and rate of change. Here is what to watch for:

WAL generation rate doubling over a week signals a workload change or a configuration drift that will eventually exhaust disk
Dead tuple ratio climbing on a specific table means vacuum is falling behind on that table, even if overall vacuum health looks fine
Vacuum duration increasing over time means bloat is accumulating between runs and you need to trigger vacuum more frequently
All autovacuum workers busy for more than 10 consecutive minutes means tables are queuing and the backlog is growing

A proper monitoring setup collects these metrics at regular intervals and presents them as time series, so you can distinguish a one-time spike (a batch import) from a sustained trend (an application change generating more writes).

How to Fix It

WAL accumulation -- tune checkpoint and WAL configuration:

-- Increase max_wal_size to reduce forced checkpoints
ALTER SYSTEM SET max_wal_size = '4GB';
-- Spread checkpoint I/O over the full interval
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
-- Compress WAL to reduce disk usage and archive bandwidth
ALTER SYSTEM SET wal_compression = 'lz4';
SELECT pg_reload_conf();

If WAL accumulation is caused by a lagging replication slot, either fix the subscriber or drop the slot:

-- Check slot lag
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

Vacuum backlog -- per-table autovacuum tuning:

-- More aggressive vacuum on high-churn tables
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = 2,
    autovacuum_vacuum_cost_limit = 1000
);

-- Add more workers if all are consistently busy
-- In postgresql.conf (requires restart):
-- autovacuum_max_workers = 5

For immediate dead tuple cleanup, run a manual vacuum on the worst tables:

VACUUM (VERBOSE) events;
VACUUM (VERBOSE) sessions;

The VERBOSE flag outputs per-page statistics including pages scanned, tuples removed, and pages truncated -- confirming that vacuum is actually making progress.

How to Prevent It

Monitor both metrics continuously with alerts on trend, not just threshold. A WAL generation rate that doubles in a week is an alert-worthy event even if the absolute value is still within disk capacity. A dead tuple count that is climbing on a specific table is a signal even if XID age is still low.

Set concrete alert thresholds:

WAL: alert when generation rate exceeds 2x the 7-day average, or when pg_wal directory exceeds 50% of available disk
Vacuum: alert when any table's dead tuple ratio exceeds 20%, or when all autovacuum workers are busy for more than 10 consecutive minutes

The goal is not to react to outages -- it is to see them forming days in advance and intervene when the fix is a configuration change rather than an emergency recovery.

Wait Event Heatmaps: See What PostgreSQL Is Actually Waiting On

Philip McClarence — Wed, 01 Apr 2026 10:00:05 +0000

Wait Event Heatmaps: See What PostgreSQL Is Actually Waiting On

If you have ever stared at a Grafana dashboard full of green CPU and memory panels while your users complain about slow queries, you have experienced the fundamental blind spot of resource utilization monitoring. PostgreSQL has a built-in profiler that most teams completely ignore — wait events.

The Problem

Your database feels slow but the usual metrics look fine. CPU is at 30%. Memory is stable. Disk I/O graphs show nothing dramatic. The problem is invisible because you are looking at resource utilization metrics, which tell you how much of each resource is consumed but not what PostgreSQL is spending its time doing.

PostgreSQL tracks exactly what every backend is waiting on at any given moment. These are wait events, and they fall into categories: IO (reading or writing data files), Lock (heavyweight row or table locks), LWLock (lightweight internal locks like buffer mapping or WAL insertion), BufferPin (waiting for a shared buffer), Client (waiting for the application to send data or read results), and Activity (background process idle waits). When a backend is actively computing — executing an operator, sorting rows, evaluating expressions — there is no wait event at all.

Wait events are the closest thing PostgreSQL offers to a profiler. They tell you whether your database is slow because of I/O, lock contention, internal bottlenecks, or client-side delays. A database that shows 50% of sessions in IO:DataFileRead has a fundamentally different problem than one showing 50% in Lock:transactionid — the first needs better caching or faster storage, the second needs shorter transactions. Without wait event data, you are guessing at the root cause.

How to Detect It

Query pg_stat_activity to see what each active backend is currently waiting on:

-- Current wait events for all active sessions
SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    left(query, 80) AS query_snippet,
    now() - query_start AS query_duration
FROM pg_stat_activity
WHERE state = 'active'
    AND pid != pg_backend_pid()
ORDER BY wait_event_type, wait_event;

-- Aggregate current wait events by type
SELECT
    wait_event_type,
    wait_event,
    count(*) AS session_count
FROM pg_stat_activity
WHERE state = 'active'
    AND wait_event IS NOT NULL
ORDER BY count(*) DESC;

The problem with these queries is that wait events are transient. A session's wait event changes every millisecond — it reads a data page (IO:DataFileRead), processes it (no wait event), reads another page, acquires a buffer pin (BufferPin:BufferPin), and so on. A single-point query captures whatever is happening at that exact instant, which may or may not represent the actual bottleneck.

To build a meaningful picture, you need to sample repeatedly and aggregate. Some teams write cron jobs that query pg_stat_activity every second and log the results. This works, but building a sampling infrastructure, storing the time-series data, and visualizing the results is a significant investment.

Building a Wait Event Picture

The most effective way to use wait event data is as a heatmap over time. Each row represents a wait event category, and color intensity shows how many sessions were in that state at each point. This format reveals patterns that point-in-time queries cannot:

I/O spikes aligned with checkpoint intervals — If IO:DataFileRead spikes every 5 minutes, your checkpoint is too aggressive or shared_buffers is too small.
Lock contention correlated with batch jobs — Lock:transactionid spikes at the same time every day point to a batch process with long transactions.
LWLock pressure during peak writes — LWLock:WALInsert during high write throughput indicates WAL is a bottleneck.
Client waits indicating application issues — Client:ClientRead means PostgreSQL is waiting for your application, not the other way around.

Drilling into a specific wait event type to see which queries contribute the most wait time turns a vague "the database is slow" into a specific "this query on this table is causing 40% of all I/O waits."

How to Fix It

Each wait event category maps to a different class of fix:

IO:DataFileRead / IO:DataFileExtend — The database is reading data from disk instead of shared buffers, or extending table files during inserts.

-- Check buffer cache hit ratio
SELECT
    sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0)
        AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

If the hit ratio is below 99%, increase shared_buffers. If a specific query causes most of the reads, add an index to reduce the number of pages scanned. If the hit ratio is already high but I/O waits persist, the storage subsystem is the bottleneck — consider faster disks or moving to NVMe.

Lock:transactionid / Lock:tuple — Sessions are waiting for other transactions to commit or for row-level locks.

-- Find long-running transactions holding locks
SELECT pid, state, now() - xact_start AS transaction_duration,
       left(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start LIMIT 10;

Reduce transaction duration. Set idle_in_transaction_session_timeout to automatically kill forgotten sessions. Break large batch operations into smaller transactions.

LWLock:WALWrite / LWLock:WALInsert — WAL writing is a bottleneck, typically during heavy write workloads.

Move the WAL directory to faster storage. Increase wal_buffers. Tune checkpoint_completion_target toward 0.9 to spread checkpoint I/O more evenly.

Client:ClientRead / Client:ClientWrite — PostgreSQL is waiting for the application to send the next command or read the result. This is not a database problem — investigate network latency, connection pooler configuration, or application-side processing delays.

How to Prevent It

Establish wait event baselines for your workload. A healthy OLTP database typically shows minimal wait events during normal operation — most time is spent actively processing, not waiting. When a new wait event pattern appears or an existing one intensifies, it signals a change in workload or a developing bottleneck.

Sample wait events at regular intervals and retain the history. The value of wait event monitoring increases over time as you build context for what "normal" looks like. A 10% increase in IO:DataFileRead after a deployment points directly at a query change. A gradual increase in LWLock:BufferMapping over weeks suggests growing memory pressure as data volume increases.

Pair wait event monitoring with query-level analysis to close the loop — when a wait event spikes, drill through to the specific queries causing it, fix them, and verify the wait event subsides.