3 ms·

That really depends. You're right UPDATEs may be an issue (because we handle them essentially as DELETE+INSERT). Generally speaking, row churn in the table alo

That really depends. You're right UPDATEs may be an issue (because we handle them essentially as DELETE+INSERT). Generally speaking, row churn in the table alone is not an major issue - it's easy to clean up by vacuum, and it will be reused for new data. And you can limit the amount of bloat by tweaking the autovacuum parameters. What's more painful is bloated indexes (e.g. due to UPDATEs that modify indexed columns), because that's much harder / more expensive to get rid of. The thing is - this is part of the MVCC design, and it has some significant advantages too. It's not like the alternative approaches have no downsides.

emddudley 8y ago
In some applications, at least, vacuuming is not sufficient to deal with row churn. I use PostgreSQL in an embedded device. There is a high insertion rate, and eventually when the disk starts to get full I need to get rid of old rows. Using plain DELETE and VACUUM does not work. The deletes aren't fast enough to keep up with the inserts, and vacuuming reduces performance to the point that I have to drop data that is waiting to be inserted. This is on a high performance SSD and I've tuned postgresql.conf. (Bigger/better hardware is not possible in my application). Instead, I think partitions with DROP PARTITION are the only way to handle high volume row churn. Dropping a partition is practically instant and incurs no vacuum penalty.
- pgaddict 8y ago
  Yeah, DROP PARTITION is definitely going to be much more efficient than DELETE + cleanup. No doubt about that. Not sure what postgresql.conf tuning you've tried, but in general we recommend making autovacuum more frequent, but performing the cleanup in smaller chunks. Also, batching inserts usually helps a lot. But maybe you've already tried all that. There's definitely a limit - a balance between ingestion and cleanup.
- panarky 8y ago
  > eventually when the disk starts to get full Don't wait until the disk gets full. Autovacuum works great for most small to moderate sized databases. And it works great for larger databases with a few tweaks.

emddudley 8y ago

In some applications, at least, vacuuming is not sufficient to deal with row churn. I use PostgreSQL in an embedded device. There is a high insertion rate, and eventually when the disk starts to get full I need to get rid of old rows. Using plain DELETE and VACUUM does not work. The deletes aren't fast enough to keep up with the inserts, and vacuuming reduces performance to the point that I have to drop data that is waiting to be inserted. This is on a high performance SSD and I've tuned postgresql.conf. (Bigger/better hardware is not possible in my application). Instead, I think partitions with DROP PARTITION are the only way to handle high volume row churn. Dropping a partition is practically instant and incurs no vacuum penalty.

pgaddict 8y ago
Yeah, DROP PARTITION is definitely going to be much more efficient than DELETE + cleanup. No doubt about that. Not sure what postgresql.conf tuning you've tried, but in general we recommend making autovacuum more frequent, but performing the cleanup in smaller chunks. Also, batching inserts usually helps a lot. But maybe you've already tried all that. There's definitely a limit - a balance between ingestion and cleanup.
panarky 8y ago
> eventually when the disk starts to get full Don't wait until the disk gets full. Autovacuum works great for most small to moderate sized databases. And it works great for larger databases with a few tweaks.