3 ms·

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

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.

Tostino 8y ago
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.
- dap 8y ago
  > 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.
  - Tostino 8y ago
    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.
    - dap 8y ago
      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.
- etaioinshrdlu 8y ago
  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.
dap 8y ago
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-vacuum.html 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.]
manigandham 8y ago
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.
- etaioinshrdlu 8y ago
  Excessive old rows have broken production systems for me. Performance degradations can break things!
  - manigandham 8y ago
    That's an operations issue, nothing wrong with the database.
    - etaioinshrdlu 8y ago
      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".
      - jxcl 8y ago
        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 https://www.postgresql.org/docs/11/maintenance.html
paulddraper 8y ago
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.
- barrkel 8y ago
  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/ 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.
mobilemidget 8y ago
And, not unimportant, PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Tostino 8y ago

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.

dap 8y ago
> 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.
- Tostino 8y ago
  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.
  - dap 8y ago
    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.
etaioinshrdlu 8y ago
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.

dap 8y ago

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-vacuum.html 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.]

manigandham 8y ago

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.

etaioinshrdlu 8y ago
Excessive old rows have broken production systems for me. Performance degradations can break things!
- manigandham 8y ago
  That's an operations issue, nothing wrong with the database.
  - etaioinshrdlu 8y ago
    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".
    - jxcl 8y ago
      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 https://www.postgresql.org/docs/11/maintenance.html

paulddraper 8y ago

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.

barrkel 8y ago
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/ 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.

mobilemidget 8y ago

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