Join the discussion
Write your take first — we'll ask for email only when you're ready to publish.
- Hacker News
- This is directionally correct approach. Deleting a large chunk of rows, in a large table does lead to unpredictable-bad behaviour for a while until those dead tuples are handled.
I have used a very similar strategy by forking repack client https://github.com/reorg/pg_repack/pull/326 This works out of the box with rds/cloudsql etc.
by ankitml - I have been using TRUNCATE allm the way, and is fast as very good.by smithcheck4
- Yep, partitions are the way to go there.by pstuart
- ^ this
been exploring clickhouse and while it is definitely not a general purpose DB, for time-series shaped data that can survive some insert latency, the automatic partition-based TTL is very nice and, at least so far, requires zero attention to maintain
which I guess is solved by `pg_partman` at the bottom of the post
by awinter-py - DROP DATABASE, for when a bunch of calls to DROP TABLE seems like too much overhead...by saltcured
- pg_dropcluster for when a bunch of calls to DROP DATABASE seems like too much overhead.by mike_hock
- CRUD apps don't usually delete in bulk. It's also hard to structure partitions in a way that doesn't wipe out months of important business data -- this is why teams often ETL their DB into Snowflake/ClickHouse and only then drop partitions. That makes it hard for the app to use that data again.
The better approach is either to change your storage engine (e.g. OrioleDB is working on adding the undo log to Pg), or to shard which distributes the vacuum load across multiple servers.
by levkk - They should be performing bulk deletions, due to GDPR: “Data must be stored for the shortest time possible.” Unless you have some kind of rolling cron checking every few minutes (and even then, depending on your scale, that may well be considered bulk), that generally resolves to something like daily or weekly deletions.by sgarland
- TIL about pg_partman
https://github.com/pgpartman/pg_partman/blob/development/doc...
by aftbit - Materialized tables are useful for time-series or sharding-like use-cases. You essentially offload the work to INSERT time to locate the data into relevant buckets/sub-tables that you can DROP later.
We use materialized views for append-only timeseries data for https://lobu.ai and the retention policies define how we DROP the tables so we don't DELETE/UPDATE any rows in the tables.
The long term storage is Iceberg on S3 that's ingested via Postgresql replication, suitable for OLAP use-cases. Postgresql only stores the dimensional OLTP data the users can update and the hot append-only event data.
by buremba - IMO, needing to clear out an entire table is an indicator that something has gone wrong with your design.
Don't get me wrong, I've definitely done it before, but it's in the same bucket as VACUUM for me... high impact interventions used to fix a mistake I made, not "course of business" actions.
by cowthulhu - You should run vacuum as often as possible in Postgres if you’re doing anything other than INSERTs, this is a design tradeoff in Postgres itself. It’s the reason autovacuum exists and why tuning it is so important for performance; nothing wrong with doing a VACUUM ANALYZE after finishing a large DML batch job.by baq
- This generalizes to most (all?) databases. Selective deletion is largely an unsolved problem at scale in databases to the extent it doesn't release the deleted resources. Under the hood databases try to turn this into selective resource truncation, which scales much better, but in most cases that is not possible without careful design of your data model.
Similarly, you often have to remind devs that in many databases an UPDATE is just an INSERT + DELETE, with all of the scaling issues implied.
- RocksDB and other LSM tree backed databases do have cheap deletes and updates, although you could argue that's because they make everything else expensive. If you have spare cores it can be a good trade though.by mike_hearn
- Partially true but too much of a blanket statement and clickbaity.
DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows.
DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statements following it, because of how lock queues work in Postgres. And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app.
by saisrirampur - Separately, this is one of the Postgres autovacuum tuning blog that I've ever read. Have seen it work across many customers and it is also simple to decipher and implement. https://www.citusdata.com/blog/2022/07/28/debugging-postgres...by saisrirampur
In this kind of use case/design, I would assume it would make use of partitions to make this more palatable in which case it would seem that you would bypass this issue of "high concurrent DROP TABLE". Large scale CRUD app just points to recent-ish partitions. Old partitions are either going to be low or on access and can be dropped easily or transformed/transferred into some long term/cold storage.> And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app- The same is true to a lesser extent in MySQL / MariaDB. It does better since it doesn’t do oldest-to-newest tuple chains, but it’s still adding non-trivial work to the DB, much of which is effectively wasted if you don’t care about the visibility of the deleted (or soon-to-be deleted) tuples to other transactions.
I sincerely hope that Planetscale’s efforts succeed long-term to shift devs’ understanding and acceptance of RDBMS operations. Their blog posts and docs are generally quite good. IME, devs (and even ops-ish teams) simply do not care about all of this, and will create elaborate bespoke tooling to run DELETEs in bulk, because they either don’t understand the capabilities of the database, or don’t want to deal with the [minor] increased complexity that a partitioned schema brings, and will happily pay the extra cost / latency for deletions.
by sgarland - mysql/maria also lets you turn off/down the isolation level for queries if you know the guarantees aren't needed, to speed things up. I think postgres does not have that option.by kro
- Only by a weird definition of "scalable". The first sentence says:
> Counterintuitively, large DELETEs add work to the database.
There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc.
And yes, it's a well-known trick for all major relational databases (not just Postgres) that if you want to delete 90% of rows from a large table, it's much faster to just copy the rows you want to keep to a new table, run DROP TABLE on the old table, and rename the new table to the old table. Since DROP TABLE is ~instantaneous, mainly involving table-level metadata.
DELETE scales just fine, in the sense that if you are constantly inserting and deleting individual rows, DELETE scales the same as INSERT.
Basic database functionality is designed around the assumption of lots of small transactions. Whenever you have to do something involving millions of rows at once, you generally need to investigate solutions that work well in "bulk". E.g. loading rows directly from a file rather than with SQL, adding indices only after the data has been loaded rather than before, disabling foreign key checks on large operations (if you know by design that the keys are valid)... and yes, taking advantage of DROP TABLE instead of DELETE. This doesn't mean small transactions aren't scalable, it just means bulk operations are qualitatively different and benefit from their own solutions. And DELETE is no different from INSERT in this regard.
by crazygringo - how does that solution work if the table that is dropped has foreign key constraints?by winterbloom
- >There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it?
Because e.g. DROP also effectively deletes the rows but takes way way less work.
by coldtea - > It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it?
Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?
- Does this “Drop hack” work well with foreign keys, triggers and constraints?by umpalumpaaa
- > if you are constantly inserting and deleting individual rows, DELETE scales the same as INSERT
Technically correct, but for a small table with a high churn rate, the performance characteristics may be surprising in that the "n" in most big-O calculations includes all inserts since the last VACUUM, not the actual number of resident rows.
by Retr0id - > And yes, it's a well-known trick for all major relational databases (not just Postgres) that if you want to delete 90% of rows from a large a table, it's much faster to just copy the rows you want to keep to a new table, run DROP TABLE on the old table, and rename the new table to the old table.
Dumb question but why does the optimizer not just do that in secret then? Seems like something that should be detectable with some heuristics.
by echoangle - Yea, this seems as obvious to me as why it’s more efficient to reformat a drive partition than to delete every file that might be stored there. Or why it’s more efficient to free a whole memory arena than to free every single memory block allocated within it. If you know you’re throwing everything away, it’s more efficient to invoke a “throw it all away” action than to throw away each piece individually.by drob518
- > It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc.
It takes far more work to delete/update than insert. My recent example is updating ~2TB of text data was about 40x slower than inserting 12TB (was trying to correct some large text truncation that occurred during migration into PG, ended up being faster to redo).
by setr