Forem: Philip McClarence

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.

Isolating the Query Type That's Killing Your Database

Philip McClarence — Tue, 31 Mar 2026 10:00:06 +0000

Isolating the Query Type That's Killing Your Database

When your PostgreSQL database is on fire at 2am, the last thing you want to be doing is scrolling through a flat list of 400 query templates trying to visually pattern-match which ones are SELECTs and which ones are UPDATEs. Yet that is exactly what most teams do — because pg_stat_statements gives you everything in one undifferentiated list.

The Problem

During an incident, the first question is: "Is this a read problem or a write problem?" The answer determines everything about your investigation path. If the database is slow because of a runaway SELECT scanning millions of rows without an index, you need to find that query and add an index. If it is slow because of a bulk INSERT generating massive WAL, you need to find the write and throttle it. Different causes, different fixes, different urgency.

pg_stat_statements shows everything in a flat list — hundreds of query templates sorted by total time or mean time. During an incident you are scrolling through SELECTs, INSERTs, UPDATEs, DELETEs, and utility statements mixed together. The query consuming the most total time might be a harmless high-frequency SELECT that runs millions of times per day. The actual problem might be an UPDATE that runs 50 times per hour but holds row locks for 2 seconds each time.

Without filtering by statement type, you are pattern-matching visually: scanning query text for keywords, mentally categorizing each entry, trying to spot the anomaly. This is slow and error-prone, especially under incident pressure.

Workload composition also matters outside of incidents. A database that shifts from 80% reads / 20% writes to 50/50 after a new feature launch is fundamentally different — the same hardware and configuration may no longer be adequate. But you cannot see this shift without statement type classification.

How to Detect It

You can manually classify queries by parsing the statement type from pg_stat_statements:

-- Group query statistics by statement type
SELECT
    CASE
        WHEN upper(left(ltrim(query), 6)) = 'SELECT' THEN 'SELECT'
        WHEN upper(left(ltrim(query), 6)) = 'INSERT' THEN 'INSERT'
        WHEN upper(left(ltrim(query), 6)) = 'UPDATE' THEN 'UPDATE'
        WHEN upper(left(ltrim(query), 6)) = 'DELETE' THEN 'DELETE'
        ELSE 'UTILITY'
    END AS statement_type,
    count(*) AS template_count,
    sum(calls) AS total_calls,
    round(sum(total_exec_time)::numeric, 2) AS total_time_ms,
    round(avg(mean_exec_time)::numeric, 2) AS avg_mean_time_ms
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY 1
ORDER BY total_time_ms DESC;

This gives you a workload breakdown, but it is a one-time snapshot with no trending. And the regex-based classification is fragile — it breaks on queries with leading comments, CTEs, or whitespace variations.

To identify the most expensive queries per type during an incident:

-- Top 5 most expensive SELECT queries by total time
SELECT queryid, substring(query, 1, 100) AS query_preview,
       calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ~* '^\s*SELECT'
  AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 5;

-- Top 5 most expensive INSERT/UPDATE/DELETE queries
SELECT queryid, substring(query, 1, 100) AS query_preview,
       calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ~* '^\s*(INSERT|UPDATE|DELETE)'
  AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 5;

This requires multiple queries, manual regex, and mental assembly. During an incident, every minute spent writing SQL is a minute not spent fixing the problem.

Building Statement Type Classification Into Your Workflow

The right approach is to classify every query template by statement type at collection time — not at query time during an incident. A proper classifier handles edge cases that simple regex misses: CTEs starting with WITH, queries with leading comments, and prepared statement variations.

With pre-classified queries, your incident triage workflow becomes:

Filter to the suspected statement type (SELECT for read latency, INSERT/UPDATE for write pressure)
Sort by total time to find the most expensive queries of that type
Sort by mean time to find the slowest individual executions
Sort by calls to identify high-frequency queries that might benefit from caching or batching

Combined with sortable columns, statement type filtering becomes a rapid triage tool. The workflow is: click the suspected type, sort by total time, and the top entry is your primary suspect. This takes seconds rather than the minutes required for manual SQL classification.

How to Fix It

Once you have isolated the statement type, each category has a distinct investigation path:

SELECT problems — reads consuming too much time:

