16
ssoheilpro 1 day ago 0 comments
Advertisement
Advertisement
⚡ Community Insights
Discussion Sentiment
50% Positive
Analyzed from 340 words in the discussion.
Trending Topics
#postgresql#mvcc#approach#trade#where#innodb#article#offs#databases#server
Discussion (0 Comments)Read Original on HackerNews
Always hearing about the terrible MVCC in PostgreSQL and yet not hearing that any sort of versioning management is going to be about trade-offs. I spent over a decade managing Oracle databases and now 20 years managing PostgreSQL databases (with spatterings of MSSQL Server along the way). In the kinds of work loads I deal with which are admittedly quite small compared to what many here will deal with: the PostgreSQL MVCC approach is well worth never seeing ORA-01555 like error message.
Naturally, your trade-offs may be different and I completely acknowledge there are workloads where the PostgreSQL trade-offs will be a complete pain in the ass.
As for MS-SQL server? The last serious work I had where I dealt with MS-SQL server operations, the default configuration was not one where MVCC concepts were enabled and thus not in my databases. I'll take either PostgreSQL or Oracle's approach to MVCC over row/page/table locking and lock escalation any day and I can't think of a workload where I'd trade for that MS-SQL approach... hopefully that changed along the way (I simply never really looked back).
But that's a false comparison; it's not a binary decision. As InnoDB demonstrates, you can take the undo log approach without having ORA-01555 style errors.
The article greatly exaggerates the operational pain from InnoDB purge. In practice, with an OLTP workload, substantial InnoDB purge lag problems are much rarer than Postgres XID wraparound problems. Generally in InnoDB they'll only come up if you take a logical dump of an overly-large unsharded data set (solvable by using a read replica instead), or perform slow OLAP-style queries (ditto), or do a massive data migration in a single transaction (solve by breaking it up).
The rollback speed criticism is much more valid, but the impact of that depends on how often you generally need to issue rollbacks of large writes in production. But for most workloads this is rare in my experience: the large production writes should be rare in the first place, and needing to roll them back is rarer still.
Please, folks, write with your own voice. It's good for you and it's good for your readers.