<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Philip McClarence on Medium]]></title>
        <description><![CDATA[Stories by Philip McClarence on Medium]]></description>
        <link>https://medium.com/@philmcc?source=rss-f715ec56a333------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*slpZPUnxPkeJsRWy</url>
            <title>Stories by Philip McClarence on Medium</title>
            <link>https://medium.com/@philmcc?source=rss-f715ec56a333------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 18:44:44 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@philmcc/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Aurora PostgreSQL Storage Model: What Actually Changes]]></title>
            <link>https://medium.com/@philmcc/aurora-postgresql-storage-model-what-actually-changes-e9895fb66b81?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/e9895fb66b81</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database-design]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[aurora]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Thu, 20 Aug 2026 10:59:06 GMT</pubDate>
            <atom:updated>2026-08-20T10:59:06.326Z</atom:updated>
            <content:encoded><![CDATA[<h3>The pager that taught me Aurora isn’t Postgres</h3><p>Two in the morning, a payments cluster, write latency climbing on a sawtooth pattern that I would have bet money on. I’d seen that shape a hundred times on self-managed Postgres: checkpoints bunching up, max_wal_size too small, the checkpointer dumping dirty buffers in a panic while everything else waits on the same disk queue. I opened the runbook I&#39;d been carrying between jobs for six years, bumped max_wal_size, stretched checkpoint_timeout, set checkpoint_completion_target to 0.9, and waited for the sawtooth to flatten.</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/aurora-postgresql-storage-model?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=aurora-postgresql-storage-model">Aurora PostgreSQL Storage Model: What Actually Changes</a></p><p>It didn’t. It didn’t change at all.</p><p>Then I ran SELECT * FROM pg_stat_bgwriter; — which I should have done first — and buffers_checkpoint was sitting at a number so small it looked like a bug. Hours of tuning a knob that wasn&#39;t connected to anything. The actual cause turned out to be a fat UPDATE pattern rewriting rows that hadn&#39;t changed, hammering the log stream. But the lesson that stuck wasn&#39;t about the query. It was that I had brought a mental model of I/O to a system that doesn&#39;t do I/O the way I thought.</p><p>I walked through this on video as well, if you’d rather watch than read: <a href="https://www.youtube.com/watch?v=GTFHE5KJSag">https://www.youtube.com/watch?v=GTFHE5KJSag</a>.</p><p>Aurora is genuinely clever engineering. It is also not PostgreSQL underneath, and “PostgreSQL-compatible” is a phrase doing a lot of quiet work.</p><h3>What vanilla Postgres actually does on every write</h3><p>Quick refresher, because the contrast only lands if the baseline is precise.</p><p>You modify a row. Postgres pins the page in shared_buffers, writes a WAL record describing the change, and marks the buffer dirty. WAL goes to disk before the data page ever does — that&#39;s the write-ahead rule, enforced by LSN ordering. Later, the checkpointer runs (triggered by checkpoint_timeout or max_wal_size) and writes every dirty shared buffer out to the data files, spreading that work across the interval according to checkpoint_completion_target. That&#39;s the storm I spent years tuning.</p><p>full_page_writes sits on top of this: the first modification of a page after each checkpoint writes a full page image into WAL, so a torn 8 KiB write during a crash can be reconstructed. It costs WAL volume and it buys you correctness.</p><p>Replicas, meanwhile, are independent machines with independent copies of every file. You build one with pg_basebackup, it streams WAL, it replays that WAL, and it performs its own writes to its own disks. Two copies of the data, two sets of dirty buffers, two checkpointers.</p><p>That’s the model. Everything I’d learned about tuning write latency assumed this was universally true. On Aurora, most of it isn’t — so throw half of it away.</p><h3>Aurora’s inversion: the log is the database</h3><p>Aurora’s writer instance sends only redo log records to the storage layer — this is the “log-only writes” design at the center of the Aurora PostgreSQL storage model. It does not write dirty data pages down to a data file, and it does not push full-page images across the wire the way a standalone primary writes to disk. The storage fleet receives the log and applies it itself, materializing 16 KiB pages on demand and coalescing them in the background.</p><p>That storage fleet is not a disk. It’s six copies of your data across three Availability Zones, two copies per AZ, with a 4-of-6 quorum for writes and a 3-of-6 quorum for reads. This shared storage architecture means durability is a property of the fleet, not of the instance flushing something.</p><blockquote><em>In Aurora, the log is the database — the pages are just a cache the storage layer builds for you.</em></blockquote><p>The SIGMOD 2017 paper from the Aurora team reports roughly a 7.7x reduction in network traffic, measured as I/Os per transaction, against a mirrored MySQL configuration on a sysbench write-only workload. That’s their benchmark, not a promise for your schema, but the direction is structural: if you never ship pages, you ship far less.</p><p>What leaves the instanceVanilla PostgresAurora PostgreSQLWAL / redo recordsYes, to local WAL, then archive/replicasYes — and this is essentially allDirty data pagesYes, checkpointer writes them to data filesNo page writes to storage from the instanceFull page imagesYes, after each checkpoint per pageNot shipped as page writes; durability handled by storageBase backup trafficpg_basebackup copies the whole clusterNo — readers attach to the existing volume</p><h3>So what happened to Aurora checkpoints?</h3><p>The parameters still exist. checkpoint_timeout, max_wal_size, checkpoint_completion_target are all there, and you can set them, and mostly nothing dramatic happens. Because Aurora offloads page materialization to storage, there is no dirty-buffer flush storm for the checkpointer to spread out. The checkpoint on Aurora is mostly about establishing a durable recovery point in the log, not about pushing megabytes of buffer cache to disk.</p><p>Go look:</p><pre>SELECT * FROM pg_stat_bgwriter;<br>-- PG 17+: checkpoint columns moved to pg_stat_checkpointer<br>SELECT * FROM pg_stat_checkpointer;</pre><p>On a busy self-managed cluster, buffers_checkpoint climbs steadily and dwarfs buffers_clean over a day. On Aurora, expect pg_stat_bgwriter to show buffers_checkpoint near zero and stay near zero under heavy write load. That is normal. It is not a broken statistics collector, and it is not evidence that checkpoints are &quot;stuck&quot;.</p><p>The practical fallout: every Grafana panel you built around checkpoint write rate, every alert on “checkpoints requested vs timed”, every capacity model that treats checkpoint I/O as your write ceiling — dead panels. I’ve watched a team stare at a flat checkpoint graph during an incident and conclude the database was healthy. It wasn’t. They were just reading a gauge wired to nothing.</p><p>While you’re in there, run these two and internalise the difference:</p><pre>SELECT aurora_version();<br>SELECT version();</pre><p>version() gives you the PostgreSQL compatibility version. aurora_version() gives you the Aurora engine version. They are different numbers, they have different release notes, and when you&#39;re chasing a bug you need both — one of the sharper Aurora vs PostgreSQL differences that catches people out.</p><h3>Replicas that share a disk</h3><p>This is the part that reorganised my thinking the most.</p><p>Aurora readers attach to the <strong>same</strong> cluster storage volume as the writer. They do not maintain their own copies of the data files. Adding a reader does not copy data anywhere, which is why a new instance comes up in minutes regardless of whether the volume holds 40 GB or 40 TB.</p><p>So Aurora replica lag means something different. It isn’t the time to replay WAL and write pages locally, because the reader isn’t writing pages. It’s the time for the writer’s log stream to reach the reader so it can invalidate or update its own buffer cache. That’s typically low tens of milliseconds — not the seconds-to-minutes lag you can see on a physical streaming replica under load. Typically — I’ve seen it stretch under heavy cache churn, and a reader running big analytical scans can still evict everything useful and slow down. Sharing the storage volume doesn’t make a reader immune to its own buffer cache pressure.</p><p>Two consequences worth writing on a sticky note. First, pg_basebackup is not how you build an Aurora reader; you can&#39;t, there&#39;s no filesystem access, and readers are provisioned by AWS against the existing volume. Second, the metrics you want are AuroraReplicaLag and AuroraReplicaLagMaximum in CloudWatch, plus this in-database:</p><pre>SELECT server_id, session_id, durable_lsn, highest_lsn_rcvd,<br>       cur_replay_latency_in_usec, last_update_timestamp<br>FROM aurora_replica_status();</pre><p>cur_replay_latency_in_usec is the number I actually watch during an incident. CloudWatch is a minute behind reality; this isn&#39;t.</p><h3>Aurora failover is a pointer swap — until it isn’t</h3><p>Because the volume is already shared and durable, promoting a reader doesn’t mean replaying a backlog onto a separate copy of the data. AWS documents typical failover completing in well under a minute when a reader exists in the cluster, and in practice that matches what I’ve measured.</p><p>But “well under a minute” isn’t zero, and the parts that cost time are the parts people forget to test. Failover terminates every existing database connection. Your application reconnects or it doesn’t, and I have seen far more outage minutes come from connection pools than from Aurora itself. Clients that cache DNS beyond the endpoint’s TTL will happily keep dialling the old writer. The new writer also starts with a colder cache than the old one had, so your p99 is worse for a while even after everything is technically “up”.</p><p>What I do: always connect via the cluster endpoint, never a hardcoded instance endpoint. Check your driver’s DNS caching (JVM networkaddress.cache.ttl has ruined more than one of my evenings). And run a deliberate failover in a maintenance window, with the app under load, before you find out how it behaves at 3am.</p><h3>The knobs that lie to you</h3><p>If your runbook says…On Aurora, do this instead”Tune max_wal_size / checkpoint_completion_target to smooth write spikes&quot;Look at WAL generation rate and VolumeWriteIOPs; the checkpointer isn&#39;t your bottleneck&quot;Disable full_page_writes to cut WAL&quot;Durability is the storage layer&#39;s job here; treat storage GUCs as not yours to manage&quot;Set shared_buffers to 25% of RAM&quot;Aurora ships a default sized as a large fraction of instance memory; change it only with a measured reason&quot;Build the replica with pg_basebackup&quot;Add a reader instance to the cluster; there is no filesystem to copy&quot;Check fsync, archive_command, WAL archiving&quot;Not your surface; backups and durability are cluster-level features</p><p>One caveat I’d insist on: Aurora’s modifiable-parameter list changes between engine versions. Check the current parameter group documentation for your exact version rather than trusting any blog post, including this one, on which specific GUC is locked.</p><h3>What Aurora bills you for, and why that changes tuning</h3><p>On the standard (non-I/O-Optimized) configuration, Aurora meters and bills storage I/O per operation, and write I/Os are counted in 4 KB units of log records. Read that twice, because it moves the tuning lever.</p><p>On self-managed Postgres I optimised to avoid checkpoint write storms. On Aurora I optimise to <strong>reduce WAL volume</strong>, because WAL volume is the bill and it’s also the load on the storage fleet. Concretely: fewer and narrower indexes (every index is more redo per row change), stop issuing UPDATEs that set columns to the values they already hold, batch small writes into fewer transactions, and be suspicious of any ORM that rewrites whole rows on every save.</p><p>Watch VolumeWriteIOPs and VolumeReadIOPs — both are cluster-volume metrics, not per-instance, which trips people up when they try to attribute I/O to a specific reader. If your cluster is write-heavy enough that I/O charges dominate, Aurora I/O-Optimized removes the per-I/O charge in exchange for higher instance and storage rates. Do the arithmetic for your own workload; I&#39;m not putting numbers here that will be stale in a quarter.</p><h3>What still behaves exactly like Postgres, and will still page you</h3><p>None of the above rescues you from the classics, because they’re properties of the MVCC engine sitting above the storage layer, not of the storage layer itself.</p><p>Autovacuum works the same. Dead tuples accumulate the same. Bloat is bloat. Transaction ID wraparound will absolutely take you down on Aurora, and it remains the single most effective way to destroy a weekend. A long-running query on a reader with hot_standby_feedback on still holds back vacuum on the writer, which is one of my least favourite ways to discover that someone pointed a BI tool at production.</p><p>And abandoned replication slots still pin WAL and grow your storage. I once inherited a cluster where a decommissioned CDC consumer had left a logical slot behind for eleven weeks. On self-managed Postgres that fills a filesystem; on Aurora it quietly grows the volume and the bill until someone notices. Same query either way:</p><pre>SELECT slot_name, active, restart_lsn FROM pg_replication_slots;</pre><p>Check it weekly. On both.</p><h3>A migration checklist for your monitoring</h3><ol><li>Retire panels for checkpoint write rate, buffers_checkpoint, and checkpoint duration.</li><li>Retire anything reading pg_stat_bgwriter as a proxy for write pressure.</li><li>Add AuroraReplicaLag and AuroraReplicaLagMaximum, alert on sustained rather than spike values.</li><li>Add VolumeWriteIOPs and VolumeReadIOPs, and treat them as cluster-level.</li><li>Add a WAL-generation-rate panel; it’s now your write-cost signal.</li><li>Poll aurora_replica_status() for cur_replay_latency_in_usec at higher resolution than CloudWatch gives you.</li><li>Keep every autovacuum, dead-tuple, bloat and wraparound-age panel exactly as it is.</li><li>Keep pg_replication_slots monitoring, with an alert on slot age.</li><li>Keep pg_stat_statements and your plan-regression tracking; bad plans are unchanged.</li><li>Record both version() and aurora_version() in your inventory, and run a deliberate failover test each quarter.</li></ol><p>If it helps, MyDBA’s free health check reads both self-managed and Aurora topologies, so you can diff what actually changed rather than guessing.</p><p>Aurora’s storage design is a good piece of engineering and I’d choose it again for plenty of workloads. But the compatibility label describes the SQL surface, not the machine underneath, and the gap between those two is where the 3am pages live.</p><h3>Where that leaves you</h3><p>The instinct that got me out of bed at 2am — “checkpoints are stalling, tune the checkpointer” — was built on years of watching vanilla Postgres, and it was simply the wrong tool for Aurora’s log-structured storage. That’s not a knock on either system. It’s a reminder that “PostgreSQL-compatible” buys you the SQL surface and the MVCC engine, not the operational model underneath it. Keep your vacuum, bloat, and replication-slot instincts — they’re still correct. Retire your checkpoint and page-write instincts — they’re measuring nothing. Build your dashboards around what Aurora actually bills and moves: WAL volume, replica lag via the log stream, and volume-level IOPS.</p><p>Philip McClarence builds MyDBA, a Postgres monitoring and health-check tool — <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=aurora-postgresql-storage-model">https://mydba.dev/</a></p><p>If you’re moving a cluster onto Aurora and want a second pair of eyes on which alerts still mean something, run it through <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=aurora-postgresql-storage-model">MyDBA</a> — it’s built to catch exactly this kind of stale-runbook trap before it costs you a weekend.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e9895fb66b81" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Moment I Stopped Assuming DELETE Meant Deleted]]></title>
            <link>https://medium.com/@philmcc/the-moment-i-stopped-assuming-delete-meant-deleted-826d91e81f92?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/826d91e81f92</guid>
            <category><![CDATA[mvcc]]></category>
            <category><![CDATA[database-maintenance]]></category>
            <category><![CDATA[autovacuum]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Thu, 20 Aug 2026 10:58:08 GMT</pubDate>
            <atom:updated>2026-08-20T10:58:08.701Z</atom:updated>
            <content:encoded><![CDATA[<p>Postgres never deletes rows in place — it marks them dead and lets autovacuum reclaim the space later, update statistics, and freeze old transaction IDs before wraparound forces a shutdown. Understanding that MVCC tradeoff, and the handful of settings that control when autovacuum fires, is the difference between a database that stays lean and one that quietly bloats until a 3am page.</p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=_x8NZMbFYzc">Understanding autovacuum: why Postgres cleans up after itselfhttps://www.youtube.com/watch?v=_x8NZMbFYzc</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*DZzbxSRCHUy4hovN.png" /></figure><p>A couple years back I was staring at a monitoring dashboard that just didn’t add up. We had a cron job deleting about half a million rows every hour from a queue-like table, yet the table’s disk footprint kept rising. No new inserts — only deletes, plus a steady stream of updates. The on‑disk size grew by 20% that week. My first thought: “We clearly have a leak.” My second thought, after checking pg_total_relation_size(), was that DELETE must not mean what I thought it meant.</p><p>That’s the day I learned that Postgres doesn’t actually delete a row when you run DELETE. It <em>pretends</em> to. It marks the row as “dead” and moves on. If nobody comes along to sweep up those corpses, the table bloats, queries scan over ghosts, and your disk steadily fills with data nobody can see.</p><p>I made a companion video walking through this visually — the moment you see those dead tuples stacking up under n_dead_tup in pg_stat_user_tables, the whole model clicks. This article goes deeper, into the exact mechanics, the formula that decides when the cleanup crew wakes up, and the real‑world failures that turn “autovacuum is taking care of it” into a pager storm.</p><h3>Why Postgres Lies to You About Deleting Rows (MVCC, Briefly)</h3><p>Postgres uses Multi‑Version Concurrency Control — MVCC. Every statement that changes a row creates a new <em>version</em> of that row and keeps the old version around until no active transaction might still need it.</p><ul><li><strong>DELETE</strong> marks the existing row version as dead. The disk space isn’t freed.</li><li><strong>UPDATE</strong> is really a DELETE of the old version plus an INSERT of the new version. If you update a single column in a 1 kB row, Postgres writes an entirely new 1 kB row version and marks the old one dead.</li><li>Even <strong>INSERT</strong> can create dead tuples if a transaction rolls back — the row versions are left behind for vacuum to clean.</li></ul><p>This isn’t a bug; it’s a deliberate tradeoff. Concurrent readers running on older snapshots need those old row versions. If Postgres overwrote in place, a long‑running SELECT would either block all writes or see partially updated data. The price we pay is dead tuples everywhere, and the housekeeper that reclaims them — autovacuum — can easily fall behind without us noticing.</p><pre>+--------------------------------------------------------+<br>|                      Page on Disk                      |<br>+--------------------------------------------------------+<br>| [Active Row 1] [Dead Row (Deleted)] [Active Row 2]    |<br>|                                                        |<br>| (Vacuum scans this page, marks the dead slot reusable, |<br>| and updates the Visibility Map for index-only scans)   |<br>+--------------------------------------------------------+</pre><h3>The Four Jobs Autovacuum Actually Does</h3><p>Ask most people what autovacuum does and they’ll say “cleans up dead tuples.” That’s true, but it’s only one of its four crucial jobs. Miss any of them and the consequences range from slow queries to full database shutdown.</p><ol><li><strong>Reclaim dead tuple space</strong><br>When autovacuum scans a table, it identifies dead tuples that are no longer visible to any active transaction and marks the space as reusable for future inserts or updates. Skip this and you get bloat — the table file grows but most of the pages are half‑empty. Queries scan more disk pages than necessary, and your cache hit ratio tanks.</li><li><strong>Run ANALYZE to update planner statistics</strong><br>The query planner relies on column‑value histograms, most‑common‑values lists, and row‑count estimates. Autovacuum’s autoanalyze component periodically refreshes those stats. Stale stats lead to terrible join ordering, wrong index choices, and the kind of nested‑loop‑on‑a‑million‑rows surprise that arrives in Slack at 4:00 PM on a Friday.</li><li><strong>Freeze old transaction IDs to prevent wraparound</strong><br>More on this in a moment, but in short: Postgres’s transaction IDs are a 32‑bit counter, and if the “age” of the oldest unfrozen XID gets too high, the database shuts down to protect your data. Autovacuum’s freeze passes advance that age and keep you safe.</li><li><strong>Update the visibility map for index‑only scans</strong><br>A visibility map bit is set when every row on a page is visible to all transactions. Index‑only scans can skip visiting the heap entirely for those pages, making them dramatically faster. If autovacuum neglects the visibility map, your index‑only scans degrade to ordinary index scans, fetching heap pages needlessly — a silent performance killer.</li></ol><p>When you see a table with last autovacuum days ago and a million dead tuples, you’re almost certainly paying for all four failures simultaneously.</p><h3>Transaction ID Wraparound: The Failure Mode Nobody Wants to Explain at a Post‑Mortem</h3><p>Here’s the thing that makes seasoned DBAs twitch: Postgres’s transaction ID space is ²³¹, or just over 2 billion distinct XIDs. Each transaction that modifies data consumes one. When the system approaches that limit, it wraps around to the beginning — and suddenly rows that were “frozen” (and thus older than any active XID) reappear as “in the future,” making a mess of visibility rules. Postgres won’t gamble with your data; it shuts down instead.</p><p>Not a graceful “server disconnected” message. A hard shutdown, with a log line like:</p><pre>database is not accepting commands to avoid wraparound data loss in database &quot;mydb&quot;<br>HINT: Stop the postmaster and vacuum that database in single-user mode.</pre><p>Recovering from that in production means downtime you can’t skip, possibly while a manager asks why nobody saw it coming. The trigger is autovacuum_freeze_max_age — typically 200 million. When the age of the oldest unfrozen XID hits that mark, the system forces an autovacuum (even if you disabled it) to freeze tuples. If writes are coming in faster than freezing can keep up, you’re on a countdown to shutdown.</p><p>I once sat in a war room at 2:00 AM watching that counter climb because a bulk‑update job had been hammering a neglected table all afternoon. We had to pause the application and run a manual VACUUM FREEZE in single‑user mode, praying the checkpoint wouldn’t take half an hour. It’s not a performance nuisance — it’s an existential risk to your data’s availability.</p><h3>How Autovacuum Decides When to Run — The Actual Formula</h3><p>Autovacuum wakes up every autovacuum_naptime seconds (default 1 minute) and checks each table. It triggers a vacuum when the number of dead tuples crosses a threshold:</p><pre>dead_tuples &gt; autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * live_tuples</pre><ul><li>autovacuum_vacuum_threshold defaults to <strong>50</strong>.</li><li>autovacuum_vacuum_scale_factor defaults to <strong>0.2</strong> (20% of the table’s live rows).</li></ul><p>For a small table of 1,000 rows, that means vacuum kicks in at 50 + 0.2 × 1000 = 250 dead tuples — fine. For a 10‑million‑row table, the threshold is <strong>50 + 0.2 × 10,000,000 = 2,000,050 dead tuples</strong>. You need to accumulate roughly two million corpses before autovacuum will even glance at it. Think about that: on a busy queue table processing thousands of DELETEs and UPDATEs per second, 2 million dead tuples can pile up in minutes. And while they sit there, the table bloats, the visibility map rots, and index scans drag through dead space.</p><p>I learned this the hard way when a monitoring alert fired for disk usage, not for dead tuples — because nobody had tuned the scale factor. The default is a relic of the 1990s. For any table beyond a few hundred thousand rows, you must lower it.</p><h3>Checking Your Own Tables: The Query I Run First</h3><p>When I suspect autovacuum trouble, the first thing I run is this snippet against pg_stat_user_tables:</p><pre>SELECT<br>  schemaname || &#39;.&#39; || relname AS table_name,<br>  n_live_tup,<br>  n_dead_tup,<br>  CASE<br>    WHEN n_live_tup + n_dead_tup = 0 THEN 0<br>    ELSE round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2)<br>  END AS dead_ratio_pct,<br>  last_autovacuum,<br>  last_autoanalyze<br>FROM pg_stat_user_tables<br>ORDER BY dead_ratio_pct DESC NULLS LAST;</pre><p>Dead tuple ratio tells you immediately how much of the table’s row‑version space is wasted. A 30% dead ratio and a last_autovacuum timestamp from three days ago — that’s a table begging for a manual vacuum. If last_autoanalyze is old, the planner is flying blind. I’ve seen partitioned tables where the parent shows NULL for these columns because only the child partitions hold data; be sure to check the partitions individually.</p><p>Stale last_autovacuum doesn’t always mean autovacuum is sleeping; it might mean the threshold was never crossed despite mounting dead tuples, or that a long‑running transaction is blocking cleanup of those dead tuples (more on that shortly).</p><h3>Tuning Per‑Table Thresholds with ALTER TABLE</h3><p>The quickest fix for a table that’s generating dead tuples faster than the default threshold fires is to lower the scale factor — or ditch it entirely and use a fixed threshold:</p><pre>ALTER TABLE queue_jobs SET (autovacuum_vacuum_scale_factor = 0.05);</pre><p>That changes the 10‑million‑row table example from needing ~2 million dead tuples to needing 50 + 0.05 × 10,000,000 = 500,050 dead tuples — still a large number, but a 4× improvement. For very large tables (hundreds of millions of rows), the scale factor still produces absurd thresholds; then you might set autovacuum_vacuum_threshold to something like 100000 and set the scale factor to 0, so vacuum triggers after any 100,000 dead tuples regardless of table size.</p><p>But there’s a catch: once vacuum starts, it can consume I/O capacity. The throttling knobs are autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit. The cost delay adds a sleep between “cost points” burned during vacuum, so a higher delay slows it down to avoid saturating your disk. On systems with fast NVMe, I often drop the delay to 1 ms and raise the cost limit to 2000 to let vacuum clean aggressively — but you need to test that on your hardware.</p><h3>When Autovacuum Can’t Keep Up — Real Failure Patterns</h3><p>Even with perfect threshold tuning, I’ve seen tables spiral because of one of these patterns:</p><ul><li><strong>Write rate outpacing vacuum throughput</strong><br>A batch DELETE job that fires every few minutes can accumulate millions of dead tuples between vacuum cycles. If the vacuum takes three minutes to clean the table and the job runs every two minutes, you’re losing ground — dead tuple count rises monotonically. You’ll see last_autovacuum happening frequently but n_dead_tup still climbing.</li><li><strong>Long‑running transactions blocking cleanup</strong><br>A dead tuple can only be removed after the <em>oldest transaction that started before it was dead</em> has finished. If you have a misbehaving reporting query that runs for an hour inside a REPEATABLE READ transaction, or an idle‑in‑transaction connection holding a snapshot, it pins all dead tuples that were created after that snapshot’s start time. I’ve seen a single forgotten BEGIN; SELECT ...; session prevent autovacuum from reclaiming gigabytes of space. Check pg_stat_activity for state=&#39;idle in transaction&#39; and a long xact_start.</li><li><strong>Too many small tables competing for autovacuum workers</strong><br>autovacuum_max_workers (default 3) limits how many simultaneous tables can be vacuumed. If you have hundreds of small tables with activity, workers can get tied up on them while a critical large table queues behind. Autovacuum does skip a table if a worker isn’t available, but it might never catch up.</li><li><strong>Toast table bloat sneaking under the radar</strong><br>Large text or JSON column updates produce significant dead tuple activity in the associated toast table. People monitor the main table but forget to look at pg_toast.pg_toast_&lt;oid&gt;. I once spent an afternoon wondering why pg_table_size kept rising on a lightly‑updated table — turned out the toast table had 90% dead tuples and hadn’t been vacuumed in weeks. The toast table has its own autovacuum trigger based on the same rules, but if you don’t check it, it won’t show in your main monitoring.</li></ul><h3>What I Actually Check When Something Looks Off</h3><p>My background is incidents and monitoring, so when I get a ticket about slow queries or a disk‑space alert, I don’t start by tweaking memory settings. I work through a mental checklist that almost always points at autovacuum or its side effects:</p><ol><li><strong>Dead tuple ratio trend over time</strong> — A single high value is a snapshot. I keep a simple cron job logging the output of that query above into a table so I can see if dead tuples are climbing steadily. A slope upward, even if the ratio is only 5% today, means trouble by tomorrow night.</li><li><strong>Autovacuum log messages</strong> — I set log_autovacuum_min_duration = 0 temporarily on a suspect instance so I can watch vacuum decisions in real time. If I see a table repeatedly</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=826d91e81f92" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Five schema decisions that cost me sleep (and eventually a late‑night page)]]></title>
            <link>https://medium.com/@philmcc/five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-page-caefcd876063?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/caefcd876063</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[database-design]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Mon, 10 Aug 2026 17:31:30 GMT</pubDate>
            <atom:updated>2026-08-10T17:31:30.743Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>They aren’t bugs.</strong> They pass code review. They look perfectly reasonable when you’re staring at a CREATE TABLE statement on a Wednesday afternoon. And then, six months later, your phone buzzes at 2 a.m. because the database that breezed through load testing with a thousand rows is now choking on fifty million.</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/five-postgres-schema-decisions-you-ll-regret-at-2am?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">Five Postgres Schema Decisions You’ll Regret at 2am</a></p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=i0FvxZVs3TU">5 Postgres Schema Mistakes That Quietly Kill Performancehttps://www.youtube.com/watch?v=i0FvxZVs3TU</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*C7AZpjEYUxMb47lP.png" /></figure><p>I’ve been dragged out of bed enough times to recognize the quiet killers — the schema decisions that don’t crash anything, they just slowly turn your instance into a resource hog. I made a companion video walking through these, but articles are where I can actually show you the exact SQL and the edge cases that catch people off guard. So let’s do that. Here are five schema mistakes I’ve extracted from real incident reviews, code‑review scars, and the occasional therapeutic rant at a whiteboard.</p><h3>1. The unbounded VARCHAR</h3><p>Most developers think VARCHAR without a length is just “a string.” It is, except PostgreSQL will let you cram up to 1 GB into it. That’s not a theoretical maximum; I watched a botnet payload turn a harmless event_payload column into a 47 MB XML blob. The insert succeeded. The table didn’t complain. But then every SELECT that touched that row had to chase a TOAST pointer.</p><p>Here’s what happens under the hood: if a row’s total width exceeds roughly 2 KB, PostgreSQL pushes large values out of the main table and into a separate TOAST table. The original row stores a tiny pointer. That’s fine for data you rarely read, but the moment a routine query scans a bunch of toasted rows — like SELECT * FROM events WHERE created_at &gt; now() - interval &#39;1 hour&#39;—each row requires an extra random I/O to fetch the toasted value. You don’t notice with 100 rows. With 10 million, your sequential scan turns into a random‑read carnival. Buffer cache hit ratios drop, CPU wait time climbs, and your pager goes off.</p><p>And it’s not just reads: indexes that include the toasted column (like a covering index or a UNIQUE constraint on that column) have to store the full value. A single 22 MB value stuffed into a B‑tree index will balloon a leaf page, causing index bloat and page splits on every similar insert. I once traced a 40 GB index back to three gigantic rows that should have been rejected at the application layer.</p><p><strong>The fix is one line</strong>, usually VARCHAR(255) or a CHECK constraint. If the table is already live and big, you can’t just add a CHECK with an exclusive lock. Instead:</p><ol><li>Add the constraint as NOT VALID:</li></ol><ul><li>ALTER TABLE events ADD CONSTRAINT payload_len CHECK (length(event_payload) &lt;= 10000) NOT VALID;</li></ul><ol><li>This enforces the rule on new rows immediately but doesn’t scan existing ones.</li><li>Validate it later with ALTER TABLE events VALIDATE CONSTRAINT payload_len; — this takes a SHARE UPDATE EXCLUSIVE lock, which won’t block writes, but until it finishes you’re paying a small overhead for new writes.</li></ol><p>The point: a length‑less VARCHAR is not a safe default. Give it a bound, or at least a CHECK that makes you think about what “reasonable” means.</p><h3>2. A nullable column inside a multi‑column UNIQUE constraint</h3><p>This one has bitten me in user‑identity codebases more times than I can count. You set up a tenants table and want to guarantee that each tenant’s email is unique. So you write:</p><pre>CREATE TABLE tenant_users (<br>    user_id   bigserial PRIMARY KEY,<br>    tenant_id int,<br>    email     text,<br>    UNIQUE (email, tenant_id)<br>);</pre><p>Looks correct. Then you insert:</p><pre>INSERT INTO tenant_users (tenant_id, email) VALUES (NULL, &#39;alice@example.com&#39;);<br>INSERT INTO tenant_users (tenant_id, email) VALUES (NULL, &#39;alice@example.com&#39;);</pre><p>Both rows go in. Why? SQL’s rules: NULL is never equal to NULL. A unique constraint checks inequality of all columns, and since NULL != NULL, each row is considered distinct. You’ve just allowed duplicate emails for users who aren’t assigned to a tenant yet. The guarantee you thought you had is vapor.</p><p>I encountered this in a B2B SaaS app where “unassigned” users were flagged with tenant_id IS NULL. Support started getting reports that two different people ended up with the same login email, and the application logic that relied on the unique constraint to prevent duplicates just silently let it happen. It took three days to untangle the data.</p><p><strong>Two real fixes, plus a modern convenience:</strong></p><ul><li>If tenant_id should <em>never</em> be NULL, slap NOT NULL on it. The unique constraint then works as expected because every row has a real value.</li><li>If NULL is genuinely allowed but you still want per‑tenant uniqueness for those with a tenant, use a <strong>partial unique index</strong>:</li><li>CREATE UNIQUE INDEX uq_tenant_email ON tenant_users (email) WHERE tenant_id IS NOT NULL;</li><li>That index ignores rows where tenant_id IS NULL, so duplicates among unassigned users are still allowed (you’d need some other mechanism), but assigned users are protected.</li><li>If you’re on <strong>PostgreSQL 15 or newer</strong>, there’s an even cleaner option: NULLS NOT DISTINCT. This clause tells PostgreSQL to treat NULL as equal for the purpose of uniqueness:</li><li>ALTER TABLE tenant_users ADD CONSTRAINT unique_email_tenant UNIQUE NULLS NOT DISTINCT (email, tenant_id);</li><li>A single constraint now prevents duplicates across <em>all</em> rows, including those with NULL tenant IDs. This eliminates the need for multiple partial indexes and makes the intent crystal clear.</li></ul><p>Either way, you need to understand the nullable‑unique trap. It’s not a Postgres bug — it’s just SQL being SQL — but it’s a design decision you make at CREATE TABLE that silently changes the semantics of your application.</p><h3>3. Leaving columns nullable “just in case”</h3><p>Every column that doesn’t have NOT NULL adds a bit to the row’s null bitmap. For a table with 40 columns, that’s 5 bytes of overhead <em>per row</em>, which sounds trivial until you’re storing half a billion rows. But the bigger problem isn’t the storage; it’s what the planner can’t do.</p><p>When the database knows a column is NOT NULL, it can optimize IS NOT NULL checks out of execution. It can use certain join strategies that rely on the guarantee. And, critically, when you define a FOREIGN KEY, a nullable column means the relationship is <em>not</em> compulsory—a NULL in a foreign‑key column means “this relationship is broken.” I’ve seen entire reporting pipelines build defensive COALESCE and LEFT JOIN logic around nullable columns that were never, ever actually null in production. The schema told the application “be prepared for nothing,” so the application wrote defensive code, and the defensive code introduced bugs.</p><p>A practical audit I run on every new client’s schema: look at pg_stats.null_frac. A column with null_frac = 0 for a table with millions of rows is a column that deserves NOT NULL. You can find them with:</p><pre>SELECT attname<br>FROM pg_stats<br>WHERE tablename = &#39;orders&#39; AND null_frac = 0;</pre><p>The problem is that adding NOT NULL on a large live table used to be painful. It required a full table scan with an exclusive lock. Nowadays you can do it safely with a two‑step pattern:</p><ol><li>ALTER TABLE orders ADD CONSTRAINT nn_order_date CHECK (order_date IS NOT NULL) NOT VALID;</li><li>ALTER TABLE orders VALIDATE CONSTRAINT nn_order_date;</li></ol><p>Validate takes a SHARE UPDATE EXCLUSIVE lock—writes can still happen, but it’ll scan the table to confirm no existing nulls. Once done, you can ALTER TABLE orders ALTER COLUMN order_date SET NOT NULL; without another scan (Postgres knows the constraint guarantees it). You’ve just tightened your schema without downtime.</p><h3>4. TEXT where JSONB belongs (and the reverse mistake)</h3><p>This one is two mistakes wrapped in a single typing decision.</p><p><strong>Mistake A: storing structured data as </strong><strong>TEXT.</strong><br>I worked with a team that stored raw JSON API responses from a partner service in a response_body TEXT column. Every time they needed to filter on order_total or customer_id inside that payload, they’d pull the entire blob back to the app, parse it, and filter. With 300 million rows, the read amplification was staggering—the table itself was 2.1 TB, and even a simple dashboard query could pull 5 GB of text over the wire just to find three rows. They were paying CPU and network for parsing they didn’t need to do.</p><p>When we changed it to JSONB, query latency for a common filter dropped from 1.4 seconds to 9 ms. Not because JSONB is magic, but because we could add a GIN index on response_body and push the filter into the database with WHERE response_body @&gt; &#39;{&quot;customer_id&quot;: &quot;abc&quot;}&#39;. GIN indexes on JSONB are fast for existence and containment queries, and you avoid the full‑parse‑on‑every‑read tax.</p><p><strong>Mistake B: stuffing plain strings into </strong><strong>JSONB.</strong><br>The reverse happens when developers decide every column should be a JSON document. I saw a table where email, display_name, and avatar_url were stored as a single profile JSONB column. For an equality check on email, the query was WHERE profile -&gt;&gt; &#39;email&#39; = &#39;x&#39;. That forced the database to parse the binary JSONB representation at read time for every row that survived other filters. Write‑side, every insert had to serialize those three simple fields into JSONB’s decomposed binary format. The insert throughput dropped by roughly 30% compared to normal columns, and the planner couldn’t use statistics on individual fields effectively.</p><p>The rule of thumb: if you need to query <em>inside</em> the structure, JSONB plus a GIN index is your friend. If you’re just storing unstructured text that you’ll never query by its internal fields, TEXT is lower overhead. If the data has a stable schema with fields you query individually, use real columns—they’re cheaper to read, index, and maintain. Don’t let JSONB become a blanket excuse to stop designing a schema.</p><h3>5. UUID primary keys without version 7</h3><p>I’ve got nothing against UUIDs in principle. But if you use UUIDv4 as a primary key on a large append‑heavy table, you’re trading away insert performance and index locality for randomness you almost certainly don’t need.</p><p>Here’s what happens: a B‑tree index stores keys in sorted order. With a monotonically increasing key (a sequence, or UUIDv7 whose first bytes are a timestamp), each new insert lands on the rightmost leaf page. That page stays hot in memory. New pages are allocated sequentially. The index stays compact and cache‑friendly. With random UUIDv4, a new row’s primary key could land anywhere in the tree, forcing the database to read a random leaf page, possibly split it, and write multiple dirty pages. Your buffer cache can’t keep the hot pages in memory because there <em>is</em> no single hot page; the entire index churns.</p><p>This difference is stark at scale:</p><p>ID TypeInsert LocalityB‑tree BehaviourPage SplitsBigInt / SerialSequentialAppends to end of indexVery RareUUIDv7SequentialAppends to end of indexVery RareUUIDv4 (Random)Completely RandomScatters across all pagesExtreme</p><p>I saw this on an event‑sourcing table with 800 million rows. With UUIDv4, the primary key index was 143 GB, the cache hit ratio on that index was below 65%, and writes were bottlenecked on random I/O. After a painful migration to UUIDv7 (timestamp‑ordered), the index size dropped to 91 GB because of reduced fragmentation, and the insert throughput improved by a factor of three under load.</p><p>In Postgres 17, native support for UUIDv7 is being discussed, but you can get it today via extensions like pg_uuidv7 or by writing your own function that packs the timestamp into the first 48 bits. The migration cost is real: you can’t just alter the primary key data type; you’d need to rebuild the table and all foreign keys referencing it. If you’re already deep into UUIDv4 on a huge table, the fix is often not to change the PK but to add a separate sequential column for clustering, or a time‑ordered index, and accept the sunk cost. For any new table you expect to grow beyond twenty million rows, choosing UUIDv7 from day one avoids that massive migration entirely.</p><h3>How to catch these before they cost you</h3><h3>How to catch these before they cost you</h3><p>Prevention starts at the code‑review level, but I’ve learned that even the best reviewers miss the subtle ones — the nullable unique trap, the unbounded VARCHAR, the UUID choice that looks fine on a fresh table. So I added a second line of defense: an automated schema‑audit step that runs on every pull request. It’s a simple CI job that connects to a scratch replica and queries pg_stats for null fractions, scans pg_constraint for improperly nullable unique columns, and flags any VARCHAR without a length. If you don’t want to build that yourself, there are extensions like pgspot and tools that do this statically on DDL files.</p><p>But static analysis misses what happens at scale. A column that’s zero‑null today might become 20% null six months from now when a new integration path bypasses the application. That’s where live monitoring comes in. I built <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">MyDBA</a> to track these patterns in real time — it alerts when a table’s null fraction drifts, when a previously useful index starts bloating from toasted values, or when page splits on a random UUID index spike during peak hours. It’s the sort of guardrail I needed at 2 a.m. when I was still SSH’ing into boxes and squinting at pg_stat_user_tables. Having a tool that continuously scores your schema “health” means you catch these before the phone rings.</p><p>The most impactful thing you can do today, however, is just audit the big tables. Run the null_frac query from earlier on your top‑ten tables by size. Check every UNIQUE constraint that includes nullable columns. If you find a VARCHAR without a bound, at least add a CHECK with a generous limit. And if you’re still on UUIDv4 for a fast‑growing table, file a ticket now—even if the migration isn’t immediate, the knowledge that you’re burning cache and write throughput should inform every future architectural decision.</p><h3>Sleep is a database feature</h3><p>These five schema decisions aren’t edge cases found in obscure manuals; they’re the ones that pass code review, survive load tests with toy data, and eventually show up as a 503 error at the worst possible time. They’re also entirely avoidable if you treat your schema with the same scrutiny you give your application code. The database doesn’t complain when you feed it a 47 MB XML blob in a VARCHAR—it just does the work, silently, until everything slows down. That’s the real danger: Postgres will let you build a system that works until it doesn’t, and the tipping point is often a schema choice made months earlier.</p><p>I’ve stopped thinking of schema design as a one‑time project‑phase activity and started treating it as an ongoing discipline — something you monitor, lint, and refine as data grows. It’s not glamorous, but neither is getting paged at 2 a.m. because someone left a column nullable “just in case.” The good news is that with a handful of queries, a few constraint tricks, and tools that keep an eye on the things you can’t watch manually, you can catch the quiet killers before they turn into late‑night incidents.</p><p>Philip McClarence builds MyDBA, a Postgres monitoring and health-check tool — <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">https://mydba.dev/</a></p><p>If you’d rather not wait until the pager buzzes, give <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">MyDBA</a> a try — it’s built to catch exactly these patterns so you can sleep through the night.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=caefcd876063" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres Time-Series Partition Maintenance Guide]]></title>
            <link>https://medium.com/@philmcc/postgres-time-series-partition-maintenance-guide-1d4302792197?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/1d4302792197</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[time-series-analysis]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Mon, 10 Aug 2026 17:29:58 GMT</pubDate>
            <atom:updated>2026-08-10T17:29:58.950Z</atom:updated>
            <content:encoded><![CDATA[<p>Postgres time-series partition maintenance usually fails quietly: a cron job stops succeeding, nobody notices, and data piles into the default partition until disk usage spikes. The fix isn’t a smarter partitioning scheme — it’s monitoring the automation itself, not just the table. Here’s what that looks like in practice, including the pg_partman config, the pg_cron scheduling, and the queries that would have caught my incident six weeks earlier.</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/postgres-partition-maintenance-guide?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-time-series-partition-maintenance">Postgres Partition Maintenance: The Runbook That Works</a></p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=2pe61MS2bdk">Time-series in vanilla Postgres: partition maintenance that scaleshttps://www.youtube.com/watch?v=2pe61MS2bdk</a></p><h3>The 3 a.m. page that wasn’t about a query</h3><p>The page said “disk usage above 90% on metrics-primary.” By the time I was logged in it was 93%. Inserts into the metrics table were taking 40 to 60 ms each instead of the usual sub-millisecond, and the ingest workers were backing up in a way that had already started dropping samples.</p><p>I spent the first ten minutes doing what monitoring people do: checking pg_stat_activity, looking for a lock pile-up, looking for a bad plan. Nothing. Locks were clean. The plans were the same plans they&#39;d been for a year.</p><p>Then I ran a size query against the partition set and found a child table called metrics_default sitting at 214 GB.</p><p>pg_partman had stopped running on the 14th of the previous month. The pg_cron job was still listed in cron.job. It just hadn&#39;t succeeded since a failed extension upgrade left run_maintenance_proc() erroring out, and nobody was watching the job history. Six weeks of metrics had quietly flowed into the default partition, which had no useful indexes for our access pattern and was growing at roughly 5 GB a day.</p><blockquote><em>The partition table was fine. The cron job was not.</em></blockquote><p>What follows is the SQL, the edge cases, and the monitoring queries I now put on every partitioned time-series table before I consider the work done.</p><h3>Why range partitioning by time is the right shape for time-series</h3><p>Declarative partitioning has been in Postgres since version 10 (hash arrived in 11), and for append-mostly time-series data, postgres declarative partitioning by time on the timestamp is the obvious fit. Three concrete wins.</p><p><strong>Pruning.</strong> The planner excludes partitions that can’t satisfy your WHERE clause. It&#39;s controlled by enable_partition_pruning, which is on by default, and it also happens at execution time for parameters the planner didn&#39;t know about. A dashboard query for the last 6 hours touches one or two children instead of the whole dataset.</p><p><strong>Retention becomes </strong><strong>DROP TABLE.</strong> This is the big one operationally, and it&#39;s the core of any postgres partition retention policy. Deleting 30 days of rows from a monolithic table creates a mountain of dead tuples that autovacuum has to chew through, and the space doesn&#39;t go back to the OS without a VACUUM FULL or a rewrite. On the system in that incident, our old nightly DELETE job ran for 4 to 6 hours and left bloat we cleaned up quarterly. Dropping old partitions in Postgres this way takes about 30 milliseconds and returns the files immediately.</p><p><strong>Maintenance per partition.</strong> Autovacuum doesn’t process the parent, because the parent holds no rows. Each child is vacuumed and analyzed independently. A REINDEX on a 12 GB daily partition finishes in a couple of minutes; on a 1.4 TB monolith it does not finish inside any window you&#39;re allowed to have. One wrinkle worth knowing: before PG 15, running ANALYZE on the bare parent didn&#39;t reliably roll up useful planner statistics at the parent level, so if you saw odd plans on parent-level aggregate queries, an explicit ANALYZE metrics after backfills was worth doing.</p><p>The costs are real too. More relations means more catalog entries and more planner work. Very high partition counts increase planning time and memory because every partition is considered before pruning happens. PG 12 improved this substantially, but it isn’t free. And the partition key has to appear in your WHERE clauses, or you scan everything, every time.</p><h3>The minimum viable setup, and the two decisions that matter</h3><pre>CREATE TABLE metrics (<br>    recorded_at   timestamptz NOT NULL,<br>    device_id     bigint      NOT NULL,<br>    metric_name   text        NOT NULL,<br>    value         double precision NOT NULL,<br>    PRIMARY KEY (recorded_at, device_id, metric_name)<br>) PARTITION BY RANGE (recorded_at);</pre><pre>CREATE TABLE metrics_p2026_08_08 PARTITION OF metrics<br>    FOR VALUES FROM (&#39;2026-08-08&#39;) TO (&#39;2026-08-09&#39;);</pre><p>Notice the primary key. Every unique index or primary key on a partitioned table must include all the partition key columns. That’s a hard constraint of declarative partitioning, not something you configure away. If your existing table has a surrogate id bigserial PRIMARY KEY and you were hoping to keep it unique on its own, you can&#39;t. You get (recorded_at, id) or you drop the uniqueness. If your application does lookups or foreign-key joins on the surrogate key alone, you&#39;ll need to carry recorded_at through those queries too, or accept a second, differently-scoped unique index. Teams discover this late and it derails migrations, so decide early.</p><p>The second decision is interval size. Pick it for maintenance, not for tidiness. My rule of thumb: aim for partitions in the 5 to 50 GB range and keep the total count under a few hundred for a given set. Daily partitions with 90 days of retention gives 90 children plus premake, which is comfortable. Hourly partitions with a year of retention gives 8,760 children, and you’ll feel it in planning time on every query.</p><h3>A pg_partman tutorial for handing off the lifecycle</h3><p>pg_partman creates future partitions ahead of time and handles retention. In 5.x you register a set like this:</p><pre>SELECT partman.create_parent(<br>    p_parent_table := &#39;public.metrics&#39;,<br>    p_control      := &#39;recorded_at&#39;,<br>    p_interval     := &#39;1 day&#39;,<br>    p_premake      := 7,<br>    p_template_table := &#39;partman.template_public_metrics&#39;<br>);</pre><pre>UPDATE partman.part_config<br>   SET retention = &#39;30 days&#39;,<br>       retention_keep_table = false,<br>       retention_keep_index = false,<br>       infinite_time_partitions = true<br> WHERE parent_table = &#39;public.metrics&#39;;</pre><p>What people get wrong:</p><p>premake too low. Seven days of runway means a maintenance job can fail for a week before you notice. That&#39;s a feature if you&#39;re monitoring, a trap if you&#39;re not.</p><p>retention_keep_table = true is the default and it only detaches. Detached tables still occupy disk. I&#39;ve walked into two shops where &quot;retention is configured&quot; meant a hundred orphaned tables nobody had dropped. It&#39;s the right setting if legal or a downstream consumer might need a &quot;just in case&quot; window, but it isn&#39;t free storage.</p><p>infinite_time_partitions = true keeps creating partitions even when data stops flowing. If your ingest pauses for a maintenance window and this is off, partition creation stops with it, and you land back in the default when ingest resumes.</p><p>The template table matters less than it used to. Since PG 11 indexes on the parent propagate to children, but unlogged status and some constraint types still ride through the template. Set it up anyway; it costs nothing.</p><h3>Scheduling with run_maintenance_proc and pg_cron</h3><p>OptionManaged availabilityFailure visibilityNotespg_cronWidely available, including RDS/Aurora and Azurecron.job_run_details if enabledEasiest to check independentlypg_partman BGWNeeds pg_partman_bgw in shared_preload_librariesPostgres log onlyNot available where you can&#39;t set preload libsExternal schedulerAlways availableWhatever your scheduler gives youAdds a network dependency and a credential</p><p>The pg_cron line itself is trivial:</p><pre>SELECT cron.schedule(&#39;partman-maintenance&#39;, &#39;15 * * * *&#39;,<br>                     $$CALL partman.run_maintenance_proc()$$);</pre><p>run_maintenance_proc() via pg_cron is a procedure, so it can commit between partition sets. That&#39;s good for large installs and it means a failure partway through leaves some work done and some not.</p><p>Whichever you pick, the scheduler’s own status is not evidence. My cron job was still “scheduled” the whole six weeks. A silently failed maintenance job looks exactly like a healthy database, right up until the disk graph goes vertical.</p><h3>The monitoring queries I actually run</h3><p><strong>1. Future runway versus premake.</strong> If this drops below your configured premake, maintenance is behind.</p><pre>SELECT p.parent_table, p.premake,<br>       count(*) FILTER (WHERE s.partition_start &gt; now()) AS future_parts<br>FROM partman.part_config p<br>CROSS JOIN LATERAL partman.show_partitions(p.parent_table) s<br>GROUP BY 1, 2;</pre><p><strong>2. Anything in the default partition.</strong> This is the check that would have saved me. Alert on any non-zero result.</p><pre>SELECT count(*) FROM metrics_default;</pre><p>Run it as a bounded check (SELECT 1 FROM metrics_default LIMIT 1) if the default has grown, because counting 200 GB is not a friendly monitoring query.</p><p><strong>3. Partition sizes ordered by range.</strong> For growth forecasting and for spotting the one child that’s ten times its siblings.</p><pre>SELECT c.relname,<br>       pg_size_pretty(pg_total_relation_size(c.oid)) AS size,<br>       pg_get_expr(c.relpartbound, c.oid) AS bounds<br>FROM pg_inherits i<br>JOIN pg_class c ON c.oid = i.inhrelid<br>WHERE i.inhparent = &#39;public.metrics&#39;::regclass<br>ORDER BY c.relname;</pre><p><strong>4. Last successful maintenance run.</strong> Query cron.job_run_details for the partman job and alert if the newest succeeded status is older than two intervals.</p><p>Four checks. Twenty minutes of work. They cover the failure that actually happens.</p><h3>Edge cases that bit me</h3><p><strong>Default partition locking.</strong> Attaching a new partition to a set that has a default partition requires scanning the default to prove no existing rows belong in the new range, and that scan takes a lock blocking queries on the default. If your default is empty this is instant. If it has 214 GB in it, your attach hangs and takes your ingest with it. My preference now is to keep a default partition purely as a tripwire and alert on the first row that lands in it.</p><p><strong>Attach validation.</strong> ATTACH PARTITION normally scans the incoming table to validate every row satisfies the constraint. Add a matching CHECK constraint before attaching and Postgres skips the scan. On an 800 GB table that&#39;s the difference between an hour and a second.</p><p><strong>Detaching.</strong> PG 14 added DETACH PARTITION CONCURRENTLY, which avoids holding ACCESS EXCLUSIVE on the parent throughout. Before 14, a long-running analytics query on any child will block your retention drop, and your retention drop will block everything else. I&#39;ve seen a 40 minute report queue up the entire ingest path this way. Even with concurrent detach available, a plain DROP TABLE when you drop old partitions in Postgres can still queue behind a long-running query holding a lock on that specific child, so don&#39;t assume drops are instant under load.</p><p><strong>Foreign keys and dump ordering.</strong> Foreign keys referencing a partitioned table work, but referencing one from another table needs care around the compound key, since the FK has to match the full unique constraint, not just the logical identifier. Separately, pg_dump/restore ordering on a set with a large partition count can be noticeably slower than the same data unpartitioned, purely from the object count. Neither is a reason to avoid partitioning, but both are things to test before you&#39;re relying on them during an incident.</p><p><strong>Timezone drift.</strong> If your partitions are bounded on UTC dates and your product defines “day” in America/Chicago, every daily rollup straddles two partitions. Not fatal, but pick one definition and document it.</p><p><strong>Legal hold.</strong> Sooner or later someone will ask you to keep one customer’s data past the retention window. Retention is per partition set, not per tenant. Have that conversation before you set retention_keep_table = false.</p><h3>Converting an 800 GB table without a maintenance window</h3><p>The recipe that has worked for me:</p><ol><li>Create the new partitioned parent with the same column types and the partition key in the PK.</li><li>Backfill by range in chunks of a few million rows, oldest first, with a sleep between batches so autovacuum keeps up. On 800 GB across 14 months this took about 26 hours of wall clock, spread over four nights.</li><li>Dual-write new rows to both tables during the backfill, or write to the new table only and backfill the tail last.</li><li>Cut over reads in a transaction with a rename, then drop the old table once you’re confident.</li></ol><p>If the old table’s data all falls inside one partition range, you can add a matching CHECK and ATTACH it whole. That&#39;s rare for a multi-year table but it&#39;s the fastest path when it applies. Genuinely painful part: the backfill, and getting foreign keys and dependent views to follow the rename.</p><h3>pg_partman vs TimescaleDB: when the extension earns its keep</h3><p>Vanilla + pg_partmanTimescaleDBPartition lifecycleYou configure it, ~30 linesHandled by hypertablesIncremental rollupsWrite your ownContinuous aggregatesCompressionTable-level tricks onlyNative columnar compressionAmazon RDSWorksNot an available extensionUpgrade pathPostgres onlyExtension version couples to PG versionLicensingPostgres license throughoutSome features under a source-available license</p><p>Decision rule: if you need incrementally maintained rollups over billions of rows, or your storage bill is dominated by cold time-series and compression would halve it, Timescale is doing real work you’d otherwise build badly. If your needs are “create partitions ahead, drop old ones, keep queries fast,” pg_partman covers it and you keep a plain Postgres upgrade path. Being on RDS often makes the decision for you.</p><h3>What I’d tell my past self</h3><p>Automate on day one, before there’s enough data for it to matter. Monitor the automation, not the table: the table will look healthy the entire time it’s failing. Size partitions for the maintenance operation you’ll need to run under pressure, not for a calendar that looks neat. And don’t adopt an extension to avoid writing thirty lines of config, but do adopt one when it’s genuinely solving a problem you’d otherwise solve worse.</p><p>If you want a second pair of eyes, <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-time-series-partition-maintenance">MyDBA</a> has a free health check that flags stalled partition maintenance and rows drifting into default partitions, which are the two things I’d have wanted a page about at 2 a.m. instead of 3.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1d4302792197" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The psql Password Prompt You Never Set: It’s Not a Bug, It’s pg_hba.conf]]></title>
            <link>https://medium.com/@philmcc/the-psql-password-prompt-you-never-set-its-not-a-bug-it-s-pg-hba-conf-907aa5ad13e0?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/907aa5ad13e0</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Sat, 08 Aug 2026 10:04:53 GMT</pubDate>
            <atom:updated>2026-08-08T10:04:53.661Z</atom:updated>
            <content:encoded><![CDATA[<p>You install PostgreSQL, open a terminal, and run psql -U postgres. Instead of a prompt, you get:</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/the-psql-password-prompt-and-pg-hba-conf?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=the-psql-password-prompt-you-never-set-it-s-not-a-bug-it-s-pg-hba-conf">The psql password prompt and pg_hba.conf</a></p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=GdeFOe9lmV0">Why psql Asks for a Password You Never Sethttps://www.youtube.com/watch?v=GdeFOe9lmV0</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*uUe-FIBqeATQcgV2.png" /></figure><pre>Password for user postgres:</pre><p>You didn’t set a password during installation. Hitting Enter or guessing fails every time. This catches almost every new PostgreSQL user. The immediate assumption: the installer is broken, or there’s a secret default password buried in a README.</p><p>Neither is true. The database behaves exactly as configured. The password prompt comes from the client reacting to whatever authentication method matched first in pg_hba.conf. Most often, the root cause is a mismatch between your operating system user and the database role you’re trying to connect as. The host-based authentication file controls everything, and understanding its top‑to‑bottom evaluation immediately explains the prompt.</p><h3>How pg_hba.conf Decides Authentication</h3><p>Every connection goes through pg_hba.conf. Postgres reads the file top to bottom and uses the <strong>first</strong> line that matches your connection’s type, target database, username, and (for TCP) source address. After a match, evaluation stops cold. The method on that line must succeed; if it demands a password and you don’t supply one, you’re rejected regardless of any later rules that might be more permissive.</p><p>To locate the file on a running instance, connect if possible and run:</p><pre>SHOW hba_file;</pre><p>Typical paths by platform:</p><ul><li><strong>Ubuntu/Debian (apt):</strong> /etc/postgresql/16/main/pg_hba.conf</li><li><strong>Red Hat/CentOS (yum):</strong> /var/lib/pgsql/16/data/pg_hba.conf</li><li><strong>macOS (Homebrew):</strong> /opt/homebrew/var/postgres/pg_hba.conf</li><li><strong>Docker:</strong> /var/lib/postgresql/data/pg_hba.conf</li></ul><p>Inside, you’ll see entries like these:</p><pre># TYPE  DATABASE        USER            ADDRESS                 METHOD</pre><pre># Unix-domain socket connections – by default this is peer<br>local   all             all                                     peer</pre><pre># TCP connections over loopback – typically scram‑sha‑256<br>host    all             all             127.0.0.1/32            scram-sha-256</pre><p>On a fresh Ubuntu install, local connections default to peer. That method checks whether your OS user name matches the database role name. If you’re logged in as ubuntu but trying to connect as postgres, the match fails and Postgres falls through to the next rule. In many default configs, the next matching local rule asks for scram-sha-256 or md5. Suddenly you’re facing a password prompt for a role that never had one.</p><p>TCP connections over 127.0.0.1 often default to scram‑sha‑256 right away. Running psql -h 127.0.0.1 -U postgres immediately demands a password, even though the postgres role may have no password set. That’s exactly what the config ordered.</p><p>The fix isn’t to guess passwords or reinstall. Either adjust the authentication method to trust for local development (knowing the security trade‑off) or set a password for the role and supply it reliably. The prompt isn’t an accident — it’s a direct consequence of the first matching rule in pg_hba.conf that requires a credential. Once you see the file, the mystery vanishes.</p><p>To see the current rules in play without manually parsing the file, you can run this diagnostic query from any connection that does succeed:</p><pre>SELECT line_number, type, database, user_name, address, netmask,<br>       auth_method, options<br>FROM pg_hba_file_rules<br>WHERE  1=1<br>ORDER BY line_number;</pre><p>The pg_hba_file_rules view (available since PostgreSQL 10) shows exactly what the server sees after processing includes and group mappings. It’s a far safer way to sanity-check your configuration than editing the file blindly, especially on systems where multiple pg_hba.conf files might exist due to leftover installations.</p><p>When I’m troubleshooting a client’s connection issue, this is one of the first things I pull up after verifying the server is running. Tools like <strong>MyDBA</strong>, a Postgres monitoring and health‑check tool I built (<a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=the-psql-password-prompt-you-never-set-it-s-not-a-bug-it-s-pg-hba-conf">https://mydba.dev/</a>), bake this kind of introspection into automated health reports so you’re not manually stitching together system catalogs at 2 a.m. But even without any tooling, the view alone will clarify why that password prompt appeared.</p><h3>The Three Most Common Surprise‑Prompt Scenarios</h3><p><strong>1. Peer rejection with </strong><strong>local connections</strong><br>On most Linux distributions, the top local rule uses peer. If your OS user and database role don’t match, peer rejects you immediately. Postgres then falls through to the next local rule—often scram-sha-256—and you get a password prompt. Running sudo -u postgres psql bypasses the mismatch without touching any configuration file.</p><p><strong>2. Forcing TCP changes the matched rule</strong><br>Even if local works perfectly, adding -h localhost or -h 127.0.0.1 switches the connection from a Unix socket to TCP. The socket rules no longer apply; the first matching TCP rule wins. On a typical default install, that TCP rule demands a password even for the postgres role.</p><p><strong>3. Docker’s default </strong><strong>trust—until you bridge a network</strong><br>Official PostgreSQL Docker images often ship with trust for local Docker‑network connections. Attach a container to a custom bridge or expose it on a host port, and the source address no longer matches the trusted range. The next rule down is frequently password‑based. You haven’t changed any setting, but the network topology moved your connection into a different authentication bracket.</p><p>In all three cases, the database isn’t misbehaving. It’s methodically evaluating the rules. The fix is always the same: decide what authentication you actually want for that connection path, then change exactly one line in pg_hba.conf to match—or, if you prefer, set a password and use it everywhere.</p><h3>Making Changes You Won’t Regret</h3><p>Aim for the least permissive change that unblocks you. The most common practical options, in order of increasing security:</p><ul><li><strong>trust</strong> – No password, no OS‑user check. Only for disposable local development environments that never see production data.</li><li><strong>peer</strong> – Uses the OS user name. Excellent for local scripting because it requires no password management yet still ties identity to a verified OS account.</li><li><strong>md5</strong> – Widely supported but no longer considered cryptographically safe. Use it only if you must support very old drivers.</li><li><strong>scram-sha-256</strong> – The modern default. Requires a password to be set for the role and uses a proper challenge‑response handshake. This is what you should aim for on any server that accepts remote connections.</li></ul><p>After editing pg_hba.conf, reload—do <em>not</em> restart—with:</p><pre>SELECT pg_reload_conf();</pre><p>If you accidentally lock yourself out, check the server log before panicking. Postgres writes FATAL lines with the exact file, line number, and authentication method that rejected the connection.</p><h3>One Rule Out of Order Is All It Takes</h3><p>The fundamental thing to internalise is that pg_hba.conf stops at the first match. A perfectly crafted trust rule twenty lines down will never be evaluated if an earlier password‑based rule matches first. When in doubt, inspect pg_hba_file_rules, note the line numbers, and trace your connection parameters against them in order. That practice alone eliminates the “but I never set a password” confusion.</p><p>Over the years I’ve watched teams spend hours reinstalling Postgres, resetting role passwords, or disabling the firewall — all because a single peer rule rejected a user mismatch and pushed the connection into a scram-sha-256 prompt they didn’t expect. The good news is that the fix rarely takes more than five minutes once you understand the top‑to‑bottom evaluation. The password prompt isn’t a bug; it’s the database doing its job.</p><p><em>Philip McClarence builds MyDBA, a Postgres monitoring and health‑check tool — </em><a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=the-psql-password-prompt-you-never-set-it-s-not-a-bug-it-s-pg-hba-conf"><em>https://mydba.dev/</em></a><em>. It automates the very configuration checks this article describes, so your team sees a clear pass/fail before users hit a mystery password prompt.</em></p><p>Next time you’re staring at a password prompt you never created, skip the reinstall. Open pg_hba.conf, run pg_hba_file_rules, and trace your connection through the first matching line. And if you’d rather have that diagnosis handed to you automatically, MyDBA covers this and dozens of other Postgres health checks at <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=the-psql-password-prompt-you-never-set-it-s-not-a-bug-it-s-pg-hba-conf">https://mydba.dev/</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=907aa5ad13e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Five schema decisions that cost me sleep (and eventually a late‑night page)]]></title>
            <link>https://medium.com/@philmcc/five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-page-de1d08eff5da?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/de1d08eff5da</guid>
            <category><![CDATA[database-design]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Sat, 08 Aug 2026 10:03:47 GMT</pubDate>
            <atom:updated>2026-08-08T10:03:47.610Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>They aren’t bugs.</strong> They pass code review. They look perfectly reasonable when you’re staring at a CREATE TABLE statement on a Wednesday afternoon. And then, six months later, your phone buzzes at 2 a.m. because the database that breezed through load testing with a thousand rows is now choking on fifty million.</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/five-postgres-schema-decisions-you-ll-regret-at-2am?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">Five Postgres Schema Decisions You’ll Regret at 2am</a></p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=i0FvxZVs3TU">5 Postgres Schema Mistakes That Quietly Kill Performancehttps://www.youtube.com/watch?v=i0FvxZVs3TU</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*098muM4rHQ9f5xcC.png" /></figure><p>I’ve been dragged out of bed enough times to recognize the quiet killers — the schema decisions that don’t crash anything, they just slowly turn your instance into a resource hog. I made a companion video walking through these, but articles are where I can actually show you the exact SQL and the edge cases that catch people off guard. So let’s do that. Here are five schema mistakes I’ve extracted from real incident reviews, code‑review scars, and the occasional therapeutic rant at a whiteboard.</p><h3>1. The unbounded VARCHAR</h3><p>Most developers think VARCHAR without a length is just “a string.” It is, except PostgreSQL will let you cram up to 1 GB into it. That’s not a theoretical maximum; I watched a botnet payload turn a harmless event_payload column into a 47 MB XML blob. The insert succeeded. The table didn’t complain. But then every SELECT that touched that row had to chase a TOAST pointer.</p><p>Here’s what happens under the hood: if a row’s total width exceeds roughly 2 KB, PostgreSQL pushes large values out of the main table and into a separate TOAST table. The original row stores a tiny pointer. That’s fine for data you rarely read, but the moment a routine query scans a bunch of toasted rows — like SELECT * FROM events WHERE created_at &gt; now() - interval &#39;1 hour&#39;—each row requires an extra random I/O to fetch the toasted value. You don’t notice with 100 rows. With 10 million, your sequential scan turns into a random‑read carnival. Buffer cache hit ratios drop, CPU wait time climbs, and your pager goes off.</p><p>And it’s not just reads: indexes that include the toasted column (like a covering index or a UNIQUE constraint on that column) have to store the full value. A single 22 MB value stuffed into a B‑tree index will balloon a leaf page, causing index bloat and page splits on every similar insert. I once traced a 40 GB index back to three gigantic rows that should have been rejected at the application layer.</p><p><strong>The fix is one line</strong>, usually VARCHAR(255) or a CHECK constraint. If the table is already live and big, you can’t just add a CHECK with an exclusive lock. Instead:</p><ol><li>Add the constraint as NOT VALID:</li></ol><ul><li>ALTER TABLE events ADD CONSTRAINT payload_len CHECK (length(event_payload) &lt;= 10000) NOT VALID;</li></ul><ol><li>This enforces the rule on new rows immediately but doesn’t scan existing ones.</li><li>Validate it later with ALTER TABLE events VALIDATE CONSTRAINT payload_len; — this takes a SHARE UPDATE EXCLUSIVE lock, which won’t block writes, but until it finishes you’re paying a small overhead for new writes.</li></ol><p>The point: a length‑less VARCHAR is not a safe default. Give it a bound, or at least a CHECK that makes you think about what “reasonable” means.</p><h3>2. A nullable column inside a multi‑column UNIQUE constraint</h3><p>This one has bitten me in user‑identity codebases more times than I can count. You set up a tenants table and want to guarantee that each tenant’s email is unique. So you write:</p><pre>CREATE TABLE tenant_users (<br>    user_id   bigserial PRIMARY KEY,<br>    tenant_id int,<br>    email     text,<br>    UNIQUE (email, tenant_id)<br>);</pre><p>Looks correct. Then you insert:</p><pre>INSERT INTO tenant_users (tenant_id, email) VALUES (NULL, &#39;alice@example.com&#39;);<br>INSERT INTO tenant_users (tenant_id, email) VALUES (NULL, &#39;alice@example.com&#39;);</pre><p>Both rows go in. Why? SQL’s rules: NULL is never equal to NULL. A unique constraint checks inequality of all columns, and since NULL != NULL, each row is considered distinct. You’ve just allowed duplicate emails for users who aren’t assigned to a tenant yet. The guarantee you thought you had is vapor.</p><p>I encountered this in a B2B SaaS app where “unassigned” users were flagged with tenant_id IS NULL. Support started getting reports that two different people ended up with the same login email, and the application logic that relied on the unique constraint to prevent duplicates just silently let it happen. It took three days to untangle the data.</p><p><strong>Two real fixes, plus a modern convenience:</strong></p><ul><li>If tenant_id should <em>never</em> be NULL, slap NOT NULL on it. The unique constraint then works as expected because every row has a real value.</li><li>If NULL is genuinely allowed but you still want per‑tenant uniqueness for those with a tenant, use a <strong>partial unique index</strong>:</li><li>CREATE UNIQUE INDEX uq_tenant_email ON tenant_users (email) WHERE tenant_id IS NOT NULL;</li><li>That index ignores rows where tenant_id IS NULL, so duplicates among unassigned users are still allowed (you’d need some other mechanism), but assigned users are protected.</li><li>If you’re on <strong>PostgreSQL 15 or newer</strong>, there’s an even cleaner option: NULLS NOT DISTINCT. This clause tells PostgreSQL to treat NULL as equal for the purpose of uniqueness:</li><li>ALTER TABLE tenant_users ADD CONSTRAINT unique_email_tenant UNIQUE NULLS NOT DISTINCT (email, tenant_id);</li><li>A single constraint now prevents duplicates across <em>all</em> rows, including those with NULL tenant IDs. This eliminates the need for multiple partial indexes and makes the intent crystal clear.</li></ul><p>Either way, you need to understand the nullable‑unique trap. It’s not a Postgres bug — it’s just SQL being SQL — but it’s a design decision you make at CREATE TABLE that silently changes the semantics of your application.</p><h3>3. Leaving columns nullable “just in case”</h3><p>Every column that doesn’t have NOT NULL adds a bit to the row’s null bitmap. For a table with 40 columns, that’s 5 bytes of overhead <em>per row</em>, which sounds trivial until you’re storing half a billion rows. But the bigger problem isn’t the storage; it’s what the planner can’t do.</p><p>When the database knows a column is NOT NULL, it can optimize IS NOT NULL checks out of execution. It can use certain join strategies that rely on the guarantee. And, critically, when you define a FOREIGN KEY, a nullable column means the relationship is <em>not</em> compulsory—a NULL in a foreign‑key column means “this relationship is broken.” I’ve seen entire reporting pipelines build defensive COALESCE and LEFT JOIN logic around nullable columns that were never, ever actually null in production. The schema told the application “be prepared for nothing,” so the application wrote defensive code, and the defensive code introduced bugs.</p><p>A practical audit I run on every new client’s schema: look at pg_stats.null_frac. A column with null_frac = 0 for a table with millions of rows is a column that deserves NOT NULL. You can find them with:</p><pre>SELECT attname<br>FROM pg_stats<br>WHERE tablename = &#39;orders&#39; AND null_frac = 0;</pre><p>The problem is that adding NOT NULL on a large live table used to be painful. It required a full table scan with an exclusive lock. Nowadays you can do it safely with a two‑step pattern:</p><ol><li>ALTER TABLE orders ADD CONSTRAINT nn_order_date CHECK (order_date IS NOT NULL) NOT VALID;</li><li>ALTER TABLE orders VALIDATE CONSTRAINT nn_order_date;</li></ol><p>Validate takes a SHARE UPDATE EXCLUSIVE lock—writes can still happen, but it’ll scan the table to confirm no existing nulls. Once done, you can ALTER TABLE orders ALTER COLUMN order_date SET NOT NULL; without another scan (Postgres knows the constraint guarantees it). You’ve just tightened your schema without downtime.</p><h3>4. TEXT where JSONB belongs (and the reverse mistake)</h3><p>This one is two mistakes wrapped in a single typing decision.</p><p><strong>Mistake A: storing structured data as </strong><strong>TEXT.</strong><br>I worked with a team that stored raw JSON API responses from a partner service in a response_body TEXT column. Every time they needed to filter on order_total or customer_id inside that payload, they’d pull the entire blob back to the app, parse it, and filter. With 300 million rows, the read amplification was staggering—the table itself was 2.1 TB, and even a simple dashboard query could pull 5 GB of text over the wire just to find three rows. They were paying CPU and network for parsing they didn’t need to do.</p><p>When we changed it to JSONB, query latency for a common filter dropped from 1.4 seconds to 9 ms. Not because JSONB is magic, but because we could add a GIN index on response_body and push the filter into the database with WHERE response_body @&gt; &#39;{&quot;customer_id&quot;: &quot;abc&quot;}&#39;. GIN indexes on JSONB are fast for existence and containment queries, and you avoid the full‑parse‑on‑every‑read tax.</p><p><strong>Mistake B: stuffing plain strings into </strong><strong>JSONB.</strong><br>The reverse happens when developers decide every column should be a JSON document. I saw a table where email, display_name, and avatar_url were stored as a single profile JSONB column. For an equality check on email, the query was WHERE profile -&gt;&gt; &#39;email&#39; = &#39;x&#39;. That forced the database to parse the binary JSONB representation at read time for every row that survived other filters. Write‑side, every insert had to serialize those three simple fields into JSONB’s decomposed binary format. The insert throughput dropped by roughly 30% compared to normal columns, and the planner couldn’t use statistics on individual fields effectively.</p><p>The rule of thumb: if you need to query <em>inside</em> the structure, JSONB plus a GIN index is your friend. If you’re just storing unstructured text that you’ll never query by its internal fields, TEXT is lower overhead. If the data has a stable schema with fields you query individually, use real columns—they’re cheaper to read, index, and maintain. Don’t let JSONB become a blanket excuse to stop designing a schema.</p><h3>5. UUID primary keys without version 7</h3><p>I’ve got nothing against UUIDs in principle. But if you use UUIDv4 as a primary key on a large append‑heavy table, you’re trading away insert performance and index locality for randomness you almost certainly don’t need.</p><p>Here’s what happens: a B‑tree index stores keys in sorted order. With a monotonically increasing key (a sequence, or UUIDv7 whose first bytes are a timestamp), each new insert lands on the rightmost leaf page. That page stays hot in memory. New pages are allocated sequentially. The index stays compact and cache‑friendly. With random UUIDv4, a new row’s primary key could land anywhere in the tree, forcing the database to read a random leaf page, possibly split it, and write multiple dirty pages. Your buffer cache can’t keep the hot pages in memory because there <em>is</em> no single hot page; the entire index churns.</p><p>This difference is stark at scale:</p><p>ID TypeInsert LocalityB‑tree BehaviourPage SplitsBigInt / SerialSequentialAppends to end of indexVery RareUUIDv7SequentialAppends to end of indexVery RareUUIDv4 (Random)Completely RandomScatters across all pagesExtreme</p><p>I saw this on an event‑sourcing table with 800 million rows. With UUIDv4, the primary key index was 143 GB, the cache hit ratio on that index was below 65%, and writes were bottlenecked on random I/O. After a painful migration to UUIDv7 (timestamp‑ordered), the index size dropped to 91 GB because of reduced fragmentation, and the insert throughput improved by a factor of three under load.</p><p>In Postgres 17, native support for UUIDv7 is being discussed, but you can get it today via extensions like pg_uuidv7 or by writing your own function that packs the timestamp into the first 48 bits. The migration cost is real: you can’t just alter the primary key data type; you’d need to rebuild the table and all foreign keys referencing it. If you’re already deep into UUIDv4 on a huge table, the fix is often not to change the PK but to add a separate sequential column for clustering, or a time‑ordered index, and accept the sunk cost. For any new table you expect to grow beyond twenty million rows, choosing UUIDv7 from day one avoids that massive migration entirely.</p><h3>How to catch these before they cost you</h3><h3>How to catch these before they cost you</h3><p>Prevention starts at the code‑review level, but I’ve learned that even the best reviewers miss the subtle ones — the nullable unique trap, the unbounded VARCHAR, the UUID choice that looks fine on a fresh table. So I added a second line of defense: an automated schema‑audit step that runs on every pull request. It’s a simple CI job that connects to a scratch replica and queries pg_stats for null fractions, scans pg_constraint for improperly nullable unique columns, and flags any VARCHAR without a length. If you don’t want to build that yourself, there are extensions like pgspot and tools that do this statically on DDL files.</p><p>But static analysis misses what happens at scale. A column that’s zero‑null today might become 20% null six months from now when a new integration path bypasses the application. That’s where live monitoring comes in. I built <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">MyDBA</a> to track these patterns in real time — it alerts when a table’s null fraction drifts, when a previously useful index starts bloating from toasted values, or when page splits on a random UUID index spike during peak hours. It’s the sort of guardrail I needed at 2 a.m. when I was still SSH’ing into boxes and squinting at pg_stat_user_tables. Having a tool that continuously scores your schema “health” means you catch these before the phone rings.</p><p>The most impactful thing you can do today, however, is just audit the big tables. Run the null_frac query from earlier on your top‑ten tables by size. Check every UNIQUE constraint that includes nullable columns. If you find a VARCHAR without a bound, at least add a CHECK with a generous limit. And if you’re still on UUIDv4 for a fast‑growing table, file a ticket now—even if the migration isn’t immediate, the knowledge that you’re burning cache and write throughput should inform every future architectural decision.</p><h3>Sleep is a database feature</h3><p>These five schema decisions aren’t edge cases found in obscure manuals; they’re the ones that pass code review, survive load tests with toy data, and eventually show up as a 503 error at the worst possible time. They’re also entirely avoidable if you treat your schema with the same scrutiny you give your application code. The database doesn’t complain when you feed it a 47 MB XML blob in a VARCHAR—it just does the work, silently, until everything slows down. That’s the real danger: Postgres will let you build a system that works until it doesn’t, and the tipping point is often a schema choice made months earlier.</p><p>I’ve stopped thinking of schema design as a one‑time project‑phase activity and started treating it as an ongoing discipline — something you monitor, lint, and refine as data grows. It’s not glamorous, but neither is getting paged at 2 a.m. because someone left a column nullable “just in case.” The good news is that with a handful of queries, a few constraint tricks, and tools that keep an eye on the things you can’t watch manually, you can catch the quiet killers before they turn into late‑night incidents.</p><p>Philip McClarence builds MyDBA, a Postgres monitoring and health-check tool — <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">https://mydba.dev/</a></p><p>If you’d rather not wait until the pager buzzes, give <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=five-schema-decisions-that-cost-me-sleep-and-eventually-a-late-night-p">MyDBA</a> a try — it’s built to catch exactly these patterns so you can sleep through the night.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=de1d08eff5da" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Partition pruning isn’t a switch you flip once]]></title>
            <link>https://medium.com/@philmcc/partition-pruning-isnt-a-switch-you-flip-once-75322f465bb5?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/75322f465bb5</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Tue, 04 Aug 2026 16:02:13 GMT</pubDate>
            <atom:updated>2026-08-04T16:02:13.664Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>I walk through this live in the companion video — but here’s the written deep dive, with the plans and the pain that got me paged at 3 a.m.</strong></p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/why-your-partitioned-tables-are-still-slow-a-deep-dive-into-postgresql?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=partition-pruning-isn-t-a-switch-you-flip-once">Why Your Partitioned Tables Are Still Slow: A Deep Dive into PostgreSQL Partitio</a></p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=7-UnHDvOtbY">Partition Pruning: Making the Planner Skip 99% of Your Datahttps://www.youtube.com/watch?v=7-UnHDvOtbY</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-ri18rDD9sATLBCc.png" /></figure><h3>The pager that went off anyway</h3><p>The orders table had 2.1 billion rows and crossed 100 monthly partitions. Every DBA in the company had nodded approvingly at the partitioning scheme. The most frequent query was innocuous: “give me all orders for a particular day.” Since we partition by order_date, the planner should touch exactly one partition. Life should have been good.</p><p>Then the pager fired. P99 latency on the main API jumped from 12 ms to 230 ms. I pulled up pg_stat_statements and saw the same SELECT … FROM orders WHERE order_date = $1 suddenly clocking 5× the shared-blocks hit. Ten minutes of frantic digging later, I had a smoking gun: the query was scanning <em>all 100 partitions</em>, not one.</p><p>The assumption that “partitioning just works” had bitten me, hard. The culprit wasn’t some exotic edge case — it was an ORM that, during a recent “string formatting cleanup,” had started casting order_date to text before sending it to the database. Partition pruning, that silent guardian, had vanished. The lesson: partition pruning is a per‑query contract between your WHERE clause and the planner. Break it, and you pay for the entire table.</p><h3>What pruning actually promises (and what it doesn’t)</h3><p>Partition pruning means the planner or executor eliminates child tables that can’t possibly hold matching rows. Get the pruning right, and a query that logically asks “rows from 2024‑06‑01” never even opens the file for 2024‑06‑02, much less scans the index. It’s not about skipping rows — that’s what indexes do. Pruning decides <em>which tables</em> to touch; indexes decide <em>how</em> to dig through a table once you’re inside it. Confuse the two, and you’ll spend hours adding indexes to partitions you didn’t need to touch in the first place.</p><p>Pruning works against the partition bounds of RANGE, LIST, and HASH tables alike. The nuances differ — HASH pruning only helps with equality, RANGE with equality and range conditions — but the core promise is the same: the database does not waste I/O on partitions that are provably irrelevant.</p><p>The gotcha is that “provably” can happen at one of two moments.</p><h3>Two flavors: planner‑time vs execution‑time pruning</h3><p>Postgres can prune early (during planning, with zero runtime overhead) or late (during execution, with a small one‑time cost). The distinction shows up right in EXPLAIN.</p><h3>Planner‑time pruning</h3><p>When the WHERE clause supplies a constant literal on the partition key, the planner knows the value before the query ever runs. It narrows the plan to exactly the partitions that need to be opened, and the EXPLAIN output contains no Append node at all — just a scan on the single surviving partition:</p><pre>EXPLAIN SELECT * FROM orders WHERE order_date = &#39;2024-06-01&#39;;</pre><pre>Index Scan using orders_2024_06_pkey on orders_2024_06<br>  Index Cond: (order_date = &#39;2024-06-01&#39;::date)</pre><p>There is no Subplans Removed line because nothing was removed at execution time; the planner never considered the other 99 partitions. This is the “proven at plan time, zero runtime cost” ideal.</p><h3>Execution‑time pruning</h3><p>But if the pruning value arrives from a parameter, a subquery, or a function whose result isn’t constant, the planner has to stay flexible. It builds a plan that includes all partitions (Append) and defers the pruning decision until execution. The EXPLAIN (plan‑time output) will show an Append over all partitions; EXPLAIN ANALYZE will reveal how many partitions were actually skipped:</p><pre>PREPARE p(date) AS SELECT * FROM orders WHERE order_date = $1;<br>EXPLAIN ANALYZE EXECUTE p(&#39;2024-06-01&#39;);</pre><pre>Append  (cost=0.00..132.12 rows=3 width=...) (actual ...)<br>  Subplans Removed: 99<br>  -&gt;  Index Scan ... on orders_2024_06  ...<br>        Index Cond: (order_date = &#39;2024-06-01&#39;::date)</pre><p>The Subplans Removed: 99 line appears only in EXPLAIN ANALYZE and tells you execution‑time pruning worked. The overhead is small — typically microseconds — but it’s non‑zero, and if the query runs millions of times a day, it starts to matter. Worse, if you’re only looking at EXPLAIN without ANALYZE, you might misinterpret the plan as “scanning all partitions” and panic unnecessarily. That’s a diagnostic trap I’ve stepped in more than once.</p><h3>The WHERE‑clause shapes that get you pruning</h3><p>To set yourself up for success, stick to these patterns — they let the planner (or executor) prove which partitions are needed:</p><ul><li><strong>Literal equality or range</strong> on the partition key: WHERE order_date = &#39;2024-06-01&#39;, WHERE order_date &gt;= &#39;2024-06-01&#39;.</li><li><strong>BETWEEN with literal bounds</strong>: WHERE order_date BETWEEN &#39;2024-06-01&#39; AND &#39;2024-06-30&#39;.</li><li><strong>IN with a literal list</strong>: WHERE order_date IN (&#39;2024-06-01&#39;,&#39;2024-06-02&#39;).</li><li><strong>Correlated subqueries</strong> where the outer query’s partition key can be evaluated once per partition (often planner‑time if the correlation is simple).</li><li><strong>Prepared statement parameters</strong> — they work, but only via execution‑time pruning (the plan will show the full Append, and EXPLAIN ANALYZE will confirm the removal count).</li></ul><p>For RANGE partitioning, equality and range conditions both work; for LIST, equality and IN lists; for HASH, only equality on the partition key.</p><h3>The shapes that silently disable it</h3><p>This is the section that would have saved my sleep. Here are the anti‑patterns that silently turn partition pruning off, often leaving no trace except latency.</p><h3>Wrapping the partition key in a function</h3><p>Any non‑immutable expression on the key column forces the planner to throw up its hands. I’ve seen DATE_TRUNC, TO_CHAR, EXTRACT, even a stray ::text cast:</p><p><strong>Broken:</strong></p><pre>WHERE TO_CHAR(order_date, &#39;YYYY-MM-DD&#39;) = &#39;2024-06-01&#39;</pre><p>Postgres sees a volatile expression and cannot prune at plan time. At execution time it <em>might</em> still prune if the expression is stable and the executor can evaluate it, but in practice it often falls back to scanning every partition.</p><p><strong>Fixed:</strong></p><pre>WHERE order_date &gt;= &#39;2024-06-01&#39; AND order_date &lt; &#39;2024-07-01&#39;</pre><h3>Implicit type casts and mismatched comparisons</h3><p>If your partition key is a date column but the query compares it to a text literal that PostgreSQL must cast, you can lose pruning. Even a benign‑looking parameter type mismatch can cause trouble. SQL functions that are marked STABLE rather than IMMUTABLE will also disqualify planner‑time pruning.</p><p>One real‑world trap: an application that sends all parameters as text strings (… WHERE order_date = $1::text). The planner can’t assume the text value will be a valid date at plan time, so it bails.</p><p><strong>Broken:</strong></p><pre>PREPARE bad AS SELECT * FROM orders WHERE order_date = $1::text;<br>EXPLAIN EXECUTE bad(&#39;2024-06-01&#39;);<br>-- All partitions appear; no pruning.</pre><p><strong>Fixed:</strong></p><pre>PREPARE good(date) AS SELECT * FROM orders WHERE order_date = $1;</pre><h3>OR across non‑partition columns</h3><p>A condition like WHERE order_date = &#39;2024-06-01&#39; OR customer_id = 42 completely short‑circuits pruning. Even though one branch ties to a partition key, the planner cannot prove the other branch won’t select rows from <em>any</em> partition, so it keeps all of them.</p><p>Similarly, a JOIN where the partition key is constrained on only one side of the join will often defeat pruning for the other side’s partitions.</p><h3>Prepared statements, generic plans, and plan_cache_mode</h3><p>Prepared statements are a loaded gun. The first five executions use a custom plan (with the actual parameter values known during planning), so the planner might prune at plan time. After that, PostgreSQL switches to a generic plan and loses visibility into the parameter values entirely. Suddenly, queries that were touching one partition start scanning all of them — unless the executor can prune at execution time (which, as we’ve seen, often fails when the parameter is wrapped in a function or cast).</p><p>Setting plan_cache_mode = &#39;force_custom_plan&#39; forces the planner to rebuild a plan for each set of parameter values, preserving planner‑time pruning at the expense of some planning overhead. It’s a sledgehammer, but when a single generic plan causes a 100× scan, it’s the right one.</p><h3>How to actually check — don’t trust assumptions, check the plan</h3><p>Before you declare a partitioning scheme “done,” run this diagnostic checklist:</p><ol><li><strong>EXPLAIN (no </strong><strong>ANALYZE)</strong> on your top queries using literal values. If you don’t see the plan narrow down to one or a few partitions, planner‑time pruning is broken.</li><li><strong>EXPLAIN ANALYZE</strong> on prepared‑statement shapes your app actually sends. Look for the Subplans Removed: N line. If N is zero or missing, your query is scanning all partitions at execution time too.</li><li><strong>Confirm </strong><strong>enable_partition_pruning is </strong><strong>on</strong> (it defaults to on since PostgreSQL 11). You can check with SHOW enable_partition_pruning;. I once found a dev environment where a SET in postgresql.conf had turned it off globally. Hours wasted.</li><li><strong>Inspect partition boundaries</strong> with pg_partition_tree() or pg_partition_ancestors(). Queries like SELECT * FROM pg_partition_tree(&#39;table_name&#39;) can reveal if a partition boundary was created differently than you expected, leading to surprising pruning failures.</li><li><strong>Monitor live query performance</strong> — pg_stat_statements and auto_explain are your friends. When partition pruning breaks, the shared-blocks hit count and total time for a query will spike, often long before your users start screaming. Catch it early.</li></ol><h3>What I do differently now</h3><p>After that 3 a.m. scramble, I realized you can’t rely on manual plan review to catch every regressed query. Partition pruning breaks silently — a schema change, an ORM update, even a new junior developer who thinks DATE_TRUNC is harmless. That’s why I now run automated checks on every deployment that scan pg_stat_statements for queries whose shared-blocks hit count has spiked unexpectedly. If a query suddenly touches 5× more blocks, it’s almost always because partitions are being scanned that shouldn’t be. I’ve built those checks directly into <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=partition-pruning-isn-t-a-switch-you-flip-once">MyDBA</a>, the Postgres monitoring tool I created to keep health checks simple and actionable. It alerts me via Slack long before a pager has a chance to ruin my night.</p><p>Philip McClarence builds <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=partition-pruning-isn-t-a-switch-you-flip-once">MyDBA</a>, a Postgres monitoring and health-check tool — <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=partition-pruning-isn-t-a-switch-you-flip-once">https://mydba.dev/</a></p><p>If your current monitoring can’t spot a partition that stopped pruning, give <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=partition-pruning-isn-t-a-switch-you-flip-once">MyDBA</a> a try. It ships with a pruning health check that would have caught mine.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=75322f465bb5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Postgres DELETE Doesn’t Shrink Your Table]]></title>
            <link>https://medium.com/@philmcc/why-postgres-delete-doesnt-shrink-your-table-dbd084c74f8d?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/dbd084c74f8d</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Tue, 04 Aug 2026 16:01:01 GMT</pubDate>
            <atom:updated>2026-08-04T16:01:01.401Z</atom:updated>
            <content:encoded><![CDATA[<h3>The ticket that started it: “I deleted 40 million rows and nothing happened”</h3><p>It came in at 21:40 on a Tuesday, priority raised twice before it reached me. A developer had run a cleanup on an events table, deleted roughly 40 million rows out of 52 million, watched the DELETE commit, and then checked df -h on the data volume. Same number. Not smaller by a byte. They ran VACUUM ANALYZE events, waited, checked again. Still the same number. By the time it hit my queue the ticket title had the word &quot;corruption&quot; in it.</p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/postgres-free-space-map?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-free-space-map-delete-disk-space">Postgres Free Space Map: Why Deletes Don’t Shrink Tables</a></p><p>Nothing was corrupt. Nothing was even wrong. But the confusion is completely reasonable, because every other storage system most of us touch behaves differently: delete a file, get the space back. Postgres does not work that way, and the reason has a name and a file on disk.</p><p>I walked through the mechanics on the whiteboard in a short video here: <a href="https://www.youtube.com/watch?v=qgS3ZLQZLNM">https://www.youtube.com/watch?v=qgS3ZLQZLNM</a>. This piece goes considerably deeper, with the SQL you can run yourself, the one case where the file genuinely does shrink, and the incident where I was the one who got it wrong.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*nZLCS89xxQekorPr.png" /></figure><p><strong>Target of this article: the postgres free space map</strong>, what it stores, what it lies about on purpose, and how to tell real bloat from imaginary bloat.</p><h3>Where the space actually goes</h3><p>A DELETE in Postgres does not erase anything. It stamps the tuple header’s xmax field with the deleting transaction id and moves on. The row data sits exactly where it was, byte for byte, until VACUUM (or an opportunistic page prune during a later scan of that page) decides no snapshot could still need it and reclaims the line pointer. That is the whole MVCC bargain: readers never block writers because old versions stay put.</p><p>So the heap file does not shrink. What changes is bookkeeping, and that bookkeeping lives in a separate file.</p><p>Every relation has a main data fork, plus two optional auxiliary forks stored as their own files next to it: the Free Space Map (_fsm suffix) and the Visibility Map (_vm suffix). You can size each one independently:</p><pre>SELECT pg_size_pretty(pg_relation_size(&#39;events&#39;,&#39;main&#39;)) AS heap,<br>       pg_size_pretty(pg_relation_size(&#39;events&#39;,&#39;fsm&#39;))  AS fsm,<br>       pg_size_pretty(pg_relation_size(&#39;events&#39;,&#39;vm&#39;))   AS vm;</pre><p>On that 52 million row table the answer was 9.8 GB of heap, 2.5 MB of FSM, 320 kB of VM. If you want the actual paths, pg_relfilenode_relation() and pg_relation_filepath() will map them for you, and you will find 16487, 16487_fsm, 16487_vm sitting in the same directory. Deleting rows doesn&#39;t touch the size of any of them until VACUUM runs and rewrites the FSM to reflect where the newly dead space is.</p><h3>What the free space map really is</h3><p>Here is the part most people never look at, and it explains the behaviour better than any amount of arguing about VACUUM settings.</p><p>The FSM stores <strong>one byte per heap page</strong>. One byte. That gives you a granularity of BLCKSZ/256, which on a standard 8 kB build is 32 bytes. The value is always rounded <em>down</em>, never up, so the FSM can understate available space but never overstate it. That asymmetry is deliberate: an understatement wastes a little space, an overstatement would send an inserter to a page that cannot hold the tuple.</p><p>Those bytes are arranged as a tree. Each FSM page holds a binary tree whose leaves cover roughly 4000 heap pages, and three levels of that structure are enough to address the largest relation Postgres can create. A search for “give me a page with at least 900 bytes free” walks down from the root and lands in effectively constant time — it’s a tree walk, not a scan, which is why FSM lookups feel instant even on huge tables.</p><p>The overhead is negligible. A 1 GB table is 131,072 heap pages, which needs about 33 FSM leaf pages, so a few hundred kilobytes. Call it under 0.1% and stop worrying about it.</p><p>Two properties matter far more than the layout:</p><p><strong>FSM updates are not WAL-logged.</strong> The map is a hint, not truth. After a crash it can be stale or plain wrong, and nothing rolls it back to a known-good state — subsequent VACUUMs correct it lazily. A wrong entry costs one wasted page visit at insert time, never a correctness bug, because the real free space gets checked against the actual page once Postgres arrives there. That cheapness is exactly the point.</p><p><strong>Since PostgreSQL 12, relations under four pages get no FSM at all.</strong> Below that threshold Postgres just checks the pages directly; building a map for four pages would be pure overhead. So if your reproduction uses a tiny table and you see no _fsm file, that is the feature working.</p><blockquote><em>The FSM is approximate on purpose. Precision would cost WAL volume and lock contention to buy you nothing but a slightly tidier page choice.</em></blockquote><h3>The lifecycle: delete, vacuum, FSM, reuse</h3><p>The order matters, and skipping a step is where most misunderstanding lives.</p><ol><li>DELETE stamps xmax on the tuple. The tuple stays on its page. The FSM is unchanged — it still says 0 free bytes there.</li><li>VACUUM (autovacuum or manual) scans the table, confirms no live snapshot can still see the dead tuple, frees the line pointer, and <strong>records the newly freed space in the FSM</strong>. This is the step that actually updates the map.</li><li>A later INSERT asks the FSM for a page with enough free space for the new tuple.</li><li>Postgres checks that candidate page against the table’s fillfactor. A page with 400 bytes free on a table with fillfactor=70 may still get skipped, because the reservation eats the available space. Free space and usable space are not the same number.</li><li>The tuple lands, and the FSM entry for that page is decremented.</li></ol><p>StepHeap fileDead tuplesFSM entryFile sizeAfter DELETE commitsunchangedpresent, xmax setunchanged, still says 0unchangedAfter VACUUMline pointers freed, space compacted within pageremovedupdated with real free spaceunchangedAfter later INSERTsnew tuples land in reclaimed spacen/adecrementedunchanged</p><p>It is VACUUM that writes the FSM. Until VACUUM runs, the space is free in principle and invisible in practice. That is the answer to “I deleted and then vacuumed and nothing happened” as well: something did happen, it just was not visible through df.</p><h3>See it for yourself</h3><p>Twelve lines, no setup beyond a scratch database:</p><pre>CREATE TABLE t (id int, payload text) WITH (fillfactor=100);<br>INSERT INTO t SELECT g, repeat(&#39;x&#39;, 200) FROM generate_series(1,500000) g;<br>SELECT pg_size_pretty(pg_relation_size(&#39;t&#39;));   -- 118 MB<br>DELETE FROM t WHERE id % 10 &lt;&gt; 0;               -- 90%, scattered<br>VACUUM t;<br>SELECT pg_size_pretty(pg_relation_size(&#39;t&#39;));   -- 118 MB, unchanged</pre><p>Now look at what the map thinks:</p><pre>CREATE EXTENSION pg_freespacemap;<br>SELECT count(*), pg_size_pretty(sum(avail))<br>  FROM pg_freespace(&#39;t&#39;) WHERE avail &gt; 0;</pre><p>On my box: 15,152 pages carrying 106 MB of recorded free space, inside a file still reporting 118 MB. The space is there. It is scattered front to back, with nothing at the physical tail to truncate, and it is queued up for the next inserts.</p><p>Worth correcting a mistake I have seen repeated (and made myself on that whiteboard): pg_freespace() comes from the <strong>pg_freespacemap</strong> contrib extension, not pageinspect. Different extension, different purpose — pageinspect shows raw page and index internals, pg_freespacemap exposes exactly what the FSM has recorded.</p><p>Cross-check the FSM’s claim against reality with pgstattuple, which scans the table and reports free_percent and dead_tuple_percent from the actual pages. On very large tables use pgstattuple_approx, which samples instead of scanning exhaustively. When the FSM sum and pgstattuple&#39;s free_percent disagree badly, the FSM is stale and a VACUUM will reconcile them.</p><h3>The one case where the file does shrink</h3><p>Plain VACUUM can return space to the OS, but only by lopping off pages at the physical end of the relation, and only when those trailing pages are entirely empty. Holes in the middle stay.</p><p>Rerun the repro with the deletes aimed at the tail instead:</p><pre>DELETE FROM t WHERE id &gt; 50000;<br>VACUUM t;<br>SELECT pg_size_pretty(pg_relation_size(&#39;t&#39;));   -- 12 MB</pre><p>Same row count deleted, completely different outcome on disk, because the newest rows in an append-heavy, id-ordered table tend to sit physically clustered near the tail.</p><p>Truncation needs an ACCESS EXCLUSIVE lock. VACUUM takes it <em>conditionally</em> and gives up rather than blocking your queries, which is why vacuum truncate table behavior sometimes silently does not happen on a busy table. It also only bothers when the freeable trailing pages exceed a minimum threshold (16 pages, or one sixteenth of the relation, whichever is larger). And you can switch it off entirely with the vacuum_truncate storage parameter or VACUUM (TRUNCATE off). That last one earns its keep on hot standbys, where the exclusive lock replays as a recovery conflict and cancels reporting queries.</p><h3>When bloat is real: the transactions that freeze the horizon</h3><p>Now the part where I was wrong.</p><p>VACUUM cannot mark anything reusable below the oldest xmin the cluster still has to respect. Anything holding that horizon back stops cleanup dead: a long-running analytical query, an idle in transaction session someone left open in a GUI, an unconsumed replication slot, a prepared transaction nobody committed, a standby with hot_standby_feedback = on.</p><p>I once spent a full evening tuning autovacuum thresholds on a 177 GB blob table that had grown 60 GB in three weeks, convinced the scale factor was too lax. It was not. A deployment had shipped an extension whose .so failed to load, and the autovacuum launcher had been erroring out cluster-wide for 19 days. last_autovacuum was null on every table in the database, which I had not checked because I was staring at one table. By the time we caught it, anti-wraparound work was crowding out everything else, WAL volume had tripled, and the nightly base backup had gone from 40 minutes to over three hours.</p><p>The FSM was accurate the entire time — it faithfully recorded that almost nothing was reusable, because almost nothing was.</p><blockquote><em>The FSM was never the problem. The thing that feeds it had stopped running, and nobody was watching for that.</em></blockquote><p>Autovacuum’s default trigger is autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the row count. On a 400 million row table that is 80 million dead rows before anything fires. Lower it per table on your big ones.</p><h3>Reclaiming for real: vacuum full vs pg_repack and the rest</h3><p>MethodLockExtra diskOnline?NotesVACUUM (plain)SHARE UPDATE EXCLUSIVEnoneyestrailing empty pages onlyVACUUM FULLACCESS EXCLUSIVE, whole runfull second copy of table + indexesnoonly when the table won’t grow back and you have a maintenance windowCLUSTERACCESS EXCLUSIVE, whole runfull second copynoalso reorders rows by index; same lock cost as VACUUM FULLpg_repackbrief ACCESS EXCLUSIVE at start and endroughly 2x tableyesneeds PK or UNIQUE on NOT NULL colspg_squeezebrief exclusive at swaproughly 2x tableyessimilar approach, background-worker drivenREINDEX CONCURRENTLYno blocking lockone extra index copyyesPG 12+; for B-tree bloat specificallyTRUNCATEACCESS EXCLUSIVE, briefnonenonew relfilenode, all-or-nothing, instantDROP / DETACH PARTITIONbrief exclusive on partitionnoneyes at table levelbest answer for time-series purges, no rewrite needed</p><p>My opinions, stated plainly. For time-series purges, partitioning is the answer and everything else is a workaround; dropping a partition returns the space instantly with no rewrite. Between vacuum full vs pg_repack, VACUUM FULL is defensible only when the table will genuinely never grow back to its old size and you have a maintenance window, because you are paying an exclusive lock and a full second copy of the data to buy back space that inserts would have reused for free. Telling someone to “just run VACUUM FULL” on a hot production table because df looks big is bad advice, and it has caused more outages in my career than the bloat it was meant to fix.</p><p>Indexes deserve a note: empty B-tree pages are marked deleted and recycled through the index’s own FSM once no snapshot could need them, so index files rarely shrink either. REINDEX CONCURRENTLY is the online fix.</p><h3>What I actually monitor</h3><p>Four numbers, not table size, and I alert on all of them.</p><p>Dead tuple ratio per table from pg_stat_user_tables, weighted by table size so the 200 GB table shouts louder than the 2 MB one. Oldest xmin age and longest running transaction, joined across pg_stat_activity, pg_prepared_xacts, and pg_replication_slots, because that is the single metric that predicts real postgres table bloat, not the FSM. Autovacuum liveness, specifically last_autovacuum going stale across <em>many</em> tables at once, which is the check that would have saved me those 19 days. And the trend of FSM-recorded free space against total table size, cross-checked periodically with pgstattuple_approx, since a table where free space climbs and never gets consumed is telling you the reuse loop is broken.</p><p>I keep those four on a dashboard alongside the usual query-latency panels; MyDBA (<a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-free-space-map-delete-disk-space">https://mydba.dev/</a>) is where I have mine wired up these days.</p><p>The reframing I give every developer who files that ticket: a table holding 106 MB of free space inside a 118 MB file is not sick. It has a warehouse with empty shelves and a working inventory system. Bloat only becomes a problem when the table cannot reuse what it has, and that is a vacuum problem, not a disk problem.</p><p><em>Tags: Programming, Technology, Software Engineering, PostgreSQL, Databases</em> ## Closing thought</p><p>If you take one thing from all this: the file on disk is not lying to you, it is just answering a different question than the one you’re asking. df -h tells you how many bytes the relation occupies. The free space map tells you how many of those bytes are ready to be reused. Both are true at the same time, and confusing the two is what turns a routine cleanup into a 21:40 incident ticket with &quot;corruption&quot; in the title. Watch the four numbers that actually predict trouble — dead tuple ratio, transaction horizon age, autovacuum liveness, and the FSM-versus-reality trend — and the table size graph stops being scary and starts being background noise, which is exactly where it belongs.</p><p><em>Philip McClarence builds MyDBA, a Postgres monitoring and health-check tool — </em><a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-free-space-map-delete-disk-space"><em>https://mydba.dev/</em></a></p><p>If this saved you a late-night ticket, it might be worth pointing MyDBA at your cluster before the next one lands.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dbd084c74f8d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Zero Downtime Postgres Major Upgrade with Logical Replication: The Exact Sequence]]></title>
            <link>https://medium.com/@philmcc/zero-downtime-postgres-major-upgrade-with-logical-replication-the-exact-sequenc-30d4c29ccbec?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/30d4c29ccbec</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Mon, 03 Aug 2026 13:11:33 GMT</pubDate>
            <atom:updated>2026-08-03T13:14:33.306Z</atom:updated>
            <content:encoded><![CDATA[<h3>Why pg_upgrade Wasn’t an Option</h3><p>The client ran a payments ledger on Postgres 12, about 400GB, feeding a reconciliation system with 24/7 ingestion from three time zones. There was no quiet window — I checked. Traffic dips maybe 15% overnight and that’s the extent of it. Their contract required the API to stay up, and there was no asterisk next to that word.</p><p><strong>▶ Watch the video walkthrough:</strong> <a href="https://www.youtube.com/watch?v=nvmx84OZgXo">Zero-Downtime Postgres Major Upgrades with Logical Replicationhttps://www.youtube.com/watch?v=nvmx84OZgXo</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*jWd85seky8RL8-iE.png" /></figure><p>pg_upgrade was the obvious first answer, and I still recommend it to anyone who can actually stop the old cluster for the run. It&#39;s fast, it&#39;s well-tested, and with --link it barely touches disk. The trade for that speed is simple: the old cluster goes quiet while it runs, unless you&#39;re doing an in-place copy — and at this volume, that would have meant a window measured in tens of minutes. No amount of tuning gets around that math. For this client, it was a non-starter.</p><p>So we did it the other way. New cluster on the new major, logical replication from old to new, cut over when lag hit zero, keep the old one warm as a parachute. The whole thing took about three weeks of calendar time and roughly four minutes of actual write pause on the night of the flip.</p><p>I recorded a walkthrough of this pattern on video, and it covers the shape of it well enough. What the video didn’t have room for was the commands, and specifically the three places teams get hurt: sequences, DDL, and the rollback story nobody writes down until they need it. That’s what this is.</p><h3>The Shape of the Migration: Two Clusters, One Truth</h3><p>Here’s the mental model, and if you hold onto nothing else from this article, hold onto this.</p><p>The old cluster is the source of truth for the entire migration, right up until the moment you flip connection strings. The new cluster is a replica — an unusual one, built by streaming logical changes rather than shipping WAL blocks, running a different major version entirely — but conceptually it’s downstream. You read from it, you validate against it, you do not write to it.</p><p>This is exactly why logical replication works as an upgrade path at all. The subscriber can run a different major version than the publisher, and even a different CPU architecture. Physical streaming replication can’t do that; it demands matching majors. Logical decodes changes into a version-independent form and replays them as row operations on the other side. That single property is the whole trick.</p><p>The part people gloss over: replication runs one direction. Old to new. Once you cut over and the application starts writing to the new cluster, those writes never flow back to the old one. If someone writes a row on the new cluster at 10:03am and you decide at 10:04am that the cutover was a mistake, that row exists nowhere else. Your rollback net is real, but it has a hole in it that grows every second after cutover. More on that in Phase 5, because it deserves its own honest conversation.</p><h3>Phase 1 — Stand Up the Target Cluster</h3><p>Provision the new cluster on the target major version. Match the config that matters: shared_buffers, work_mem, max_connections, locale and collation settings, and every extension you actually use, at compatible versions.</p><p>Then restore the schema. Schema only — logical replication is going to do the data copy for you as part of the initial sync, and if you load data first you’re just setting up conflicts.</p><pre>pg_dump --schema-only --no-owner --no-privileges \<br>  -h old-primary -U migrator appdb &gt; schema.sql</pre><pre>psql -h new-primary -U migrator -d appdb -f schema.sql</pre><p>CREATE SUBSCRIPTION does not create tables for you. The subscriber&#39;s schema has to exist already, with column names and types compatible with the publisher&#39;s. Skip this step and go straight to subscribing, and you&#39;ll get an error and a wasted evening.</p><p>Three things that bite here:</p><p><strong>Extension versions.</strong> If the old cluster has PostGIS 3.1 and your new cluster’s package manager gives you 3.4, custom types and operator classes can drift underneath you. Check \dx (or SELECT * FROM pg_extension;) on both and reconcile before you subscribe — especially anything with C code, like pg_stat_statements.</p><p><strong>Custom types and enums.</strong> These come across in --schema-only fine, but if someone has been adding enum values through a migration tool that runs ALTER TYPE ... ADD VALUE at deploy time, you need to know that, because those are DDL, and DDL doesn&#39;t replicate. Custom types need to exist identically on both sides, or the subscription will fail to apply rows that use them.</p><p><strong>Large objects.</strong> If any part of the application uses the large object API — lo_import, lo_export, pg_largeobject — logical replication skips that data entirely. No degraded version of it comes across; none of it does. You need a separate migration path for large objects, and you need to know about it in Phase 1, not Phase 4. Grep the codebase for lo_ calls before you commit to this plan.</p><p>A related trap, and it’s the one that sets up the sequence issue in Phase 4: schema-only dumps bring over sequence <em>objects</em>, but not their current values, and logical replication doesn’t replicate sequence state either — only the row data that happens to contain a value a sequence generated. The restore doesn’t cover this gap. Plan for it now.</p><h3>Phase 2 — Set Up Logical Replication (the gotchas)</h3><p>On the publisher, wal_level has to be logical:</p><pre>ALTER SYSTEM SET wal_level = &#39;logical&#39;;</pre><p>This is a postmaster-context parameter. Setting it requires a restart of the source cluster — a real restart, not a reload. Plan that in advance, because it’s the one bit of downtime in a “zero downtime” migration, and it stings to discover it on the night you wanted to start.</p><p>After the restart, confirm it took:</p><pre>SHOW wal_level;</pre><p>Then create the publication:</p><pre>CREATE PUBLICATION migration_pub FOR ALL TABLES;</pre><p>FOR ALL TABLES is the right default for a whole-cluster upgrade. If you&#39;re migrating a subset, CREATE PUBLICATION also supports FOR TABLE with an explicit list and FOR TABLES IN SCHEMA. On PostgreSQL 15 and later you additionally get row filters (WHERE clauses) and per-table column lists, which are genuinely useful if you&#39;re shrinking the initial sync payload for a huge table. For a straight upgrade I&#39;d leave all that alone. Fewer moving parts.</p><p>On the subscriber:</p><pre>CREATE SUBSCRIPTION migration_sub<br>  CONNECTION &#39;host=old-primary port=5432 dbname=appdb user=repl password=...&#39;<br>  PUBLICATION migration_pub;</pre><p>By default this creates a replication slot on the publisher and performs an initial data synchronization — a COPY of existing table contents — before it switches to streaming ongoing changes. Both behaviors are controllable via the create_slot and copy_data parameters, useful if you&#39;re re-attaching a subscription after an aborted attempt and don&#39;t want to pay for a second full copy.</p><h3>The four gotchas</h3><p><strong>Tables without primary keys.</strong> Published tables need a REPLICA IDENTITY for UPDATE and DELETE to replicate. The default replica identity is the primary key. A table without one needs either a suitable unique index or:</p><pre>ALTER TABLE audit_events REPLICA IDENTITY FULL;</pre><p>FULL works, but it logs every column value for every change, so WAL volume goes up — we found this out on a 40-column audit log table and ended up adding a real unique index instead. Find candidates before you start:</p><pre>SELECT c.relname<br>FROM pg_class c<br>JOIN pg_namespace n ON n.oid = c.relnamespace<br>WHERE c.relkind = &#39;r&#39;<br>  AND n.nspname NOT IN (&#39;pg_catalog&#39;,&#39;information_schema&#39;)<br>  AND c.relreplident = &#39;d&#39;<br>  AND NOT EXISTS (<br>    SELECT 1 FROM pg_index i<br>    WHERE i.indrelid = c.oid AND i.indisprimary<br>  );</pre><p><strong>DDL doesn’t replicate.</strong> Say it out loud at the planning meeting. CREATE TABLE, ALTER TABLE, DROP TABLE and friends are not carried by logical replication. Every schema change during the migration window has to be applied manually to both publisher and subscriber, in the correct order, or the subscription breaks. Related: a newly created table added to the publication is not automatically picked up by an existing subscription. You have to run ALTER SUBSCRIPTION migration_sub REFRESH PUBLICATION; on the subscriber.</p><p><strong>Initial sync on large tables.</strong> The COPY phase takes a snapshot, holds locks, and generates serious I/O. If you have a 2TB table, its initial sync is going to run for hours and compete with your production workload. We staged the big fact tables&#39; initial sync overnight and let small dimension tables sync during the day without much concern. If the table list is lopsided, consider explicit FOR TABLE publications so you can stagger the big ones.</p><p><strong>Slot cleanup if you abort.</strong> This is the one that can take the source cluster down, which is a hell of an outcome for a zero-downtime migration. A logical replication slot retains WAL on the publisher until the subscriber consumes it. Disable the subscription, drop it without cleanup, or let it fall far enough behind, and retained WAL grows without bound until the publisher’s disk fills. DROP SUBSCRIPTION cleans up its own slot; if you dropped the slot manually first, use ALTER SUBSCRIPTION ... DISABLE then DROP SUBSCRIPTION ... (SLOT_NAME = NONE) so the subscriber doesn&#39;t try to drop a slot that&#39;s already gone. Then verify on the publisher:</p><pre>SELECT slot_name, active,<br>       pg_size_pretty(<br>         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)<br>       ) AS retained_wal<br>FROM pg_replication_slots;</pre><p>Anything inactive with a growing retained_wal is a countdown timer. pg_drop_replication_slot(&#39;name&#39;) it.</p><h3>Phase 3 — Monitor, Validate, and Actually Test the New Cluster</h3><p>On the subscriber, pg_stat_subscription tells you where each worker is:</p><pre>SELECT subname, pid, received_lsn,<br>       latest_end_lsn, latest_end_time<br>FROM pg_stat_subscription;</pre><p>On the publisher, pg_stat_replication gives you lag in bytes per connected consumer:</p><pre>SELECT application_name, state,<br>       pg_size_pretty(<br>         pg_wal_lsn_diff(sent_lsn, replay_lsn)<br>       ) AS replay_lag<br>FROM pg_stat_replication;</pre><p>Watch that number for a few days, and watch the trend, not just the current value — a subscription that’s 2MB behind and stable is fine, one that’s climbing is not. You want to know its normal shape: what it does during your nightly batch job, what it does at peak. Start looking at it only on cutover night, and you have no way of judging whether 40MB of lag is normal or a symptom.</p><p>For validation, row counts are a starting point, and a low bar at that:</p><pre>SELECT relname, n_live_tup<br>FROM pg_stat_user_tables<br>ORDER BY relname;</pre><p>Compare across clusters, sure. But n_live_tup is an estimate, and matching counts prove almost nothing about correctness. Go further with checksums on the tables you actually care about — aggregate hashes across a chunk of rows, compared without pulling full tables across the wire:</p><pre>SELECT md5(string_agg(t::text, &#39;|&#39; ORDER BY id))<br>FROM (SELECT * FROM invoices ORDER BY id) t;</pre><p>And then the part I insist on: <strong>run real application queries against the new cluster.</strong> Point a read replica connection, or a shadow-traffic reader, at the new cluster and let it serve genuine query patterns — we ran a reporting service’s reads against the new cluster for four days before cutover. Major versions change planner behavior. A query that ran on an index-only scan on 13 might pick a different plan on 16, and you find that out either during a controlled read test or during the first minute of production traffic. I’ve watched a report query go from 200ms to 90 seconds across a major bump because of a stats change on a partitioned table. Row counts would never have shown it.</p><h3>Phase 4 — The Cutover Sequence</h3><p>Order matters here. This is the runbook.</p><p><strong>1. Freeze DDL.</strong> Not a polite request in a channel — a real lock. Freeze the migration pipeline, disable auto-migrate on deploy, tell the team somewhere they’ll actually read it. Any schema change from this point until post-cutover breaks the subscription or leaves the clusters divergent.</p><p><strong>2. Drain writes.</strong> Either stop the writer services or flip the application to a read-only mode. This is your actual window, and it should be the length of steps 3 through 6 — usually a few minutes.</p><p><strong>3. Wait for lag to hit zero.</strong> On the publisher:</p><pre>SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_behind<br>FROM pg_stat_replication;</pre><p>Zero, exactly, and don’t take “close” for an answer. Query it, confirm it, then wait one more cycle.</p><p><strong>4. Sync sequences — and do it last, after lag is confirmed zero, not before.</strong> Sequences are the part nobody thinks about until they get a duplicate key error in production. Sequence data doesn’t replicate; only the row values a sequence happened to generate come across. So your rows look correct, but the sequence objects on the subscriber still sit at their local starting values. The first insert on the promoted cluster tries nextval = 1 and collides with a row that&#39;s been there for four years. Sync sequences before the initial copy has actually settled, off a stale snapshot of last_value, and you&#39;re buying yourself a duplicate key violation a couple of hours later, once real traffic outpaces the number you picked.</p><p>Generate the setval statements from the source:</p><pre>SELECT format(<br>  &#39;SELECT setval(%L, %s, true);&#39;,<br>  schemaname || &#39;.&#39; || sequencename,<br>  last_value + 1000<br>)<br>FROM pg_sequences<br>WHERE last_value IS NOT NULL;</pre><p>Run that on the publisher, take the output, run it on the subscriber. The + 1000 is deliberate padding. A gap in an ID sequence costs you nothing; an overlap costs you an incident.</p><p><strong>5. Flip connection strings.</strong> Pooler config, DNS, service env vars, whatever your mechanism is. Rehearse this specific step in staging so nobody is editing a HAProxy config by hand at 2am.</p><p><strong>6. Verify writes are landing.</strong> Insert a canary row, read it back, confirm inet_server_addr() on a live application connection points where you think it does. Watch error rates for the next fifteen minutes like you mean it. Then take the application out of read-only.</p><h3>Phase 5 — Keep the Old Cluster as Your Rollback Net</h3><p>Leave the old cluster running — reachable, data intact, nothing torn down. We kept it for two weeks after this cutover; that’s not a rule handed down from anywhere, it’s what felt proportionate to the dataset size and the client’s risk tolerance. I’ve used one to three weeks on other migrations depending on how business-critical the data is, and I’d lean toward the longer end if the workload has monthly batch jobs that haven’t exercised the new cluster yet. Cost is trivial next to the alternative.</p><p>Here’s the honest limitation, and I want it stated plainly because I’ve seen teams assume otherwise. Replication was one-directional. Every write that lands on the new cluster after cutover exists only on the new cluster. Revert to the old one, and those writes are gone unless you replay them by hand from application logs or a WAL-based tool. Rollback at T+5 minutes is nearly free. Rollback at T+6 hours means reconciling six hours of production writes. Rollback at T+3 days stops being a rollback at all — by then you’re running a data recovery project.</p><p>That decay curve is what actually determines your rollback window, not how long you leave the server powered on.</p><p>Bidirectional replication tooling closes the hole, at real cost: conflict resolution policies, more slots, more failure modes, and a setup you have to test as carefully as the migration itself. I reach for it when the write loss would be legally or financially unrecoverable — payments, ledgers, anything with a regulator attached. For a clean cutover with a write freeze measured in minutes, it usually isn’t worth the added complexity. If your cutover window is going to stretch to hours, or the cost of losing even a few minutes of writes is unacceptable, that’s the point where I’d budget for bidirectional setup instead of just accepting the risk window. For most application databases the smarter play is a hard decision deadline: if we haven’t seen a reason to revert within four hours, we’re committed, and we plan forward instead of backward. Ambiguity about whether rollback is still on the table is worse than either answer.</p><h3>What I’d Do Differently Next Time</h3><p>Two things nearly went wrong on this migration, and both were process, not Postgres.</p><p>The DDL freeze got announced in the wrong channel on an earlier migration I ran. Someone shipped a routine index-add migration against the source twenty minutes before cutover, not realizing the freeze was in effect, and the subscription choked on it. We caught it fast because we happened to be watching pg_stat_subscription closely that day — but &quot;we happened to be watching closely&quot; is luck, not a system. Now I put the freeze in the deploy pipeline itself, as a blocking check. Social conventions don&#39;t survive a busy Tuesday.</p><p>The other was sequences, and it was closer than I’d like. On a dry run against a staging copy, I synced sequences before the initial data copy had actually settled, off an earlier snapshot of last_value than the one that mattered, and got a duplicate key violation two hours later against orders_id_seq. The + 1000 padding absorbed a similar close call in production. Sequences are the part nobody thinks about until they get a duplicate key error in production. Now sequence sync is the last thing I do, right after lag hits zero, never before.</p><p>The third lesson isn’t a mistake exactly, more a gap. We were watching lag in a psql window on a laptop, refreshing by hand. That works for the few minutes you’re actively cutting over and is useless for the three weeks of initial sync before it, which is precisely when a stalled apply worker or a quietly growing replication slot will hurt you. Slot-driven WAL growth on the publisher is a slow-motion outage, and nobody notices it manually until the disk alert fires. Steady alerting on pg_stat_subscription lag and pg_replication_slots growth would have flagged problems hours before a manual check did — that&#39;s the kind of gap that&#39;s part of why I ended up building monitoring around exactly this into <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=zero-downtime-postgres-major-upgrade-with-logical-replication-the-exac">MyDBA</a>, after watching it almost bite two different clients the same way.</p><p>The migration itself worked. A few minutes of read-only, no data loss, clients never noticed. But the parts that nearly went sideways had nothing to do with logical replication being hard and everything to do with the boring operational scaffolding around it. Freeze the DDL for real, in a system, not a Slack message. Sync sequences last, with padding. Know exactly how long your rollback is worth something. Run real application queries before cutover — row counts lie by omission, query plans don’t. The rest is just waiting for lag to hit zero.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=30d4c29ccbec" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres Transaction ID Wraparound: Full Recovery Guide]]></title>
            <link>https://medium.com/@philmcc/postgres-transaction-id-wraparound-full-recovery-guide-1fd3b313e74c?source=rss-f715ec56a333------2</link>
            <guid isPermaLink="false">https://medium.com/p/1fd3b313e74c</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Philip McClarence]]></dc:creator>
            <pubDate>Mon, 03 Aug 2026 13:10:16 GMT</pubDate>
            <atom:updated>2026-08-03T13:10:16.769Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Postgres will stop your writes to protect your data. Here’s the 32-bit clock behind it, and the three queries that tell you how much runway you have left.</strong></p><p><strong>📖 Read the full guide:</strong> <a href="https://mydba.dev/blog/postgres-transaction-id-wraparound?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-transaction-id-wraparound-guide">Postgres Transaction ID Wraparound: Causes, Checks, Fix</a></p><h3>The write that wouldn’t commit</h3><p>The first call is always the same shape. The app is up. Health checks are green because health checks run SELECT 1. Dashboards look fine. But every INSERT and every UPDATE comes back with:</p><pre>ERROR: database is not accepting commands to avoid wraparound data loss in database &quot;prod&quot;<br>HINT: Stop the postmaster and vacuum that database in single-user mode.</pre><p>Somebody has already read the HINT out loud on the bridge call and somebody else has already suggested a failover. Both instincts are wrong, and the second one is actively dangerous, because the replica shares the same frozen horizon as the primary. The database isn’t corrupted. It’s doing exactly what it was designed to do.</p><p>I recorded <a href="https://www.youtube.com/watch?v=t9AhvAefBdk">a five-minute whiteboard version of this</a> if you want the shape of it fast. This is the long one: the clock arithmetic, the exact SQL I run in the first ninety seconds, the version-specific thresholds, and a recovery runbook that does not involve single-user mode.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*o3AKiTEO_wzaGcy-.png" /></figure><h3>A 32-bit clock with no hour hand</h3><p>Every write transaction in Postgres gets a 32-bit transaction ID. That is roughly 4.29 billion values, minus three reserved ones: 0 is invalid, 1 is bootstrap, 2 is frozen. Ordinary XIDs start at 3, and when the counter runs out it wraps back around to 3, not to 0.</p><p>Row visibility is decided by comparing the XID stamped in a tuple header against your snapshot. Because the counter is circular, that comparison is done modulo 2³². At any given instant, about 2.1 billion XIDs (2³¹) are treated as being in the past and 2.1 billion in the future.</p><p>That 2³¹ number is your real budget. Not 4 billion. Half the clock face.</p><p>The failure mode is subtle and worth stating precisely: the counter overflowing is harmless by itself. The damage is that a row written 2.1 billion transactions ago stops looking old and starts looking like it was written by a transaction that has not committed yet. Rows do not get corrupted. They vanish from every query. Silently.</p><p><em>(Diagram: a circular clock face, current XID as the hand, the 2³¹ arc behind it labelled “visible past”, the 2³¹ arc ahead labelled “future / invisible”. Old unfrozen rows drift clockwise until they cross into the future.)</em></p><h3>Freezing: how Postgres cheats the clock</h3><p>The escape hatch is freezing. VACUUM marks old row versions as frozen, meaning “unconditionally visible, stop comparing me to the counter.” A frozen row is out of the race permanently.</p><p>Progress is recorded in two places: pg_class.relfrozenxid per relation, and pg_database.datfrozenxid per database, where the database value is effectively the minimum across every relation it contains.</p><p>Here is the part that catches people. Your cluster’s headroom is the minimum across <em>everything</em>: ordinary tables, TOAST tables, materialized views, and the template databases nobody has connected to since install. One 900GB archive table that autovacuum has been quietly failing on for a month holds the entire cluster hostage, including the twelve healthy databases sitting next to it.</p><p>The age() function turns an XID into a distance: the number of transactions between that XID and the current one. age(datfrozenxid) is the standard risk metric, and it is the number you should have been graphing.</p><h3>Four alarms before the fire</h3><p>Postgres does not go from healthy to refusing writes in one step. There are four escalations, and in every incident I have worked, all four fired into a log file nobody was reading.</p><p>SettingDefaultWhat happensvacuum_freeze_min_age50 millionIndividual tuples become eligible for freezingvacuum_freeze_table_age150 millionVACUUM escalates to a whole-table aggressive scan that can advance relfrozenxidautovacuum_freeze_max_age200 millionAnti-wraparound autovacuum is forced for that tablevacuum_failsafe_age1.6 billionFailsafe: cost delays off, index vacuuming skippedautovacuum_multixact_freeze_max_age400 millionSame escalation for MultiXactsvacuum_multixact_failsafe_age1.6 billionMultiXact failsafe</p><p>Two properties of anti-wraparound vacuum surprise people every single time.</p><p>First, it runs even if you have set autovacuum = off. Turning autovacuum off does not turn this off. autovacuum_freeze_max_age can be raised as high as 2 billion, which is a bad idea for reasons that will become obvious in the recovery section.</p><p>Second, it does not politely cancel itself when it blocks somebody. Ordinary autovacuum steps aside for a conflicting lock request. The “to prevent wraparound” variety does not, which is why an ALTER TABLE can sit in a lock queue for six hours and the on-call engineer concludes the database is deadlocked when it is actually saving their job.</p><p>Past the failsafe, the server starts logging escalating warnings counting down remaining transactions, and then refuses to assign new XIDs. On PostgreSQL 14 and later the stop reserve is 3 million XIDs, with warnings starting about 40 million earlier. On older releases those numbers were 1 million and 10 million — a much thinner margin at a few thousand transactions per second. The warning text itself got clearer in PG14 too: it now states explicitly that only commands assigning new XIDs are refused, which matters a great deal when you’re deciding whether to panic.</p><h3>The three queries I run first</h3><p>Database-level headroom, with the percentage that actually matters:</p><pre>SELECT datname,<br>       age(datfrozenxid) AS xid_age,<br>       round(100.0*age(datfrozenxid)/2147483648, 1) AS pct_to_wrap,<br>       2147483648 - age(datfrozenxid) AS xids_left<br>FROM pg_database ORDER BY 2 DESC;</pre><p>Healthy looks like every database under 200 million, because autovacuum is doing its job. Anything over 500 million means the pipeline has been broken for a while.</p><p>Then find the actual culprit, because datfrozenxid is just the minimum and tells you nothing about which relation set it:</p><pre>SELECT c.oid::regclass AS rel, c.relkind,<br>       age(c.relfrozenxid) AS xid_age,<br>       pg_size_pretty(pg_total_relation_size(c.oid)) AS size<br>FROM pg_class c<br>WHERE c.relkind IN (&#39;r&#39;,&#39;m&#39;,&#39;t&#39;) AND c.relfrozenxid &lt;&gt; 0<br>ORDER BY 3 DESC LIMIT 20;</pre><p>Run that in every database, including template1 and postgres. The relkind column matters: t is a TOAST table, and a TOAST table with a huge age usually means its parent is a wide table full of large values that vacuum keeps timing out on. Look at both age and size together — a large, old table is a vacuum-duration problem; a small, old table with a suspiciously round age usually means something is pinning it rather than autovacuum simply not getting to it yet.</p><p>Third, the blocker sweep. If something is pinning the horizon, vacuuming harder will not help, because vacuum cannot freeze rows newer than the oldest xmin in the system:</p><pre>SELECT pid, state, xact_start, age(backend_xmin) AS xmin_age<br>FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY 4 DESC;</pre><pre>SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts ORDER BY 3 DESC;</pre><pre>SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS cat_age<br>FROM pg_replication_slots ORDER BY 3 DESC NULLS LAST;</pre><p>In a healthy cluster the oldest backend_xmin age is seconds to low minutes, pg_prepared_xacts is empty, and every slot is active with a recent xmin. An abandoned logical slot from a decommissioned CDC consumer is the single most common pin I find. Standbys running with hot_standby_feedback on will do it too.</p><h3>The XID burn rate nobody computes</h3><p>An age of 400 million means nothing on its own. On a batch-only reporting cluster that is six months of runway. On a high-throughput OLTP system it is a week.</p><p>Turn it into a countdown. Sample age(datfrozenxid) hourly for your busiest database and take the delta:</p><pre>days_to_limit = (2147483648 - current_age) / xids_per_hour / 24</pre><p>Worked example from a payments cluster I look after: it burns about 3 million XIDs per hour at steady state. From a cold start at zero, 2,147,483,648 / 3,000,000 is roughly 716 hours, just under 30 days to the wall with no vacuum at all — and that’s assuming the burn rate stays flat, which it usually doesn’t during a batch job or a migration. If that database is already sitting at 900 million, the honest number is 417 hours, about 17 days.</p><p>Percentage alerts are almost useless. A page at “80% of wraparound” gives you the same warning on a cluster with three months of runway as one with four days. A cluster sitting at 60% for three months is fine; a cluster that jumped from 20% to 60% in a week is not, even though the raw number looks less alarming. Graph the slope, alert on days-to-limit dropping below 14, and alert again on the <em>rate itself</em> changing, because a sudden jump in XIDs per hour usually means somebody deployed a retry loop wrapping every read in an explicit transaction. MyDBA’s free health check computes exactly this countdown if you would rather not build the collector yourself.</p><blockquote><em>Wraparound is a monitoring failure that finally got loud enough to sound like a database failure.</em></blockquote><h3>War story: 19 days of autovacuum that never ran</h3><p>July this year. A host migration moved a cluster onto new instances and the automation missed one thing: a shared library, postgis-3.so, was not present on the new hosts.</p><p>Every autovacuum worker that tried to connect to the affected databases failed at startup. Not a warning. The worker died. And because the launcher kept dutifully spawning replacements, the process list looked normal from the outside. Autovacuum was “enabled” in every configuration dump anyone checked.</p><p>Autovacuum ran nowhere in that cluster for 19 days.</p><p>The visible symptom was not wraparound. It was storage. One ingest table bloated to roughly 177GB, which inflated full backups, then differentials, then WAL, until the backup object store filled. Repeatedly. We spent the better part of a week treating a backup capacity problem before anyone read far enough back in the server log to find the extension load failures.</p><p>When vacuum came back, it was scheduling by XID age, not by bloat, so the tables screaming loudest about dead tuples were not the ones it picked up first.</p><p>The lesson I now push everywhere: monitor that autovacuum is <em>succeeding</em>, not that it is <em>enabled</em>. Track last_autovacuum per table and alert when a hot table has not been vacuumed in N hours. A worker that dies on connect produces no rows anywhere in pg_stat_user_tables, which means the absence of data is your signal.</p><h3>If you’re already at the wall</h3><p>The order matters.</p><p><strong>1. Find the pinning relations.</strong> Run the relfrozenxid query in every database. You usually have one to three offenders, not fifty.</p><p><strong>2. Clear the blockers first.</strong> Terminate idle-in-transaction sessions holding old xmins. Roll back or commit stale prepared transactions. Drop abandoned replication slots. Vacuuming before you do this wastes hours: vacuum will run to completion and advance relfrozenxid almost not at all.</p><p><strong>3. Then vacuum the offenders directly</strong>, with cost delay off and generous memory:</p><pre>SET vacuum_cost_delay = 0;<br>SET maintenance_work_mem = &#39;4GB&#39;;<br>VACUUM (FREEZE, VERBOSE) schema.big_table;</pre><p>Now the myth. The HINT tells you to stop the postmaster and vacuum in single-user mode. You almost never need to. The stop limit blocks the <em>assignment of new XIDs</em>. It does not block connections, it does not block reads, and VACUUM itself does not consume an XID. A normal superuser connection can do the entire recovery.</p><p>Single-user mode exists for the case where you genuinely cannot start normally or need to bypass a broken shared catalog, and it costs you every bit of parallelism and every concurrent session. On a 300GB table, an aggressive freeze is a multi-hour operation regardless. Doing it with the application offline and no other vacuum running is a choice you should make deliberately, not because a HINT string from 2005 told you to.</p><p>Version matters here. PostgreSQL 15 lets VACUUM set relfrozenxid to the oldest XID actually remaining in the table rather than a conservative cutoff derived from vacuum_freeze_min_age, so ordinary vacuums advance the horizon far more often. PostgreSQL 17 replaced the dead-tuple array with a new memory structure, removing the effective 1GB tracking cap and cutting the number of index passes on very large tables, which materially shortens emergency freeze work. PostgreSQL 18 added autovacuum_worker_slots, so you can raise autovacuum_max_workers with a reload instead of a restart. If you are on 12 or 13 and running big tables, your recovery window is genuinely longer than a colleague&#39;s on 17.</p><h3>The second wraparound nobody warned you about</h3><p>MultiXact IDs are a separate 32-bit counter with their own wraparound risk. They get allocated when multiple transactions hold row-level locks on the same tuple, which in practice means SELECT ... FOR SHARE workloads and foreign-key-heavy schemas where every child insert takes a share lock on the parent row.</p><p>Track it with mxid_age(datminmxid) from pg_database, governed by autovacuum_multixact_freeze_max_age at 400 million by default. There is a second trap: the pg_multixact members storage can fill before the MultiXact ID counter is anywhere near exhausted, which triggers aggressive vacuuming on its own schedule. I have seen a cluster with comfortable XID headroom go into permanent aggressive vacuum because of members pressure alone.</p><p>And no, 64-bit XIDs have not saved you. As of PostgreSQL 18, on-disk transaction IDs in tuple headers are still 32-bit. The xid8 type and pg_current_xact_id() (PostgreSQL 13) give you a wider counter for external tracking, but the on-disk format is unchanged.</p><h3>What I actually alert on</h3><p>Five things, in priority order:</p><p>XID age <strong>trend</strong> with days-to-limit, not a percentage threshold. Oldest backend_xmin age above a few minutes. Oldest replication slot xmin age. Autovacuum last-success per table, so a worker that dies on connect shows up as silence rather than nothing. And a lock-wait alert that specifically matches &quot;to prevent wraparound&quot; vacuums, because that one tells you a scheduled migration is about to sit in a queue for hours and you would rather find out from a page than from a deploy timeout.</p><p>Every wraparound outage I have worked was visible in the numbers for weeks before it became visible in the application. The 32-bit clock is not the problem. Nobody looking at it is.</p><p><strong>Tags:</strong> Programming, Technology, Software Engineering, PostgreSQL, Databases ## Where this leaves you</p><p>None of this requires heroics. Wraparound only becomes an emergency because the warning signs — a climbing age(datfrozenxid), a stalled autovacuum worker, an abandoned replication slot — sit quietly in system catalogs that nobody queries until the writes stop. The fix isn&#39;t a bigger autovacuum_freeze_max_age or a calendar reminder to &quot;check on it sometime.&quot; It&#39;s turning those three queries into a dashboard, graphing the slope instead of the snapshot, and treating &quot;autovacuum is enabled&quot; as a meaningless statement unless you can also prove it&#39;s succeeding. If you want that dashboard without building the collector yourself, <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-transaction-id-wraparound-guide">MyDBA</a> runs this exact set of checks — XID age, burn rate, pinning sessions, dead replication slots — as part of its Postgres health check.</p><p>The clock keeps turning either way. The only choice you get is whether you’re watching it.</p><p>Philip McClarence builds MyDBA, a Postgres monitoring and health-check tool — <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-transaction-id-wraparound-guide">https://mydba.dev/</a></p><p>If this saved you from a 2am wraparound page, the free health check at <a href="https://mydba.dev/?utm_source=medium&amp;utm_medium=platform&amp;utm_campaign=postgres-transaction-id-wraparound-guide">mydba.dev</a> will tell you how many days of runway your cluster actually has left.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1fd3b313e74c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>