4 ms·

One thing that Postgres does not do well is row churn. If you have a table that have rows that are constantly added/deleted/updated, Postgres tends to bloat pre

One thing that Postgres does not do well is row churn. If you have a table that have rows that are constantly added/deleted/updated, Postgres tends to bloat pretty quickly because of the way they handle updates. Say what you will about using a relational db for that kind of data, but if you need to, Postgres isn't a great choice.

panarky 8y ago
What databases do you recommend to handle frequent updates? How does their design differ? What are the tradeoffs to consider?
- zzzcpan 8y ago
  LSM-tree, fractal tree, B-epsilon tree or similar ideas based stuff for on disk storage is what solves the problem. Pretty much no tradeoffs compared to classic approaches, these things are just better.
  - dhd415 8y ago
    LSM trees are good for frequent updates on recent data. Frequent updates of old data are much worse on LSM trees since they trigger merges on the oldest (and typically largest) levels of the tree. Some storage engines allow for in-place updates of records if the fields are fixed-length or if the updated variable-length value fits in the existing space. I've used some of those (e.g., MS SQL Server) to good effect in high-update scenarios.
    - zzzcpan 8y ago
      Depends on your implementation of course, but updates shouldn't be triggering merges on the largest levels. They should be accumulated until there is enough of them to merge or there is some other reason to merge things, like scrabbing. Either way, it's still more efficient.
jeltz 8y ago
Only in some cases of churn plus there are plenty of workarounds where you sacrifice some performance and/or usability to avoid the bloat so I would still recommend PostgreSQL since few databases consist only, or almost only, of the bad cases for bloat. I used to work in online gambling where we had plenty of row churn and not much bloat at all without having to use any of the workarounds.
pgaddict 8y ago
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.
pella 8y ago
ZHEAP (maybe in the PG12; Check Pluggable Storage API! ) * http://amitkapila16.blogspot.com/2018/03/zheap-storage-engine-to-provide-better.html http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin... * https://news.ycombinator.com/item?id=16526623 https://news.ycombinator.com/item?id=16526623 * https://github.com/EnterpriseDB/zheap https://github.com/EnterpriseDB/zheap
- twic 8y ago
  That's very interesting. At the moment, PostgreSQL keeps all versions of all tuples in its heap files. Inserts, updates, and deletes all result in addition of new tuples to the heap, and the engine keeps track of which transactions can see which tuples. The vacuum process deletes tuples which are no longer needed, but in the meantime, there is bloat. zheap would keep only the latest version of each tuple in its heap files. When a tuple got updated or deleted, the engine would move the old version into separate storage, the "undo" log. A vacuum process would need to clean up the undo log, but the heap would remain unbloated. This is obviously very practical. But it's a shame that it introduces an asymmetry, where some transactions will be reading tuples from the heap, and some will need to root around in the undo log. I note that this is the approach that Oracle has always used - as explained in this fine article by the same chap who wrote the blog post about zheap above: https://www.enterprisedb.com/blog/databases-different-approaches-mvcc https://www.enterprisedb.com/blog/databases-different-approa...
Fergi 8y ago
We are about to release an open-source extension for PostgreSQL that will help with the bloat problem you mentioned here, at least for continuous updates like high volume aggregation (see: pipelinedb.com). PipelineDB will be refactored as a standard PostgreSQL extension this month. Continuous aggregation can help with the bloat problem for use cases where you only need aggregated / summary data, like realtime reporting & monitoring.

panarky 8y ago

What databases do you recommend to handle frequent updates? How does their design differ? What are the tradeoffs to consider?

zzzcpan 8y ago
LSM-tree, fractal tree, B-epsilon tree or similar ideas based stuff for on disk storage is what solves the problem. Pretty much no tradeoffs compared to classic approaches, these things are just better.
- dhd415 8y ago
  LSM trees are good for frequent updates on recent data. Frequent updates of old data are much worse on LSM trees since they trigger merges on the oldest (and typically largest) levels of the tree. Some storage engines allow for in-place updates of records if the fields are fixed-length or if the updated variable-length value fits in the existing space. I've used some of those (e.g., MS SQL Server) to good effect in high-update scenarios.
  - zzzcpan 8y ago
    Depends on your implementation of course, but updates shouldn't be triggering merges on the largest levels. They should be accumulated until there is enough of them to merge or there is some other reason to merge things, like scrabbing. Either way, it's still more efficient.

jeltz 8y ago

Only in some cases of churn plus there are plenty of workarounds where you sacrifice some performance and/or usability to avoid the bloat so I would still recommend PostgreSQL since few databases consist only, or almost only, of the bad cases for bloat. I used to work in online gambling where we had plenty of row churn and not much bloat at all without having to use any of the workarounds.

pgaddict 8y ago

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.

pella 8y ago

ZHEAP (maybe in the PG12; Check Pluggable Storage API! ) * http://amitkapila16.blogspot.com/2018/03/zheap-storage-engine-to-provide-better.html http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin... * https://news.ycombinator.com/item?id=16526623 https://news.ycombinator.com/item?id=16526623 * https://github.com/EnterpriseDB/zheap https://github.com/EnterpriseDB/zheap

twic 8y ago
That's very interesting. At the moment, PostgreSQL keeps all versions of all tuples in its heap files. Inserts, updates, and deletes all result in addition of new tuples to the heap, and the engine keeps track of which transactions can see which tuples. The vacuum process deletes tuples which are no longer needed, but in the meantime, there is bloat. zheap would keep only the latest version of each tuple in its heap files. When a tuple got updated or deleted, the engine would move the old version into separate storage, the "undo" log. A vacuum process would need to clean up the undo log, but the heap would remain unbloated. This is obviously very practical. But it's a shame that it introduces an asymmetry, where some transactions will be reading tuples from the heap, and some will need to root around in the undo log. I note that this is the approach that Oracle has always used - as explained in this fine article by the same chap who wrote the blog post about zheap above: https://www.enterprisedb.com/blog/databases-different-approaches-mvcc https://www.enterprisedb.com/blog/databases-different-approa...

Fergi 8y ago

We are about to release an open-source extension for PostgreSQL that will help with the bloat problem you mentioned here, at least for continuous updates like high volume aggregation (see: pipelinedb.com). PipelineDB will be refactored as a standard PostgreSQL extension this month. Continuous aggregation can help with the bloat problem for use cases where you only need aggregated / summary data, like realtime reporting & monitoring.