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?
> 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.
> 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.
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.
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.
> 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.
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.
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?