[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_639":640},"routes/blog.author.$slug",{"_9":10,"_11":12,"_13":14,"_5":15},"author",{"_25":53,"_54":55},"blogs",[24],"pagination",{"_16":17,"_18":19,"_20":19,"_21":17,"_22":23},"https://planetscale.com","page",1,"hasPrev",false,"hasNext","totalPages","url","https://planetscale.com/blog/author/tom",{"_25":26,"_27":28,"_29":30,"_31":-7,"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":45},"slug","the-only-scalable-delete","body",[81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116],"body_text","Counterintuitively, large DELETEs add work to the database.\nFrom experience we can plainly claim the following: the most scalable Postgres data-deletion strategies revolve around deleting entire tables.\nIndividual row DELETE is fine at a small scale.However, big batch DELETE operations don't immediately free up physical disk space, add write and replication overhead, and are ultimately not good for large scale row cleanup.\nIf your application needs to delete large amounts of data, even very rarely, we recommend moving towards schema designs that let you express that as a DROP TABLE or a TRUNCATE.\nLet's study why this is by looking at how DELETE works in Postgres.\nDeletes hurt\nWhen rows mutate, Postgres can maintain multiple versions of the same row, so that different transactions can see row values as of the time they were queried. This is Postgres' implementation of \"Multi-Version Concurrency Control\" (MVCC) and a core principle of its design.\nPostgres makes an intentional tradeoff here. It stores modified and deleted rows alongside current ones, relying on transaction IDs and visibility maps to skip over \"dead tuples.\" Later on, a vacuum process comes along and says, \"Hey, these bytes in this heap page are now free, you can overwrite them.\"\n\nDeletes also need to be fully replicated; they are still a work of writes, which means large-scale DELETEs can impact other writers to your application and cause them to wait for the DELETE replication to finish (under synchronous and semi-synchronous replication).\nIt's worth noting here that DELETE or even autovacuum doesn't typically return data to the operating system; they only say \"the space in those pages can be written over.\" This is an intentional choice by Postgres. It optimizes for the case where DELETE workloads are mixed with INSERT ones, and releasing space to the operating system and then asking for it back is relatively expensive and should be avoided. VACUUM FULL allows for this, but takes an expensive lock for a long time.\nAnother related tradeoff Postgres makes is that index data is not touched at all when issuing a DELETE; instead, readers reading the index have to resolve \"is this tuple dead.\" There's also a best-effort optimization where an index scan that finds a dead row can mark the entry as dead itself.\nOverall, DELETE is really \"work added,\" not \"work done.\" If you want more details on Postgres MVCC, see Keeping a Postgres queue healthy.\nIf you're running a DELETE over a large amount of data, you can imagine how it adds work to every read query and autovacuum. Be aware that using foreign keys and CASCADE for deletions can cause a single row delete to delete gigabytes of data, resulting in the same set of problems.\nDrop DELETE for DROP\nIn contrast, DROP TABLE and TRUNCATE require a heavyweight AccessExclusiveLock on the table, but are loosely independent of data size. At the physical layer they remove files from the operating system directly, plus sweep the Postgres buffer cache to remove pages related to the table.\nThat sweep can be less trivial on databases with large shared buffers, but it is only a metadata sweep. Postgres keeps a small fixed-size header (a BufferDesc, padded to 64 bytes) for every 8KB buffer, and dropping a table scans those headers, not the pages themselves. At 64 bytes per 8KB page, that's 1/128th of the cache size: with 128GB of shared buffers, you are sweeping only ~1GB of memory, sequentially, which is very fast on modern hardware.\nDROP TABLE and TRUNCATE scale much better than DELETE. They produce zero dead tuples, zero vacuum debt, zero work for readers. They immediately free up space for the operating system.\nA performant one-off delete\nOne common case where folks need to delete large amounts of data is \"my table is full of junk due to a bug.\" We encountered this recently in an internal observability tool. A bug caused the tool to write millions of rows that we wanted to delete from the database. The bad rows had an old updated_at timestamp; anything with a recent one was designed to be kept. There were only a few hundred thousand rows to keep; most of the data was junk.\nFor this case, especially because \"lock the database for minutes\" was not an issue at all, we performed some surgery, leaning on Postgres' transactional DDL:\nBEGIN\nExplicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE on the table in question; this prevents other transactions from reading or writing the table, so we get consistent data.\nCreate a temporary table to hold just the kept data, like so:CREATE TEMP TABLE temp_keep_big_table AS\n SELECT * FROM big_table\n WHERE updated_at >= '2026-04-01';\n\nTRUNCATE big_table;\nINSERT INTO big_table SELECT * FROM temp_keep_big_table;. In our example, this took a handful of minutes to process on a very small instance with hundreds of thousands of rows.\n\nThis worked very well for a one-off; the only data written to the Write Ahead Log (WAL) are the reinserted rows in the big_table.\nIf holding an AccessExclusiveLock on the table for minutes during TRUNCATE is unacceptable, use a trigger-based approach instead: mirror writes to a new table, then swap with an atomic rename.\nYou should also know that this more advanced maneuver is roughly what the Postgres extension pg_squeeze (a more modern version of pg_repack) does. pg_squeeze is for optimizing tables that already have significant bloat. This blog post is really about preventing bloat in the first place. By structuring your schema to avoid large bulk DELETE, pg_squeeze becomes less necessary.\nIn cases where the data to keep is much larger than the data to discard, but the data to discard is still substantial, the typical approach is to perform many isolated batched deletes in a loop, e.g., 10,000 rows at a time. This keeps transactions short, avoids lock pileups, and lets you pace things so that autovacuum keeps up.\nPostgres partitions for ongoing deletes\nSince version 10, Postgres has had great partitioning support. A \"parent\" table can have \"child\" tables, and queries can be automatically routed to them. Postgres supports a variety of partitioning schemes; one that is extremely useful is date-based partitioning, but many others are available.\nPartitioning can transform a workload that does \"lots of DELETE\" into a workload that does \"occasional DROP TABLE.\" For example, if you have historical data that needs to be aged out, you can have a child partition per day, and a periodic process that deletes older child partitions (or use the pg_partman extension).\n\nYou can go further still. Partitioning in Postgres is recursive, so you could partition the top level by LIST (e.g., a \"visible\" rows partition), then partition the \"not visible\" child table by RANGE to age out old data.\nGo forth and DROP\nStructuring your schema and application so that large-scale DELETE becomes DROP or TRUNCATE can dramatically improve your database.It helps reduce read query latency in some cases, mitigates replication lag spikes, and overall improves database health.","aside","toc",[57,58,59,60,61],"title","The only scalable delete in Postgres is DROP TABLE","authors",[52],"categories",[51],"excerpt","Large DELETEs add work instead of reclaiming it. Structure your database so deletion becomes DROP TABLE or TRUNCATE.","createdAt","2026-06-11","meta",{"_46":47,"_48":41,"_49":50,"_34":35},"canonical","https://planetscale.com/blog/the-only-scalable-delete","description","image","/assets/the-only-scalable-delete-social-DZ748lqJ.png","engineering",{"_25":53,"_54":55,"_56":-7},"tom","name","Tom Pang","x",{"_62":78,"_64":79,"_66":67,"_34":80},{"_62":75,"_64":76,"_66":67,"_34":77},{"_62":72,"_64":73,"_66":67,"_34":74},{"_62":69,"_64":70,"_66":67,"_34":71},{"_62":63,"_64":65,"_66":67,"_34":68},"children",[],"id","go-forth-and-drop","level",2,"Go forth and DROP",[],"postgres-partitions-for-ongoing-deletes","Postgres partitions for ongoing deletes",[],"a-performant-one-off-delete","A performant one-off delete",[],"drop-delete-for-drop","Drop DELETE for DROP",[],"deletes-hurt","Deletes hurt",["SingleFetchClassInstance",630],["SingleFetchClassInstance",626],["SingleFetchClassInstance",611],["SingleFetchClassInstance",598],["SingleFetchClassInstance",589],["SingleFetchClassInstance",581],["SingleFetchClassInstance",577],["SingleFetchClassInstance",573],["SingleFetchClassInstance",549],["SingleFetchClassInstance",535],["SingleFetchClassInstance",509],["SingleFetchClassInstance",500],["SingleFetchClassInstance",485],["SingleFetchClassInstance",470],["SingleFetchClassInstance",462],["SingleFetchClassInstance",444],["SingleFetchClassInstance",434],["SingleFetchClassInstance",416],["SingleFetchClassInstance",408],["SingleFetchClassInstance",398],["SingleFetchClassInstance",394],["SingleFetchClassInstance",366],["SingleFetchClassInstance",358],["SingleFetchClassInstance",332],["SingleFetchClassInstance",308],["SingleFetchClassInstance",298],["SingleFetchClassInstance",283],["SingleFetchClassInstance",252],["SingleFetchClassInstance",248],["SingleFetchClassInstance",240],["SingleFetchClassInstance",236],["SingleFetchClassInstance",215],["SingleFetchClassInstance",178],["SingleFetchClassInstance",162],["SingleFetchClassInstance",146],["SingleFetchClassInstance",117],{"_118":119,"_54":120,"_121":122,"_62":123},"$$mdtype","Tag","p","attributes",{},[124,125,126,127,128,129,130,131,132],"Structuring your schema and application so that large-scale ",["SingleFetchClassInstance",142]," becomes ",["SingleFetchClassInstance",138]," or ",["SingleFetchClassInstance",133]," can dramatically improve your database."," ","It helps reduce read query latency in some cases, mitigates replication lag spikes, and overall improves database health.",{"_118":119,"_54":134,"_121":135,"_62":136},"code",{},[137],"TRUNCATE",{"_118":119,"_54":134,"_121":139,"_62":140},{},[141],"DROP",{"_118":119,"_54":134,"_121":143,"_62":144},{},[145],"DELETE",{"_118":119,"_54":147,"_121":148,"_62":149},"h2",{"_64":65},[150],["SingleFetchClassInstance",151],{"_118":119,"_54":152,"_121":153,"_62":154},"a",{"_160":161},[155,156],"Go forth and ",["SingleFetchClassInstance",157],{"_118":119,"_54":134,"_121":158,"_62":159},{},[141],"href","#go-forth-and-drop",{"_118":119,"_54":120,"_121":163,"_62":164},{},[165,166,167,168,169],"You can go further still. Partitioning in Postgres is recursive, so you could partition the top level by ",["SingleFetchClassInstance",174]," (e.g., a \"visible\" rows partition), then partition the \"not visible\" child table by ",["SingleFetchClassInstance",170]," to age out old data.",{"_118":119,"_54":134,"_121":171,"_62":172},{},[173],"RANGE",{"_118":119,"_54":134,"_121":175,"_62":176},{},[177],"LIST",{"_118":119,"_54":120,"_121":179,"_62":180},{},[181],["SingleFetchClassInstance",182],{"_118":119,"_54":183,"_121":184,"_62":185},"picture",{},[186,187,188],["SingleFetchClassInstance",211],["SingleFetchClassInstance",203],["SingleFetchClassInstance",189],{"_118":119,"_54":190,"_121":191,"_62":192},"img",{"_193":194,"_195":196,"_197":198,"_199":200,"_201":202},[],"alt","Age-based partitioning with Postgres","height",703,"loading","lazy","src","https://planetscale-images.imgix.net/assets/age-based-table-partitions-PRSu2xpR.png?auto=compress%2Cformat","width",1626,{"_118":119,"_54":204,"_121":205,"_62":206},"source",{"_207":208,"_209":210},[],"srcSet","https://planetscale-images.imgix.net/assets/age-based-table-partitions-darkmode-D_FoypLJ.png?auto=compress%2Cformat","media","(prefers-color-scheme: dark)",{"_118":119,"_54":204,"_121":212,"_62":213},{"_207":200,"_209":214},[],"(prefers-color-scheme: light), (prefers-color-scheme: no-preference)",{"_118":119,"_54":120,"_121":216,"_62":217},{},[218,219,220,221,222,223,224],"Partitioning can transform a workload that does \"lots of ",["SingleFetchClassInstance",233],"\" into a workload that does \"occasional ",["SingleFetchClassInstance",229],".\" For example, if you have historical data that needs to be aged out, you can have a child partition per day, and a periodic process that deletes older child partitions (or use the ",["SingleFetchClassInstance",225]," extension).",{"_118":119,"_54":134,"_121":226,"_62":227},{},[228],"pg_partman",{"_118":119,"_54":134,"_121":230,"_62":231},{},[232],"DROP TABLE",{"_118":119,"_54":134,"_121":234,"_62":235},{},[145],{"_118":119,"_54":120,"_121":237,"_62":238},{},[239],"Since version 10, Postgres has had great partitioning support. A \"parent\" table can have \"child\" tables, and queries can be automatically routed to them. Postgres supports a variety of partitioning schemes; one that is extremely useful is date-based partitioning, but many others are available.",{"_118":119,"_54":147,"_121":241,"_62":242},{"_64":70},[243],["SingleFetchClassInstance",244],{"_118":119,"_54":152,"_121":245,"_62":246},{"_160":247},[71],"#postgres-partitions-for-ongoing-deletes",{"_118":119,"_54":120,"_121":249,"_62":250},{},[251],"In cases where the data to keep is much larger than the data to discard, but the data to discard is still substantial, the typical approach is to perform many isolated batched deletes in a loop, e.g., 10,000 rows at a time. This keeps transactions short, avoids lock pileups, and lets you pace things so that autovacuum keeps up.",{"_118":119,"_54":120,"_121":253,"_62":254},{},[255,256,257,258,259,260,261,262,263,264,265],"You should also know that this more advanced maneuver is roughly what the Postgres extension ",["SingleFetchClassInstance",280]," (a more modern version of ",["SingleFetchClassInstance",276],") does. ",["SingleFetchClassInstance",273]," is for optimizing tables that already have significant bloat. This blog post is really about preventing bloat in the first place. By structuring your schema to avoid large bulk ",["SingleFetchClassInstance",270],", ",["SingleFetchClassInstance",266]," becomes less necessary.",{"_118":119,"_54":134,"_121":267,"_62":268},{},[269],"pg_squeeze",{"_118":119,"_54":134,"_121":271,"_62":272},{},[145],{"_118":119,"_54":134,"_121":274,"_62":275},{},[269],{"_118":119,"_54":134,"_121":277,"_62":278},{},[279],"pg_repack",{"_118":119,"_54":134,"_121":281,"_62":282},{},[269],{"_118":119,"_54":120,"_121":284,"_62":285},{},[286,287,288,289,290],"If holding an ",["SingleFetchClassInstance",294]," on the table for minutes during ",["SingleFetchClassInstance",291]," is unacceptable, use a trigger-based approach instead: mirror writes to a new table, then swap with an atomic rename.",{"_118":119,"_54":134,"_121":292,"_62":293},{},[137],{"_118":119,"_54":134,"_121":295,"_62":296},{},[297],"AccessExclusiveLock",{"_118":119,"_54":120,"_121":299,"_62":300},{},[301,302,303],"This worked very well for a one-off; the only data written to the Write Ahead Log (WAL) are the reinserted rows in the ",["SingleFetchClassInstance",304],".",{"_118":119,"_54":134,"_121":305,"_62":306},{},[307],"big_table",{"_118":119,"_54":120,"_121":309,"_62":310},{},[311],["SingleFetchClassInstance",312],{"_118":119,"_54":183,"_121":313,"_62":314},{},[315,316,317],["SingleFetchClassInstance",329],["SingleFetchClassInstance",325],["SingleFetchClassInstance",318],{"_118":119,"_54":190,"_121":319,"_62":320},{"_193":321,"_195":322,"_197":198,"_199":323,"_201":324},[],"Remove rows with a temp table + full table drop",941,"https://planetscale-images.imgix.net/assets/drop-with-temp-table-CEPEDOsv.png?auto=compress%2Cformat",1568,{"_118":119,"_54":204,"_121":326,"_62":327},{"_207":328,"_209":210},[],"https://planetscale-images.imgix.net/assets/drop-with-temp-table-darkmode-BER-MnjZ.png?auto=compress%2Cformat",{"_118":119,"_54":204,"_121":330,"_62":331},{"_207":323,"_209":214},[],{"_118":119,"_54":333,"_121":334,"_62":335},"ol",{"_356":357},[336,337],["SingleFetchClassInstance",348],["SingleFetchClassInstance",338],{"_118":119,"_54":339,"_121":340,"_62":341},"li",{},[342,343],["SingleFetchClassInstance",344],". In our example, this took a handful of minutes to process on a very small instance with hundreds of thousands of rows.",{"_118":119,"_54":134,"_121":345,"_62":346},{},[347],"INSERT INTO big_table SELECT * FROM temp_keep_big_table;",{"_118":119,"_54":339,"_121":349,"_62":350},{},[351],["SingleFetchClassInstance",352],{"_118":119,"_54":134,"_121":353,"_62":354},{},[355],"TRUNCATE big_table;","start",4,{"_118":119,"_54":359,"_121":360,"_62":361},"CodeBlock",{"_362":363,"_364":365},[],"html","

CREATE TEMP TABLE temp_keep_big_table AS\n  SELECT * FROM big_table\n  WHERE updated_at >= '2026-04-01';\n

","language","sql",{"_118":119,"_54":333,"_121":367,"_62":368},{},[369,370,371],["SingleFetchClassInstance",386],["SingleFetchClassInstance",376],["SingleFetchClassInstance",372],{"_118":119,"_54":339,"_121":373,"_62":374},{},[375],"Create a temporary table to hold just the kept data, like so:",{"_118":119,"_54":339,"_121":377,"_62":378},{},[379,380,381],"Explicit ",["SingleFetchClassInstance",382]," on the table in question; this prevents other transactions from reading or writing the table, so we get consistent data.",{"_118":119,"_54":134,"_121":383,"_62":384},{},[385],"LOCK TABLE ... IN ACCESS EXCLUSIVE MODE",{"_118":119,"_54":339,"_121":387,"_62":388},{},[389],["SingleFetchClassInstance",390],{"_118":119,"_54":134,"_121":391,"_62":392},{},[393],"BEGIN",{"_118":119,"_54":120,"_121":395,"_62":396},{},[397],"For this case, especially because \"lock the database for minutes\" was not an issue at all, we performed some surgery, leaning on Postgres' transactional DDL:",{"_118":119,"_54":120,"_121":399,"_62":400},{},[401,402,403],"One common case where folks need to delete large amounts of data is \"my table is full of junk due to a bug.\" We encountered this recently in an internal observability tool. A bug caused the tool to write millions of rows that we wanted to delete from the database. The bad rows had an old ",["SingleFetchClassInstance",404]," timestamp; anything with a recent one was designed to be kept. There were only a few hundred thousand rows to keep; most of the data was junk.",{"_118":119,"_54":134,"_121":405,"_62":406},{},[407],"updated_at",{"_118":119,"_54":147,"_121":409,"_62":410},{"_64":73},[411],["SingleFetchClassInstance",412],{"_118":119,"_54":152,"_121":413,"_62":414},{"_160":415},[74],"#a-performant-one-off-delete",{"_118":119,"_54":120,"_121":417,"_62":418},{},[419,420,421,422,423,424],["SingleFetchClassInstance",431]," and ",["SingleFetchClassInstance",428]," scale much better than ",["SingleFetchClassInstance",425],". They produce zero dead tuples, zero vacuum debt, zero work for readers. They immediately free up space for the operating system.",{"_118":119,"_54":134,"_121":426,"_62":427},{},[145],{"_118":119,"_54":134,"_121":429,"_62":430},{},[137],{"_118":119,"_54":134,"_121":432,"_62":433},{},[232],{"_118":119,"_54":120,"_121":435,"_62":436},{},[437,438,439],"That sweep can be less trivial on databases with large shared buffers, but it is only a metadata sweep. Postgres keeps a small fixed-size header (a ",["SingleFetchClassInstance",440],", padded to 64 bytes) for every 8KB buffer, and dropping a table scans those headers, not the pages themselves. At 64 bytes per 8KB page, that's 1/128th of the cache size: with 128GB of shared buffers, you are sweeping only ~1GB of memory, sequentially, which is very fast on modern hardware.",{"_118":119,"_54":134,"_121":441,"_62":442},{},[443],"BufferDesc",{"_118":119,"_54":120,"_121":445,"_62":446},{},[447,448,420,449,450,451,452],"In contrast, ",["SingleFetchClassInstance",459],["SingleFetchClassInstance",456]," require a heavyweight ",["SingleFetchClassInstance",453]," on the table, but are loosely independent of data size. At the physical layer they remove files from the operating system directly, plus sweep the Postgres buffer cache to remove pages related to the table.",{"_118":119,"_54":134,"_121":454,"_62":455},{},[297],{"_118":119,"_54":134,"_121":457,"_62":458},{},[137],{"_118":119,"_54":134,"_121":460,"_62":461},{},[232],{"_118":119,"_54":147,"_121":463,"_62":464},{"_64":76},[465],["SingleFetchClassInstance",466],{"_118":119,"_54":152,"_121":467,"_62":468},{"_160":469},[77],"#drop-delete-for-drop",{"_118":119,"_54":120,"_121":471,"_62":472},{},[473,474,475,476,477],"If you're running a ",["SingleFetchClassInstance",482]," over a large amount of data, you can imagine how it adds work to every read query and autovacuum. Be aware that using foreign keys and ",["SingleFetchClassInstance",478]," for deletions can cause a single row delete to delete gigabytes of data, resulting in the same set of problems.",{"_118":119,"_54":134,"_121":479,"_62":480},{},[481],"CASCADE",{"_118":119,"_54":134,"_121":483,"_62":484},{},[145],{"_118":119,"_54":120,"_121":486,"_62":487},{},[488,489,490,491,303],"Overall, ",["SingleFetchClassInstance",497]," is really \"work added,\" not \"work done.\" If you want more details on Postgres MVCC, see ",["SingleFetchClassInstance",492],{"_118":119,"_54":152,"_121":493,"_62":494},{"_160":496},[495],"Keeping a Postgres queue healthy","/blog/keeping-a-postgres-queue-healthy",{"_118":119,"_54":134,"_121":498,"_62":499},{},[145],{"_118":119,"_54":120,"_121":501,"_62":502},{},[503,504,505],"Another related tradeoff Postgres makes is that index data is not touched at all when issuing a ",["SingleFetchClassInstance",506],"; instead, readers reading the index have to resolve \"is this tuple dead.\" There's also a best-effort optimization where an index scan that finds a dead row can mark the entry as dead itself.",{"_118":119,"_54":134,"_121":507,"_62":508},{},[145],{"_118":119,"_54":120,"_121":510,"_62":511},{},[512,513,514,515,516,517,518,519,520],"It's worth noting here that ",["SingleFetchClassInstance",532]," or even autovacuum doesn't typically return data to the operating system; they only say \"the space in those pages can be written over.\" This is an intentional choice by Postgres. It optimizes for the case where ",["SingleFetchClassInstance",529]," workloads are mixed with ",["SingleFetchClassInstance",525]," ones, and releasing space to the operating system and then asking for it back is relatively expensive and should be avoided. ",["SingleFetchClassInstance",521]," allows for this, but takes an expensive lock for a long time.",{"_118":119,"_54":134,"_121":522,"_62":523},{},[524],"VACUUM FULL",{"_118":119,"_54":134,"_121":526,"_62":527},{},[528],"INSERT",{"_118":119,"_54":134,"_121":530,"_62":531},{},[145],{"_118":119,"_54":134,"_121":533,"_62":534},{},[145],{"_118":119,"_54":120,"_121":536,"_62":537},{},[538,539,540,541,542],"Deletes also need to be fully replicated; they are still a work of writes, which means large-scale ",["SingleFetchClassInstance",546],"s can impact other writers to your application and cause them to wait for the ",["SingleFetchClassInstance",543]," replication to finish (under synchronous and semi-synchronous replication).",{"_118":119,"_54":134,"_121":544,"_62":545},{},[145],{"_118":119,"_54":134,"_121":547,"_62":548},{},[145],{"_118":119,"_54":120,"_121":550,"_62":551},{},[552],["SingleFetchClassInstance",553],{"_118":119,"_54":183,"_121":554,"_62":555},{},[556,557,558],["SingleFetchClassInstance",570],["SingleFetchClassInstance",566],["SingleFetchClassInstance",559],{"_118":119,"_54":190,"_121":560,"_62":561},{"_193":562,"_195":563,"_197":198,"_199":564,"_201":565},[],"Postgres table update and delete",1692,"https://planetscale-images.imgix.net/assets/postgres-table-update-delete-C_kRTY7t.png?auto=compress%2Cformat",1504,{"_118":119,"_54":204,"_121":567,"_62":568},{"_207":569,"_209":210},[],"https://planetscale-images.imgix.net/assets/postgres-table-update-delete-darkmode-9qQ2iRur.png?auto=compress%2Cformat",{"_118":119,"_54":204,"_121":571,"_62":572},{"_207":564,"_209":214},[],{"_118":119,"_54":120,"_121":574,"_62":575},{},[576],"Postgres makes an intentional tradeoff here. It stores modified and deleted rows alongside current ones, relying on transaction IDs and visibility maps to skip over \"dead tuples.\" Later on, a vacuum process comes along and says, \"Hey, these bytes in this heap page are now free, you can overwrite them.\"",{"_118":119,"_54":120,"_121":578,"_62":579},{},[580],"When rows mutate, Postgres can maintain multiple versions of the same row, so that different transactions can see row values as of the time they were queried. This is Postgres' implementation of \"Multi-Version Concurrency Control\" (MVCC) and a core principle of its design.",{"_118":119,"_54":147,"_121":582,"_62":583},{"_64":79},[584],["SingleFetchClassInstance",585],{"_118":119,"_54":152,"_121":586,"_62":587},{"_160":588},[80],"#deletes-hurt",{"_118":119,"_54":120,"_121":590,"_62":591},{},[592,593,594],"Let's study why this is by looking at how ",["SingleFetchClassInstance",595]," works in Postgres.",{"_118":119,"_54":134,"_121":596,"_62":597},{},[145],{"_118":119,"_54":120,"_121":599,"_62":600},{},[601,602,603,604,303],"If your application needs to delete large amounts of data, even very rarely, we recommend moving towards schema designs that let you express that as a ",["SingleFetchClassInstance",608]," or a ",["SingleFetchClassInstance",605],{"_118":119,"_54":134,"_121":606,"_62":607},{},[137],{"_118":119,"_54":134,"_121":609,"_62":610},{},[232],{"_118":119,"_54":120,"_121":612,"_62":613},{},[614,615,616,131,617,618,619],"Individual row ",["SingleFetchClassInstance",623]," is fine at a small scale.","However, big batch ",["SingleFetchClassInstance",620]," operations don't immediately free up physical disk space, add write and replication overhead, and are ultimately not good for large scale row cleanup.",{"_118":119,"_54":134,"_121":621,"_62":622},{},[145],{"_118":119,"_54":134,"_121":624,"_62":625},{},[145],{"_118":119,"_54":120,"_121":627,"_62":628},{},[629],"From experience we can plainly claim the following: the most scalable Postgres data-deletion strategies revolve around deleting entire tables.",{"_118":119,"_54":120,"_121":631,"_62":632},{},[633,634,635],"Counterintuitively, large ",["SingleFetchClassInstance",636],"s add work to the database.",{"_118":119,"_54":134,"_121":637,"_62":638},{},[145],"current",{"_641":19,"_642":643,"_644":19},"development","env",{"_645":646,"_647":648,"_649":650,"_651":652,"_653":654},"userSignedIn","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","RELEASE","0bef80fc15730264368760b9675859e1ca4923cc","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032"]

Posts by Tom Pang

The only scalable delete in Postgres is DROP TABLE