--- title: Getting rid of a 4 TB table in PostgreSQL | daily.dev description: Dropping a table in PostgreSQL writes almost no WAL itself, but replaying that drop freezes a replica while it unlinks the table's files. A cold 1 TB table... canonical: https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Getting rid of a 4 TB table in PostgreSQL | daily.dev og:description: Dropping a table in PostgreSQL writes almost no WAL itself, but replaying that drop freezes a replica while it unlinks the table's files. A cold 1 TB table... og:url: https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy og:image: https://api.daily.dev/og/posts/vhzX8EYPy.png og:image:alt: Getting rid of a 4 TB table in PostgreSQL og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Getting rid of a 4 TB table in PostgreSQL **[Planet PostgreSQL](https://daily.dev/sources/planet-postgresql)** · 24 min read · 1 upvotes · 0 comments ## Summary Dropping a table in PostgreSQL writes almost no WAL itself, but replaying that drop freezes a replica while it unlinks the table's files. A cold 1 TB table took 16.1 seconds to drop on ext4 versus 80 ms on XFS, over a hundred times faster. On a 192 GB Hetzner table, replay froze for 11 seconds while every other change queued behind it. Batch deleting 90% of a 28-million-row table wrote 8.2 GB of WAL, more than the data removed, and the first read afterward wrote another 7.8 GB repairing hint bits. Copying the surviving 10% into a new table and swapping it in wrote only 882 MB of WAL and took 184 ms to lock and commit. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why does dropping a large table in PostgreSQL freeze replication on my replicas? Replaying the DROP's commit record makes the standby's startup process unlink every file for that table before it can replay anything else, so all replication pauses until the unlink finishes. On a 192 GB table this held replay for about 11 seconds on ext4; a cold 1 TB table took 16.1 seconds on ext4 versus 80 ms on XFS because ext4's allocator has to free far more fragmented extents. _Anyone planning a large table drop can check real benchmark numbers like these on daily.dev before scheduling the maintenance window._ ### Is DELETE or copy-and-swap better for purging most rows from a large PostgreSQL table? Copying the rows you want to keep into a new table and swapping it in writes far less WAL than batch deleting the rows you want to remove. In one test, deleting 90% of a 28-million-row table wrote 8.2 GB of WAL plus another 7.8 GB when the table was first read afterward, while copying the surviving 10% and swapping wrote only 882 MB and completed the lock-to-commit swap in 184 ms. _Developers weighing delete strategies for huge tables can compare these tradeoffs on daily.dev before picking an approach._ ### Why did my PostgreSQL query after a mass DELETE take much longer than normal and generate huge WAL? The first read after a mass delete has to set hint bits and prune dead tuples on every page it touches, and with data checksums on (the PostgreSQL 18 default) that change forces a full 8 KB page image to be logged for each page. On one 192 GB table this first count(*) ran for 41 minutes and wrote 151 GB of WAL, compared to 87 seconds for a repeat run. _Teams debugging unexpected post-delete WAL spikes can dig into cases like this on daily.dev._ ## Similar posts on daily.dev - [The only scalable delete in Postgres is DROP TABLE — PlanetScale](https://daily.dev/posts/the-only-scalable-delete-in-postgres-is-drop-table-planetscale-48j5xsfzx) · Lobsters · 0 upvotes · 0 comments - [Front-end observability at scale: How OpenReplay captures billions of daily events in real time with ClickHouse](https://daily.dev/posts/front-end-observability-at-scale-how-openreplay-captures-billions-of-daily-events-in-real-time-with-yqqchvg5m) · ClickHouse · 1 upvotes · 0 comments - [3 Hours to 7 Minutes: Fixing Aurora PostgreSQL’s Silent CREATE DATABASE Regression](https://daily.dev/posts/3-hours-to-7-minutes-fixing-aurora-postgresql-s-silent-create-database-regression-ea1iig0ij) · Medium · 0 upvotes · 0 comments --- Tags: [#database](https://daily.dev/tags/database), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Getting rid of a 4 TB table in PostgreSQL","url":"https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy"},"datePublished":"2026-10-31T22:05:28.252Z","dateModified":"2026-10-31T22:06:02.937Z","description":"Dropping a table in PostgreSQL writes almost no WAL itself, but replaying that drop freezes a replica while it unlinks the table's files. A cold 1 TB table...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/41e717310a04593053dd6a4687f6b53a?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/41e717310a04593053dd6a4687f6b53a?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Planet PostgreSQL","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Planet PostgreSQL","logo":"https://media.daily.dev/image/upload/logos/placeholder.jpg","url":"https://daily.dev/sources/planet-postgresql"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":1},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"database,postgresql","timeRequired":"PT24M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Planet PostgreSQL","item":"https://daily.dev/sources/planet-postgresql"},{"@type":"ListItem","position":3,"name":"Getting rid of a 4 TB table in PostgreSQL"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/getting-rid-of-a-4-tb-table-in-postgresql-vhzx8eypy#faq","mainEntity":[{"@type":"Question","name":"Why does dropping a large table in PostgreSQL freeze replication on my replicas?","acceptedAnswer":{"@type":"Answer","text":"Replaying the DROP's commit record makes the standby's startup process unlink every file for that table before it can replay anything else, so all replication pauses until the unlink finishes. On a 192 GB table this held replay for about 11 seconds on ext4; a cold 1 TB table took 16.1 seconds on ext4 versus 80 ms on XFS because ext4's allocator has to free far more fragmented extents. Anyone planning a large table drop can check real benchmark numbers like these on daily.dev before scheduling the maintenance window."}},{"@type":"Question","name":"Is DELETE or copy-and-swap better for purging most rows from a large PostgreSQL table?","acceptedAnswer":{"@type":"Answer","text":"Copying the rows you want to keep into a new table and swapping it in writes far less WAL than batch deleting the rows you want to remove. In one test, deleting 90% of a 28-million-row table wrote 8.2 GB of WAL plus another 7.8 GB when the table was first read afterward, while copying the surviving 10% and swapping wrote only 882 MB and completed the lock-to-commit swap in 184 ms. Developers weighing delete strategies for huge tables can compare these tradeoffs on daily.dev before picking an approach."}},{"@type":"Question","name":"Why did my PostgreSQL query after a mass DELETE take much longer than normal and generate huge WAL?","acceptedAnswer":{"@type":"Answer","text":"The first read after a mass delete has to set hint bits and prune dead tuples on every page it touches, and with data checksums on (the PostgreSQL 18 default) that change forces a full 8 KB page image to be logged for each page. On one 192 GB table this first count(*) ran for 41 minutes and wrote 151 GB of WAL, compared to 87 seconds for a repeat run. Teams debugging unexpected post-delete WAL spikes can dig into cases like this on daily.dev."}}]} ```