DEV Community: Philip McClarence

pg_cron Tutorial: Schedule Postgres Jobs and Monitor Them

Philip McClarence — Tue, 11 Aug 2026 10:00:12 +0000

TL;DR

pg_cron is an open-source Postgres extension (Citus Data, now Microsoft), PostgreSQL-licensed, at github.com/citusdata/pg_cron. A background worker wakes every minute and runs SQL on a five-field cron schedule.
Install is three lines and a restart: shared_preload_libraries = 'pg_cron', restart, CREATE EXTENSION pg_cron;.
First three jobs worth scheduling: retention (batched delete or partition drop), partition maintenance, materialized view refresh. A fourth, the hourly rollup, replaces a surprising amount of pipeline tooling.
You do not need to build your own job logging. cron.log_run is on by default and every run lands in cron.job_run_details.
Four things that bite: schedules are UTC, cron.database_name needs a restart, cron.job_run_details grows forever, and VACUUM fails inside a multi-statement job body.
I covered this in a 5-minute whiteboard video: https://www.youtube.com/watch?v=ku7K7lusTuY. This post is the written, deeper version with SQL you can actually paste.

📖 Read the full guide: pg_cron Monitoring: Failures, Staleness, and Bloat

Why schedule inside the database at all

The default answer is a line in the system crontab calling psql. It works until it doesn't. You now have credentials on a box, a job that keeps firing happily against a demoted standby, and job history in a log file that nobody has read since the person who wrote it left.

The other default answer is Airflow. Airflow is genuinely good at cross-system pipelines with dependencies and retries. For "delete rows older than 90 days" it is a second production system to patch.

pg_cron's pitch is narrow and I like it: the schedule is data in a table, it travels with the cluster under physical replication, and the run history is queryable with the same SQL and dashboards you already point at everything else.

Where pg_cron is the wrong tool: multi-system DAGs, task dependencies, retries with exponential backoff, anything that needs to touch S3 and Postgres in one transactional-ish flow. It has no dependency graph. If you find yourself encoding one in cron expressions, stop.

Install: four lines, and the two settings people get wrong

pg_cron runs as a background worker, so it has to be preloaded before CREATE EXTENSION will work.

ALTER SYSTEM SET shared_preload_libraries = 'pg_cron';
-- restart the server here. Not reload. Restart.
CREATE EXTENSION pg_cron;

Setting one people get wrong: cron.database_name. The pg_cron worker connects to exactly one database, defaulting to postgres, and changing it requires a restart. If your app lives in appdb and you installed the extension there but never changed the setting, nothing runs and nothing errors visibly.

For multi-database clusters, leave the worker where it is and use cron.schedule_in_database() (pg_cron 1.4+):

SELECT cron.schedule_in_database(
  'purge-appdb-events', '17 3 * * *',
  $$SELECT app.purge_old_events()$$,
  'appdb', 'app_maint', true);

Setting two: cron.use_background_workers. By default jobs run over a libpq connection to cron.host (localhost), which eats a regular max_connections slot. Turn background workers on and they draw from max_worker_processes instead. Either way, size the pool. cron.max_running_jobs caps concurrency.

Platform	How to set it
Amazon RDS / Aurora	DB parameter group: `shared_preload_libraries`, `cron.database_name`
Azure Flexible Server	Server parameters blade, then `CREATE EXTENSION`
Cloud SQL	`cloudsql.enable_pg_cron` flag

How it actually runs your SQL

The worker ticks once a minute, reads cron.job, and launches whatever is due. One-minute granularity is the floor. There is no sub-minute scheduling and no catch-up for missed ticks.

A running job looks like any other backend:

SELECT pid, usename, state, now() - query_start AS runtime, left(query, 60)
FROM pg_stat_activity WHERE application_name LIKE 'pg_cron%' OR query LIKE '%purge_old%';

  pid  | usename   | state  |    runtime      |                    left
-------+-----------+--------+-----------------+---------------------------------------------
 20481 | app_maint | active | 00:22:14.881203 | SELECT app.purge_old_events()

Kill it with SELECT pg_cancel_backend(20481); and it will show up as failed in the run history, which is what you want.

Job 1: retention that doesn't lock the table for 40 minutes

The naive version, straight from the video:

SELECT cron.schedule('nightly-purge', '0 2 * * *',
  $$DELETE FROM events WHERE created_at &lt; now() - interval '90 days'$$);

 schedule
----------
        4

That returns a bigint jobid. Fine for a small table. On 200 million rows it holds one enormous transaction, bloats WAL, and leaves dead tuples for autovacuum to chew through. Deleted space is not returned to the OS, it becomes reusable only after vacuum.

Batch it:

CREATE OR REPLACE FUNCTION app.purge_old_events(p_batch int DEFAULT 10000)
RETURNS bigint LANGUAGE plpgsql
SET statement_timeout = '30min'
SET lock_timeout = '5s'
AS $$
DECLARE removed bigint := 0; n int;
BEGIN
  LOOP
    DELETE FROM events WHERE ctid IN (
      SELECT ctid FROM events
      WHERE created_at &lt; now() - interval '90 days'
      LIMIT p_batch);
    GET DIAGNOSTICS n = ROW_COUNT;
    removed := removed + n;
    EXIT WHEN n = 0;
    COMMIT;
  END LOOP;
  RETURN removed;
END $$;

Better still: partition by time and drop the old partition. That is a catalog and file operation, not a row-by-row scan, and it leaves nothing for autovacuum. Retention by DROP PARTITION beats retention by DELETE every time you can arrange it — this is the pattern behind most postgres data retention jobs that actually scale.

Inspect and remove jobs:

SELECT jobid, schedule, jobname, active FROM cron.job;

 jobid | schedule  |     jobname      | active
-------+-----------+------------------+--------
     4 | 0 2 * * * | nightly-purge    | t
     7 | */5 * * * *| refresh-daily-mv | t

SELECT cron.unschedule(4); or SELECT cron.unschedule('nightly-purge');.

Job 2: partition maintenance (pg_partman does this better than you will)

Hand-rolled works: a function that creates next month's partition and detaches plus drops anything past retention, scheduled 0 3 1 * *. Use ALTER TABLE ... DETACH PARTITION CONCURRENTLY on PG14+ so you aren't holding ACCESS EXCLUSIVE on the parent while a reporting query finishes.

The answer most shops land on is pg_partman, whose documented model is to call run_maintenance_proc() on a schedule:

SELECT cron.schedule('partman-maintenance', '@hourly',
  $$CALL partman.run_maintenance_proc()$$);

That single line handles future partition creation and retention.

Job 3: schedule refresh materialized view postgres without blocking readers

REFRESH MATERIALIZED VIEW CONCURRENTLY lets SELECTs keep running against the view during the refresh. Two prerequisites people forget: it needs at least one UNIQUE index covering all rows, and it will not work on a view that has never been populated.

CREATE UNIQUE INDEX ON reporting.daily_sales (sale_date, region);
REFRESH MATERIALIZED VIEW reporting.daily_sales;  -- populate once, non-concurrently

SELECT cron.schedule('refresh-daily-sales', '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_sales$$);

CONCURRENTLY is not free. It does a diff against the existing contents and can be dramatically slower on large views. Time both forms before you commit.

SELECT max(sale_date), now() - max(sale_date) AS staleness
FROM reporting.daily_sales;

Wrap job bodies in functions

Put the job body in a function. Inline SQL in cron.job is how you end up with a 400-character DELETE that nobody can read, nobody can test, and nobody can find in version control.

The SET clause on CREATE FUNCTION pins execution settings for the duration of the call, which makes it the cleanest place to bound a scheduled job. Add an advisory lock for anything you never want overlapping with itself or with a manual run — a fifteen-minute job scheduled every ten minutes will eventually stack two copies on top of each other without one:

CREATE OR REPLACE FUNCTION app.rollup_hourly() RETURNS void LANGUAGE plpgsql
SET statement_timeout = '10min'
SET lock_timeout = '5s'
SET application_name = 'cron:rollup_hourly'
AS $$
BEGIN
  IF NOT pg_try_advisory_lock(88101) THEN
    RAISE NOTICE 'rollup already running, skipping'; RETURN;
  END IF;

  INSERT INTO events_hourly (bucket, event_type, n)
  SELECT date_trunc('hour', created_at), event_type, count(*)
  FROM events
  WHERE created_at &gt;= (SELECT coalesce(max(bucket), '-infinity') FROM events_hourly)
  GROUP BY 1, 2
  ON CONFLICT (bucket, event_type) DO UPDATE SET n = EXCLUDED.n;

  PERFORM pg_advisory_unlock(88101);
END $$;

That watermark makes it idempotent and safely re-runnable. It is an entire ETL pipeline, and you don't need Airflow for it yet.

pg_cron monitoring: cron.job_run_details is your job log

The most common misconception I run into is that pg_cron gives you no visibility and you have to build your own logging table. Not true. cron.log_run is on by default, and every run is recorded in cron.job_run_details with jobid, runid, job_pid, database, username, command, status, return_message, start_time and end_time.

SELECT jobid, runid, status, return_message, start_time
FROM cron.job_run_details WHERE status = 'failed' ORDER BY start_time DESC LIMIT 1;

 jobid | runid | status |                    return_message                     |         start_time
-------+-------+--------+-------------------------------------------------------+----------------------------
    11 | 90422 | failed | ERROR: VACUUM cannot run inside a transaction block    | 2026-08-09 06:00:00.114+00

Failures in the last 24 hours:

SELECT j.jobname, d.status, d.return_message, d.start_time
FROM cron.job_run_details d JOIN cron.job j USING (jobid)
WHERE d.start_time &gt; now() - interval '24 hours' AND d.status &lt;&gt; 'succeeded'
ORDER BY d.start_time DESC;

Slowest jobs, p95:

SELECT jobid,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY end_time - start_time) AS p95
FROM cron.job_run_details
WHERE start_time &gt; now() - interval '7 days' GROUP BY jobid ORDER BY p95 DESC;

Stale jobs, meaning scheduled but not succeeding:

CREATE VIEW ops.stale_cron_jobs AS
SELECT j.jobid, j.jobname, j.schedule, max(d.end_time) FILTER (WHERE d.status='succeeded') AS last_ok
FROM cron.job j LEFT JOIN cron.job_run_details d USING (jobid)
WHERE j.active GROUP BY 1,2,3
HAVING coalesce(max(d.end_time) FILTER (WHERE d.status='succeeded'), '-infinity') &lt; now() - interval '25 hours';

Now the mandatory part. Nothing prunes cron.job_run_details. The README says so plainly, and I once inherited a cluster where it had reached the low millions of rows and was the largest table in the maintenance database — the nightly backup job was slower because it was scanning that table too. Schedule the prune before you schedule anything else.