-- Check for sequential scans on large tables
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 100 AND pg_relation_size(relid) > 10485760  -- > 10MB
ORDER BY seq_tup_read DESC;

Common fixes: add missing indexes, rewrite N+1 query patterns into JOINs, add LIMIT clauses to unbounded queries.

INSERT problems — writes overwhelming WAL:

-- Check WAL generation rate
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal;

-- Check for indexes slowing down inserts
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Common fixes: use COPY instead of row-by-row INSERT, drop unused indexes on write-heavy tables, batch inserts into larger transactions.

UPDATE/DELETE problems — lock contention:

-- Check for lock waits
SELECT blocked.pid, blocked.query, blocking.pid AS blocking_pid
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

Common fixes: add missing foreign key indexes (cascading deletes scan child tables), reduce transaction scope, avoid SELECT ... FOR UPDATE where SKIP LOCKED would work.

How to Prevent It

Track statement type distribution as a workload health indicator. Establish your baseline — a healthy ratio for your application — and alert when the mix shifts significantly. A sudden increase in DELETE percentage might indicate a runaway cleanup job, while a spike in UTILITY statements might signal excessive ANALYZE or VACUUM activity competing for resources.

Review workload composition after every major feature launch. A feature that adds a new UPDATE-heavy workflow to a previously read-dominated database changes the performance profile fundamentally. Index strategies, work_mem settings, and WAL configuration that worked for a read workload may need adjustment for a mixed workload.

Schema Diff: Catch Drift Between Staging and Production

Philip McClarence — Mon, 30 Mar 2026 10:00:06 +0000

Schema Diff: Catch Drift Between Staging and Production

Your migration tool says everything is applied. Your CI pipeline is green. But production has a column that staging does not, and nobody knows who added it or when. Sound familiar? Schema drift is the gap between what your migration history says should exist and what actually exists in the database.

The Problem

A deployment fails in production because a migration expects a column that exists in staging but not production. Someone added it manually during debugging weeks ago and forgot to create a proper migration. Or the deployment succeeds, but a query starts failing because the index it depends on was only created in the staging environment. Or a constraint was dropped in production to fix a data issue and never re-added.

Schema drift between environments is one of those problems everyone accumulates and nobody actively monitors. Each drift is individually small — a missing column, a different default value, an index present in one environment but not the other. But they compound. By the time you discover them, it is during a deployment or an incident, when the cost of surprise is highest.

The drift is not always between staging and production. Replicas can drift from their primary if DDL is applied directly to one node. Development databases diverge as engineers apply ad-hoc changes. Even within a single environment, a failed migration that partially applied can leave the schema in an inconsistent state — half the tables have the new column, half do not.

Migration tools like Flyway, Liquibase, and Alembic track what migrations were applied, but they do not verify the actual schema matches the expected state. A migration marked as "applied" in the history table says nothing about whether someone later modified the schema by hand.

How to Detect It

Comparing schemas manually means querying information_schema on both databases and diffing the results. Start with tables and columns:

-- Compare columns between two schemas
-- Run on EACH database and diff the output
SELECT
    table_name,
    column_name,
    data_type,
    column_default,
    is_nullable,
    character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Then indexes:

-- List all indexes with their definitions
SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Then constraints, functions, triggers, and sequences. Each object type requires its own query, its own export, and its own diff. For a schema with 50 tables, this is hundreds of rows to compare across six or more categories. You can script it, but maintaining the script and interpreting the diff requires ongoing effort.

The deeper problem is that nobody does this proactively. Schema comparison is a reactive activity — you do it after something breaks, not before. The gap between "last time someone checked" and "now" is where drift accumulates undetected.

A Practical Approach to Schema Comparison

Whether you build custom tooling or use a monitoring platform, an effective schema diff should categorize differences by type: missing tables, missing columns, different column types or defaults, missing indexes, missing constraints, and missing functions. Color-coding by severity helps with triage — a missing column that exists in production but not staging is a deployment risk, while a different column default may be intentional.

Even better, generate the exact ALTER statements needed to reconcile each difference. A missing column should produce ALTER TABLE ADD COLUMN. A different default should produce ALTER COLUMN SET DEFAULT. A missing index should produce CREATE INDEX CONCURRENTLY. This eliminates the manual step of writing migration SQL.

