51–60 of 87 posts
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.
Earlier quoted context omitted.
> It takes far more work to delete/update than insert. Updating rows of text data is going to be more work, because variable-length text can't be updated in-place. So in terms of allocating space, it's more like a delete plus an insert. That's not surprising. (An in-place update that doesn't touch indices is generally going to be faster than an insert, though.) I'm not aware of instances where a delete is "far more w…
> So in terms of allocating space, it's more like a delete plus an insert. Unless you're using zHeap, you have a narrow Heap-only-Tuples scenario where the indexes stay the same. TOAST kinda helps there, if the update is off the tuple area itself. The original zHeap docs have a lot of detail about why an UNDO log can help with long running transactions from the past etc. That is a postgresql specific thing though. My…
The post on IA - https://web.archive.org/web/20160304013342/https://eng.uber....
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.
I can't believe I'm the first to Rick roll this thread with the most famous XKCD comic of all time: https://xkcd.com/327/
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.
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.
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 tric…
DROP DATABASE, for when a bunch of calls to DROP TABLE seems like too much overhead...
Deletes are Writes and Writes are resource intensive. This is more prominent on databases like Elastic Search.
When I was tasked to delete millions of (old) documents, it overloaded the cluster and almost brought it down. Only scalable solution was to split the index and drop the whole index.