SELECT cron.schedule('purge-cron-history', '5 4 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time &lt; now() - interval '30 days'$$);

If you don't want history at all, set cron.log_run = off, though you lose the audit trail along with it. cron.log_statement additionally writes the command to the server log if you want it there too.

Gotchas I've been bitten by

Schedules are UTC. Not server local time, not your session TimeZone. 0 2 * * * fires at 02:00 UTC, which is 21:00 the previous day in America/New_York during EDT and 22:00 during EST. My "2am maintenance window" ran during Tuesday evening peak for three weeks before anyone connected the dots. Write the UTC offset into the job name if it helps.

VACUUM in a multi-statement body fails. VACUUM ANALYZE t alone is fine. ANALYZE a; VACUUM b; in one job body runs as one implicit transaction and errors with VACUUM cannot run inside a transaction block. One statement per job.

Long jobs don't double-fire. Schedule SELECT pg_sleep(300) every minute and verify for yourself; you get one run, not five.

Jobs run as the role that scheduled them. Schedule as a dedicated maintenance role with the grants it needs. Since 1.4 a superuser can GRANT USAGE ON SCHEMA cron to a non-superuser so that role manages its own jobs.

The extension lives in one database. See cron.database_name above.

Standbys are read-only. cron.job and cron.job_run_details replicate physically, but only the primary executes. After a failover, re-verify shared_preload_libraries and cron.database_name on the promoted node — a failover with no shared_preload_libraries entry on the new primary means silence, not an error. I keep that as an explicit step in the runbook.

Errors are invisible unless someone queries for them. Point your alerting at the failures query. pg_cron will not page you.

When to reach for something else

Tool	Dependencies	Retries	Cross-system	Failover aware	Observability
pg_cron	no	no	no	follows the primary	SQL over `job_run_details`
cron + psql	no	no	yes	no	log files
pgAgent	steps, sequential	limited	shell steps	no	its own tables
pg_timetable	chains	yes	shell/HTTP	no	its own tables
Airflow / Dagster	full DAG	yes, backoff	yes	external	rich UI
K8s CronJob	no	pod-level	yes	external	cluster logs

The starter kit

-- postgresql.conf: shared_preload_libraries = 'pg_cron', cron.database_name = 'appdb'; restart
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 1. history prune. Do this first, always.
SELECT cron.schedule('purge-cron-history', '5 4 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time &lt; now() - interval '30 days'$$);

-- 2. retention (function from above), 02:00 UTC
SELECT cron.schedule('purge-old-events', '0 2 * * *', $$SELECT app.purge_old_events()$$);

-- 3. matview refresh, needs a unique index and one prior non-concurrent refresh
SELECT cron.schedule('refresh-daily-sales', '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_sales$$);

-- 4. hourly rollup
SELECT cron.schedule('rollup-hourly', '7 * * * *', $$SELECT app.rollup_hourly()$$);

-- 5. monitoring
CREATE OR REPLACE VIEW ops.cron_failures_24h AS
SELECT j.jobname, d.status, d.return_message, d.start_time
FROM cron.job_run_details d JOIN cron.job j USING (jobid)
WHERE d.start_time &gt; now() - interval '24 hours' AND d.status &lt;&gt; 'succeeded';

SELECT * FROM ops.cron_failures_24h;

If you'd rather not build these dashboards yourself, tools like MyDBA (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cron-tutorial-schedule-monitor-postgres-jobs) wrap this kind of cron.job_run_details monitoring into something you can hand to the team.

What is the worst thing you have scheduled in cron.job? Mine involved a 400-character DELETE and a comma in the wrong place.

Tags: postgres database sql devops ## Where this leaves you

pg_cron won't build you a DAG, retry a failed API call, or coordinate a multi-service pipeline — and it shouldn't try to. What it does is take the boring 80% of database housekeeping (retention, partition rotation, refreshes, rollups) and keep it exactly where that logic belongs: in the database, versioned as functions, visible in cron.job_run_details, replicated with the cluster. Start with the four jobs above, prune your own history table before you forget, and point something at the failures view so you find out about a broken job before your users do.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cron-tutorial-schedule-monitor-postgres-jobs

If you're already scheduling jobs with pg_cron, give MyDBA a look — it'll flag stale jobs, bloated cron.job_run_details tables, and the other things this post just told you to watch for, without you writing the queries by hand.

Aurora PostgreSQL Storage Model: What Actually Changed

Philip McClarence — Mon, 10 Aug 2026 10:00:13 +0000

Short answer: Aurora PostgreSQL never writes heap or index pages to durable storage — only redo log records — so roughly half of your checkpoint and replication tuning playbook is dead weight. The other half (vacuum, work_mem, connection limits) matters exactly as much as it always did.

📖 Read the full guide: Aurora PostgreSQL Storage Model: What Actually Changes

I've watched teams port a checkpoint-tuning runbook to Aurora and spend a week chasing a parameter that does nothing. They opened a ticket about checkpoint_completion_target and argued over max_wal_size while the real problem was an autovacuum that hadn't finished on a 400 GB table since the last deploy.

The confusion is understandable. Aurora PostgreSQL speaks the wire protocol, runs the same planner, has the same MVCC semantics, and answers SELECT version() in a way that looks familiar. Underneath, the durability layer is different software entirely — and that gap is where most Aurora vs PostgreSQL architecture confusion comes from.

There's a companion video that walks through the architecture visually in about ten minutes: https://www.youtube.com/watch?v=GTFHE5KJSag. This post goes further, with the SQL you'd actually run to check any of it.

TL;DR

The Aurora writer sends redo log records to storage. It does not write heap or index pages to durable storage.
Storage nodes apply the log and materialize page versions themselves, across six copies in three AZs.
Checkpoints stop being an I/O event you can feel, so pg_stat_bgwriter dashboards go quiet and stay quiet.
Readers attach to the same volume as the writer, so "replica lag" measures cache application, not WAL replay into a second copy of your data.
Roughly half of a classic tuning playbook is inert. The half that still matters (vacuum, wraparound, work_mem, connections) matters exactly as much as it always did.

Aurora vs PostgreSQL Architecture: The Vanilla Write Path

On a self-managed box: a backend dirties a page in shared_buffers, writes a WAL record into pg_wal, and fsyncs that WAL at commit. If this is the first touch of the page since the last checkpoint, full_page_writes=on stuffs a full 8 KB image of the page into the WAL stream, because an 8 KB write isn't atomic against an OS or hardware crash. The checkpointer periodically flushes dirty buffers out to the data files, governed by checkpoint_timeout, max_wal_size, and checkpoint_completion_target. The background writer trickles in between. A physical streaming replica receives the WAL and replays it into its own complete copy of every data file. Ask anyone who's watched iostat spike the moment checkpoint starting: time shows up in the logs — that's a real, feelable I/O event.

Two things to hold onto: there are two full copies of the data, and the checkpoint is a real I/O event with a real cost you tune away from peak traffic.

What the Aurora PostgreSQL Storage Model Does Instead

The Aurora paper's phrase is "the log is the database" (Verbitski et al., SIGMOD 2017). The database tier ships redo log records to the storage tier. Storage nodes apply those records to their own page versions, in the background and on demand when a read needs a version that hasn't been materialized yet.

The cluster volume keeps six copies across three Availability Zones, with a 4-of-6 write quorum and a 3-of-6 read quorum, sliced into 10 GB protection groups. The volume autoscales up to 128 TiB for Aurora PostgreSQL.

Because only log records cross the network, network amplification drops sharply — the paper reports roughly a 7.7x reduction in network IOs per transaction against a mirrored-MySQL configuration in their sysbench comparison. That figure is from the MySQL-flavored benchmark, not a PostgreSQL measurement, so treat it as directional rather than a number to quote in a capacity plan.

What's published: the quorum scheme, the segment size, the log-only write path. What's inference on my part: exactly when a given storage node decides to materialize a page version versus serve it from the log chain. AWS doesn't document that scheduling, and it doesn't matter much operationally, but I'd rather flag it than pretend.

Consequence 1: Aurora Checkpoints Stop Being an Event

If the writer never flushes dirty data pages to durable storage, the parameters that throttle that flush have lost their job. Here's a version-portable query, because PostgreSQL 17 moved the checkpoint counters out of pg_stat_bgwriter into pg_stat_checkpointer, and which columns Aurora exposes depends on the engine version you're running.

DO $$
DECLARE rec record; q text;
BEGIN
  IF EXISTS (SELECT 1 FROM pg_views WHERE viewname = 'pg_stat_checkpointer') THEN
    q := $q$SELECT num_timed, num_requested, write_time, sync_time,
                   buffers_written, stats_reset FROM pg_stat_checkpointer$q$;
  ELSE
    q := $q$SELECT checkpoints_timed   AS num_timed,
                   checkpoints_req     AS num_requested,
                   checkpoint_write_time AS write_time,
                   checkpoint_sync_time  AS sync_time,
                   buffers_checkpoint    AS buffers_written,
                   stats_reset FROM pg_stat_bgwriter$q$;
  END IF;
  FOR rec IN EXECUTE q LOOP
    RAISE NOTICE 'timed=% req=% write_ms=% sync_ms=% buffers=% since=%',
      rec.num_timed, rec.num_requested, rec.write_time,
      rec.sync_time, rec.buffers_written, rec.stats_reset;
  END LOOP;
END $$;

Self-managed box under a steady OLTP load, one week of uptime:

NOTICE:  timed=2016 req=143 write_ms=41883204 sync_ms=98771 buffers=118442901 since=2026-08-02 04:11:07+00

Aurora writer, comparable workload and uptime:

NOTICE:  timed=2016 req=0 write_ms=612 sync_ms=0 buffers=1174 since=2026-08-02 04:09:55+00

The timed count still ticks because the checkpointer process still runs. The buffer and write-time numbers are effectively noise. If you have a Grafana panel plotting buffers_checkpoint rate or checkpoint_write_time as your write-pressure signal, that panel is lying to you — pg_stat_bgwriter on Aurora just doesn't carry the same meaning. Delete it and watch WriteIOPS, WriteLatency, and CommitLatency in CloudWatch instead.

Consequence 2: full_page_writes Isn't Yours

SHOW full_page_writes;
 full_page_writes
------------------
 off

Storage handles page materialization, so torn pages aren't a risk the writer has to insure against by doubling its WAL volume after every checkpoint. Try to set it in a cluster parameter group and you'll find it's not a modifiable parameter. This is also why WAL volume per transaction on Aurora looks different from a vanilla instance running the same workload — no periodic full-page-image spike after each checkpoint.

Consequence 3: Replicas Are Compute, Not Copies

Up to 15 Aurora Replicas attach to the same cluster volume as the writer. No pg_basebackup. No base backup transfer. No second physical copy of your data. Adding a reader is provisioning an instance and pointing it at storage.

The reader still consumes the log stream, but only to keep its own buffer cache and in-memory structures consistent. A page the writer just modified can be served correctly by a reader only after the reader applies the relevant record. That's why read-after-write against a reader endpoint is typically low single-digit milliseconds rather than zero, and why routing a read-your-own-write flow to the reader endpoint produces the occasional bug report you'll spend two days reproducing.

Consequence 4: Measuring Aurora Replica Lag Correctly

pg_stat_replication on an Aurora writer doesn't enumerate Aurora Replicas the way it enumerates streaming standbys, because they aren't streaming standbys. Dashboards built on it silently render an empty panel, which people misread as "no lag."

Use the Aurora function on the writer:

SELECT server_id, session_id, highest_lsn_rcvd,
       cur_replica_lag_in_msec, last_update_timestamp
FROM aurora_replica_status();

   server_id    |              session_id              | highest_lsn_rcvd | cur_replica_lag_in_msec |    last_update_timestamp
----------------+--------------------------------------+------------------+-------------------------+---------------------------
 prod-writer-1  | MASTER_SESSION_ID                    |     412998877123 |                         | 2026-08-09 11:42:18.204+00
 prod-reader-1a | 8f2c1e77-3a4b-4e19-9d02-1c6a4e2f8b33 |     412998877098 |                    3.12 | 2026-08-09 11:42:18.199+00
 prod-reader-1b | 2b91f043-7c55-41aa-8f6e-90b7c1d4a207 |     412998876940 |                   11.48 | 2026-08-09 11:42:18.201+00

On the reader itself, these still work and are worth keeping in your checks:

SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

For alerting, AuroraReplicaLag and AuroraReplicaLagMaximum in CloudWatch are the metrics to page on. Both are in milliseconds — not the seconds you're used to from pg_stat_replication lag columns — so set thresholds accordingly or you'll never fire.

Consequence 5: Aurora Failover and Recovery

With at least one reader present, failover is a promotion plus a cluster-endpoint DNS change. There's no replay of WAL from a checkpoint into a local heap, because storage already holds durable, materializable pages.

The honest version of the numbers: promotion is fast, and then you wait on DNS TTL, a connection storm from every app pod reconnecting at once, and a cold buffer cache on the new writer serving your worst queries. Failover tiers (0 highest through 15, instance size as tiebreaker) let you control which reader gets promoted. RDS Proxy is the other lever that meaningfully cuts observed downtime, because it absorbs the reconnect stampede.

A single-instance cluster with no reader doesn't get promotion. Aurora recreates or restarts the writer, which takes considerably longer. If your production cluster is one instance, you don't have HA — you have a good backup story.

Aurora vs Vanilla PostgreSQL: Key Differences

Concern	Vanilla PostgreSQL	Aurora PostgreSQL
Writes to durable storage	WAL plus dirty data pages	Redo log records only
Checkpoints	Real I/O event, tuned via `checkpoint_timeout`, `max_wal_size`, `checkpoint_completion_target`	Metadata-ish; counters near-flat
`full_page_writes`	`on`, doubles WAL after each checkpoint	Storage-managed, not modifiable
Replica data	Own full copy of every file	Same shared cluster volume
Replica build	`pg_basebackup` / base backup transfer	Provision an instance, minutes
Lag semantics	WAL replay lag, seconds	Cache application lag, milliseconds
Failover	Manual or Patroni-style promotion + fencing	Reader promotion + endpoint DNS, tier-controlled
Backup	`pg_basebackup`, pgBackRest, `archive_command`	Continuous storage-level, PITR to a new cluster

What Still Matters

VACUUM, bloat, and transaction ID wraparound behave identically on Aurora PostgreSQL. MVCC, dead tuples, table and index bloat, autovacuum tuning, and wraparound are engine-layer concerns, not storage-layer ones. Storage magic does not save you from wraparound. I've seen an Aurora cluster inside 12 million transactions of a forced shutdown, and the storage architecture contributed nothing to the fix. Keep an eye on age(datfrozenxid) the same way you would on a self-managed box — "managed" doesn't mean "unmonitored."

Things worth your time:

shared_buffers. Aurora's default parameter group sets it as a formula tied to DBInstanceClassMemory, landing around 75%, far above the ~25% heuristic for self-managed. That's deliberate: there's no OS page cache doing useful double-buffering the way there is on a normal box. Don't "fix" it down to 25% out of habit.
work_mem, per-connection and per-node, same math as always.
max_connections plus a real pooler.
Autovacuum: autovacuum_vacuum_cost_limit, autovacuum_max_workers, per-table thresholds on your hot tables.
statement_timeout and idle_in_transaction_session_timeout. The second one prevents more incidents than any storage feature.

Backups and PITR

Aurora takes continuous automatic backups of the cluster volume and supports point-in-time restore within the retention window. There's no pg_basebackup, no archive_command, no pgBackRest against the cluster volume.

The catch: restore always creates a new DB cluster. There is no restore-in-place. Your RTO includes provisioning a cluster and cutting over connections, which is not thirty seconds.

Also, PITR doesn't help with the 3 a.m. DELETE that gets discovered on Tuesday if your retention is short, and it's clumsy for single-table recovery. Keep taking logical dumps of the tables you actually care about, and restore one occasionally. I once inherited a cluster where "managed backups" had been the answer for three years and nobody had ever performed a restore. The restore worked. The application's connection string, secret rotation, and parameter group did not.

Migration Gotchas

No superuser. The master user gets rds_superuser.
Extensions limited to the AWS allowlist, governed by shared_preload_libraries and the rds.allowed_extensions style parameters.
No filesystem access: COPY FROM PROGRAM and arbitrary pg_read_file paths are out.
Logical replication and pglogical are available with caveats. Physical standbys outside the cluster aren't possible; use logical replication or DMS for cross-system replication.
Local temp storage is limited on smaller instance classes. Large sorts and hash joins will hit it.
Storage I/O is billed per request on the standard configuration. A plan regression that flips index lookups to sequential scans costs money as well as latency. Aurora I/O-Optimized removes per-request I/O charges in exchange for higher instance and storage rates; run the arithmetic before assuming either is cheaper.

So Is It Real Postgres?

At the SQL, planner, and MVCC layer, yes. It's the actual PostgreSQL code doing query processing, and your EXPLAIN (ANALYZE, BUFFERS) habits transfer intact. At the durability layer, no — and pretending otherwise is how you burn a week on checkpoint_completion_target.

The trade is worth it when you want fast reader provisioning, storage that grows on its own, and failover you don't have to build. It's a poor trade when you have a 200 GB database with modest write volume, a working Patroni setup, and someone competent watching it. That workload does fine on a well-tuned RDS instance or your own hardware for a fraction of the bill.

If it's useful, MyDBA's free health check (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=aurora-postgresql-storage-model) reads both topologies and won't hand you checkpoint advice on a cluster where checkpoints don't do anything.

Tags: postgres aws database devops ## Bringing It Back to the Storage Model

None of this is an argument for or against Aurora — it's an argument for tuning the layer that's actually yours to tune. The engine-level knobs (vacuum, work_mem, connection limits, timeouts) do exactly what they've always done, because Aurora didn't touch the engine. The storage-level knobs (checkpoint tuning, full_page_writes, replica lag interpretation) belong to a layer AWS manages for you now, and pretending otherwise just burns engineering time on parameters that can't move the needle. Know which half of your playbook survived the migration, watch the metrics that actually reflect Aurora's architecture, and don't let a familiar SHOW command convince you the storage underneath is familiar too.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=aurora-postgresql-storage-model

If you're not sure which of your alerts and dashboards still mean anything on Aurora, run them past MyDBA — it's free and it won't waste your time on advice for a layer you don't control.

Postgres Is Down: A 5-Check Triage Tree for Fast Recovery

Philip McClarence — Sun, 09 Aug 2026 10:00:10 +0000

I have been paged for "postgres is down" maybe two hundred times. Almost every one of those pages resolved into one of five buckets. The fastest recovery I have ever run was not the one where I was cleverest, it was the one where I ran the same checks in the same order and refused to guess.

📖 Read the full guide: Postgres Is Down: A 15-Minute Triage Runbook

So here is the tree. Run it top to bottom. Each check takes under two minutes and eliminates an entire class of failure.

TL;DR: the five checks, in order

Disk. Is the PGDATA or pg_wal filesystem full?
Connections. Are you out of connection slots?
OOM. Did the kernel kill the postmaster?
WAL and archiving. Is a failing archive_command or a stale replication slot pinning WAL?
Logs and corruption. Everything else, read from the server log.

Paste this before you read another word:

# 1. state of the world
systemctl status postgresql* --no-pager | head -20
pg_isready -h localhost -p 5432; echo "exit=$?"
ps aux | grep -c '[p]ostgres'

# 2. disk
df -h $(psql -Atc 'show data_directory' 2&gt;/dev/null || echo /var/lib/postgresql)
du -sh /var/lib/postgresql/*/main/pg_wal 2&gt;/dev/null

# 3. oom
journalctl -k --since "1 hour ago" | grep -i -E 'out of memory|oom-kill|killed process'

# 4. recent server log
tail -100 /var/log/postgresql/postgresql-*-main.log 2&gt;/dev/null || \
  tail -100 $(psql -Atc 'show data_directory')/log/*.log

There is a companion video that walks the first four checks live; this article is the version you can bookmark and grep.

Before you touch anything: the 30-second orientation

You are in exactly one of three states. Figure out which before you do anything else.

Postmaster is dead. systemctl status shows inactive/failed, ps aux | grep postgres shows nothing.
Postmaster is alive but refusing connections. Normal during startup/recovery, or something is actively blocking new sessions.
Postmaster is crash-looping. It starts, dies, restarts, dies again.

$ pg_isready -h localhost -p 5432
localhost:5432 - no response

pg_isready distinguishes "accepting connections", "rejecting connections" and "no response". That single line splits the tree. No response means the postmaster is dead or not listening. Rejecting connections means it is alive but in recovery or refusing you. Accepting connections while your app screams means the problem is downstream: connection limits, a lock pileup, or DNS.

Then check whether it is crash looping:

$ systemctl status postgresql@16-main --no-pager
   Active: activating (auto-restart) (Result: exit-code) since Sat 2026-08-08 03:14:22 UTC

activating (auto-restart) in a loop is the tell. The service is starting, PANICking, dying, and systemd is restarting it every few seconds.

Do not restart it reflexively. I know the urge. But if the postmaster is currently alive and in crash recovery, restarting throws away recovery progress and starts it over from the last checkpoint. On a busy cluster with large max_wal_size that can turn a four-minute recovery into a twelve-minute one, and you will have no idea why. Restarting can also overwrite the exact log lines and WAL state you need to diagnose a disk-full or corruption branch. Let it finish. Watch the log for redo in progress and consistent recovery state reached.

Check 1: Disk. Is the data directory full?

$ df -h /var/lib/postgresql
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme1n1    500G  500G     0 100% /var/lib/postgresql

$ du -sh /var/lib/postgresql/16/main/pg_wal
312G    /var/lib/postgresql/16/main/pg_wal

That is your outage. When Postgres cannot write to pg_wal because the filesystem is full, it raises a PANIC and the postmaster shuts down. On restart it tries to write WAL again, fails again, and PANICs again. Crash loop, not corruption. The log looks like this:

PANIC:  could not write to file "pg_wal/xlogtemp.2104": No space left on device
LOG:  startup process (PID 2104) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure

Work the emergency ladder from safest to most destructive. Stop as soon as the server comes up.

Rung 1: delete things that are not Postgres data.

journalctl --vacuum-size=100M
find /var/log/postgresql -name '*.log.[0-9]*' -mtime +2 -delete
rm -f /var/lib/postgresql/16/main/pgsql_tmp/*   # only with the server stopped

Rung 2: expand the volume. If you are on cloud block storage or LVM, this is a single API call and an online resize2fs or xfs_growfs — almost always faster than people think. Do this if you can. It is the only rung with no downside.

Rung 3: reclaim the ext4 reserve. Ext4 reserves 5% of the filesystem for root by default. On a 500G volume that is 25G of breathing room:

tune2fs -m 1 /dev/nvme1n1     # drops reserve to 1%

Set it back afterwards.

Rung 4: neutralise archiving. Covered in Check 4, because you need to understand what it costs.

Never delete files from pg_wal by hand. Not the oldest ones, not the ones that "look archived", not any of them. WAL segments are removed by the checkpointer when it recycles them, or by pg_archivecleanup against an archive directory. Manually removing a segment the server still needs turns a recoverable disk-full into an unrecoverable cluster. I have watched someone do this at 4am and spend the next nine hours on a restore.

Check 2: Connections. Did you hit max_connections?

The symptom in the app log:

FATAL:  sorry, too many clients already

To the application this is indistinguishable from down. The server is fine. It has simply run out of slots.

SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY 2 DESC;
SHOW max_connections;

        state         | count
----------------------+-------
 idle in transaction  |   287
 idle                 |    94
 active               |    11

You can still get in: superuser_reserved_connections defaults to 3 and holds slots back for superusers. On PG16 and later there is also reserved_connections plus the pg_use_reserved_connections role, so you can give a non-superuser break-glass account its own pool. Set that up now, not during the next incident.

Kill the offenders. pg_terminate_backend releases the slot; pg_cancel_backend only cancels the query and leaves the connection sitting there, which does not help you.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change &lt; now() - interval '5 minutes'
  AND pid &lt;&gt; pg_backend_pid();

Raising max_connections is almost always the wrong first move. Every slot costs memory and every additional backend costs you on lock contention and context switching, and you need a restart to apply it. It doesn't fix the leak, it delays the next occurrence and multiplies memory pressure — which walks you straight into Check 3. You will be back here in three weeks with a bigger number and the same problem.

The durable fix is two settings and one process:

idle_in_transaction_session_timeout = '60s'   # default is 0, disabled

Then put PgBouncer in transaction pooling mode in front. It multiplexes a few hundred client connections onto twenty or thirty server connections, which is the actual answer to application connection sprawl.

Check 3: OOM. Did the kernel kill the postmaster?

$ journalctl -k --since "2 hours ago" | grep -i 'out of memory'
kernel: Out of memory: Killed process 1841 (postgres) total-vm:9124560kB, \
  anon-rss:7742108kB, file-rss:0kB, shmem-rss:1048576kB, UID:26 pgtable:16924kB

And in the server log:

LOG:  server process (PID 1841) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system is in recovery mode

That last sequence is protective behaviour, not corruption. When a backend dies uncleanly the postmaster cannot trust shared memory, so it aborts everything and runs crash recovery. Let it.

Root causes, in the order I find them:

work_mem times concurrency. work_mem is per sort or hash node, not per connection. One query with three hash joins and a sort can allocate four or five multiples of it. Set 4MB and run 300 connections doing analytics and you have engineered your own OOM.
shared_buffers too high relative to RAM, especially with no swap configured to give you a cushion for transient spikes.
Overcommit. The docs recommend vm.overcommit_memory = 2 with a sane overcommit_ratio on a dedicated database host, so allocations fail with an honest error instead of the kernel picking a victim later.

sysctl -w vm.overcommit_memory=2
sysctl -w vm.overcommit_ratio=80

Also protect the postmaster so children die before the parent — losing one bad query is a far better outcome than losing the whole instance:

# /etc/systemd/system/postgresql@.service.d/oom.conf
[Service]
OOMScoreAdjust=-900
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

Check 4: WAL and archiving. Is a broken archive_command holding your disk hostage?

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

 archived_count | last_archived_wal        | failed_count | last_failed_time
----------------+--------------------------+--------------+---------------------------
         418122 | 0000000100000A4C000000B2 |        90441 | 2026-07-19 02:11:07+00

last_archived_time from three days ago plus a failed_count in the tens of thousands tells you the whole story. With archive_mode = on, Postgres retains every segment that has not been successfully archived. Forever. pg_wal grows until archiving succeeds or you disable it. Common real causes: expired storage credentials, a bucket quota or policy rejection, DNS or network flakiness to the archive target.

Two other things pin WAL. Check both:

SELECT slot_name, active, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots ORDER BY 3;

An inactive slot from a replica you decommissioned in March will happily hold 400GB of WAL. Drop it with pg_drop_replication_slot('old_replica') once you are certain nothing needs it. On PG13+, set max_slot_wal_keep_size so a slot can never take the primary down; the slot gets invalidated instead. The third pinner is a base backup that started and never finished, so check for stale pg_basebackup or pgBackRest processes.

The emergency lever, and what it costs

psql -c "ALTER SYSTEM SET archive_command = '/bin/true'"
psql -c "SELECT pg_reload_conf()"

archive_command takes a reload. (archive_mode needs a restart, which is why you change the command, not the mode.) The checkpointer will now recycle segments and your disk will drain, often within a minute or two.

This breaks PITR continuity. Every segment "archived" from that point on is a lie — it's discarded, not stored. Your ability to recover to any point after your last full backup is severed at that moment. The instant the fire is out:

Fix the real archive target.
Restore the real archive_command, reload.
Take a fresh full backup. Not tomorrow. Now. Your recovery window starts from that backup, not from whenever you think it started.
Verify pg_stat_archiver.failed_count stops climbing.

The war story

July 2026. Page at 02:40: cluster crash looping, PANIC: could not write to file. df showed 100%, pg_wal was 312GB on a volume sized for 40. pg_stat_archiver showed last_archived_time four days stale and failed_count past 90,000. First instinct was a broken pgBackRest stanza after a recent upgrade. Wrong. The archive command was fine. The object storage bucket had hit its quota and was returning HTTP 403 on every PUT, and the error was being swallowed into a generic archive failure line. Nobody caught it because the archiver retried quietly and the alert threshold was wrong.

Neutralised archive_command, disk drained in about ninety seconds, cluster came up and finished crash recovery. Raised the bucket quota, restored the real command, took a fresh full backup before going back to bed.

The actual root cause started nineteen days earlier. A missing extension .so after a package upgrade was causing autovacuum workers to fail cluster-wide. Nothing was being vacuumed. One table bloated to 177GB against maybe 12GB of live data. Bloated tables inflate base backups, and inflated backups filled the bucket, and the full bucket broke archiving, and broken archiving filled the WAL volume. The outage was three weeks old before anyone got paged. Watch your autovacuum failure counts.

Check 5: The logs and the scary stuff

On Debian and Ubuntu packages, /var/log/postgresql/. On RHEL-family, the log/ subdirectory inside PGDATA. If you are not sure, ask the server: SHOW log_directory; and SHOW logging_collector;.

grep -E 'PANIC|FATAL|invalid page|checksum|could not' \
  /var/log/postgresql/postgresql-16-main.log | tail -40

Crash recovery is normal and can be slow. redo starts at 0/A4C000B2 followed by silence for eight minutes is a healthy server doing its job. Leave it alone.

These are different:

WARNING:  page verification failed, calculated checksum 21847 but expected 9033
ERROR:  invalid page in block 84722 of relation base/16384/24601

That is real damage. Stop.

Copy PGDATA at the filesystem level before you attempt any repair. Stop the server, snapshot the volume or cp -a the directory somewhere else. Every repair tool below is destructive and one-way. If the repair goes wrong, you want the broken state preserved, not gone.

zero_damaged_pages zeroes damaged pages and permanently loses their contents. pg_resetwal discards WAL and can leave you with silently inconsistent data; the docs are explicit that it is a last resort for a server that will not start, and that you should take a filesystem backup first. Neither of these is a fix. They are a way to get a corpse upright long enough to pg_dump what survived.

If you have a tested backup and the damage is more than a page or two, restore. Restoring is boring and predictable. Fixing corruption in place is neither.

Decision table: symptom → check → fix

What you are staring at	Check	First move
`PANIC: could not write to file ... No space left on device`	1	df, then the disk ladder
Service in `activating (auto-restart)` loop	1 or 4	df and pg_wal size
`FATAL: sorry, too many clients already`	2	Terminate idle-in-transaction, then PgBouncer
`FATAL: remaining connection slots are reserved`	2	Same, connect as superuser
App reports "can't connect" but `pg_isready` says accepting	2	Check pool exhaustion at app/pooler layer
`Out of memory: Killed process (postgres)` in dmesg	3	work_mem, overcommit, oom_score_adj
`terminated by signal 9` in server log	3	Confirm with dmesg before assuming corruption
`database system is in recovery mode`	3 or 5	Wait, watch redo progress, do not restart
pg_wal huge but disk was fine yesterday	4	pg_stat_archiver and pg_replication_slots
`archive command failed with exit code 1`	4	Test the command by hand as the postgres user
`invalid page in block N of relation`	5	Copy PGDATA, then plan a restore
`pg_isready` says rejecting connections	orient	Read the log, server is alive

After the fire: the four things that prevent the repeat

Forecast disk runway, not disk usage. An alert at 85% gives you a number. An alert that says "pg_wal is growing 8GB/hour and you have six hours left" gives you an action — a 30-day trend graph of pg_wal size would have shown the July incident coming a week out.

Monitor the archiver. Alert on pg_stat_archiver.failed_count increasing and on last_archived_time older than fifteen minutes. That single alert would have caught the July incident on day one.

Put a pooler in front. Before you need it, not after connection exhaustion becomes an outage.

Test the restore. A backup you have never restored is a hypothesis. Restore to a scratch host on a schedule, time it, and write the number down so you know your actual RTO.

If you want the archiver, slot lag and disk runway checks running continuously without building them yourself, that is roughly what we built MyDBA to do. But the checks above work fine from a laptop at 3am, and knowing them is the part that matters.

pgvector in Production: HNSW, Filtering, and Tuning

Philip McClarence — Sun, 09 Aug 2026 10:00:09 +0000

pgvector turns Postgres into a real vector database — no second system, no sync jobs, just an ORDER BY on a new column type. It scales to tens of millions of rows on a single node, but the defaults, the filtered-search gotchas, and the point where it stops being the right tool all matter more than the "just add an extension" pitch suggests.

📖 Read the full guide: pgvector in Production: What the Quickstart Skips

TL;DR

pgvector is a real production vector store for most teams. Tens of millions of rows on one decent node is fine.
Start with HNSW, and I mean it. IVFFlat only when build time or maintenance_work_mem forces your hand.
The index is only used for ORDER BY ... LIMIT n, and only when the opclass matches the operator. Mismatch gives you a seq scan with no warning.
Filtered search is where people get burned: either brute force over a subset, or 3 rows back when you asked for 10.
Since 0.8.0, iterative index scans fix the over-filtering case. Turn them on deliberately, with the bounds set.
Measure recall against exact ground truth. Do not eyeball result quality and call it good.
The ceiling is single-node. There is no built-in sharding of a vector index.

The setup

The reason pgvector wins arguments is boring: the embedding sits in the same row as tenant_id, created_at, and status. Filtering is a WHERE clause. No dual write, no sync job, no reconciliation script that someone will have to debug at 3am after a partial failure. I have watched teams bolt on a second datastore before they measured a single query against Postgres, and then spend a quarter keeping two systems consistent.

There's a companion video that covers this at a whiteboard level (pgdba on YouTube). This post goes deeper: exact SQL, real EXPLAIN output, tuning tables, and the edge cases that bite.

Install, types, and what a vector costs you

CREATE EXTENSION vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   int         NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  content     text        NOT NULL,
  embedding   vector(1536)
);

Four types exist. vector (4-byte float), plus halfvec, sparsevec, and bit, all added in 0.7.0. vector stores up to 16,000 dimensions, but HNSW and IVFFlat indexes on it only support up to 2,000. halfvec is 2-byte floats, halves storage, and indexes up to 4,000 dimensions. If you're at 3072 dims and want an index, halfvec is the answer.

Storage math is simple and worth doing on a napkin before you provision: 4 × dimensions + 8 bytes. At 1536 dims that's 6,152 bytes per row. Five million rows is about 30 GB of embedding data alone.

That data doesn't live where you think:

=&gt; \d documents
                Table "public.documents"
   Column   |           Type           | Storage
------------+--------------------------+----------
 id         | bigint                   | plain
 tenant_id  | integer                  | plain
 content    | text                     | extended
 embedding  | vector(1536)             | external

=&gt; SELECT pg_size_pretty(pg_relation_size('documents'))     AS heap,
          pg_size_pretty(pg_total_relation_size('documents')) AS total;
  heap   | total
---------+--------
 412 MB  | 34 GB

pgvector sets EXTERNAL storage on the vector type, so every 6 KB embedding goes out of line into the TOAST relation. pg_relation_size() on the main table lies to you by a factor of eighty. Always use pg_total_relation_size() when sizing disk, and watch TOAST growth separately, not just row count in \dt+.

Distance operators and opclasses

Get this pair right or your index is decorative.

Operator	Distance	Opclass	Notes
`<->`	L2 / Euclidean	`vector_l2_ops`
`<=>`	Cosine	`vector_cosine_ops`	Most common default
`<#>`	Negative inner product	`vector_ip_ops`	Returns negative values
`<+>`	L1 / taxicab	`vector_l1_ops`	Added 0.7.0, HNSW only

A vector_cosine_ops index will not serve a <-> query. You get a sequential scan, no error, no notice, and a p99 that quietly triples once the table grows. This is the single most common pgvector bug I get called about.

If your embeddings are unit-normalized (OpenAI's text-embedding-3 models are), cosine distance and negative inner product rank identically, and inner product is cheaper to compute. If that's you, don't just benchmark it as a maybe — build with vector_ip_ops and query with <#>. It's free performance with no accuracy cost.

HNSW vs IVFFlat: how I choose

	HNSW	IVFFlat
Added in	0.5.0	0.4.0
Build time	Slow	Fast
Build memory	High; falls back to a slower two-pass build if the graph exceeds `maintenance_work_mem`	Modest
Index size	Larger	Smaller
Recall at speed	Better	Good, degrades faster
Build params	`m` (16), `ef_construction` (64)	`lists`
Query param	`hnsw.ef_search` (40)	`ivfflat.probes` (1)
Empty table	Fine, create it before load	No. Needs representative data for k-means centroids
Data drift	Tolerates it	Centroids stale as data shifts; rebuild periodically

-- default choice
CREATE INDEX idx_embedding_hnsw ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- when build time genuinely matters
CREATE INDEX idx_embedding_ivf ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);

pgvector's own sizing guidance for IVFFlat: lists = rows / 1000 up to 1M rows, lists = sqrt(rows) above that. Start probes at sqrt(lists). For 5M rows that's roughly 2,236 lists and 47 probes — do this arithmetic for your actual row count rather than copying a lists value out of a blog post.

IVFFlat's centroid requirement also means it can't be built empty and filled incrementally the way HNSW can. If your pipeline bulk-loads first and indexes after, that's a non-issue; if it streams rows in continuously, it's a real workflow constraint.

Making the build not take all night

SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;  -- 8 workers total

Parallel HNSW builds landed in 0.6.0 and scale reasonably. Bulk load first, then build. Building the index and then inserting 5M rows through it is dramatically slower.

CREATE INDEX CONCURRENTLY avoids the write lock but takes two table passes and is meaningfully slower, and it can't run inside a transaction block. On a live system I still use it; on a migration window I don't.

Watch progress:

SELECT phase, round, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

        phase         | round | tuples_done | tuples_total
----------------------+-------+-------------+--------------
 building index: ...  |     0 |     3120000 |      5000000

If round climbs above 0, your graph didn't fit in maintenance_work_mem and you're in the two-pass path. Cancel, raise it, restart.

Proving the index is used

The good plan:

=&gt; EXPLAIN (ANALYZE, BUFFERS)
   SELECT id, content FROM documents
   ORDER BY embedding &lt;=&gt; '[0.014,-0.221,...]'::vector LIMIT 10;

 Limit  (actual time=1.842..4.061 rows=10 loops=1)
   Buffers: shared hit=2188 read=94
   -&gt;  Index Scan using idx_embedding_hnsw on documents
         (actual time=1.839..4.055 rows=10 loops=1)
         Order By: (embedding &lt;=&gt; '[0.014,-0.221,...]'::vector)
         Buffers: shared hit=2188 read=94
 Execution Time: 4.238 ms

Drop the LIMIT, or swap <=> for <->, and you get this:

 Sort  (actual time=9412.663..9680.114 rows=5000000 loops=1)
   Sort Key: ((embedding &lt;-&gt; '[...]'::vector))
   Sort Method: external merge  Disk: 412104kB
   -&gt;  Seq Scan on documents (actual time=0.031..6119.882 rows=5000000 loops=1)
         Buffers: shared hit=1204 read=3821194
 Execution Time: 9844.291 ms

Now measure recall instead of trusting it:

SET LOCAL hnsw.ef_search = 40;

WITH q AS (SELECT '[0.014,-0.221,...]'::vector AS v),
ann AS (
  SELECT id FROM documents, q ORDER BY embedding &lt;=&gt; q.v LIMIT 10
),
truth AS (
  SELECT id FROM documents, q ORDER BY embedding &lt;=&gt; q.v LIMIT 10
)
SELECT count(*) / 10.0 AS recall_at_10
FROM ann JOIN truth USING (id);

For ground truth, run the truth half in a separate transaction with SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; so it brute-forces. Loop over 200 held-out query vectors and average. On a 5M-row corpus I typically see recall@10 around 0.94 at ef_search = 40, 0.98 at 100, 0.99+ at 200, with latency roughly tripling from the first to the last. Pick your point on that curve with numbers, not vibes.

One hard rule: hnsw.ef_search must be at least your LIMIT, or you won't reliably get the row count you asked for.

Filtered search: the part that bites everyone

SELECT id, content FROM documents
WHERE tenant_id = 42
ORDER BY embedding &lt;=&gt; $1 LIMIT 10;

Failure mode one, the planner uses the btree:

 Limit  (actual time=812.443..812.449 rows=10 loops=1)
   -&gt;  Sort  (actual time=812.441..812.444 rows=10 loops=1)
         Sort Key: ((embedding &lt;=&gt; '[...]'::vector))
         -&gt;  Bitmap Heap Scan on documents (actual rows=418302 loops=1)
               Recheck Cond: (tenant_id = 42)
               Heap Blocks: exact=61044
               -&gt;  Bitmap Index Scan on idx_documents_tenant  (actual rows=418302)
 Execution Time: 812.6 ms

Exact brute force over the subset. Perfect when the tenant has 800 rows, awful at 400k.

Failure mode two, post-filtering: the HNSW scan returns its candidates, the WHERE eliminates most of them, and you get rows=3 back from a LIMIT 10. No error. Just quietly incomplete results, usually noticed by a customer.

Four fixes, ranked

1. Iterative scans (0.8.0+)

The index keeps scanning until enough rows survive the filter.

SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SET LOCAL hnsw.max_scan_tuples = 40000;      -- default 20000
SET LOCAL hnsw.scan_mem_multiplier = 2;      -- default 1

WITH ann AS (
  SELECT id, content, embedding &lt;=&gt; $1 AS dist
  FROM documents
  WHERE tenant_id = 42
  ORDER BY embedding &lt;=&gt; $1
  LIMIT 10
)
SELECT * FROM ann ORDER BY dist;

hnsw.iterative_scan takes off, strict_order, or relaxed_order. relaxed_order is faster but can return rows slightly out of distance order, hence the re-sorting CTE. IVFFlat only supports off and relaxed_order, bounded by ivfflat.max_probes. Set max_scan_tuples higher for very selective filters, or you'll still under-return.

2. Partial indexes

For known high-traffic filter values:

CREATE INDEX idx_docs_hnsw_t42 ON documents
  USING hnsw (embedding vector_cosine_ops)
  WHERE tenant_id = 42;

True pre-filtering. Cost is one index per value, so reserve it for your five biggest tenants, not ten thousand of them.

3. Partitioning

By tenant or time with an HNSW index per partition. Same idea, scalable: the planner prunes partitions before the ANN scan runs, so each partition's index only ever sees its own rows.

4. CTE pre-filter with exact distance

Use when the filter is very selective (a few thousand rows). Skip the index entirely and compute exact distances.

And the correction I make constantly: you cannot stash the embedding in a btree INCLUDE column.

=&gt; CREATE INDEX idx_tenant_embedding ON documents (tenant_id) INCLUDE (embedding);
ERROR:  index row size 6216 exceeds btree version 4 maximum 2704 for index "idx_tenant_embedding"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.

Btree tuples cap at roughly one third of an 8 KB page. 6,152 bytes was never going to fit.

Operational reality

Re-generating embeddings is a full row rewrite. Postgres MVCC writes a new row version for any update, even one unrelated to the embedding, and with a 6 KB TOASTed vector that means the TOAST chunks get rewritten too, plus HNSW graph churn absorbing the change. A nightly job that re-embeds 10% of a 5M-row table generates a lot of WAL, which your replicas have to receive and replay. Watch replication lag on the first run.

If you regenerate embeddings in bulk — new model version, new chunking strategy — expect index bloat and plan a REINDEX CONCURRENTLY afterward rather than letting autovacuum fight a losing battle. HNSW build work happens on the primary and ships as WAL, so a REINDEX CONCURRENTLY on a 30 GB index is a replication event, not just a local one. Schedule it accordingly.

Also worth knowing: pgvector is available as a managed extension on RDS/Aurora, Cloud SQL/AlloyDB, and Azure Database for PostgreSQL. Adopting it rarely means self-hosting.

Where pgvector stops being the right answer

There is no distributed vector index. One node, one memory budget, and that budget competes directly with the shared_buffers your OLTP workload needs. There's no native BM25 or hybrid fusion, and no reranking stage.

My rule: if you're past roughly 50M vectors at 1536 dims, or you need sub-10ms p99 at hundreds of millions of vectors, or vector work is starving your transactional workload of memory, start looking. Before you leave Postgres entirely, try pgvectorscale, which adds a StreamingDiskANN index for larger-than-memory workloads and keeps the "embedding next to metadata" model. After that, Qdrant, Milvus, and Pinecone exist for exactly this reason. Measure before you migrate — I've watched teams add a second database before they'd run a single recall benchmark on the first one.

Pre-production checklist

Opclass matches the operator in every query path.
Every vector query has ORDER BY and a LIMIT.
hnsw.ef_search >= the largest LIMIT you issue.
Recall@10 measured against exact ground truth across at least 100 held-out queries.
Iterative scan configured, with max_scan_tuples and scan_mem_multiplier set explicitly.
Filtered queries have a plan you've read, not one you assume.
Partial or partitioned indexes in place for your highest-traffic filter values.
Index build is scripted, reproducible, and timed on production-sized data.
maintenance_work_mem sized so pg_stat_progress_create_index never shows round > 0.
Monitoring on index size, TOAST growth, pg_total_relation_size, and vector query p99.
WAL volume checked after your first bulk re-embedding run.

If you'd rather have something check the schema side of this for you, MyDBA has a free health check that flags missing or mismatched indexes and prints the exact CREATE INDEX for your tables.

PostgreSQL Transaction ID Wraparound: The Slow-Motion Outage You Can See Coming

Philip McClarence — Sat, 08 Aug 2026 10:15:04 +0000

TL;DR

Wraparound is what happens when Postgres's 32-bit transaction ID counter runs out of room and autovacuum hasn't frozen old rows fast enough to make space. It is not a random failure mode — it's the endpoint of weeks of autovacuum falling behind.
Run SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC; right now. Anything north of a few hundred million deserves attention; past 1 billion, you need a plan this week.
Postgres escalates in stages: normal autovacuum, then forced anti-wraparound vacuum at 200 million XIDs from the limit, then failsafe vacuum at 1.6 billion age, then a hard write-block with about 1 million XIDs left.
The fix is almost never single-user mode. VACUUM is exempt from the write freeze, so in most cases you vacuum your way out while the app stays up (mostly) online.

Quick hit before we start

If you've watched our whiteboard explainer on wraparound, you've got the mental model: a clock that runs out of numbers. This article is the version with the copy-paste SQL, the exact default thresholds, and the recovery nuance the video simplifies — namely that hitting the wall doesn't automatically mean single-user mode. Keep reading if you want the actual queries you'd run at 2am.

The 32-bit clock: what an XID actually is

Every transaction in Postgres gets a transaction ID — a 32-bit integer, assigned in order, used by MVCC to decide which rows are visible to which transactions. A 32-bit counter gives you roughly 4.2 billion values before it wraps back to 0. That sounds like a lot until you remember every insert, update, delete, and even some reads inside a transaction consumes one. On a busy OLTP system doing a few hundred transactions per second, that's a few years of runway under ideal conditions — and a lot less if things go sideways.

Why it's a circle, not a line

Here's the part most people skip past: Postgres doesn't treat XIDs as a simple ascending line. It splits the XID space into a "past half" and a "future half" relative to the current XID, and uses that split to decide whether one transaction happened before or after another. This is what makes wraparound genuinely dangerous rather than just an inconvenient counter reset. If old, unfrozen rows sit around long enough that the counter wraps past them, those rows can suddenly look like they were written in the future from the perspective of current transactions. MVCC visibility logic then treats them as not-yet-visible — and they silently disappear from query results. This is documented plainly in the Postgres manual's section on preventing wraparound failures: it's framed explicitly as a data-loss scenario, not a housekeeping nuisance.

Postgres's escalating defenses

Autovacuum is the only thing standing between you and this outcome, and Postgres has built an escalating alarm system on top of it:

Normal autovacuum — runs based on dead tuple thresholds, freezing rows as it goes via vacuum_freeze_min_age.
Anti-wraparound vacuum — triggered per table once its age exceeds autovacuum_freeze_max_age, default 200 million. These vacuums can't be canceled by lock conflicts the way ordinary ones can.
Failsafe vacuum — introduced in PG 14, triggered at vacuum_failsafe_age, default 1.6 billion. This mode disables cost-based delays and skips index vacuuming entirely to race toward freezing tuples before the wall.
Hard stop — Postgres emits warnings once you're within about 10 million XIDs of the limit, and refuses to assign new XIDs once you're within roughly 1 million. At that point, new writes are blocked cluster-wide.

None of this is subtle. If autovacuum is keeping pace, you'll never see stage 2, let alone 3 or 4.

Check your risk right now

Two queries, thirty seconds:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

This tells you, per database, how many XIDs old the freeze horizon is. Danger zone starts getting real around 1.5–2.1 billion — the failsafe kicks in at 1.6 billion, and the hard stop is at roughly 2.1 billion minus a million.

SELECT relname, age(relfrozenxid) FROM pg_class
WHERE relkind IN ('r','m') ORDER BY 2 DESC LIMIT 20;

This finds the actual tables dragging the database's age up. Almost always it's one or two large, high-churn tables — not an even distribution.

Find what's actually blocking vacuum

Autovacuum age climbing steadily usually means one of these:

Idle-in-transaction sessions holding an old xmin open.
Long-running transactions — batch jobs, ETL, reporting queries wrapped in BEGIN and forgotten.
Abandoned or inactive replication slots, which hold back catalog_xmin even when nothing is consuming them.
Orphaned prepared (two-phase) transactions left by a crashed application or broker that never called COMMIT PREPARED.

Check pg_stat_activity for state = 'idle in transaction' and long xact_start values, and pg_replication_slots for slots with no active consumer. Any of these can freeze the effective freeze horizon even while autovacuum looks perfectly healthy in the logs.

What happens when you actually hit the wall

The oversimplified version says "you're stuck in single-user mode." That's mostly wrong. VACUUM, including a manual VACUUM (FREEZE, VERBOSE), is specifically exempt from the wraparound write-block — it needs to run precisely because it's the cure. Most recoveries happen with the database up, reads working, and targeted vacuums running against the worst offenders.

Single-user mode is genuinely required only in narrower cases: disk full so vacuum can't write, superuser connections exhausted so nobody can even connect to run the fix, or catalog corruption bad enough that normal startup fails. A real incident we reviewed involved a missing PostGIS shared library silently blocking autovacuum cluster-wide for 19 days — the extension load failure meant autovacuum workers kept erroring out on startup, table bloat climbed toward 177 GB on one ingest table, and WAL inflation from the backlog eventually filled the backup bucket. No corruption, no single-user mode needed — just three weeks of nobody noticing the age climbing.

Recovery playbook

Run the pg_class query, sort by age, identify the worst tables.
VACUUM (FREEZE, VERBOSE) tablename; directly on those tables, largest first.
Kill idle-in-transaction sessions holding back xmin: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start < now() - interval '1 hour';
Drop abandoned replication slots you've confirmed are dead.
Watch pg_stat_progress_vacuum for live progress on the emergency vacuums.
Re-run the pg_database age query every few minutes until the trend reverses.

Preventing the next one

Set an alert on age(datfrozenxid) well before 1 billion, not at the failsafe threshold. Turn on log_autovacuum_min_duration so slow or skipped autovacuum runs show up in logs instead of silence. Audit for long-lived transactions and batch jobs that hold connections open across commits. Review replication slots quarterly — a slot with no subscriber is a liability, not a backup plan. If cost-based throttling (autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit) is still at conservative defaults on a busy cluster, autovacuum is structurally guaranteed to lose the race eventually.

If you want a second set of eyes on where your freeze age actually sits, MyDBA's free health check flags this exact metric alongside bloat and replication lag in one pass — worth running before it becomes a page at 2am.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgresql-transaction-id-wraparound-the-slow-motion-outage-you-can-se

If this piece saved you from finding out about wraparound the hard way, run the two queries above today, and consider pointing MyDBA at your cluster for a standing check on freeze age, bloat, and slot hygiene.

pg_cancel_backend vs pg_terminate_backend Explained

Philip McClarence — Sat, 08 Aug 2026 10:00:14 +0000

TL;DR

pg_cancel_backend(pid) sends SIGINT. It kills the running statement; the session stays connected.
pg_terminate_backend(pid) sends SIGTERM. It kills the whole backend process and drops the client connection.
Both are cooperative. They're acted on at CHECK_FOR_INTERRUPTS() points, so a backend stuck in a blocking syscall will ignore both until it comes up for air.
The return value means "signal sent", not "query stopped". If the PID isn't a backend you get a warning and false.
Rollback in Postgres is cheap because there is no undo log. What it costs you is bloat and the autovacuum work that follows.
Never kill -9 a backend. Set statement_timeout instead and stop doing this by hand.

📖 Read the full guide: pg_cancel_backend vs pg_terminate_backend Explained

I walked through this whole decision tree with live psql output in a companion video, if you'd rather watch than read: pgdba on YouTube.

First, find the right PID

At 2am the failure mode isn't hesitating. It's killing the wrong session. Here's the triage query I keep in ~/.psqlrc as :runaway:

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       age(now(), xact_start)  AS xact_age,
       age(now(), query_start) AS query_age,
       pg_blocking_pids(pid)   AS blocked_by,
       left(query, 120)        AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid &lt;&gt; pg_backend_pid()
  AND state &lt;&gt; 'idle'
ORDER BY xact_start NULLS LAST;

  pid  | usename | application_name |        state        | wait_event_type | wait_event |   xact_age   |  query_age   | blocked_by |                query
-------+---------+------------------+---------------------+-----------------+------------+--------------+--------------+------------+--------------------------------------
 41022 | app_rw  | orders-api       | active              | Lock            | transactio | 00:00:41.2   | 00:00:41.2   | {40988}    | UPDATE orders SET status = 'shipped'
 40988 | app_rw  | psql             | idle in transaction |                 |            | 00:22:07.9   | 00:21:55.1   | {}         | SELECT * FROM orders WHERE id = 91
 41104 | report  | metabase         | active              |                 |            | 00:14:33.0   | 00:14:33.0   | {}         | SELECT o.id, sum(l.qty) FROM orders

Three things that query buys you. backend_type = 'client backend' filters out walsenders, autovacuum workers and the logical replication launcher, none of which you want to signal casually. pid <> pg_backend_pid() stops you from cancelling yourself, which is embarrassing but harmless. And pg_blocking_pids(pid) tells you who the actual culprit is: in that output, 41022 is a victim. The problem is 40988, sitting idle in transaction for 22 minutes.

One gotcha: pg_stat_activity.query is truncated to track_activity_query_size bytes, default 1024. A generated ORM query will look cut off mid-clause. That's the setting, not a bug, and raising it costs shared memory per connection slot.

pg_cancel_backend vs pg_terminate_backend: what each does

Both functions do one thing: send a signal and return whether the send succeeded.

SELECT pg_cancel_backend(41104);

Client side:

ERROR:  canceling statement due to user request
SQLSTATE: 57014
report=&gt; SELECT 1;
 ?column?
----------
        1

The session lives. Contrast with terminate:

SELECT pg_terminate_backend(40988);

FATAL:  terminating connection due to administrator command
SQLSTATE: 57P01
server closed the connection unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.

	`pg_cancel_backend`	`pg_terminate_backend`
Signal	SIGINT	SIGTERM
Scope	current statement	entire backend process
Session survives?	yes	no
Client sees	ERROR 57014 `query_canceled`	FATAL 57P01 `admin_shutdown`
Permission	superuser, `pg_signal_backend`, or member of the owning role	same
Timeout argument	no	yes, since PG 14

Members of pg_signal_backend cannot signal a superuser's backend. That bites during incidents when the runaway query is being run by the DBA who went home.

And in psql, Ctrl+C is not a client-side abort. It sends a cancel request over the protocol, which is exactly pg_cancel_backend aimed at your own backend.

The myth I want to kill: "rollback takes as long as the write"

You will read this on Stack Overflow roughly once a week: don't cancel that 40-minute UPDATE, the rollback will take another 40 minutes replaying it backwards.

That's Oracle thinking. Postgres has no undo log. Aborting a transaction writes the xid as aborted in pg_xact and releases its locks. That's it. It doesn't matter whether the transaction touched 12 rows or 120 million; the abort itself is essentially constant time.

What actually happened is that every row the UPDATE wrote is now a dead tuple sitting in the heap and the indexes. So:

You bloated the table and its indexes by the size of the write.
You handed autovacuum a job it will do later, on its schedule, competing with your production traffic.
The WAL for all of it was already written and already streamed to your replicas. WAL is generated regardless of eventual commit or abort. Cancelling doesn't un-ship it.

So cancel that runaway UPDATE when you need the locks back. It'll return in seconds. Just know you've traded a long write for a vacuum bill, and postgres cancel query rollback is fast for exactly this reason.

When cancel doesn't work, and why terminate usually won't either

Interrupts are processed at CHECK_FOR_INTERRUPTS() macros scattered through the executor and utility code. If the backend never reaches one, nothing happens. The real cases:

Blocked in a syscall against hung storage or a stalled NFS/EBS volume.
A tight loop inside extension C code that never checks for interrupts. PostGIS geometry work and some FDWs have historically been guilty here.
A handful of genuine critical sections.

Now the correction I want to make. A very common claim is that a backend waiting on a heavyweight lock ignores cancel. It doesn't. Lock waits are interruptible latch waits, and pg_cancel_backend on a session sitting in wait_event_type = 'Lock' works immediately, every time. Try it yourself with two psql windows and a SELECT ... FOR UPDATE.

The punchline: if a backend ignored your cancel, escalating to terminate rarely helps, because SIGTERM is checked at the same points. If a session is truly wedged, you're looking at a storage problem, not a signalling problem.

The timeout argument (PG 14+)

SELECT pg_terminate_backend(41022, 5000);  -- wait up to 5s for actual exit

With a non-zero timeout it waits for the process to actually exit and returns true, or emits a warning and returns false if the timeout elapsed. That's the difference between "I sent a signal" and "the backend is gone".

An escalation snippet I use, wrapped in DO for one-shot incident work:

DO $$
DECLARE
  target int := 41022;
  gone   boolean;
BEGIN
  PERFORM pg_cancel_backend(target);
  PERFORM pg_sleep(3);
  IF EXISTS (SELECT 1 FROM pg_stat_activity WHERE pid = target AND state = 'active') THEN
    gone := pg_terminate_backend(target, 5000);
    IF NOT gone THEN
      RAISE WARNING 'pid % did not exit; check storage/IO, do NOT kill -9', target;
    END IF;
  END IF;
END $$;

The decision tree I actually use

Runaway read-only SELECT. Cancel. Session survives, the app's connection pool doesn't notice.
Big write you've decided to abandon. Cancel. Accept the bloat, then check pg_stat_progress_vacuum an hour later.
Idle in transaction holding locks. Terminate. Cancel is a no-op here: there is no statement to cancel, and it will not roll back the open transaction or release its locks. I've watched people fire cancel three times at an idle-in-transaction session and conclude Postgres is broken.
Blocking chain. Cancel the root blocker from pg_blocking_pids, not the victims. Killing victims just makes the app retry into the same wall.
Not sure how far along it is? Check pg_stat_progress_create_index or pg_stat_progress_copy first. A CREATE INDEX at 94% is worth 90 more seconds.

Edge cases that bite

Concurrent index builds. Cancel a CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY and you get an invalid index: unusable for queries, still maintained on every write. Worst of both worlds. Find and clean them:

SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index
WHERE NOT indisvalid;

Then DROP INDEX CONCURRENTLY each one and restart the build.

Prepared transactions. A transaction that reached PREPARE TRANSACTION outlives its backend. Terminating the session releases nothing; the locks stay until ROLLBACK PREPARED or COMMIT PREPARED. Check pg_prepared_xacts before you conclude the lock is a ghost.

PgBouncer transaction pooling. In pool_mode = transaction, a server connection is leased only for the duration of a transaction. The PID you read from pg_stat_activity may be serving a completely different client by the time you signal it. Requery immediately before you act, then read fast, act fast.

Autovacuum workers. Cancelling one stops that run only; the table gets rescheduled. Habitually killing anti-wraparound vacuums is exactly how clusters end up in wraparound trouble.

Retry loops. Half the apps I've seen will re-issue the identical query within two seconds. Fix the query or add a timeout; killing it is theatre.

Never SIGKILL a backend

kill -9 on a backend makes the postmaster assume shared memory may be corrupt. It terminates every other backend and forces crash recovery. You took the whole cluster down to stop one report query. There is no situation where this is the right first move. Neither pg_cancel_backend nor pg_terminate_backend uses SIGKILL, and that's not an accident.

The settings that mean you never have to do this by hand

Setting	Action	SQLSTATE
`statement_timeout`	cancels the statement	57014
`lock_timeout`	aborts statement waiting on a lock	55P03
`idle_in_transaction_session_timeout`	terminates the session	25P03
`transaction_timeout` (PG 17+)	terminates the session

statement_timeout does not cover idle time inside an open transaction, which is why idle_in_transaction_session_timeout exists — the standard fix for a session that shows idle in transaction terminate in monitoring alerts. transaction_timeout on PG 17 closes the last gap: many short statements interleaved with idle time.

Scope them per role, not globally:

ALTER ROLE app_rw     SET statement_timeout = '30s';
ALTER ROLE app_rw     SET lock_timeout = '3s';
ALTER ROLE app_rw     SET idle_in_transaction_session_timeout = '60s';

ALTER ROLE report     SET statement_timeout = '5min';
ALTER ROLE migrations SET statement_timeout = 0;
ALTER ROLE migrations SET lock_timeout = '5s';

ALTER DATABASE prod   SET idle_in_transaction_session_timeout = '10min';  -- backstop

Rough starter values by workload:

OLTP app role: statement_timeout = 5-30s, lock_timeout = 2-5s, idle_in_transaction_session_timeout = 1-5min
Reporting/analytics role: statement_timeout = 5-15min
Migration/maintenance role: leave statement_timeout unset, but still set lock_timeout so a migration doesn't sit forever waiting on a lock during business hours

The docs explicitly warn against setting statement_timeout in postgresql.conf, and this is why: pg_dump, a four-hour CREATE INDEX, and your migration tooling all inherit it. You'll discover this when the nightly backup starts failing at 30 seconds. Role-level settings apply to new sessions; SET LOCAL inside a transaction overrides them when you genuinely need longer.

Recovery conflicts: the standby cancels for you

On a hot standby you'll see cancels you didn't send:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
SQLSTATE: 40001

Same mechanism, just triggered by the server itself instead of by you: a query on the standby is holding up WAL replay past max_standby_streaming_delay. hot_standby_feedback = on trades that for bloat on the primary, because the standby tells the primary to hold back vacuum on rows it still needs. Pick one deliberately; the default gives you neither.

Cheat sheet

-- who's the root blocker? (pg_stat_activity long running query check)
SELECT pid, pg_blocking_pids(pid), age(now(), query_start), state, left(query,80)
FROM pg_stat_activity WHERE backend_type='client backend' AND state&lt;&gt;'idle';

-- runaway SELECT / abandoned write -- kill query postgres, session lives
SELECT pg_cancel_backend(:pid);
-- idle in transaction, or cancel was ignored
SELECT pg_terminate_backend(:pid, 5000);
-- never
kill -9

If you'd rather have the long runners and blocking chains handed to you instead of typing that query at 2am, MyDBA's health check surfaces both with a single ready-to-run command.

Tags: postgres, database, sql, devops ## Wrapping up

None of this is exotic — it's a handful of signals, a couple of timeouts, and knowing which one applies to the session in front of you. The mistakes that actually hurt come from skipping the diagnosis step: killing a victim instead of the blocker, cancelling an idle-in-transaction session and expecting locks to release, or reaching for kill -9 because cancel "didn't work fast enough." Set your timeouts per role, keep the triage query handy, and you'll rarely need to make these calls under pressure in the first place.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cancel-backend-vs-pg-terminate-backend

If 2am triage queries aren't something you want to remember by heart, point MyDBA at your cluster and let it surface the blocking chains and long runners for you.

Postgres TOAST Storage: Where Your Big JSONB Really Lives

Philip McClarence — Sat, 08 Aug 2026 10:00:13 +0000

I got paged once for a "small" table. Twelve million rows, one jsonb column, pg_relation_size said 2.1GB. The disk said 41GB. The backup job said "still running" at 6am. That table taught me more about TOAST than any doc page ever did, mostly because I was reading the wrong size function for three years.

📖 Read the full guide: Postgres TOAST: How It Works, Bloats, and Slows Queries

Here is the whole thing, with the SQL I actually run.

TL;DR

Postgres pages are 8KB by default (BLCKSZ=8192), and a heap tuple cannot span pages. Once a row crosses ~2000 bytes, TOAST gets involved.
TOAST compresses first, then moves values out of line into a hidden pg_toast table in chunks of roughly 2KB, leaving an 18-byte pointer in the heap.
Four strategies: PLAIN, EXTENDED, EXTERNAL, MAIN. pg_attribute.attstorage records them as p, x, e, m. EXTENDED is the default for text, varchar, jsonb, bytea, arrays.
pg_relation_size() excludes the TOAST table. pg_total_relation_size() includes it. That gap is where 39GB hides.
pg_column_size() vs octet_length() shows you the compression ratio for free.
TOAST tables live in the pg_toast schema, so every monitoring query filtered to public misses their bloat entirely.
Companion video if you want the visual version: https://www.youtube.com/watch?v=Eqwf4Yo1R-4

The 8KB wall

Postgres reads and writes in 8KB pages. That is a compile-time constant, and unless you built your own binaries you have 8192. A heap tuple must fit inside one page, no spanning.

So the practical ceiling for a row before Postgres starts intervening is TOAST_TUPLE_THRESHOLD, 2000 bytes by default, roughly a quarter of a page. Four fat rows per page is the design target.

Which raises the obvious question: you have a 50KB JSON document. It does not fit. It does not fit by a factor of six even after compression. Where does it go?

What TOAST actually does, in order

TOAST is The Oversized-Attribute Storage Technique. That is the one acronym joke you get from me.

The algorithm, in the order it runs:

Is the tuple bigger than TOAST_TUPLE_THRESHOLD (2000 bytes)? If no, stop. Nothing happens.
Pick the widest TOAST-able attribute in the row.
If its strategy allows compression (EXTENDED or MAIN), compress it in place.
If the tuple is still over TOAST_TUPLE_TARGET (also 2000 by default) and the strategy allows it, move the value out of line into the TOAST table.
Repeat with the next widest attribute until the tuple fits.

Compression happens first. Out-of-line storage is the fallback. Most people I talk to have this backwards and assume any big value goes straight to the TOAST table. A 3KB text field that compresses to 900 bytes stays right there in the heap and never touches TOAST.

Also: only variable-length (varlena) types are eligible. Your bigint, timestamptz, and integer columns are never TOASTed, no matter how many of them you have.

When a value does go out of line, it is split into chunks of at most TOAST_MAX_CHUNK_SIZE, about 2KB, sized so four chunk rows fit per TOAST page. Those chunks land in pg_toast.pg_toast_ with columns chunk_id oid, chunk_seq int, chunk_data bytea, plus a unique index on (chunk_id, chunk_seq). What stays in your heap tuple is an 18-byte TOAST pointer holding the total size, compressed size, chunk_id, and the TOAST relation OID.

Maximum size of a single TOASTed value is 1GB, same as any varlena. If you are near that, we need a different conversation.

The four storage strategies

Strategy	`attstorage`	Compress?	Out-of-line?	Default for	When I reach for it
PLAIN	`p`	no	no	fixed-length types	Never manually. It errors if the value can't fit.
EXTENDED	`x`	yes	yes	text, varchar, jsonb, bytea, arrays	The default, and correct 90% of the time.
EXTERNAL	`e`	yes-ish (no)	yes	none	Big text/bytea where you do `substring()` or prefix `LIKE`.
MAIN	`m`	yes	last resort	none	Values that compress well and are read on every query.

EXTERNAL exists for a real reason. With compression off, Postgres can fetch only the chunks it needs to satisfy a substring() on a long text or bytea value instead of pulling and decompressing the entire datum. If you store 200KB documents and routinely read the first 500 bytes, EXTERNAL is a genuine win. It costs you disk. What it will not do is help jsonb key extraction — ->> has no partial-fetch path regardless of storage strategy, so don't bother flipping a jsonb column to EXTERNAL expecting cheaper key lookups.

The video keeps this simple; here is the exact nuance: EXTERNAL does not "disable TOAST," it disables compression while still allowing out-of-line storage. MAIN does not "keep it in the heap forever" either. MAIN still goes out of line if the row cannot be made to fit any other way.

Look at your own database

Per-column strategies:

SELECT a.attname,
       format_type(a.atttypid, a.atttypmod) AS type,
       a.attstorage,
       CASE a.attstorage
         WHEN 'p' THEN 'plain' WHEN 'x' THEN 'extended'
         WHEN 'e' THEN 'external' WHEN 'm' THEN 'main'
       END AS strategy
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders' AND n.nspname = 'public'
  AND a.attnum &gt; 0 AND NOT a.attisdropped
ORDER BY a.attnum;

  attname  |           type           | attstorage | strategy
-----------+--------------------------+------------+----------
 id        | bigint                   | p          | plain
 status    | text                     | x          | extended
 metadata  | jsonb                    | x          | extended
 created_at| timestamp with time zone | p          | plain

Compression in action:

SELECT octet_length(metadata::text) AS raw_bytes,
       pg_column_size(metadata)      AS on_disk_bytes,
       round(100.0 * pg_column_size(metadata)
             / octet_length(metadata::text), 1) AS pct
FROM orders WHERE id = 88123;

 raw_bytes | on_disk_bytes | pct
-----------+---------------+------
     51234 |          9418 | 18.4

That is my 50KB document. It compressed to 9,418 bytes, still way over 2000, so it went out of line. Five chunks:

SELECT chunk_id, count(*) AS chunks, sum(length(chunk_data)) AS bytes
FROM pg_toast.pg_toast_16419
GROUP BY chunk_id ORDER BY bytes DESC LIMIT 1;

 chunk_id | chunks | bytes
----------+--------+-------
    20117 |      5 |  9418

Five index lookups plus five heap fetches, every time something evaluates that column.

How big is the TOAST table really

This is the single most useful query in this article. Bookmark it.

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid))              AS heap,
       pg_size_pretty(pg_relation_size(t.oid))              AS toast,
       pg_size_pretty(pg_indexes_size(t.oid))               AS toast_idx,
       pg_size_pretty(pg_total_relation_size(c.oid))        AS total
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind = 'r' AND c.relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 5;

 relname  |  heap   |  toast  | toast_idx |  total
----------+---------+---------+-----------+---------
 orders   | 2141 MB | 36 GB   | 2380 MB   | 41 GB
 events   | 1802 MB | 118 MB  | 9648 kB   | 2014 MB

reltoastrelid is 0 when a table has no TOAST table at all — usually one made entirely of fixed-length columns. If your dashboards graph pg_relation_size, you are graphing 5% of that table.

Why your jsonb reads got slower

Detoasting is lazy. SELECT id FROM orders does not touch a single TOAST chunk, because nothing ever evaluates metadata. I hear the opposite claim constantly and it is wrong: selecting other columns from a wide table pays nothing for the TOASTed one. Same reason COUNT(*) on a 41GB table is fast.

But the moment you apply any operator to the value, Postgres fetches and decompresses the whole datum. metadata->>'status' does not do a partial read of one key. There is no partial-detoast path for -> or ->>. You asked for 12 bytes and paid for 9,418 bytes across five chunk fetches plus decompression, per row.

That is the difference between a 40ms scan and a 9-second one, and it will not show up as anything obvious in EXPLAIN output beyond mysteriously high runtime.

The escape hatch is an expression index:

CREATE INDEX orders_status_idx ON orders ((metadata-&gt;&gt;'status'));

Now a predicate on that key is answered from the index without detoasting every document at scan time. If the query only needs that key, an index-only scan skips the heap and TOAST entirely.

Why your jsonb updates got expensive

Second myth, more damaging than the first: "every UPDATE rewrites the TOAST value." No. If an UPDATE does not modify the TOASTed column, Postgres copies the existing TOAST pointer into the new row version. Eighteen bytes, not nine kilobytes. Updating status on a row with a 50KB document is cheap.

Modify the column and everything changes. There's no diffing and no partial chunk update — the new value is compressed from scratch, chunked from scratch, and written as fresh rows in the TOAST table. The old chunks become dead and sit there until vacuum. Change one key in a 50KB document and you have written 9KB of new chunks and orphaned 9KB of old ones.

Consequence: a high-churn jsonb column bloats its TOAST table faster than anything else in your database. And because the toasted column changed, you cannot get a HOT update, so every index on the table takes a new entry too. If you're updating a jsonb column on every request just to bump one field — a last_seen timestamp, a status flag — pull that field into its own narrow column. You are otherwise paying full-document recompression on every request to move one value.

Tuning knobs I actually use

ALTER TABLE orders ALTER COLUMN raw_payload SET STORAGE EXTERNAL;

Critical caveat that bites everyone: this only affects rows inserted or updated after the change. Existing rows keep their current physical representation. To apply it retroactively you need a rewrite:

VACUUM FULL orders;                              -- takes ACCESS EXCLUSIVE
-- or a no-op type change, also a full rewrite:
ALTER TABLE orders ALTER COLUMN raw_payload TYPE text;

Both take heavy locks. Plan the window, or use pg_repack if you cannot.

Compression method: since PG14, default_toast_compression = lz4 if your build has --with-lz4. I switch to lz4 on anything write-heavy. Lower ratio than pglz, considerably faster both directions. The method is recorded per value, so old pglz values and new lz4 values coexist happily in the same column. No rewrite required to start benefiting.

toast_tuple_target (128 bytes up to TOAST_TUPLE_THRESHOLD) lets you push values out of line more aggressively per table. I have used this exactly twice, both times on tables where the wide column was almost never read and I wanted the heap dense for sequential scans.

And the option nobody wants to hear: if the blob is a 2MB PDF, put it in object storage and keep a URL in Postgres. TOAST is good engineering, but it is not a file server.

The bloat trap nobody watches

TOAST tables are autovacuumed as part of the parent's autovacuum. They also accept independent settings via toast.autovacuum_* storage parameters on the parent. And they show up in pg_stat_all_tables under the pg_toast schema, which is exactly why the monitoring query you inherited (WHERE schemaname = 'public') has never once reported them.

SELECT s.relname, s.n_dead_tup, s.last_autovacuum, s.autovacuum_count,
       pg_size_pretty(pg_relation_size(s.relid)) AS size
FROM pg_stat_all_tables s
WHERE s.schemaname = 'pg_toast'
ORDER BY s.n_dead_tup DESC LIMIT 10;

     relname     | n_dead_tup |        last_autovacuum        | count | size
-----------------+------------+-------------------------------+-------+-------
 pg_toast_16419  |   48211903 | 2026-06-19 04:11:02.338+00     |    12 | 36 GB

Seven weeks since the last autovacuum on the table that holds 88% of the cluster's data. That is the incident. Autovacuum was starved by cost limits and a long-running replication slot holding back the horizon, TOAST bloat grew unbounded, base backups doubled in wall time and size, WAL archive volume climbed, and the disk alert fired at 3am on the standby first because it had the smaller volume.

Tune it directly:

ALTER TABLE orders SET (
  toast.autovacuum_vacuum_scale_factor = 0.02,
  toast.autovacuum_vacuum_cost_limit   = 2000
);

If you want this kind of thing surfaced without writing the queries yourself, MyDBA covers it.

A checklist for wide-column tables

I always look at pg_total_relation_size, never pg_relation_size, when sizing anything with a text or jsonb column.
I add pg_stat_all_tables rows from the pg_toast schema to bloat monitoring on day one.
I never assume SELECT id is expensive on a wide table. It is not.
I do assume metadata->>'k' in a WHERE clause on a large table is a full detoast per row, and I add an expression index.
I do not split a jsonb column into a side table for read performance before checking whether the queries touch it at all.
If I'm updating one small field on every request, I pull it out of the wide jsonb column into its own column rather than rewriting the whole document.
I set toast.autovacuum_vacuum_scale_factor explicitly on any table with a high-churn large column. The default 0.2 on a 36GB TOAST table is absurd.
I switch default_toast_compression to lz4 on write-heavy clusters and leave read-mostly archives on pglz.
When I change SET STORAGE, I schedule the rewrite in the same maintenance window, or I write down that I did not and why.

The 3am lesson, distilled

TOAST is not an edge case you'll hit someday — it's the default fate of any text or jsonb column past 2000 bytes, and the gap between pg_relation_size and reality is where most storage surprises live. Read the pointer arithmetic once, and jsonb bloat, mystery-slow queries, and starved autovacuum jobs stop being mysterious. Check pg_total_relation_size, watch the pg_toast schema, and index the keys you actually query instead of detoasting the whole document every time.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-toast-storage-explained

If you'd rather not write these queries by hand every incident, point MyDBA at your cluster and let it flag TOAST bloat and dead tuples before they page you.

Postgres Row-Level Security: 5 Policies That Actually Work

Philip McClarence — Fri, 07 Aug 2026 10:00:28 +0000

I've been called into the same incident twice: a multi-tenant app where "we have RLS" turned out to mean RLS was enabled and the application connected as the table owner. Zero policies ran. Both times the fix was one ALTER TABLE away, and both times nobody had a test that would have caught it.

📖 Read the full guide: Postgres Row-Level Security: Policies That Actually Work

Postgres row-level security is a small rulebook. Deny by default once enabled. Permissive policies OR together, restrictive ones AND. USING filters what you can see, WITH CHECK validates what you write. And three roles walk straight past all of it: superusers, anything with BYPASSRLS, and the table owner unless you force it.

I walked through these five policies on the whiteboard in the companion video; this post is the copy-paste version with the edge cases the video didn't have room for.

TL;DR

ENABLE ROW LEVEL SECURITY means deny by default. No policy, no rows, no error.
The table owner bypasses its own policies until you add FORCE ROW LEVEL SECURITY.
Superuser and BYPASSRLS always bypass. FORCE does not override that.
Permissive policies combine with OR; restrictive with AND. Restrictive policies alone grant nothing.
USING applies to existing rows (SELECT/UPDATE/DELETE), WITH CHECK to new row contents (INSERT/UPDATE). Omit WITH CHECK on UPDATE and the USING expression gets applied to the new row too — that's a fallback, not a guarantee you should rely on.
RLS sits on top of GRANTs, not instead of them.
Index the columns your policies filter on, and EXPLAIN as the app role, because as superuser the quals vanish.
GUC-based tenancy (current_setting('app.tenant_id')) stops application bugs. It does not stop someone who can run arbitrary SQL on that connection.

The 90 seconds of setup that decide whether RLS does anything

Roles first. One role owns the schema, a different role runs the app.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app LOGIN PASSWORD 'redacted' NOBYPASSRLS;
CREATE ROLE support LOGIN PASSWORD 'redacted' NOBYPASSRLS;

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  customer    text   NOT NULL,
  amount_cents bigint NOT NULL,
  deleted_at  timestamptz
);
ALTER TABLE orders OWNER TO app_owner;

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app;
GRANT SELECT ON orders TO support;

That GRANT line matters. RLS narrows what a privilege reaches; it never hands out a privilege. A role with no SELECT grant gets a permission error regardless of how generous your policies are.

Now the switch, and the second switch people forget:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE  ROW LEVEL SECURITY;

ENABLE turns policies on for everyone except the exempt roles. FORCE adds the table owner to the list of roles that must obey. If your migration tool, your background jobs, or your app connect as the owner, ENABLE alone buys you nothing.

Sanity check, run it as the role your app actually connects with:

SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user;

 rolsuper | rolbypassrls
----------+--------------
 f        | f

Two f values or you are not testing RLS, you are testing your own optimism.

Postgres RLS multi-tenant isolation on one shared table

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING      (tenant_id = current_setting('app.tenant_id', true)::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

The true second argument to current_setting is doing real work. Without it, an unset app.tenant_id raises an error. With it, you get NULL, the comparison yields NULL, and no rows qualify. Fails closed, which is what you want when a connection escapes the pooler without setup.

Usage:

BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT count(*) FROM orders;
COMMIT;

SET LOCAL scopes the value to the transaction. Under PgBouncer transaction pooling, a plain SET sticks to the server connection after your transaction ends and the next client inherits it. I have watched tenant 7 read tenant 3's dashboard because of exactly one missing LOCAL. Wire the SET LOCAL into your pool checkout or your ORM's transaction hook, never into "connection init".

Now the honest part. app.tenant_id is a customized option, and any session can change it with a plain SET. If an attacker reaches arbitrary SQL execution on that connection, they set the GUC to 1 and read tenant 1. This design defends against forgotten WHERE clauses, a misconfigured ORM scope, a raw SQL report someone pasted in. Against SQL injection it does nothing at all.

If you need the stronger version, two options. Set the tenant from the pooler or a SECURITY DEFINER login function that derives it from a signed token, so the value never comes from client-controlled SQL. Or issue one database role per tenant and write the policy against current_user, which the session cannot change without the password.

Per-user rows, and the WITH CHECK you forgot

CREATE TABLE notes (id bigserial PRIMARY KEY, owner name NOT NULL, body text);
ALTER TABLE notes OWNER TO app_owner;
GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO app;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;

CREATE POLICY notes_owner ON notes
  FOR ALL
  USING      (owner = current_user)
  WITH CHECK (owner = current_user);

Here's the failure mode I've seen twice in code review — someone writes only USING and skips WITH CHECK:

CREATE POLICY notes_owner_broken ON notes
  FOR ALL
  USING (owner = current_user);

For UPDATE, if WITH CHECK is omitted, Postgres reuses the USING expression against the new row. That sounds safe until you notice what it actually verifies: the row you started with, not the row you're leaving behind. I can see my row, and I can update it to say owner = 'alice', giving away a row I no longer control. Don't rely on the fallback as your actual defense — write WITH CHECK explicitly whenever ownership is a mutable column.

With the correct policy above, the violation looks like this:

app=> UPDATE notes SET owner = 'alice' WHERE id = 9;
ERROR:  new row violates row-level security policy for table "notes"

SQLSTATE 42501, insufficient_privilege. Same error you get on a cross-tenant insert:

app=> BEGIN; SET LOCAL app.tenant_id = '42';
app=> INSERT INTO orders (tenant_id, customer, amount_cents) VALUES (43, 'acme', 1000);
ERROR:  new row violates row-level security policy for table "orders"

	SELECT	INSERT	UPDATE	DELETE
`USING`	filters visible rows	not used	picks which rows may be updated	picks which rows may be deleted
`WITH CHECK`	not used	validates the new row	validates the resulting row (falls back to `USING`)	not used
PERMISSIVE	OR'd with other permissive policies
RESTRICTIVE	AND'd on top of the permissive result

Admin override, because permissive policies OR

Never edit the tenant policy to carve out exceptions. Add a second one.

CREATE POLICY support_read_all ON orders
  FOR SELECT
  TO support
  USING (true);

A policy with no TO clause applies to PUBLIC. Scoping to TO support means the support role gets tenant_isolation OR support_read_all, which is true, while the app role is unaffected. If you'd rather not name roles in the policy, USING (pg_has_role(current_user, 'support', 'member')) gets you the same effect through group membership.

Check your work:

SELECT policyname, permissive, roles, cmd, qual
FROM pg_policies WHERE tablename = 'orders';

   policyname    | permissive | roles     |  cmd   | qual
-----------------+------------+-----------+--------+---------------------------------
 tenant_isolation| PERMISSIVE | {public}  | ALL    | (tenant_id = (current_setting(...
 support_read_all| PERMISSIVE | {support} | SELECT | true

\d+ orders shows the same thing in psql if you prefer.

Team rows via a membership table

CREATE POLICY doc_membership ON documents
  FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM doc_members m
    WHERE m.doc_id = documents.id
      AND m.user_id = current_setting('app.user_id', true)::bigint
  ));

This one reads well and plans badly if you skip the index. As the app role:

Seq Scan on documents  (cost=0.00..48210.00 rows=3333 width=64)
                       (actual time=0.4..612.9 rows=41 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 99959
  Buffers: shared hit=812 read=41520
  SubPlan 1
    ->  Seq Scan on doc_members m ...

Add the index the subquery actually wants:

CREATE INDEX ON doc_members (user_id, doc_id);

The subplan turns into an index-only lookup and the runtime drops off a cliff. When the join stays hot at scale, the escape hatch is denormalization: carry a team_id on documents and write the policy against that column, keeping the membership table for the UI.

Restrictive policies for soft delete and an append-only log

CREATE POLICY hide_deleted ON orders
  AS RESTRICTIVE
  FOR ALL
  USING (deleted_at IS NULL);

Restrictive policies AND with everything else, so no future permissive policy, including one added at 2am during an incident, can expose tombstoned rows. Remember the rule: a table with only restrictive policies grants nobody anything, because access still requires at least one permissive policy to pass.

Write-only audit log, same idea from the other direction:

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;
GRANT INSERT ON audit_log TO app;

CREATE POLICY audit_append_only ON audit_log
  FOR INSERT WITH CHECK (true);

No SELECT/UPDATE/DELETE policy means the app role can write and never read back. One warning: there is no TRUNCATE policy in RLS at all. Any role holding the TRUNCATE privilege empties the table regardless. Don't grant it.

Postgres RLS performance: what it actually costs

Three real costs, in the order they bite.

The invisible predicate needs an index. Every query against orders now carries tenant_id = ..., so your indexes should lead with tenant_id: (tenant_id, created_at), not (created_at).

Policy expressions are security-barrier conditions. Any user-supplied condition using a non-leakproof function or operator is evaluated only after the policy quals, which can block an index or pushdown the planner would otherwise use. Plans get worse in ways that look mysterious until you remember this.

The GUC lookup is a per-row function call. Wrap it in a scalar subquery so it becomes a one-time InitPlan:

ALTER POLICY tenant_isolation ON orders
  USING      (tenant_id = (SELECT current_setting('app.tenant_id', true)::bigint))
  WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id', true)::bigint));

Index Scan using orders_tenant_created_idx on orders
  Index Cond: (tenant_id = $0)
  InitPlan 1 (returns $0)
    ->  Result

Always EXPLAIN (ANALYZE, BUFFERS) as the app role. Run it as superuser and the quals disappear, and you tune a query that never runs in production. This is also where a second set of eyes helps — I've had a MyDBA review catch a missing composite index on a policy column before it turned into a slow-query page at 2am.

Edge cases that bite

A plain view over an RLS table executes with the view owner's permissions, so the owner's policies apply, not the caller's. PostgreSQL 15 added CREATE VIEW ... WITH (security_invoker = true) to flip that.
SECURITY DEFINER functions run as the function owner. If the owner bypasses RLS, so does the function. Useful on purpose, dangerous by accident.
Foreign key and unique constraint checks always bypass row security. A duplicate-key error can confirm a row exists that the querying role cannot see.
COPY orders TO applies SELECT policies for a non-exempt role, so an "export everything" script quietly exports a subset.
pg_dump sets row_security = off and errors out if the dumping role cannot bypass RLS. Passing --enable-row-security makes the dump succeed and contain only visible rows, which is an incomplete backup. Document which role takes backups.
With row_security = off, a query that needs a policy errors rather than silently returning fewer rows. That's a feature for batch jobs.
Policies are per-table. A policy on the partitioned parent does not protect a partition queried directly by name, and the same goes for inheritance children.
Logical replication operates at the WAL level and ignores RLS entirely — a publication built off an RLS-protected table replicates every row to the subscriber, policies or not. Don't assume a replica inherits your row security.

RLS landed in 9.5 and AS RESTRICTIVE in 10, so all of this works on every supported major version.

Pre-flight checklist

App role: rolsuper = f, rolbypassrls = f, verified in production.
FORCE ROW LEVEL SECURITY on every table the owner role touches.
At least one permissive policy for every command the app issues.
SET LOCAL wired into the pooler or transaction hook, never SET.
Composite indexes led by the policy column.
A CI test asserting tenant B sees zero of tenant A's rows.
Backup role documented, with its RLS exemption explicit.
Migration role documented (it usually needs to bypass; know that it does).
No TRUNCATE grant on RLS-protected tables.
Views audited for security_invoker, functions audited for SECURITY DEFINER.

Test it or it isn't real

SET ROLE app;
BEGIN;
  SET LOCAL app.tenant_id = '1';
  SELECT count(*) = 3 AS tenant1_ok FROM orders;
  SET LOCAL app.tenant_id = '2';
  SELECT count(*) = 0 AS no_leak_ok FROM orders WHERE customer = 'tenant1-acme';
  SAVEPOINT s;
  INSERT INTO orders (tenant_id, customer, amount_cents) VALUES (1, 'x', 1);
  -- expect: ERROR: new row violates row-level security policy for table "orders"
  ROLLBACK TO SAVEPOINT s;
COMMIT;
RESET ROLE;

Run it in CI on every migration. The failure mode I keep meeting: a policy that was fine on the day it shipped, then quietly stopped applying six months later when someone changed the connection role and nobody re-ran this test.

Tags: postgres database sql security ## Wrapping up

Row-level security earns its keep the moment you stop treating it as a checkbox and start treating it as code: it needs the same review, the same indexes, and the same tests as anything else that decides who sees what. The five policies above cover almost every shape of multi-tenant, per-user, and append-only access I've run into, and the edge cases are the ones that turn "we have RLS" into an incident report. Enable it, force it, index it, and — most importantly — write the CI test that fails loudly when someone quietly breaks it later.

If you want a second set of eyes on whether your policies are actually doing what you think, checks like the ones in MyDBA are a fast way to catch a missing FORCE, an unindexed policy column, or a role that shouldn't have BYPASSRLS before it becomes a postmortem.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-row-level-security-multi-tenant

If this saved you a debugging session, give your RLS setup a quick audit with MyDBA — it's a lot cheaper than the incident.

Postgres Full-Text Search: The Setup You Should Ship

Philip McClarence — Fri, 07 Aug 2026 10:00:18 +0000

Postgres full-text search works out of the box for most apps, and you probably don't need Elasticsearch to get good results. I've built app search on Postgres four times and torn it out for Elasticsearch exactly once. That one time was justified — the product grew a facet-heavy search UI that Postgres was never meant to serve. That distinction matters more than row count, and it's the whole thesis of this article. The other three times, moving would have cost a team a cluster, a sync pipeline, and a permanent class of "the search index is stale" bugs.

📖 Read the full guide: Postgres Full-Text Search: tsvector, GIN, and Real Limits

The companion video walks the mental model at a whiteboard pace: watch it here. This article is the copy-paste version with the edge cases that bite in production.

TL;DR

Store a tsvector in a stored generated column, index it with GIN. That's the default shape.
Parse user input with websearch_to_tsquery. I default to it and have never regretted it.
Order with ts_rank_cd, blended with recency or popularity. Raw rank alone is a weak signal.
Snippet with ts_headline, applied only to the page you're about to render.
Typos and accents are pg_trgm plus unaccent, not core FTS.

If you're under roughly ten million documents in one language, you probably don't need Elasticsearch. Treat that number as a smell test, not a law — I've seen twenty million rows behave fine on decent hardware, and I've seen three million rows struggle because of pathological ts_rank_cd over huge documents.

The primitives, in 90 seconds

tsvector is a sorted list of distinct normalized lexemes, optionally carrying positions and weight labels. tsquery holds search terms joined by &, |, !, and the phrase operator <->. The @@ operator asks whether one matches the other.

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');

                        to_tsvector
------------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
(1 row)

Note what happened. the and over vanished as stop words. foxes became fox, jumped became jump, lazy became lazi. The integers are token positions in the original stream, which is why position 1 is missing.

That stemming is why a search for runs finds a document containing running. It's dictionary normalization at index time, not a LIKE '%run%' scan pretending to be search. And those position integers aren't decoration — they're what makes phrase search and ts_rank_cd possible.

tsquery syntax: & | ! <-> and prefixes

SELECT to_tsquery('english', 'index &amp; !gist'),
       to_tsquery('english', 'bitmap &lt;-&gt; heap'),
       to_tsquery('english', 'index &lt;2&gt; scan'),
       to_tsquery('english', 'run:*');

`means "N tokens apart."run:*is a prefix match. All useful. All dangerous if you wire them to a text box, becauseto_tsquery` demands syntactically valid input and throws a syntax error on anything else:

`sql SELECT to_tsquery('english', 'fast "index scan" or bitmap -gist'); ERROR: syntax error in tsquery: "fast "index scan" or bitmap -gist" `

Here's the same input through the three safe parsers:

`sql SELECT plainto_tsquery('english', 'fast "index scan" or bitmap -gist') AS plain, phraseto_tsquery('english', 'fast "index scan" or bitmap -gist') AS phrase, websearch_to_tsquery('english','fast "index scan" or bitmap -gist') AS websearch \gx `

` -[ RECORD 1 ]------------------------------------------------------ plain | 'fast' & 'index' & 'scan' & 'bitmap' & 'gist' phrase | 'fast' <-> 'index' <-> 'scan' <2> 'bitmap' <-> 'gist' websearch | 'fast' & 'index' <-> 'scan' | 'bitmap' & !'gist' `

plainto_tsquery ANDs everything. phraseto_tsquery forces one rigid phrase (the <2> is the stop word or leaving a positional hole). websearch_to_tsquery honors quotes, or, and a leading minus — exactly what users already expect from a search box. It landed in PostgreSQL 11. Use it.

Making it fast: generated column + GIN index

`sql
CREATE TABLE article (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
published_at timestamptz NOT NULL DEFAULT now(),
search_vec tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED
);

CREATE INDEX article_search_vec_gin ON article USING GIN (search_vec);
`

Read that generated expression carefully, because this is the most common mistake I see: you must pass the configuration as a literal. The one-argument to_tsvector(text) is only STABLE, since it reads the default_text_search_config GUC. Generated columns and expression indexes require IMMUTABLE. Write to_tsvector('english', body) or Postgres rejects the DDL.

setweight labels every lexeme A/B/C/D. Title gets A, body gets B, and the ranking functions weight them {D, C, B, A} = {0.1, 0.2, 0.4, 1.0} by default — a title hit ends up outweighing a body hit by 2.5x in ts_rank_cd. Also note coalesce: a NULL anywhere in the expression nulls the whole vector, and NULL never matches @@.

Stored generated columns arrived in PG12. On PG11, or when you need per-row language (a lang column feeding a regconfig), you still need the old BEFORE INSERT OR UPDATE trigger calling tsvector_update_trigger. It works fine. It's just more moving parts.

GIN is the right index type here. It builds around three times slower than GiST and it's bigger, but it searches faster and suits read-heavy data — which is what a search table is.

Proving the GIN index is used

`sql EXPLAIN (ANALYZE, BUFFERS) SELECT id, title FROM article WHERE search_vec @@ websearch_to_tsquery('english', 'bitmap heap scan'); `

` Bitmap Heap Scan on article (cost=44.29..1893.55 rows=489 width=42) (actual time=0.612..3.887 rows=471 loops=1) Recheck Cond: (search_vec @@ websearch_to_tsquery('english'::text, 'bitmap heap scan'::text)) Heap Blocks: exact=433 Buffers: shared hit=449 -> Bitmap Index Scan on article_search_vec_gin (cost=0.00..44.17 rows=489 width=0) (actual time=0.531..0.531 rows=471 loops=1) Index Cond: (search_vec @@ websearch_to_tsquery('english'::text, 'bitmap heap scan'::text)) Buffers: shared hit=16 Planning Time: 0.214 ms Execution Time: 4.019 ms `

A GIN scan always produces a bitmap, so you get Bitmap Index Scan feeding Bitmap Heap Scan. The Recheck Cond line isn't a failure. GIN hands back candidate rows and the heap confirms them; if the bitmap goes lossy it degrades to page granularity and rechecks every tuple on those pages. Skip the GIN index entirely and you get a Seq Scan that scales linearly with table size and gets ugly fast.

One gotcha for freshly loaded tables: GIN's fastupdate buffers new entries in a pending list sized by gin_pending_list_limit. Until vacuum or a limit overflow flushes it, scans have to read that unsorted list and timings look erratic. Run VACUUM ANALYZE after a bulk load before you benchmark anything.

Ranking with ts_rank and ts_rank_cd

`sql SELECT id, title, ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', search_vec, q, 32) AS rank FROM article, websearch_to_tsquery('english', '"index scan" or bitmap') q WHERE search_vec @@ q ORDER BY rank DESC, published_at DESC LIMIT 10; `

ts_rank scores on term frequency. ts_rank_cd does cover density, which accounts for how close the matched lexemes sit, and therefore needs the positional data in your vector. I use ts_rank_cd for prose and ts_rank for short catalog fields.

That trailing 32 is the normalization bitmask: rank/(rank+1), squashing scores into 0..1 so I can blend them. 1 divides by 1 + log(length), 2 by raw length. Pick a normalization on purpose or long documents win everything.

Raw rank is a weak relevance signal on its own. Real ordering looks more like ORDER BY (0.7 * rank + 0.3 * popularity_score) DESC.

The cost you must know: ranking touches the tsvector of every matching row, so ORDER BY rank LIMIT 10 on a query matching 400k rows ranks 400k rows before truncating. Narrow the match set first with filters, or look at the RUM extension, which stores positions inside the index and can return ranked results without sorting the whole match set.

Highlighting with ts_headline

`sql WITH hits AS ( SELECT id, title, body, ts_rank_cd(search_vec, q, 32) AS rank FROM article, websearch_to_tsquery('english', 'bitmap heap scan') q WHERE search_vec @@ q ORDER BY rank DESC LIMIT 10 ) SELECT id, title, rank, ts_headline('english', body, websearch_to_tsquery('english', 'bitmap heap scan'), 'StartSel=, StopSel=, MaxWords=35, MinWords=15, MaxFragments=2, FragmentDelimiter= … ') AS snippet FROM hits; `

The CTE exists for one reason. ts_headline re-parses the original document text and cannot use the index at all. The docs say plainly to use it only on the rows you're displaying. Apply it to an unbounded match set and you'll parse a million bodies to render ten.

Typos, autocomplete, and pg_trgm fuzzy search

Core full-text search has no fuzzy matching inside tsquery. None. If a user types "fxo" instead of "fox," websearch_to_tsquery won't save you. That's pg_trgm's job:

`sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX article_title_trgm ON article USING GIN (title gin_trgm_ops);

SELECT title, similarity(title, 'postgers vaccum') AS sim
FROM article
WHERE title % 'postgers vaccum'
ORDER BY sim DESC LIMIT 5;
`

Trigrams work on raw text independently of your text search config, which makes them a clean fallback: run FTS, and if it returns nothing, retry with %.

For accents, unaccent strips diacritics, but unaccent(text) is STABLE because it depends on the default dictionary. To index it you need a wrapper:

`sql CREATE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT public.unaccent('public.unaccent', $1) $$; `

Autocomplete: prefix tsquery ('foo:*') is cheaper for a single leading token; trigram similarity handles mid-word and misspelled prefixes, since it doesn't care about word boundaries the way FTS prefix matching does.

Postgres vs Elasticsearch: the honest line

Dimension	Postgres FTS	Elasticsearch / OpenSearch
Relevance tuning	`setweight` A–D, normalization bitmask, hand-blended SQL	BM25 by default, per-field boosts, iterated by non-DBAs
Typo tolerance	`pg_trgm` bolted alongside, not inside `tsquery`	Fuzzy queries built in
Facets / aggregations	`GROUP BY` over the match set, fine until it isn't	Aggregations designed for it
Scale	Single-digit millions is comfortable	Horizontal sharding across nodes
Ops burden	Zero new infrastructure	A cluster, a sync pipeline, its own on-call
Consistency with source data	Same transaction, always current	Eventually consistent by construction

The breaking point is feature demand, not row count. Dozens of language analyzers, index-time A/B testing of analyzers, search-as-you-type at massive scale, product managers who want to tune boosts weekly — that's when you move.

Middle ground before you run two datastores: ParadeDB's pg_search embeds a Tantivy BM25 index inside Postgres, ZomboDB bridges to Elasticsearch from SQL, and logical decoding into OpenSearch is the well-trodden replication path.

Gotchas nobody mentions until 2am

GIN bloat. REINDEX INDEX CONCURRENTLY article_search_vec_gin; (PG12+) rebuilds without blocking writes. Schedule it proactively on high-churn tables rather than waiting for symptoms.
Size and WAL. A stored tsvector roughly doubles a text-heavy table and is replicated like any other column. Expect more disk and more replication lag under load; your standbys will notice.
Config changes are migrations. Switching 'english' to 'simple' means regenerating every vector and rebuilding the index. There's no in-place migration.
maintenance_work_mem dominates GIN build time. Raise it in the session doing the build or a multi-million-row build will crawl.
1 MB limit on a tsvector, with 16383 as the maximum position. Truncate huge documents deliberately.
default_text_search_config is a session setting. Which is precisely why your generated column hardcodes 'english'.

The copy-paste starter

`sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE article (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
published_at timestamptz NOT NULL DEFAULT now(),
search_vec tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED
);

CREATE INDEX article_search_vec_gin ON article USING GIN (search_vec);
CREATE INDEX article_title_trgm ON article USING GIN (title gin_trgm_ops);

INSERT INTO article (title, body) VALUES
('Bitmap heap scans explained', 'A GIN index scan builds a bitmap, then the heap rechecks each candidate row.'),
('Tuning maintenance_work_mem', 'Raising maintenance_work_mem substantially reduces GIN index build time.'),
('Phrase search with tsquery', 'Positional information enables the <-> operator and cover density ranking.');

VACUUM ANALYZE article;

WITH q AS (SELECT websearch_to_tsquery('english', 'gin "index scan"') AS tsq),
hits AS (
SELECT a.id, a.title, a.body,
ts_rank_cd(a.search_vec, q.tsq, 32) AS rank
FROM article a, q
WHERE a.search_vec @@ q.tsq
ORDER BY rank DESC, a.published_at DESC
LIMIT 10
)
SELECT h.id, h.title, round(h.rank::numeric, 4) AS rank,
ts_headline('english', h.body, q.tsq,
'StartSel=, StopSel=, MaxWords=30, MinWords=10') AS snippet
FROM hits h, q;
`

Then run EXPLAIN (ANALYZE, BUFFERS) on the inner query and confirm you see Bitmap Index Scan on article_search_vec_gin. If you see a Seq Scan, your tsquery config literal doesn't match the column's, or the table is too small for the planner to care.

That's the whole thing. Ship it, watch p95, and revisit when a product requirement — not a row count — forces your hand.

Tags: postgres, database, sql, performance ## Keep an eye on it after you ship

The starter above gets you a correct, fast implementation on day one, but full-text search tends to degrade quietly. GIN indexes bloat under heavy update churn, search_vec recalculates on every write to title or body, and nobody notices until a dashboard shows p95 creeping from 4ms to 400ms over a few months. Whatever you use to watch autovacuum lag, index bloat, and slow queries on the rest of your database should be watching this table too — it's not a special case, it's just another write-heavy index that needs the same attention as your primary keys. I run MyDBA alongside the apps I build this way specifically because bloat and autovacuum drift are the two things that silently wreck FTS performance, and catching them before they show up in query latency is a lot cheaper than debugging a "why did search get slow" ticket three months from now.

Ship the boring version first

Postgres full-text search isn't a compromise you make until you can afford Elasticsearch — for the overwhelming majority of apps, it's the correct final answer. You get relevance ranking, phrase search, typo tolerance via pg_trgm, and results that are always transactionally consistent with the row you just wrote, all without a second datastore, a sync pipeline, or a new class of on-call pages. Reach for something heavier only when a real product requirement demands it: deep facets, dozens of language analyzers, or search-as-you-type at a scale Postgres genuinely can't hold. Until then, the setup in this article is the version worth shipping.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-full-text-search-guide

If you're running Postgres in production, it's worth five minutes to point MyDBA at it and see what your indexes and autovacuum settings are actually doing.

Postgres Wait Events: Stop Guessing, Start Counting

Philip McClarence — Thu, 06 Aug 2026 10:00:42 +0000

TL;DR

wait_event_type is the bucket (nine of them), wait_event is the specific detail. Read both or you learn nothing.
Postgres reports state, never accumulated wait duration. One SELECT * FROM pg_stat_activity is a single frame of a movie. Sample it.
Filter out state='idle' and wait_event_type='Activity' before you count anything, or background processes sitting in their main loop will drown your profile.
Rough ownership: Lock is your transactions, LWLock is Postgres fighting itself, IO is storage or a missing index, Client is your application, Timeout is usually vacuum throttling.
The sampler and rollup SQL are below. Copy them, run them for sixty seconds, then argue about shared_buffers.

📖 Read the full guide: Postgres Wait Events: What Every Backend Is Blocked On

The video version

That clip is the three-minute mental model: two columns, nine buckets, sample don't snapshot. This post is the toolkit. Sampler script, rollup query with the averaging math spelled out, a per-event decoder table, the blocking-tree query, and the version gotchas that make ten-year-old blog queries return zero rows on a modern server.

Two columns, nine buckets

The wait columns landed in 9.6, replacing the old boolean waiting flag. wait_event_type tells you the category, wait_event names the exact thing. The documented types, with who owns the problem:

wait_event_type	What it is	Who owns the fix
Activity	Background process idling in its main loop	Nobody. Noise. Exclude it.
BufferPin	Waiting for exclusive access to a pinned buffer	Rare — usually vacuum vs. a long-running scan
Client	Waiting on your application over the socket	Your app team
Extension	Whatever extension author registered it	Read their docs
IO	Reading or writing files	Storage, or a query reading pages it shouldn't need
IPC	Waiting on another Postgres process	Parallel workers, sync replication
Lock	Heavyweight locks	Application-level contention, always
LWLock	Internal shared memory contention	Postgres versus Postgres
Timeout	A deliberate sleep	Vacuum cost delay, mostly

On PG 17 and later, SELECT * FROM pg_wait_events lists every event name and description for the exact version you're running. Stop searching blog posts for what WalSync means. Ask the server.

The trap: NULL does not mean CPU

Every wait-event tutorial I've read says "NULL wait event means the backend is on CPU." The docs are more careful, and so should you be: a NULL wait_event means the backend is not currently waiting on a tracked wait event. That's usually on-CPU. It is not a guarantee. Untracked waits exist, and a backend can be runnable but not scheduled.

Practically: if your profile shows 12 active sessions with wait_event IS NULL on a 4-core box, do not immediately go tune the planner. Look at the OS first. top, vmstat 1, whatever your monitoring gives you. If system CPU is at 30% and load average is 40, you have a scheduler or noisy-neighbour problem, not a query problem. I've watched a team spend a day rewriting a join because the wait profile "showed CPU," when the actual answer was a hypervisor stealing 60% of the cycles.

One snapshot tells you nothing

Postgres core does not accumulate per-event wait time. There is no total_wait_ms column, anywhere. That is the entire reason the pg_wait_sampling extension exists.

So you sample. The arithmetic is simple enough to do in your head. Take N samples over a window. If a given wait_event shows up in M rows across those samples, then M/N is the average number of sessions sitting on that event during the window. That number has a name in other database communities: average active sessions (AAS). Compare it to your core count and you know instantly whether the box is saturated.

Concretely: 600 samples at 100ms intervals is a 60-second window. If Lock:transactionid appears in 5,400 rows, that's 5400/600 = 9 sessions, on average, doing nothing but waiting for other transactions to commit. For a whole minute. That's not a tuning opportunity, that's an outage.

100ms is my default. It's cheap, and it catches anything lasting longer than a tenth of a second, which is everything that matters during an incident. Going to 10ms is fine on a quiet box and starts to cost you on a busy one.

The 10-line sampler you can run right now

CREATE UNLOGGED TABLE IF NOT EXISTS wait_samples (
  ts              timestamptz NOT NULL DEFAULT clock_timestamp(),
  pid             int,
  leader_pid      int,
  state           text,
  wait_event_type text,
  wait_event      text,
  query_id        bigint,
  datname         text,
  usename         text,
  query           text
);

UNLOGGED matters. You are writing a few hundred rows a second during an incident and you do not want that in WAL, competing with the thing you're diagnosing.

Now the collector. Put this in sample.sql:

INSERT INTO wait_samples
  (pid, leader_pid, state, wait_event_type, wait_event,
   query_id, datname, usename, query)
SELECT pid, leader_pid, state, wait_event_type, wait_event,
       query_id, datname, usename, left(query, 120)
FROM pg_stat_activity
WHERE pid &lt;&gt; pg_backend_pid()          -- don't sample the sampler
  AND state &lt;&gt; 'idle'                  -- idle backends are not waiting on anything you care about
  AND wait_event_type IS DISTINCT FROM 'Activity';  -- walwriter et al. idling in their main loop

Run it:

psql -d prod -f - &lt;&lt;'EOF'
\i sample.sql
\watch 0.1
EOF

Or, if \watch with a sub-second interval isn't available on your client version, a shell loop:

end=$((SECONDS+60))
while [ $SECONDS -lt $end ]; do
  psql -qAt -d prod -f sample.sql &gt;/dev/null
  sleep 0.1
done

Skip the Activity filter once and you'll see why it's there. WalWriterMain, CheckpointerMain, AutoVacuumMain and LogicalLauncherMain will be present in essentially every sample, and your top wait will be "background workers successfully doing nothing."

Permissions: track_activities must be on (it is by default), and a non-superuser needs pg_monitor or pg_read_all_stats to see other users' query text and wait details. Without it those columns come back NULL and you will misread the entire profile as idle. GRANT pg_monitor TO app_dba; and move on.

The rollup

WITH n AS (SELECT count(DISTINCT ts)::numeric AS samples FROM wait_samples)
SELECT coalesce(wait_event_type, 'CPU/untracked') AS type,
       coalesce(wait_event, '-')                  AS event,
       count(*)                                   AS hits,
       round(count(*) / n.samples, 2)             AS avg_active_sessions,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM wait_samples, n
GROUP BY 1, 2, n.samples
ORDER BY hits DESC
LIMIT 15;

Real output from a payments box last month, 600 samples, 8 vCPU:

      type      |      event       | hits | avg_active_sessions | pct
----------------+------------------+------+---------------------+------
 Lock           | transactionid    | 5412 |                9.02 | 63.1
 CPU/untracked  | -                | 1188 |                1.98 | 13.9
 Client         | ClientRead       |  901 |                1.50 | 10.5
 LWLock         | LockManager      |  402 |                0.67 |  4.7
 IO             | DataFileRead     |  331 |                0.55 |  3.9
 Lock           | tuple            |  208 |                0.35 |  2.4
 IO             | WALSync          |   77 |                0.13 |  0.9
 Timeout        | VacuumDelay      |   41 |                0.07 |  0.5
(8 rows)

Read it out loud. Eight cores, 14.3 AAS total, and 9 of those sessions are parked on Lock:transactionid. Nobody is CPU-bound: 1.98 AAS of CPU on an 8-core box is 25% busy. This database is not slow. It is blocked. Every millisecond spent on work_mem or random_page_cost here is wasted. Go find the transaction everyone is queued behind.

Decoder ring

Event	What it means	First thing to check
`Lock:transactionid`	Waiting for another txn to commit or roll back (row conflict)	`pg_blocking_pids`, transaction length
`Lock:relation`	Table/index-level lock. Someone ran DDL, `VACUUM FULL`, or `REINDEX`	Who holds the AccessExclusiveLock, right now
`Lock:tuple`	Queued behind other waiters for one hot row	Counter/sequence-in-a-table patterns
`LWLock:WALInsert`	Contention inserting into WAL buffers under write load	`wal_buffers`, commit rate, `synchronous_commit`
`LWLock:BufferMapping`	Shared buffer lookup table contention, high eviction churn	Working set vs `shared_buffers`, seq scans
`LWLock:BufferContent`	Multiple backends fighting over one page's content	Hot index root/leaf pages, update patterns
`LWLock:LockManager`	Fast-path lock slots exhausted, locks spilled to shared manager	Partition count per query, prepared statements
`IO:DataFileRead`	Buffer miss, reading a relation page from the filesystem	`EXPLAIN (ANALYZE, BUFFERS)`, missing index
`IO:DataFileWrite`	Backend writing dirty pages itself	Checkpoint settings, bgwriter, write burst
`IO:WALWrite` / `IO:WALSync`	Commit-path WAL write and fsync	Commit rate, fsync latency, storage
`IO:BufFileRead` / `IO:BufFileWrite`	Temp file spill from sorts, hashes, materialize	`work_mem`, `log_temp_files = 0`
`Client:ClientRead`	Waiting for the client to send the next command	Your app. Round trips, pooling
`Client:ClientWrite`	Client isn't reading results fast enough	Result set size, network, cursor use
`IPC:SyncRep`	Waiting for a synchronous standby to acknowledge commit	Replication RTT, `synchronous_standby_names`
`Timeout:VacuumDelay`	Cost-based vacuum throttling	`vacuum_cost_delay`, `vacuum_cost_limit`
`BufferPin`	Waiting for exclusive access to a pinned buffer	Long-running scans overlapping vacuum

Lock waits: find the blocker, not the victim

Your profile says Lock:transactionid. Every session you look at is a victim. You need the root of the tree.

WITH RECURSIVE tree AS (
  SELECT a.pid, a.pid AS root, 0 AS depth,
         a.wait_event_type, a.wait_event, a.state,
         a.xact_start, left(a.query, 80) AS query
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity b
                WHERE a.pid = ANY (pg_blocking_pids(b.pid)))
  UNION ALL
  SELECT c.pid, t.root, t.depth + 1,
         c.wait_event_type, c.wait_event, c.state,
         c.xact_start, left(c.query, 80)
  FROM pg_stat_activity c
  JOIN tree t ON t.pid = ANY (pg_blocking_pids(c.pid))
)
SELECT repeat('  ', depth) || pid AS pid_tree, depth, state,
       wait_event_type, wait_event,
       now() - xact_start AS xact_age, query
FROM tree
ORDER BY root, depth;

pg_blocking_pids() arrived in 9.6 and is the supported way to do this. Stop hand-joining pg_locks to itself; that query is wrong in edge cases and you will not notice.

The depth-0 row is your culprit. Nine times out of ten it's in state = 'idle in transaction' with an xact_age of four minutes, because someone opened a transaction, called an external API, and the API is timing out.

Fixes, in order of how much I trust them: shorten transactions (do the API call outside the transaction), batch updates in a consistent key order to avoid pile-ups, set lock_timeout on migration sessions so DDL fails fast instead of freezing the table, and turn on log_lock_waits. That last one is off by default and logs any wait longer than deadlock_timeout (1s default). Turn it on everywhere. It costs nothing and it gives you the post-mortem you'll want at 3am.

Lock:relation is a different animal. That's table-level, which means DDL, VACUUM FULL, or an unlucky ALTER TABLE sitting behind a long read and blocking everything behind it. Lock:tuple means a queue on one specific row, which almost always means a counter table.

LWLock waits: when Postgres fights itself

LWLock:WALInsert is a write-throughput ceiling. Backends are queuing to copy their WAL records into shared buffers. Look at wal_buffers, at whether you're committing one tiny row at a time from a hundred connections, and at whether synchronous_commit = off is acceptable for some of your workload (it is, for anything you'd be willing to lose the last 200ms of).

LWLock:BufferMapping and BufferContent mean shared buffer churn or a genuinely hot page. Bigger shared_buffers sometimes helps the first, never helps the second.

LWLock:LockManager is the one that catches people out. Each backend gets a fixed number of fast-path lock slots (16 per backend before PG 18). Blow past that and every lock goes through the shared lock manager. The classic trigger is a query touching a partitioned table with 200 partitions: even with pruning, planning can grab locks on more relations than you'd think. PG 18 scales those slots with max_locks_per_transaction, which helps, but the real fix is fewer partitions per query.

Here's my position: LWLock waits are almost never fixed by a config knob alone. They're a symptom of a workload shape. Change the shape.

IO waits: disk, or a missing index pretending to be disk

IO:DataFileRead means a page wasn't in shared_buffers. That's all it means. The read may well have been served from the OS page cache in 20 microseconds. I have watched people buy faster NVMe because of a DataFileRead-heavy profile, when the actual problem was a sequential scan on a 40GB table that needed one index.

Cross-check before you conclude anything. Turn on track_io_timing (off by default) so EXPLAIN (ANALYZE, BUFFERS) and pg_stat_statements report real I/O time. On PG 16+, pg_stat_io breaks reads and writes down by backend type and context, which tells you whether it's client backends, autovacuum, or the checkpointer. If EXPLAIN (ANALYZE, BUFFERS) shows a million shared reads and near-zero I/O time, your storage is fine and your query plan is not.

IO:BufFileRead / BufFileWrite means temp files: sorts, hashes, or materialization spilling past work_mem. Set log_temp_files = 0, find the queries, then decide whether to raise work_mem (per-session, not globally) or fix the plan.

IO:WALWrite and IO:WALSync are commit-bound. Query tuning won't touch them. Look at fsync latency on the WAL device and at your commit rate.

Client waits are almost always your application's fault

If your profile is 70% Client:ClientRead, no amount of shared_buffers tuning will save you. Go fix your ORM.

Client:ClientRead means the backend has finished and is waiting for the client to say something. Dominant ClientRead means chatty round trips, no connection pooler, or sessions holding a transaction open while doing nothing. That last one is the actual bug, and this query separates it from the harmless case:

SELECT state,
       count(*) FILTER (WHERE wait_event = 'ClientRead') AS client_read,
       max(now() - xact_start)                           AS oldest_xact,
       max(now() - state_change)                         AS oldest_idle
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY 2 DESC;

        state        | client_read |   oldest_xact   |   oldest_idle
---------------------+-------------+-----------------+-----------------
 idle                |         214 |                 | 00:41:12.339
 idle in transaction |          17 | 00:06:48.771    | 00:06:44.102
 active              |           3 | 00:00:00.412    | 00:00:00.400

214 plain-idle sessions on ClientRead is a pool at rest. Ignore them, which is exactly what the state <> 'idle' filter in the sampler does. The 17 sessions idle in transaction for nearly seven minutes are holding locks and pinning old snapshots so vacuum can't clean up. Set idle_in_transaction_session_timeout and make the application fail loudly instead of quietly bloating your tables.

Conflating a plain-idle pooled connection with an idle-in-transaction one is why people panic-tune connection pools that were never the problem. The state column, not the wait event alone, is what tells them apart.

Going continuous

Manual sampling is an incident tool. For trending, either install pg_wait_sampling, which runs a background worker doing exactly this and exposes both a live profile and per-process history without you polling, or keep your own table and store query_id with every sample.

query_id landed in pg_stat_activity in PG 14 and is populated when compute_query_id is on. It's already in my sampler DDL above. With it you can answer the question that actually ends arguments:

WITH n AS (SELECT count(DISTINCT ts)::numeric AS samples FROM wait_samples)
SELECT s.queryid, round(count(*) / n.samples, 2) AS aas_lock,
       left(s.query, 70) AS query
FROM wait_samples w, n
JOIN pg_stat_statements s ON s.queryid = w.query_id
WHERE w.wait_event_type = 'Lock'
GROUP BY s.queryid, s.query, n.samples
ORDER BY 2 DESC LIMIT 5;

"Which query burns the most Lock:transactionid time" now has a numeric answer instead of a hunch.

Disclosure: I write for pgdba, and we run MyDBA, which does a free health check if you'd rather have someone else read the profile. The queries above work fine without it.

Version gotchas that will bite you

PG 13 standardized LWLock event names (buffer_mapping became BufferMapping) and merged LWLockNamed and LWLockTranche into a single LWLock type. Any monitoring query you copied from a 2018 blog post will silently return zero rows on a modern server — which is worse than an error, because it reads as "no contention" instead of "your query is stale." PG 13 also added leader_pid, so parallel workers stop looking like mystery sessions with no query text — join on it to roll workers back up to their query. PG 16 reworked relation extension locking. PG 17 gave you pg_wait_events so you can look events up locally.

And again: pg_monitor or pg_read_all_stats, or you're profiling NULLs.

60-second triage runbook

Create the unlogged table, start the sampler at \watch 0.1, wait 60 seconds.
Run the rollup. Sum the avg_active_sessions column.
Compare that total to your core count. Above it means saturation, below it means you have a latency problem, not a capacity one.
Dominant Lock → run the blocking tree, kill or fix the depth-0 session, turn on log_lock_waits.
Dominant Client:ClientRead → check the idle-in-transaction breakdown, set idle_in_transaction_session_timeout, talk to the app team.
Dominant IO → track_io_timing = on, check pg_stat_io, then EXPLAIN (ANALYZE, BUFFERS) the top query before touching storage.
Dominant LWLock → identify the specific lock, look at workload shape (partition count, commit rate, buffer churn) before any config change.
Dominant NULL → verify against OS CPU before you blame the planner.
DROP TABLE wait_samples; when you're done. It's unlogged, but it's still disk.

Nine buckets, one sampler, one rollup. Write down the numbers. Not the vibe. Stop guessing, count something.

No pg_hba.conf Entry for Host: 5 Real Causes & Fixes

Philip McClarence — Thu, 06 Aug 2026 10:00:35 +0000

I've been paged for this error at 2am more times than I care to count. I've also watched a well-meaning junior "fix" it at 2:07am by appending host all all 0.0.0.0/0 trust to a cluster with a public IP, then going back to bed. The connection worked. So did everyone else's.

📖 Read the full guide: Fix "no pg_hba.conf entry for host" (Full Cause Map)

Here's the short version: no pg_hba.conf entry for host means your TCP connection reached Postgres fine — the failure is at the authentication-matching step, not the network step. Postgres tells you exactly which rule it needed, in the server log, on one line. Most advice you'll find skips reading that line and jumps straight to editing the file.

TL;DR — the 60-second triage

Read the server log line, not the client error. It names the host address, user, database, and encryption state Postgres matched against. That's your entire diagnosis.
Confirm where the server is listening: SHOW listen_addresses; and ss -ltnp 'sport = :5432'. (If you got the pg_hba error at all, this is already fine — more on that below.)
Read the file as Postgres parsed it: SELECT * FROM pg_hba_file_rules; — check the error column before you trust anything.
Add the narrowest rule that matches, then reload: SELECT pg_reload_conf(); No restart needed for pg_hba.conf changes.

What this error actually means (and what it doesn't)

pg_hba is the second gate, not the first. The sequence is:

TCP connect to the postmaster — governed by listen_addresses, the OS firewall, cloud security groups, and routing.
The postmaster matches the incoming connection against pg_hba.conf and either authenticates or refuses.

If you're seeing no pg_hba.conf entry for host, stage one already succeeded. The client opened a TCP session, the postmaster read the startup packet, and the server decided it had nothing to say to you. Your firewall is fine. listen_addresses is fine. The port is open.

When those are broken, you get a completely different failure — a postgres remote connection error that looks like this:

psql: error: connection to server at "db01" (10.0.3.10), port 5432 failed:
        Connection refused
        Is the server running on that host and accepting TCP/IP connections?

or a hang followed by a timeout. Neither of those is an HBA problem, and neither will ever produce the pg_hba error text. Roughly half the answers I read online respond to "no pg_hba.conf entry" with "did you set listen_addresses = '*'?" If that mattered, the asker wouldn't have gotten this error in the first place.

Read the log line before you touch anything

Here's the thing you actually need, straight out of the server log:

2026-08-04 02:14:07.118 UTC [21873] FATAL:  no pg_hba.conf entry for host "10.0.3.42", user "app", database "orders", no encryption

Decompose it:

Field	Value	What it tells you
host	`10.0.3.42`	The source address the server sees. Not what the client thinks it is.
user	`app`	The role from the startup packet, after any client-side mapping.
database	`orders`	The requested database. Note: not `all`, not the default.
encryption	`no encryption`	The connection isn't SSL, so `hostssl` lines can't match it.

That last field is the one people skim past. no encryption means a hostssl rule will never match this connection no matter how perfect the CIDR is. If it said SSL on, a hostnossl rule is excluded instead.

The 10.0.3.42 is the single most useful fact in the whole investigation, and it's the value you should be grepping your pg_hba.conf for — not the client's ip addr output. The server's view.

Root cause 1: Postgres isn't listening where you think

This is the pre-error case — you never get the HBA message, you get "Connection refused."

SHOW listen_addresses;
SHOW hba_file;

listen_addresses defaults to localhost in a source build. * binds all interfaces, 0.0.0.0 binds all IPv4, :: binds all IPv6, and an empty string disables TCP entirely (Unix sockets only). Confirm at the OS level:

$ ss -ltnp 'sport = :5432'
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port Process
LISTEN 0      244        127.0.0.1:5432       0.0.0.0:*     users:(("postgres",pid=1041,fd=6))

Bound to loopback only. No amount of pg_hba editing will help.

Two traps here. First, listen_addresses requires a full restart, not a reload — it's a postmaster-level parameter. Second, 0.0.0.0 covers IPv4 only; if your client resolves the hostname to an AAAA record you'll try to connect over IPv6 to nothing, and vice versa with ::.

Also check SHOW hba_file; before you edit anything. On Debian and Ubuntu packages it's /etc/postgresql/16/main/pg_hba.conf, not inside $PGDATA, and I have absolutely watched someone edit the wrong file for twenty minutes.

Root cause 2: no rule matches at all

A host record has five fields:

TYPE	DATABASE	USER	ADDRESS	METHOD
`host`	`orders`	`app`	`10.0.3.0/24`	`scram-sha-256`

Postgres reads the file top to bottom and the first matching record wins. There's no fall-through and no backup. If the first matching rule's authentication fails, the connection fails — even if a more permissive rule sits three lines below. Placement matters as much as content.

Verify the file parsed the way you think, via pg_hba_file_rules:

SELECT line_number, type, database, user_name, address, netmask, auth_method, error
FROM pg_hba_file_rules;

 line_number | type  |   database    | user_name |  address  |    netmask     |  auth_method   | error
-------------+-------+---------------+-----------+-----------+----------------+----------------+-------
          89 | local | {all}         | {all}     |           |                | peer           |
          91 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255| scram-sha-256  |
          93 | host  | {all}         | {all}     | ::1       | ffff:...:ffff  | scram-sha-256  |
          97 | host  | {orders}      | {app}     | 10.0.1.5  |                |                | invalid CIDR mask in address "10.0.1.5/24"

That error column is why this view exists. Non-null means the line didn't parse, and Postgres is still running on whatever was loaded before.

Root cause 3: the rule matches the host but not the rest

The subtle one, and the one that costs the most time.

`all` doesn't include `replication`

The all keyword in the database column matches every database except physical replication connections. If your standby is failing to connect and your log shows database "replication", you need an explicit line:

host    replication     repl    10.0.3.0/24    scram-sha-256

I've seen a failover rehearsal die on exactly this, with a perfectly good host all all 10.0.3.0/24 scram-sha-256 sitting right there. Logical replication connects to a named database and is matched by all; physical replication is not.

Connection type must match the encryption state

host matches both encrypted and unencrypted TCP. hostssl matches SSL-only. hostnossl matches non-SSL only. hostgssenc and hostnogssenc do the same for GSSAPI encryption. Cross-reference with the encryption clue in the log line — if it says "no encryption" and your only matching line is hostssl, Postgres skips past it as if it weren't there.

Role membership needs a `+`

A user column of app matches only the role literally named app. +app_users matches any member of app_users, directly or indirectly. @filename reads a list from an include file.

Method mismatch after PG14

Since PostgreSQL 14, password_encryption defaults to scram-sha-256. An md5 HBA line can still authenticate a role whose password is stored as a SCRAM hash — Postgres negotiates the right protocol underneath. But a scram-sha-256 line cannot authenticate a role still holding an old MD5 hash. It's a one-way door: once you migrate a password to SCRAM, the md5 line becomes unnecessary, but tightening a line to scram-sha-256 before every password is rotated will lock out anyone who hasn't reset theirs yet. Rotate the password, then tighten the line.

`peer` is local-only

It matches the OS username to the role name over Unix sockets. On a host line it's invalid and will never authenticate anyone.

You can also use reject deliberately, high in the file, to shut a subnet out regardless of what follows.

Root cause 4: CIDR arithmetic and address family

The most common typo I see is /32 where someone meant /24, or vice versa — a /32 matches exactly one address, and writing it when you meant a subnet locks out everyone except the one IP you happened to test with.

CIDR	IPv4 hosts covered	IPv6 equivalent	IPv6 hosts covered
/32	1 (single host)	/128	1 (single host)
/24	256	/64	typical LAN subnet
/16	65,536	/48	typical site allocation
/0	everything	/0	everything (never use this)

In concrete terms:

Notation	Covers
`10.0.3.42/32`	exactly that one IPv4 host
`10.0.3.0/24`	10.0.3.0 – 10.0.3.255
`10.0.0.0/16`	10.0.0.0 – 10.0.255.255
`0.0.0.0/0`	every IPv4 address on earth
`::1/128`	exactly that one IPv6 host
`fd00:dead:beef::/64`	that IPv6 subnet

Two failure modes. First, non-zero bits to the right of the mask are an error in modern versions: 10.0.1.5/24 gets flagged, not silently rounded to 10.0.1.0/24. pg_hba_file_rules.error will show it.

Second, an IPv4 entry doesn't match an IPv6 connection. 127.0.0.1/32 won't authorize a client arriving from ::1. That's why the shipped default file has both lines. If your app host resolves localhost to ::1, you need the IPv6 entry too.

samehost and samenet are decent shorthands: they match the server's own addresses and the subnets it's directly attached to.

Root cause 5: containers, NAT, and proxies

The address in the log is the last hop, not the original client — this is the classic pg_hba.conf docker connection refused scenario in disguise.

Docker bridge networks NAT everything through the gateway. Find the real numbers:

$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.Gateway}} {{.IPAddress}}{{end}}' app_web_1
172.18.0.1 172.18.0.7

Write your rule against 172.18.0.0/16, or the compose network's subnet, and be aware that this range is shared with anything else on that bridge. In Kubernetes, the pod CIDR is not the node IP — decide which one you're actually seeing before writing anything, and match your rule to what the log line says, not to what you assume the topology looks like.

PgBouncer and HAProxy collapse every client into a single source address. Your HBA rule authorizes the pooler; user-level control has to move into the pooler's own auth configuration.

The official postgres Docker image ships a permissive host rule and exposes POSTGRES_HOST_AUTH_METHOD to set it. Setting it to trust disables password checking for all host connections. Fine for a throwaway test container, unacceptable anywhere else. Set it to scram-sha-256 and provide a password.

Sanity check the exact path the server sees:

$ psql "host=10.0.3.10 port=5432 user=app dbname=orders sslmode=disable"

Applying changes safely: reload vs restart

pg_hba.conf takes effect on SIGHUP. No restart, no dropped sessions:

SELECT pg_reload_conf();

$ pg_ctl -D $PGDATA reload

If the file has a syntax error at reload time, the server logs it and keeps the previously loaded rules. That's a feature, and it's also why pg_hba_file_rules (which reads the file on disk) can disagree with what's actually enforced. Always check:

SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;

Empty result, then reload, then retest. listen_addresses and port still need a full restart.

The lines I actually use

# TYPE  DATABASE      USER        ADDRESS           METHOD

# local admin via unix socket, OS user must match role
local   all           all                           peer

# loopback: both families, always both
host    all           all         127.0.0.1/32      scram-sha-256
host    all           all         ::1/128           scram-sha-256

# application subnet, scoped to one db and one role
host    orders        +app_users  10.0.3.0/24       scram-sha-256

# physical replication: 'all' above does NOT cover this
hostssl replication   repl        10.0.3.0/24       scram-sha-256

# admin access from the bastion only, single host, SSL required
hostssl all           dbadmin     10.0.9.15/32      scram-sha-256

Never trust. Never 0.0.0.0/0. Not temporarily. "Temporary" HBA lines are the most durable objects in our industry, and the incident review is worse than the outage.

Audit before someone locks you out (or lets everyone in)

SELECT line_number, type, database, user_name, address, auth_method,
       CASE
         WHEN auth_method = 'trust'                    THEN 'CRITICAL: no authentication'
         WHEN address IN ('0.0.0.0','::')              THEN 'CRITICAL: open to the world'
         WHEN auth_method = 'md5'                      THEN 'WARN: legacy md5, move to scram-sha-256'
       END AS finding
FROM pg_hba_file_rules
WHERE auth_method IN ('trust','md5')
   OR address IN ('0.0.0.0','::')
ORDER BY line_number;

Run that on every cluster you own. Then look for shadowing: because first match wins, a broad rule near the top silently disables every narrower rule below it, and nothing warns you.

This is the check I got tired of writing by hand on every cluster. MyDBA runs this class of check continuously and flags rules that shadow or over-permit — that's the whole pitch.

If you'd rather see this walked through on a whiteboard, the companion video is up at https://www.youtube.com/@pgdba. Otherwise, you now have everything you need to fix a no pg_hba.conf entry for host error without guessing.

Tags: postgres database devops security ## One line to remember

Every one of these five failure modes produces the exact same eleven words on the client. The server log doesn't lie, and it doesn't summarize — it hands you host, user, database, and encryption state on a plate. Read that line first, every time, before you touch a single CIDR block or auth method. Nine times out of ten the fix is one narrow line added in the right place, reloaded, and retested. The tenth time is the one where someone already added trust at 2am, and now you're doing an access audit instead of a quick config fix.

pg_hba.conf isn't complicated once you stop treating it as a firewall and start treating it as what it is — a first-match ordered list of authentication contracts. Keep it narrow, keep it explicit about SSL and role membership, and check pg_hba_file_rules after every change instead of trusting that a reload did what you meant it to.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=no-pg-hba-conf-entry-for-host-fix

If you're maintaining more than a couple of clusters, it's worth having something watch your pg_hba rules for shadowing and over-permissive entries so you're not the one finding out at 2am. That's what MyDBA is for — give it a look.

Postgres max_connections: The Real Memory Math

Philip McClarence — Wed, 05 Aug 2026 10:00:37 +0000

I watched someone go from max_connections = 100 to 2000 at 4pm and take the box down the same night. The change itself took ten seconds. The 2am crash recovery took considerably longer.

📖 Read the full guide: max_connections in Postgres: A Memory Decision in Disguise

postgres max_connections isn't a knob that means "allow more clients." It resizes shared memory at startup and multiplies your worst-case per-backend memory exposure by a bigger number. Here's how to check your real usage, do the memory math, and decide whether a new value actually fits — before you touch it.

Quick answer

Run SHOW max_connections, then count only backend_type = 'client backend' in pg_stat_activity. Autovacuum workers and walsenders aren't your app.
Sample that count over hours, not once. A single count(*) tells you nothing about peak load.
Compute the ceiling first: shared_buffers + (per-backend private memory × max_connections) + concurrent work_mem allocations + maintenance_work_mem × autovacuum workers + OS headroom.
For most teams, a connection pooler solves this faster than a config change. PgBouncer in transaction mode lets 2000 clients share 40 server connections.
If you genuinely need to raise it: ALTER SYSTEM SET, check pending_restart, pre-flight shared_memory_size, raise standbys first, restart in a window with logs tailing.

What max_connections actually reserves

Postgres forks a dedicated backend process per client connection. Each connection is an OS process with its own address space and catalog caches before it runs a single query. There's no thread pool underneath — one process per connection, full stop.

max_connections has a postmaster context, meaning a reload won't apply it:

SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 100     | postmaster | f

It needs a restart because the value sizes fixed shared memory structures at startup. The lock table is sized from max_locks_per_transaction × (max_connections + max_prepared_transactions). The predicate lock table works the same way. Those are allocated once, up front.

Since PG 15 you can see the total without restarting, and even run this against a stopped or separate data directory to pre-flight a value:

$ postgres -C shared_memory_size -D $PGDATA
4489
$ postgres -C shared_memory_size_in_huge_pages -D $PGDATA
2245

Since PG 13, pg_shmem_allocations shows where it went:

SELECT name, pg_size_pretty(allocated_size) FROM pg_shmem_allocations
ORDER BY allocated_size DESC LIMIT 5;

Postgres connection limit check: find out what you're actually using

SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('superuser_reserved_connections')::int AS su_reserved,
  count(*) FILTER (WHERE backend_type = 'client backend') AS client_backends,
  current_setting('max_connections')::int
    - current_setting('superuser_reserved_connections')::int
    - count(*) FILTER (WHERE backend_type = 'client backend') AS available
FROM pg_stat_activity;

superuser_reserved_connections defaults to 3 and comes off the top for ordinary users. PG 16 added reserved_connections (default 0) for roles granted pg_use_reserved_connections. Since PG 12, max_wal_senders no longer counts against max_connections, so replication slots aren't eating your budget.

Now break it down by type. backend_type was added in PostgreSQL 10 so you stop conflating autovacuum workers and walsenders with real client load:

SELECT backend_type, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2 ORDER BY 3 DESC;

   backend_type    |        state        | count
-------------------+---------------------+-------
 client backend    | idle                |    61
 client backend    | active              |     9
 client backend    | idle in transaction |     7
 autovacuum worker | active              |     3
 walsender         | active              |     2
 background writer |                     |     1

Seventy client backends, and 61 are doing nothing. Run this on a cron every 30 seconds into a table for a day before you decide anything. One sample at 11am isn't a capacity plan.

If you're not superuser, query and state come back NULL for other roles unless you have pg_read_all_stats or pg_monitor.

Why you're seeing "sorry, too many clients already"

Symptom	Diagnostic	Fix that isn't "raise the number"
Idle-in-transaction leak	`state = 'idle in transaction'` with old `xact_start`	`idle_in_transaction_session_timeout` plus fix the app's transaction scope
App pool × pod count	pool size × replica count > max_connections	Shrink per-pod pool; 5 per pod × 40 pods is 200
No pooler	Connection count tracks request rate exactly	PgBouncer
Long analytics queries holding slots	`now() - query_start > '5 min'`	Separate read replica, `statement_timeout`
Deploy-time storms	Spike at rollout, settles in 60s	Staggered rollout, pooler absorbs it

SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start LIMIT 10;

idle_in_transaction_session_timeout defaults to 0 (disabled). Setting it to '5min' is almost always a better first move than a restart — and it applies with a reload, no downtime.

The work_mem memory calculation before you raise anything

Concrete box: 16 GB RAM, dedicated Postgres, shared_buffers = 4GB, work_mem = 16MB, hash_mem_multiplier = 2.0 (the PG 15 default), maintenance_work_mem = 512MB, three autovacuum workers.

First, measure real per-backend private memory. Don't use ps RSS — it counts shared_buffers pages the backend has touched and will tell you every backend uses 900 MB. smaps_rollup gives you the honest private footprint:

$ for p in $(pgrep -f "postgres: app"); do \
    grep -H Private_ /proc/$p/smaps_rollup 2>/dev/null | \
    awk -F: '{s+=$3} END {print s/1024" MB"}'; done | sort -rn | head -3
41.2 MB
12.8 MB
9.6 MB

Call it 10 MB typical. Now the arithmetic at 100 vs 400:

At max_connections = 100
  shared_buffers                4.0 GB
  private (100 × 10 MB)         1.0 GB
  work_mem: 25 active × 2 nodes × 16 MB × 2.0   1.6 GB
  maintenance (3 × 512 MB)      1.5 GB
  OS + page cache headroom      1.0 GB
                              --------
                                9.1 GB   fits in 16 GB

At max_connections = 400 (same active ratio)
  shared_buffers                4.0 GB
  private (400 × 10 MB)         4.0 GB
  work_mem: 100 active × 2 × 16 MB × 2.0        6.4 GB
  maintenance                   1.5 GB
  headroom                      1.0 GB
                              --------
                               16.9 GB   over budget

work_mem is a per-node limit, not per-connection. One query with two sorts and a hash join can allocate several multiples of it, and every parallel worker gets its own allowance. That 2-node assumption is conservative for OLTP and wildly optimistic for a reporting workload.

The 400 number doesn't fit — and that's before anyone runs a bad report.

PgBouncer vs raising max_connections

For most people reading this, PgBouncer in transaction mode ends the conversation. A server connection is held only for the duration of a transaction, so idle clients cost nothing on the database side.

[databases]
app = host=10.0.1.20 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
server_idle_timeout = 60

Defaults are max_client_conn = 100 and default_pool_size = 20, so both need raising deliberately, tuned against the real usage numbers from the connection check above — not guesses.

What transaction mode breaks:

Feature	Works in transaction mode?
Session-level `SET` / `RESET`	No
`LISTEN` / `NOTIFY`	No
`WITH HOLD` cursors	No
Session-level advisory locks	No
Temporary tables	No
Named prepared statements	Only from PgBouncer 1.21+
Transaction-scoped advisory locks	Yes

PgBouncer publishes a full SQL feature map by pooling mode. Read it before you flip the switch. If your app leans on any "No" row, the honest options are session pooling (which gives back most of the connection savings) or a genuinely larger max_connections, not a workaround.

One operational detail: PgBouncer is a single-threaded event loop. To use more than one core you run multiple instances behind SO_REUSEPORT, supported since 1.19.

When raising max_connections is still the right call: your app depends on LISTEN/NOTIFY or session advisory locks and can't be refactored; a fleet of stateless services each needs a handful of direct connections and a pooler adds latency you can't afford; you already run a pooler and the server-side pool needs to be bigger under genuinely measured load. PG 14's snapshot scalability work in GetSnapshotData cut the overhead idle connections impose on active ones, but an idle connection still costs a process slot and its private memory.

How to increase max_connections in PostgreSQL safely

ALTER SYSTEM SET max_connections = 200;

SELECT name, setting, pending_restart FROM pg_settings
WHERE name = 'max_connections';

      name       | setting | pending_restart
-----------------+---------+-----------------
 max_connections | 100     | t

Pre-flight the new shared memory size before restarting:

$ postgres -C shared_memory_size -D $PGDATA
5761

If huge_pages = on, recount now:

$ postgres -C shared_memory_size_in_huge_pages -D $PGDATA
2881
$ sysctl -w vm.nr_hugepages=3000
$ echo "vm.nr_hugepages = 3000" >> /etc/sysctl.d/99-postgres.conf

Raise every standby before the primary. A hot standby refuses to continue recovery if max_connections, max_worker_processes, max_prepared_transactions, or max_locks_per_transaction are lower than on the primary. Standby first, primary second, no exceptions.

A healthy restart looks like this:

LOG:  database system was shut down at 2026-08-04 02:14:07 UTC
LOG:  database system is ready to accept connections

Three ways this bites you after restart

Huge pages shortfall. You bumped the setting but forgot vm.nr_hugepages:

FATAL:  could not map anonymous shared memory: Cannot allocate memory

Recount as above, or set huge_pages = try so it falls back to normal pages instead of refusing to start — safer during a change window even if it costs some TLB efficiency.

Standby refuses recovery. You did the primary first:

FATAL:  hot standby is not possible because max_connections = 100 is a lower
        setting than on the primary server (its value was 200)

Raise and restart the standby, ordering as above.

OOM killer. The box fits at idle and doesn't fit under load:

LOG:  server process (PID 28841) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing

Postgres treats an OOM kill as a crash and forces every other session into crash recovery, so one killed backend costs you the whole cluster's availability for the recovery window. Mitigations: PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE to protect the postmaster while leaving backends killable, and vm.overcommit_memory = 2 with a tuned overcommit_ratio on dedicated hosts so the kernel refuses the overcommit instead of killing you later.

And the two errors clients will actually see at the ceiling:

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication
        superuser connections

Managed Postgres (RDS, Aurora, Cloud SQL)

On RDS and Aurora PostgreSQL the default is LEAST({DBInstanceClassMemory/9531392}, 5000), derived from instance memory. It's a static parameter, so changing it in the parameter group requires a DB instance reboot. There's no postgresql.conf to edit and no ALTER SYSTEM. Cloud SQL follows the same memory-derived, reboot-to-apply pattern. For pooling, RDS Proxy is the AWS-native option; on Supabase it's Supavisor. Same tradeoffs as PgBouncer transaction mode apply.

The order of fixes that actually works

idle_in_transaction_session_timeout = '5min'. Costs nothing, ships as a reload, catches the most common cause.
Fix app-side pool sizing. Per-pod pool × replica count is the real number, and most teams have never multiplied it out.
PgBouncer in transaction mode. This is the correct answer for the large majority of connection-exhaustion tickets.
Only then, max_connections — with the memory arithmetic written down and the standby restarted first.

If you want a second opinion on where your instance sits today, MyDBA's free health check looks at connection distribution and memory settings together, which is the pairing that matters here.

The value in postgresql.conf is easy to change. The RAM on the box is not.