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

61–70 of 71 posts

Re: Zheap – Reinvented PostgreSQL Storage

#61

Post by jeltz »

simcop2387 wrote: post #10
stingraycharles wrote: post #3

Earlier quoted context omitted.

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…

the cow approach likely plays better with flash based storage since rewrites happen less often. so if you've got a lot of otherwise extra storage then maybe it's a good idea. though if you run on top of something like f2fs maybe it'll be better in that respect than pg does on it's own.

In theory, yes, but PostgreSQL's current storage is not optimized for flash.

Re: Zheap – Reinvented PostgreSQL Storage

#62

Post by formerly_proven »

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…

Undo logging vs redo logging (except both are MVCC).

Re: Zheap – Reinvented PostgreSQL Storage

#63

Post by jmnicolas »

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…

Your write-up (or something similar) should be on their front page. Most projects assume you already know everything about them.

I don't remember the name but there was a NoSQL DB on HN a couple weeks ago. I had to Google and read different websites for a solid 10 minutes before I got that it was indeed a NoSQL DB and what was the differentiating factor from other DBs.

Re: Zheap – Reinvented PostgreSQL Storage

#64

Post by jeltz »

aeyes wrote: post #45
arthurcolle wrote: post #5

Earlier quoted context omitted.

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

I doubt that because in practice PostgreSQL is faster on many workloads than Oracle and InnoDB despite Oracle and InnoDB using similar models to zheap. If the theoretical difference has been large then we should also expect to see big differences in practice, which is not the case.

While zheap may be a superior design I do not think you should expect any major performance improvements other than on some very specific workloads.

Re: Zheap – Reinvented PostgreSQL Storage

#65

Post by jabl »

simcop2387 wrote: post #10
stingraycharles wrote: post #3

Earlier quoted context omitted.

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…

the cow approach likely plays better with flash based storage since rewrites happen less often. so if you've got a lot of otherwise extra storage then maybe it's a good idea. though if you run on top of something like f2fs maybe it'll be better in that respect than pg does on it's own.

IIRC one issue with the current design is that as new rows are written in a different place, all indexes on that table have to be updated as well, leading to write amplification. If you do an update in place, you don't need to modify indices (but now that I think of it, what about concurrently running transactions that might want to read the old values using the old indices...?)

Also during the vacuum there's a lot of writes marking rows as available.

Re: Zheap – Reinvented PostgreSQL Storage

#66

Post by sbuttgereit »

hyperman1 wrote: post #58
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.

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.

Ah the memories! Yes, the "other file" talked about in these comments is also not an unlimited resource and, depending on implementation specifics, I've found can be more limiting than the current PostgreSQL MVCC approach. In Oracle, I use to see data maintenance related procedures being written that would loop and update some large data set, but intermittently commit progress every so often... this would end up exhausting the undo availability. The more frequent the commits, the faster you'd get to the ORA-01555 before the end of the run. Not saying the procedure was right... just that the pattern was common as was the error.

Also, not too long ago PostgreSQL added Stored Procedure support... which allows for mid-transaction commits. Maybe it will be a case of "what goes around, comes around".

I should note I'm not at all against this Zheap idea, stored procedures, or multiple approaches to MVCC/storage back ends in PostgreSQL... being able to choose my effective pros/cons for a project is really desirable. But, solving the vacuum problem will create new problems.

Re: Zheap – Reinvented PostgreSQL Storage

#67

Post by linuxhiker »

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.

No, it provides a basis to ease your migration from Oracle. There is a lot it doesn't do. Basically, if you have a homegrown app on Oracle it removes a lot of the low hanging fruit to migrate but you aren't running peoplesoft or something on it.

Re: Zheap – Reinvented PostgreSQL Storage

#68

Post by [deleted] »

needusername wrote: post #59
takeda wrote: post #24

Earlier quoted context omitted.

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.

[deleted]

Re: Zheap – Reinvented PostgreSQL Storage

#69

Post by jacques_chester »

darksaints wrote: post #48
jacques_chester wrote: post #39

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

I'm not working on zedstore. I just like peppering the folks who do with my wishlist items (bitemporalism! query time forecasting and progress measurements! support for query results in binary formats like Avro, ORC or Arrow! kittens and unicorns!) while they try to do useful work.
Post reply on HN