31–40 of 148 posts
Clever Clickbait - Of course at the end of the article they offer a solution - their product (and of course it’s AI enhanced) to the problem they have overhyped.
> and of course it’s AI enhanced did they mention LLM/ChatGPT?..
This post has a valid point. But the last line makes it clear why they care so much about it. Yeah, table bloat and transaction ID wraparounds are terrible, but easily avoidable if you follow a few simple guidelines. Typically in my experience, best way to avoid these issues are to set sensible vacuum settings and track long running queries. I do hate the some of the defaults in the Postgres configuration are too con…
Oh man, a previous company I worked at had an issue with a hot table (frequent reads + writes) interfering with autovacuum. Many fires over a six month period arose from all of that. I was (luckily) only on an adjacent team, so I don't know the details, other than vacuums taking over 24 hours! I'm sure it could have been prevented, but it seemed horrible to debug
Earlier quoted context omitted.
Legacy reasons. The idea was that you wouldn't need a WAL because the table itself is the log. And then you could support time-travel queries if you never cleaned up the expired tuples.
Is MVCC actually superior by some other considerations? Less lock contentions, transactional DML.
MVCC for Amazon Redshift; (pdf) https://www.redshiftresearchproject.org/white_papers/downloa... (html) https://www.redshiftresearchproject.org/white_papers/downloa... I've been told, very kindly, by a couple of people that it's the best explanation they've ever seen. I'd like to get more eyes on it, to pick up any mistakes, and it might be useful in and of itself anyway to reader, as MVCC on Redshift is I believe the…
This paper, at least by my skimming, seems to describe Redshift's historic SERIALIZABLE ISOLATION level, but does not mention Redshift's newer SNAPSHOT ISOLATION capability. https://aws.amazon.com/about-aws/whats-new/2022/05/amazon-re... For concurrency scalability, AWS now configures SNAPSHOT ISOLATION by default if you use Redshift Serverless but non-serverless still defaults to SERIALIZABLE ISOLATION.
Earlier quoted context omitted.
Is MVCC actually superior by some other considerations? Less lock contentions, transactional DML.
The problem is not mvcc but postgres’ implementation details of it.
Clever Clickbait - Of course at the end of the article they offer a solution - their product (and of course it’s AI enhanced) to the problem they have overhyped.
So how does one work around PostgreSQL’s quirks? Well, you can spend an enormous amount of time and effort tuning it yourself. Good luck with that.
On the one hand, this does make the model Postgres uses admirably simple: the WAL is all "REDO," and the heap is all you need to accomplish any kind of read, but at the expense that stuff that normally would be copied off to a sequential UNDO log and then vaporized when the transaction commits and all possible readers have exited remains comingled with everything else in the main database heap, needing to be fished out again by VACUUM for purging and figuring out how to reclaim numerical space for more transactions.
There may be other solutions to this, but it's one unusual quality Postgres has relative to other MVCC databases, many of which sport an UNDO log.
There are downsides to UNDO, however: if a read needs an old copy of the tuple, it needs to fish around in UNDO, all the indices and synchronization need to account for this, and if there's a rollback or crash recovery event (i.e. mass-rollback of all transactions open at the time), everything has to be shuffled back into the main database storage. Hence the memorable initial comment: "Postgres is optimized for rollback."
> Another problem with the autovacuum in PostgreSQL is that it may get blocked by long-running transactions, which can result in the accumulation of more dead tuples and stale statistics. Failing to clean expired versions in a timely manner leads to numerous performance problems, causing more long-running transactions that block the autovacuum process. It becomes a vicious cycle, requiring humans to intervene manuall…
Last I checked (....a few years ago, so things may have changed,) the theory of autovacuum heuristics may not have changed much since the turn of the millennium, they're probably about due.