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

31–40 of 71 posts

Re: Zheap – Reinvented PostgreSQL Storage

#31

Post by jasonwatkinspdx »

magicalhippo wrote: post #21
macdice wrote: post #18

Earlier quoted context omitted.

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.

It's more complex than the above. The key constraint is actually that the transaction is not allowed to return as committed until the WAL is stable on disk. Whether the writes it did to various pages are allowed before that point or held until after is an implementation choice, and different databases make different choices. It gets complex fast, but the short summary is that the recovery protocols understand how to reconstruct the state after the fact, redo what needs to be redone, and undo what needs to be undone.

Re: Zheap – Reinvented PostgreSQL Storage

#32

Post by malisper »

pbalau wrote: post #28
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 s…

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

Re: Zheap – Reinvented PostgreSQL Storage

#33

Post by macdice »

aidos wrote: post #30
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?

Nope. Any time you are looking at a tuple in the existing heap, you have to test it for visibility against your snapshot. With zheap, it's much the same, except that, rather than just "yes or no", the answer could be that you have to following an update chain into the undo log to get an older version.

Re: Zheap – Reinvented PostgreSQL Storage

#34

Post by sixdimensional »

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…

This sounds like temporal tables, does it not - except deep inside the storage engine?? The historical copy goes to a separate table, the current row is in the current table.

I wonder if there is a logical overlap in the ideas between temporal table handling and this low level storage engine optimization.

Re: Zheap – Reinvented PostgreSQL Storage

#35

Post by snuxoll »

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

It should be an atomic replace - create a new table, do your inserts, swap the new table in. This way you aren’t left without the data during the rebuild, and you are guaranteed a consistent state since DDL is transactional in PostgreSQL.

Make the new table unlogged while it’s being populated as well for that performance improvement if you want, since the atomic replace guarantees your consistency anyway.

Alternative if it’s not a full rebuild, move from a delete/insert to using INSERT .. ON CONFLICT DO UPDATE where possible. I do this for cache tables that store materialized summary views that are updated by pgAgent jobs.

If you are deleting “old” data out then maybe use partitioned tables so you can just DROP the table containing the data you want gone.

Re: Zheap – Reinvented PostgreSQL Storage

#36

Post by jacques_chester »

sixdimensional wrote: post #34
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…

This sounds like temporal tables, does it not - except deep inside the storage engine?? The historical copy goes to a separate table, the current row is in the current table. I wonder if there is a logical overlap in the ideas between temporal table handling and this low level storage engine optimization.

PostgreSQL used to have a limited form of unitemporalism by exploiting its MVCC implementation. You could retrieve unvacuumed rows through some additional magic.

For true bitemporalism you need more than current-row/historical-row separations, though.

Re: Zheap – Reinvented PostgreSQL Storage

#37

Post by jacques_chester »

teej wrote: post #26
andreypopp wrote: post #20

Earlier quoted context omitted.

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.

It's tremendously useful for ETL.

Re: Zheap – Reinvented PostgreSQL Storage

#38

Post by atombender »

sixdimensional wrote: post #34
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…

This sounds like temporal tables, does it not - except deep inside the storage engine?? The historical copy goes to a separate table, the current row is in the current table. I wonder if there is a logical overlap in the ideas between temporal table handling and this low level storage engine optimization.

Indeed, Postgres used to have "time travel" [1] — allowing you to view the table as it was a given point in time. It was removed in 6.2, though some of

Of course, support doesn't require Postgres-style MVCC. Oracle has time travel, which it calls Flashback ("SELECT AS OF"), even though it uses a different type of concurrency control.

[1] https://www.postgresql.org/docs/6.3/c0503.htm

Re: Zheap – Reinvented PostgreSQL Storage

#40

Post by pbalau »

malisper wrote: post #32
pbalau wrote: post #28

Earlier quoted context omitted.

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

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