Safe patterns for generated statements include IF NOT EXISTS where supported, CONCURRENTLY for index operations, and explicit data types to avoid ambiguity.

How to Fix It

Walk through the most common drift scenarios and their fixes:

Missing column — A column exists in staging but not production:

-- Add the missing column with a safe default
ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS status_code integer DEFAULT 0;

Use IF NOT EXISTS (PostgreSQL 9.6+) so the statement is idempotent — safe to run even if someone already added the column manually.

Different default value — The column exists in both environments but defaults differ:

-- Align the default value
ALTER TABLE orders
    ALTER COLUMN status_code SET DEFAULT 1;

Missing index — An index exists in production but not staging (or vice versa):

-- Create the missing index without blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_code
    ON orders (status_code);

Always use CONCURRENTLY in production. A regular CREATE INDEX takes a ShareLock on the table, blocking all inserts and updates until the index is built.

Missing constraint — A foreign key or check constraint was dropped or never created:

-- Re-add a missing foreign key constraint
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order_id
    FOREIGN KEY (order_id) REFERENCES orders (order_id);

Adding a foreign key validates all existing rows, which can be slow on large tables. Use NOT VALID to add the constraint without validating existing data, then validate separately:

ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order_id
    FOREIGN KEY (order_id) REFERENCES orders (order_id)
    NOT VALID;

-- Validate existing rows separately (does not block writes)
ALTER TABLE order_items
    VALIDATE CONSTRAINT fk_order_items_order_id;

How to Prevent It

Run schema diff as part of your deployment pipeline. Before applying migrations to production, compare the production schema against what your migration tool expects. This catches manual changes that were applied outside the migration system — the single most common source of drift.

Compare your primary against each replica periodically. Physical replication keeps schemas in sync, but logical replication does not — DDL is not replicated by default in logical replication setups. If you use logical replication, schema drift between publisher and subscriber is almost guaranteed without explicit management.

Establish a monthly schema comparison between staging and production even when no deployment is planned. Drift accumulates in the gaps between deployments.

Treat schema as code, but verify the code matches reality. Migration history says what should have been applied. Schema diff tells you what actually exists. The gap between the two is where incidents start.

Interactive Replication Topology: See Your Cluster at a Glance

Philip McClarence — Sun, 29 Mar 2026 10:00:07 +0000

Interactive Replication Topology: See Your Cluster at a Glance

Replication lag is one of those metrics that everyone monitors as a single number — and that single number hides almost everything you need to know to actually fix the problem. Is the lag caused by network, storage, or CPU? The answer changes your remediation completely, and the default PostgreSQL views do not make it obvious.

The Problem

You manage a PostgreSQL primary with three read replicas. One of the replicas has a cascading standby attached to it. Replication lag is spiking and your application team is reporting stale reads.

To diagnose the problem, you need to answer several questions at once: Which replica is lagging? Is it write lag (the primary has sent WAL but the replica has not received it), flush lag (the replica received it but has not flushed to disk), or replay lag (flushed but not yet applied)? Is the cascading standby affected by the same lag, or is it independently lagging from its upstream replica? Are all replication slots active, or is an inactive slot causing WAL retention to balloon?

Answering these questions means connecting to each node separately. You run pg_stat_replication on the primary to see what it knows about each replica. You run pg_stat_wal_receiver on each replica to see the receiver's perspective. You check pg_replication_slots on the primary to verify slot health. Each query returns raw LSN positions and interval values that you mentally compare across nodes. With three replicas and a cascading standby, that is at least five separate connections and eight queries before you have a picture of the cluster state.

During an incident, this is too slow.

How to Detect It

On the primary, pg_stat_replication shows the current state of each connected standby:

-- Run on the PRIMARY: check replication lag to each standby
SELECT
    client_addr,
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication
ORDER BY client_addr;

On each replica, pg_stat_wal_receiver shows the receiver's perspective:

-- Run on each REPLICA: check WAL receiver status
SELECT
    status,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    slot_name,
    conninfo
FROM pg_stat_wal_receiver;

Check replication slot health — inactive slots prevent WAL recycling and cause disk usage to grow indefinitely:

-- Run on the PRIMARY: check replication slot health
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal_size,
    wal_status
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

