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

51–60 of 71 posts

Re: Zheap – Reinvented PostgreSQL Storage

#51

Post by djur »

cpard wrote: post #50
jacques_chester wrote: post #37

Earlier quoted context omitted.

It's tremendously useful for ETL.

That's super interesting. Can you share some more information on why it's so useful for ETL?

Think of it as a temporary table that doesn't automatically disappear at the end of the session. If nothing but your ETL process cares about the data until it's done being processed, and you still have the original data until the processing is done, in the worst case scenario (the server crashing) you just have to start your ETL process from the start.

Re: Zheap – Reinvented PostgreSQL Storage

#52

Post by willvarfar »

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.

Its also how MySQL works, using overwrite+undo_log rather than multiple row versions in the same table.

Re: Zheap – Reinvented PostgreSQL Storage

#53

Post by andreypopp »

snuxoll wrote: post #35
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).

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…

Yeah, transactional DDL is a good thing, the potential problem with this solution though — if you have views pointing to the old table they won’t be updated with the new table and you will have to deal with them as well.

Re: Zheap – Reinvented PostgreSQL Storage

#54

Post by ComodoHacker »

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

To put it simply (very simply), most of the time you don't need old rows (rollbacks are rare, crashes are rare, etc.). If you store old rows (undo) in a separate place, you don't have to read (and skip) them all the time while reading actual data. That's the main benefit.

Re: Zheap – Reinvented PostgreSQL Storage

#55

Post by throwdbaaway »

> What happens if the new row does not fit in? In this case performance will be worse because zheap essentially has to perform a DELETE / INSERT operation which is of course not as efficient as an in-place UPDATE.

I would be wary of this. Innodb, for example, also has an optimistic (in-place) UPDATE mode, and a pessimistic UPDATE mode.

Repeatedly updating the same row under the pessimistic mode would end up stalling the database, even at a rather low QPS.

https://bugs.mysql.com/bug.php?id=53825 was originally reported by Facebook 10 years ago, and is still not fixed in 8.0.21 with VATS / CATS scheduling.

And then there is also the performance gap between the ideal case where undo log is still in memory, versus the pathological case where undo log needs to be fetched from disk.

The last thing postgres needs is something that looks good on paper / in benchmark, but has a bunch of issues in production.

Re: Zheap – Reinvented PostgreSQL Storage

#56

Post by andreypopp »

sharadov wrote: post #43
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).

Why not use partitioned tables? Simply drop/archive the child tables that you don't need, and avoid the vacuum overhead.

That’s a different thing. In some cases the whole table needs to be replaced. Then inserts into UNLOGGED tables are still much faster than into regular tables (even if there wasn’t a DELETE before).

Re: Zheap – Reinvented PostgreSQL Storage

#57

Post by aidos »

macdice wrote: post #33
aidos wrote: post #30

Earlier quoted context omitted.

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.

Ah gotcha. Thanks for the explanation, makes sense. So I guess you still have transaction ids you need to honour on your way back.

Re: Zheap – Reinvented PostgreSQL Storage

#58

Post by hyperman1 »

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.

I have the same impression. It solves all the autovacuum problems, but has a different set of tradeoffs, e.g. the infamous 'ORA-01555 snapshot too old' when your query tries to read the old version but it has already been cleaned away.

Re: Zheap – Reinvented PostgreSQL Storage

#59

Post by needusername »

takeda wrote: post #24
castorp wrote: post #16

Earlier quoted context omitted.

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

> emulate most of Oracle database functionality

What is the basis for this statement?

* MATCH_RECOGNIZE isn't supported

* reference partitions aren't supported

* TIMESTAMP is incompatible and the default compile options are comically bad

These are the first three things that came to mind and none of them are supported so I stopped looking further.

Post reply on HN