Hacker News Reader

Comment by etaioinshrdlu | Hacker News Reader

You know, PostGres is a great database system, but its core data structure is prone to awful performance degradation if you have a lot of out of date rows, and sometimes the autovacuumer can't keep up.

There is something to be said for simpler B-Tree style databases like MySQL that are harder to screw up!

Also perhaps there could be an optional mode for Postgres that did something to the effect of immediate vacuuming for all modification statements. Collecting garbage as soon as its made. At the cost of slower statements, but no long term degradation. More predictable performance.

View on HN

I'm not sure why this comment is downvoted. The behavior of PostgreSQL on workloads that require vacuuming of large tables -- and especially the default behavior in that case -- is hugely problematic. There's a major project ongoing to build a new storage engine to address this: http://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vac...

Like you, I've long wished I could pay a performance penalty on queries to collect garbage immediately rather than batching up this debt to pay all at once some time later. [edited: I'd pay that cost on all queries, not just writes, if the performance were more consistent over time.]

It's not that simple... The whole point of keeping dead rows around is so the mvcc snapshot can show a consistent view of the database for any user within a transaction. If you instantly cleaned up those dead rows, other users suddenly don't have a consistent view of data, or have data disappear entirely on them.

The other way this is handled is to move dead rows to an undo log, and the min heap only contains the current version of any specific row, and a pointer to where to find the dead rows in the undo log.

The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks.

This is exactly what the zHeap project is doing along with the pluggable storage API.

> The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks.

I think that's consistent the parent's goal (and mine) of allowing ongoing requests to be slower in exchange for not having to pay a major cost later to vacuum.

Yup, I meant for it to be consistent with the OPs concerns. I just wanted to point out that "just paying for it up front" also has other trade-offs. For a lot of use cases, the way PG went ends up better for performance than using an undo log (after all the performance opimizations PG has put in place like freezing pages, etc). And for some use cases an undo log is by far better, and more consistent.

I can see that. I've really only worked with a few workloads where this behavior was quite bad. (In particular, the out-of-the-box policy to vacuum when the dead tuple fraction reaches 20% means that for an INSERT-mostly workload with a bit of UPDATE/DELETE, the vacuums get further and further apart and take longer and longer.) In the worst one I've worked on, the effects take several days or weeks to show up, but they degrade system throughput by 50-80% (!) for days or weeks on end.

The experience makes me wonder if the database is designed primarily using short-term, modest-scale workloads (which are much easier to test) and not behavior that only shows up after several weeks of a continuous, heavy workload. It obviously works for a lot of users.

The trouble is that old rows can stick around many hours after any transaction could ever see them. It's good that it's being addressed.

There's nothing to "screw up" as it's just a different storage system implementation to handle MVCC. There is a project called zheap underway which is designed to do just what you say by doing in-place updates.

It's true though that Postgres still needs work on performance features and optimizations.

Excessive old rows have broken production systems for me. Performance degradations can break things!

That's an operations issue, nothing wrong with the database.

Not everybody is going to use a tool the exact same way, as part of a web stack at a company large enough to have an operations team.

Maybe you're a solo researcher doing data science.

I want my tools to be the best they can be. Always striving to be better. To push the limit and make things simply awesome.

I don't like defeatist attitudes or telling the user they are "holding it wrong".

I reject the idea that all tools can be made simple enough for people to use them without understanding what they're doing.

Yes, of course we all want our tools to the best they can be, but that involves making tradeoffs in features because the maintainers don't have an infinite pool of time.

Postgres is a sufficiently advanced tool that if you're using it for any serious work you need to read the documentation for it. Ideally cover to cover, but at least skim the whole thing and pick out the important parts. The Postgres "Routine Database Maintenance Tasks" documentation [1] describes in great detail everything you need to do to keep your database working well.

[1]: https://www.postgresql.org/docs/11/maintenance.html

So you want PG to be better? We all agree with that, and progress is constantly being made, but nothing is perfect and there are always trade-offs.

Other than that, what is the complaint exactly? You do need to understand how your tools work if you want to use them correctly.

I suppose, the attitude of the PG community. I can think of one other time when the PG community was very dismissive of criticism from users, even when it is valid...

https://news.ycombinator.com/item?id=19119991

In that case they rejected a bugfix for something like 10 years, seemingly because of attitude issues.

Not to defend the community at large to a fault... But there are an amazing amount of bug reports which end up being nothing. Its easy to dismiss something as a non-issue by not understanding the underlying cause, which is why the NFS / fsync issue persisted for so long. To be fair, it was also an issue in other DBs.

Not that it forgives the fact the bug was reported 9 years ago and it just recently got fixed. I know personally I'd have messed it up.

On the other hand, you hardly every have to worry about weird locking problems.

It's nearly impossible to avoid deadlock in any reasonable complex use of MySQL.

If you use read committed, row based replication and explicit locks where necessary, rather than the default of repeatable read, avoiding deadlock isn't too hard.

It's usually the gap locks that bite you, I've found.

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

Oh, and do all your deletes by primary key. Deletes with joins are especially good for creating too many locks.

And, not unimportant, PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Reply on news.ycombinator.com