The raw data is all there, but it is distributed across nodes, expressed in LSN positions that require mental arithmetic, and presented as flat rows with no visual representation of the topology.

Making Sense of the Data

When monitoring replication, the key is breaking total lag into its three components and understanding what each one means:

Write lag (time between primary sending WAL and replica receiving it) points to network issues between primary and replica.
Flush lag (time between receiving and flushing to disk) suggests slow storage on the replica.
Replay lag (time between flushing and applying) means the replica is CPU-bound on WAL replay — common when the primary runs many parallel writes but the replica applies them single-threaded.

Trend lines matter more than point-in-time values. Is lag stable, growing, or recovering? A stable 500ms of replay lag is normal for a busy system. A replay lag that grows 100ms every minute is a replica falling behind that will eventually become unusable for reads.

For cascading standbys, check whether lag is inherited from the upstream replica or independent. If the upstream replica has 3 seconds of replay lag and the cascading standby has 3.5 seconds, the standby is fine — it is just inheriting the upstream lag plus 500ms of its own.

How to Fix It

The fix depends on which lag component is elevated:

High write lag (network) — Check bandwidth and latency between primary and replica. Verify wal_sender_timeout and wal_receiver_timeout are not too aggressive for your network conditions. For cross-region replication, ensure adequate bandwidth for your WAL generation rate.

High flush lag (storage) — The replica's storage cannot keep up with incoming WAL. Move the replica's WAL directory to faster storage (NVMe). Check for I/O contention from queries running on the replica.

High replay lag (CPU) — The replica is applying WAL single-threaded and falling behind. In PostgreSQL 15+, enable parallel apply with max_parallel_apply_workers_per_subscription for logical replication. For physical replication, reduce the write rate on the primary during peak times or add more replicas to distribute read load.

Inactive replication slots retaining WAL indefinitely:

-- Drop an inactive slot that is retaining excessive WAL
SELECT pg_drop_replication_slot('stale_replica_slot');

-- Or set a maximum WAL retention size (PG 13+)
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

Cascading standby disconnected — Check primary_conninfo on the cascading standby. Verify the upstream replica allows replication connections (max_wal_senders not exhausted, pg_hba.conf permits the connection).

How to Prevent It

Monitor all three lag types separately — write, flush, and replay. Total replication lag hides the root cause. A replica showing 5 seconds of total lag could be a network problem, a storage problem, or a CPU problem, and each requires a different fix.

Set max_slot_wal_keep_size to prevent inactive slots from consuming all available disk. Without this setting, a single inactive slot will retain WAL indefinitely until the primary runs out of disk space and stops accepting writes.

Alert on replication slot inactivity. A slot that becomes inactive usually means a replica has disconnected — either crashed, lost network connectivity, or was decommissioned without dropping its slot.

Catching a lagging replica at 2 seconds of delay is routine maintenance; catching it at 2 hours of delay is an incident.

From $1 Placeholders to Executable Queries: Debugging the Parameters That Matter

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

From $1 Placeholders to Executable Queries: Debugging the Parameters That Matter

Every PostgreSQL DBA has been in this situation: a query shows up as slow in pg_stat_statements, but the template uses $1 placeholders and you have no idea which parameter values actually caused the slow execution. The query text is WHERE customer_id = $1 — but which customer? The one with 15 orders or the one with 500,000? Without the actual values, you cannot reproduce the problem.

The Problem

pg_stat_statements is the foundation of PostgreSQL query performance monitoring. It aggregates execution statistics per query template, grouping all executions of SELECT * FROM orders WHERE customer_id = $1 regardless of the parameter value. This is exactly what you want for identifying expensive query patterns — but it hides a critical detail.

A query might average 5ms across 100,000 calls but take 12 seconds for one specific customer. The customer with ID 847291 has 500,000 orders while the median customer has 15. The index scan that works perfectly for most customers degrades to near-sequential-scan performance for this outlier. The average hides the catastrophe.

You cannot reproduce this problem from pg_stat_statements alone. You see WHERE customer_id = $1 and the mean time, but not the parameter value that caused the outlier. Without the actual parameter values, you cannot run EXPLAIN ANALYZE to see what the planner does for that specific input.

