3 ms·

Postgresql's MVCC model (copy complete row on any write, clean up old rows some time in the future) might work decently for read-centric workloads, it's unsuita

Postgresql's MVCC model (copy complete row on any write, clean up old rows some time in the future) might work decently for read-centric workloads, it's unsuitable for write-heavy stuff. We're running a setup that employs postgres for a write-heavy (but far from write-only) application, and are experiencing similarly troubling vacuum jobs. We're considering moving back to good old MySQL because of it. Anyone who's been here before and has some insight?

the_mitsuhiko 11y ago
> Anyone who's been here before and has some insight? Armin from Sentry here. We talked a bit about where we could scale with postgres and part of our problem is that we're quite write and also delete heavy which means our vacuum takes very long. A potential option would be to partition some of our time based tables and make a rolling window view over it. Then we could throw entire tables away after the hold period and our vacuums should be faster at least. We could also move some tables to another database which might help, there however we then need to deal with higher connection counts unfortunately. Downside for us is that we also want to keep the setup straightforward for our on-premise customers so we need to be careful with not introducing unnecessary complexity.
- anarazel 11y ago
  > We talked a bit about where we could scale with postgres and part of our problem is that we're quite write and also delete heavy which means our vacuum takes very long. Is this still a problem after you tuned vacuum to be actually aggressive? It sure isn't perfect but you can go a long way with with just adjusting a couple settings.
  - the_mitsuhiko 11y ago
    > Is this still a problem after you tuned vacuum to be actually aggressive? I need to check how long vacuum takes at the moment, but I think it's in the high hours.
- kuroyi 11y ago
  My tables are partitioned daily and I still have had issues. The problem is they all still need vacuumed but at least the individual table vacuums are shorter and it makes it easier to drop older data.
kuroyi 11y ago
Reducing the autovacuum_vacuum_cost_delay seems to have helped in my case. The autovacuums just run too slow.
- vladsanchez 11y ago
  They run too slow because they're background processes. Table partitioning, maintenance_work_mem tuning along with table-specific vacuum and autovacuum settings will get you a long way.
pramsey 11y ago
Always good to know what is happening under the covers... https://www.pgcon.org/2015/schedule/events/829.en.html https://www.pgcon.org/2015/schedule/events/829.en.html https://www.youtube.com/watch?v=b1fcvkl0ffQ https://www.youtube.com/watch?v=b1fcvkl0ffQ
jandrewrogers 11y ago
This is really a matter of architecting your database correctly. In all the years I used PostgreSQL for write-heavy and delete-heavy loads, I never had a problem with vacuum but I also understood how the database worked internally and how to design my data models to best fit that when performance mattered. Same thing for other database engines. If you can make it fast on MySQL, it just means you are doing it wrong on PostgreSQL. There is not a database in existence that allows you to be oblivious to the underlying organization while still giving good write/delete performance. PostgreSQL is no different in that regard.
- EmielMols 11y ago
  > If you can make it fast on MySQL, it just means you are doing it wrong on PostgreSQL. This is quite the statement and inconsistent with your later point. Postgres never updates data in-place, and certain workloads can never be as fast on Postgres as using a different storage engine such as MySQL/TokuDB.
pornel 11y ago
I've had update-heavy InnoDB table balloon in size, because its purge thread wasn't keeping up. MyISAM didn't have such problem, but it also couldn't handle high update concurrency.

the_mitsuhiko 11y ago

> Anyone who's been here before and has some insight? Armin from Sentry here. We talked a bit about where we could scale with postgres and part of our problem is that we're quite write and also delete heavy which means our vacuum takes very long. A potential option would be to partition some of our time based tables and make a rolling window view over it. Then we could throw entire tables away after the hold period and our vacuums should be faster at least. We could also move some tables to another database which might help, there however we then need to deal with higher connection counts unfortunately. Downside for us is that we also want to keep the setup straightforward for our on-premise customers so we need to be careful with not introducing unnecessary complexity.

anarazel 11y ago
> We talked a bit about where we could scale with postgres and part of our problem is that we're quite write and also delete heavy which means our vacuum takes very long. Is this still a problem after you tuned vacuum to be actually aggressive? It sure isn't perfect but you can go a long way with with just adjusting a couple settings.
- the_mitsuhiko 11y ago
  > Is this still a problem after you tuned vacuum to be actually aggressive? I need to check how long vacuum takes at the moment, but I think it's in the high hours.
kuroyi 11y ago
My tables are partitioned daily and I still have had issues. The problem is they all still need vacuumed but at least the individual table vacuums are shorter and it makes it easier to drop older data.

kuroyi 11y ago

Reducing the autovacuum_vacuum_cost_delay seems to have helped in my case. The autovacuums just run too slow.

vladsanchez 11y ago
They run too slow because they're background processes. Table partitioning, maintenance_work_mem tuning along with table-specific vacuum and autovacuum settings will get you a long way.

pramsey 11y ago

Always good to know what is happening under the covers... https://www.pgcon.org/2015/schedule/events/829.en.html https://www.pgcon.org/2015/schedule/events/829.en.html https://www.youtube.com/watch?v=b1fcvkl0ffQ https://www.youtube.com/watch?v=b1fcvkl0ffQ

jandrewrogers 11y ago

This is really a matter of architecting your database correctly. In all the years I used PostgreSQL for write-heavy and delete-heavy loads, I never had a problem with vacuum but I also understood how the database worked internally and how to design my data models to best fit that when performance mattered. Same thing for other database engines. If you can make it fast on MySQL, it just means you are doing it wrong on PostgreSQL. There is not a database in existence that allows you to be oblivious to the underlying organization while still giving good write/delete performance. PostgreSQL is no different in that regard.

EmielMols 11y ago
> If you can make it fast on MySQL, it just means you are doing it wrong on PostgreSQL. This is quite the statement and inconsistent with your later point. Postgres never updates data in-place, and certain workloads can never be as fast on Postgres as using a different storage engine such as MySQL/TokuDB.

pornel 11y ago

I've had update-heavy InnoDB table balloon in size, because its purge thread wasn't keeping up. MyISAM didn't have such problem, but it also couldn't handle high update concurrency.