Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Zheap – Reinvented PostgreSQL Storage

cybertec-postgresql.github.io

Post reply on HN
Print view View original

21–30 of 71 posts

Re: Zheap – Reinvented PostgreSQL Storage

#21

Post by magicalhippo »

macdice wrote: post #18
magicalhippo wrote: post #6

Earlier quoted context omitted.

> replace the old row with the new one and write a copy of the old row to a separate file Does it do this in the reverse order? If not, how does it prevent dataloss in case the main table update succeeds but writing to the "old copy" table fails?

Well, conceptually it happens atomically. The change is described in a single WAL (write ahead log) record, and data in the buffers representing the table and undo log isn't allowed to touch the disk until the WAL is on disk, so in any crash/restart scenario that leaves the job half done, we'll simply do it again when we replay all changes since the last checkpoint. A related question is: what happens to the uncommit…

Ah of course, how silly of me to forget about the log. Thank you, that makes perfect sense.

Re: Zheap – Reinvented PostgreSQL Storage

#23

Post by anarazel »

stingraycharles wrote: post #3
malisper wrote: post #2

For 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…

Thanks for this. So I’m fairly familiar how MVCC relates to CoW, and the way that I read this is that it’s kind of the inverse of CoW: it’s an in-place update and a copy of the old data. I can completely imagine the corner cases this ends up touching, as there must be an insane amount of logic in Postgres that assumes immutability of data on disk: if I read data block X for transaction version 1234, it will always re…

> I can completely imagine the corner cases this ends up touching, as there must be an insane amount of logic in Postgres that assumes immutability of data on disk: if I read data block X for transaction version 1234, it will always remain the same.

Most of the code having such low level assumptions has been centralized for PG12, as part of the introduction of pluggable table storage. Also note that that assumption as stated isn't actually true - we don't expect on-disk data to be immutable. There is on-access "pruning" of dead rows, there's VACUUM, there's new row versions on the same page etc.

Re: Zheap – Reinvented PostgreSQL Storage

#24

Post by takeda »

castorp wrote: post #16
malisper wrote: post #2

For 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…

> Zheap does lead to lots of tricky scenarios. If for any reason you need to access the old copy of the row, you have to fetch it from the separate file. If the transaction that performed that update is rolled back, you need to replace the new version of the row with the old version of the row. This sounds straightforward, but gets really tricky really fast. This is pretty much what Oracle is doing.

EnterpriseDB (which started this project) specializes in providing postgresql version that can emulate most of Oracle database functionality, so this is not surprising.

Re: Zheap – Reinvented PostgreSQL Storage

#25

Post by macdice »

stingraycharles wrote: post #3
malisper wrote: post #2

For 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…

Thanks for this. So I’m fairly familiar how MVCC relates to CoW, and the way that I read this is that it’s kind of the inverse of CoW: it’s an in-place update and a copy of the old data. I can completely imagine the corner cases this ends up touching, as there must be an insane amount of logic in Postgres that assumes immutability of data on disk: if I read data block X for transaction version 1234, it will always re…

There are probably some cases where traditional heap would be better that (ideal, finished) zheap. Some ideas: Crash recovery is faster without an UNDO phase. Accessing data that has been modified many times since you took your snapshot may be slower if you have to walk back in time through multiple hops to find the right version of each tuple (compared to the regular heap, which also has to step over lots of dead tuples in that case but can do so sequentially). There may be space management problems when there are too many active transactions interested in a page. There may be microscopic overheads that show up in some workloads that don't benefit from updating in place, perhaps because they are insert/delete-only.

I guess both options would be good to have, and it's interesting that recent SQL Server can do it both ways at the user's option (see "Accelerated Database Recovery", something that sounds conceptually a bit like PostgreSQL vacuum instead of ARIES style rollback where you have to undo changes).

Re: Zheap – Reinvented PostgreSQL Storage

#26

Post by teej »

andreypopp wrote: post #20
teej wrote: post #11

This 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).

UNLOGGED tables is new to me, thanks for sharing.

Re: Zheap – Reinvented PostgreSQL Storage

#27

Post by jasonwatkinspdx »

abhinav22 wrote: post #4
malisper wrote: post #2

For 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…

Thanks for the explanation. In layman terms how much is the benefit from separating the old rows from the database vs the cost of accessing it from a separate file? Just curious why it’s being done now as sounds like a major design decision that would have been considered a long time ago

It's a complex tradeoff. It depends on the specific workload which scheme is better. There's no clear winner. This is why it's being introduced as a pluggable choice vs a wholesale change.

Andy Pavlo's class has a lot of good general information on the topic: https://15721.courses.cs.cmu.edu/spring2020/slides/03-mvcc1....

Re: Zheap – Reinvented PostgreSQL Storage

#28

Post by pbalau »

malisper wrote: post #2

For 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…

> I have 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. If you want to rollback the original transaction, row X will no longer fit in its original spot because row Z is now taking up part of the space it used to occupy.

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?

Re: Zheap – Reinvented PostgreSQL Storage

#29

Post by takeda »

teej wrote: post #17
merb wrote: post #12

Earlier quoted context omitted.

well there is truncate for that. (not mvcc safe)

The table needed to never be visible as empty, so truncate wasn't a short term option.

I solved this by using partitions (at the time it was done through inheritance, where you could make one table made out of several smaller ones), and just dropping a table with unneeded data.

Re: Zheap – Reinvented PostgreSQL Storage

#30

Post by aidos »

malisper wrote: post #2

For 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?
Post reply on HN