The standard workaround is to search PostgreSQL logs. If log_min_duration_statement is configured, slow queries are logged with their parameters in a DETAIL line. But parsing log files to extract parameters, correlate them with query templates, and reconstruct executable queries is manual, tedious, and error-prone — exactly the kind of work that gets skipped during a production incident.

How to Detect It

PostgreSQL can log slow queries with their parameter values, but you need the right configuration:

-- Check if slow query logging is enabled
SHOW log_min_duration_statement;
-- -1 means disabled, 0 logs everything, positive value is milliseconds

-- Enable logging of queries slower than 500ms
ALTER SYSTEM SET log_min_duration_statement = 500;
SELECT pg_reload_conf();

When enabled, slow queries appear in the PostgreSQL log with a DETAIL line containing the parameter values:

LOG:  duration: 12847.234 ms  execute :
      SELECT * FROM orders WHERE customer_id = $1 AND order_date > $2
DETAIL:  parameters: $1 = '847291', $2 = '2020-01-01'

To reconstruct an executable query from this, you must manually substitute each parameter:

-- Manually reconstructed from log parsing
SELECT * FROM orders
WHERE customer_id = '847291'
  AND order_date > '2020-01-01';

This works for one query. For systematic analysis, you would need to parse the log files programmatically, match DETAIL lines to their query statements, and build executable queries for each slow execution. Most teams lack this tooling, so parameter values are effectively lost.

Another approach is auto_explain, which logs the EXPLAIN plan for slow queries. This gives you the plan but still not the executable query you can paste into psql to reproduce:

-- Enable auto_explain for queries over 1 second
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;
SET auto_explain.log_analyze = true;

Automating Parameter Extraction

The ideal solution is a monitoring tool that parses PostgreSQL logs automatically, extracts DETAIL parameters, and substitutes them into query templates to produce executable queries. This turns a tedious manual process into an automated pipeline.

With automated extraction, each query template accumulates a collection of real-world parameter samples. Templates with zero executable samples use $N placeholders that cannot be run directly. Templates with samples have actual parameter values from the logs. The higher the sample count, the more diverse the parameter combinations available for testing.

This is particularly valuable for queries where performance varies dramatically based on input. You can test with the actual values that caused slow executions — not guessed values or synthetic test data, but the real parameters from production.

The workflow becomes: find a slow query template, click into its samples, copy the executable query with one click, and paste it into psql with EXPLAIN (ANALYZE, BUFFERS) to see exactly what the planner does for that specific parameter combination.

How to Fix It

Once you have an executable query with the problematic parameters, diagnose the plan:

-- Run EXPLAIN ANALYZE with the exact parameters from the sample
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = '847291'
  AND order_date > '2020-01-01';

Compare this plan to the same query with a typical parameter value:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = '12345'
  AND order_date > '2020-01-01';

Common findings:

Skewed data distribution: Customer 847291 has 500,000 rows, the planner estimates 15 (the average). Fix with extended statistics or a partial index:

-- Extended statistics for correlated columns
CREATE STATISTICS orders_customer_date_stats
    ON customer_id, order_date FROM orders;
ANALYZE orders;

-- Partial index for high-volume customers
CREATE INDEX CONCURRENTLY idx_orders_high_volume
    ON orders (customer_id, order_date)
    WHERE customer_id IN (SELECT customer_id FROM orders
                          GROUP BY customer_id HAVING count(*) > 10000);

Date range too wide: $2 = '2020-01-01' pulls 5 years of data. The application may need pagination or the query needs a tighter default range.

Generic plan vs. custom plan: On PostgreSQL 12+, after 5 executions the planner may switch to a generic plan that is suboptimal for outlier parameter values. Check with plan_cache_mode = force_custom_plan as a diagnostic.

How to Prevent It

Ensure log_min_duration_statement is set to a reasonable threshold — 500ms to 1000ms is a good starting point. Too low floods the logs, too high misses important slow queries. This single setting is the prerequisite for parameter extraction; without it, no tool can recover parameter values from normalized templates.

Set log_parameter_max_length to at least 1024 (PostgreSQL 13+) to ensure long parameter values are not truncated in the log DETAIL line. Truncated parameters produce incomplete executable queries.

Review captured samples periodically to identify parameter patterns that consistently cause slow execution. If the same customer ID or date range appears across multiple slow samples, the problem is data skew rather than a missing index.