It is nice that PG got another storage engine. I hope that will ease the implementation of the column based storage in official PG, as the row based storage is a major problem with very large datasets.
Zheap – Reinvented PostgreSQL Storage
41–50 of 71 posts
Re: Zheap – Reinvented PostgreSQL Storage
#42This should solve one of the problems Uber had with PostgreSQL reported by Christophe Pettus back in 2017[1]. 1- https://thebuild.com/presentations/uber-perconalive-2017.pdf Previous discussion from 2018 https://news.ycombinator.com/item?id=16526623
I am cautiously optimistic that my own recent work on version churn in B-Tree indexes will go a long way towards fixing those problems:
https://postgr.es/m/CAH2-Wz=CEKFa74EScx_hFVshCOn6AA5T-ajFAST...
This can be used without changing anything in the heap. It's less than a thousand lines of C. You could say that it's complementary to zheap, actually.
zheap makes it possible to update the same row many times without requiring new index entries, even when there is a long running transaction that holds back VACUUM. However, it does not avoid the problem of requiring a whole new set of index entries for all indexes in the event of even one indexed column being modified by updates.
Strictly speaking my patch doesn't "fix" that problem, either, but it comes pretty close. It teaches the indexes to "fight back" against version churn caused by updates that cannot use the HOT optimization. It makes non-HOT updates responsible for cleaning up their own mess -- no more negative externalities. In practice this seems to more or less fix the exact thing that the Uber blog post complained about, which was the "write amplification" incurred in all indexes when only one indexed column was changed by an update.
(I am omitting some subtleties here, but that's the general thrust of it.)
Re: Zheap – Reinvented PostgreSQL Storage
#43This seems neat! Last year I helped a friend diagnose an issue they had with Postgres. A database table with a scheduled full DELETE/INSERT had slowed to the point of failure. It turns out, having slightly less IO than needed led the auto-VACUUM process to get further and further behind each time it ran. My friend simply provisioned more IO and moved on. Another option would be to rewrite the process to naturally pro…
Such workflows with scheduled DELETE/INSERT often mean that the data is "derived", there's unlogged tables feature for that in PostgreSQL. Table configured with unlogged are not being written to WAL and thus generate much much less I/O. The downside is that after a crash occurs the table might be empty (before it is repopulated by the DELETE/INSERT workflow again).
Why not use partitioned tables? Simply drop/archive the child tables that you don't need, and avoid the vacuum overhead.
Re: Zheap – Reinvented PostgreSQL Storage
#44Earlier quoted context omitted.
> But this only can happen if replace X with Y and add Z are in the same transaction. If you rollback the transaction, then you start by removing Z, then replacing Y with X. What I'm missing here? Z can be in a different transaction than X and Y. If two transactions run concurrently, one that replaces X with Y and a second one that inserts Z, the above scenario can happen.
No it cant. If they run concurrently, Z has no notion of Y happening. This is the whole point of transactions.
Transactions also have no "notion" of storage implementation details, so your point really isn't relevant to the topic at hand. They operate at a higher level of abstraction.
Re: Zheap – Reinvented PostgreSQL Storage
#45For those unaware, Zheap is a new storage engine for Postgres that handles updates and deletes in a different way. Currently, when you update a row in Postgres, Postgres creates a new copy of the row and marks the old one as deleted. This is done for several reasons, specifically it makes handling concurrency easier and it makes rolling back transactions easier. The issue with this approach is over time this leads to…
Have you stress tested it? How does this end up faring with large scale deployments?
In theory this is much better than what Postgres currently does. Performance depends on autovacuum cleaning up the mess you leave behind, with the amount of dead tuples increasing you also get index bloat which leads to reading much much more data than necessary. I see 3-5x read amplification before vacuum kicks in on update heavy tables (with vacuum tuned a lot).
This is basically what Oracle does with UNDO and it is obviously very scalable. Vacuum on the other hand has its limits, there is only so much you can squeeze out of a single threaded (at table level) process.
What I look most forward to is that it should enable FLASHBACK queries.
Re: Zheap – Reinvented PostgreSQL Storage
#46Earlier quoted context omitted.
> row X that takes up 1kb. I replace it with row Y that takes up 500b. I then write row Z after row Y that takes up 500b. I thought there is a fixed amount of space allocated per row and varying length blobs are stored elsewhere, is my understanding wrong?
Yes. Rows are variable length. Think null values, strings, etc. Only really large values that don't fit into a page are stored elsewhere, and only if they can't be compressed and made to fit. I think it's > 3kb. But I don't remember why that number comes to mind. Pages are 8kb.
2kb by default, configurable per-table with the toast_tuple_target storage parameter if you want more control: https://www.postgresql.org/docs/current/sql-createtable.html
Re: Zheap – Reinvented PostgreSQL Storage
#47Great to see Antonin and others working on this! I did some work on some pieces of this (along with many others), and I gave a talk about an earlier prototype of the undo log machinery and how it fits at PGCon 2019 (which now seems like a whole lifetime ago!). Slides and recording here in case the background is interesting:
https://speakerdeck.com/macdice/transactions-in-postgresql-a...
Re: Zheap – Reinvented PostgreSQL Storage
#48Confusingly, there is another new PostgreSQL storage effort called "Zedstore": https://www.postgresql.org/message-id/CALfoeiuF-m5jg51mJUPm5... Disclosure: I work for VMware, who are sponsoring zedstore development via Greenplum.
Are you working on zedstore? It is one of the postgres developments that I am anticipating the most. cstore_fdw looked pretty cool but it never lived up to the hype and had a lot of not so intuitive limitations. A storage engine built using the new table access APIs seems like the right foundation.
Any word on when you are expecting stability? I'd love to see this in RDS.
Re: Zheap – Reinvented PostgreSQL Storage
#49For those unaware, Zheap is a new storage engine for Postgres that handles updates and deletes in a different way. Currently, when you update a row in Postgres, Postgres creates a new copy of the row and marks the old one as deleted. This is done for several reasons, specifically it makes handling concurrency easier and it makes rolling back transactions easier. The issue with this approach is over time this leads to…
Oof. That sounds super hairy. Don’t you run into all sorts of weirdness with things like bitmaps expecting to find a specific version of a tuple at a specific location in the heap?
This is more or less how MySQL works, hence it's lack of vacuum.