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

Postgres data stored in Parquet on S3: LTAP architecture explained

databricks.com

Post reply on HN
Print view View original

31–40 of 66 posts

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#31

Post by nikita »

ignoreusernames wrote: post #16
andrenotgiant wrote: post #3

Here's what I don't understand: Part of the value of doing an ETL pipeline via streaming replication is you get the full history of data in a table. An SCD type 2 table where each row also has a valid_from and valid_to timestamp column. How would someone do the same thing with this architecture?

If safe keeper exposes the changes to the tables somehow, a type2 scd is just a windowed lag over the primary key sorted by the timestamp

Safekeepers keep a window of WAL in Postgres WAL format and doesn’t have an external API.

It streams WAL to pageservers and Postgres read replicas

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#32

Post by dsauerbrun »

conradludgate wrote: post #23
dsauerbrun wrote: post #22

Maybe I'm too stupid to understand the article... How does this achieve performant querying for olap and oltp purposes? Based on my understanding, olap queries will go to the parquet files which are stored in a columnar fashion and oltp style queries will go to a caching layer that sits on top of those parquet files? What's the special sauce here? Seems like they're just caching the data which, for all intents and pu…

Hi, I work on Lakebase (but not on storage), here's how I understand it. For Lakebase and Neon, our architecture needs the caching layer regardless (what we call Pageservers). Performing reads from S3 directly is too slow so we reconstruct pages and keep them on an nvme server for faster querying. Changing the format on S3 to be Parquet effectively introduces no additional copies over our existing architecture

Hmm, if the caching layer doesn't change(I assume it was optimized for olap style queries), and the new parquet format is better for olap... I'm still not understanding how it performs well for oltp reads.

I'll give the article another read... Maybe I missed something. Thank you for the response! Really nice to be able to get info straight from people who work on the product

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#33

Post by nikita »

dsauerbrun wrote: post #32
conradludgate wrote: post #23

Earlier quoted context omitted.

Hi, I work on Lakebase (but not on storage), here's how I understand it. For Lakebase and Neon, our architecture needs the caching layer regardless (what we call Pageservers). Performing reads from S3 directly is too slow so we reconstruct pages and keep them on an nvme server for faster querying. Changing the format on S3 to be Parquet effectively introduces no additional copies over our existing architecture

Hmm, if the caching layer doesn't change(I assume it was optimized for olap style queries), and the new parquet format is better for olap... I'm still not understanding how it performs well for oltp reads. I'll give the article another read... Maybe I missed something. Thank you for the response! Really nice to be able to get info straight from people who work on the product

Recent data plus working set is always in Postgres page format.

Historical data when pushed to s3 is in parquet. This happens async - not on the transaction hot path.

So older data below certain LSN is on s3 in parquet available to all analytics processing. Hot data is on page servers in page format for OLTP.

You can be smart in querying both representations for real time analytical queries

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#34

Post by re-thc »

tux3 wrote: post #21
khurs wrote: post #17

Earlier quoted context omitted.

"The large enterprise vendors are not price-sensitive." Have you ever spoken to a CTO? They most certainly are. Also many are Microsoft houses so using Azure blob plus one of the reasons for Kubernetes/Openshift adoption was to be cloud neutral

There is a scale between prise-sensitivity and risk-averseness, from my point of reference large companies are much more risk-averse than they are price sensitive. Of course this will vary, CTOs exist in all sort of different environments. Price is not the reason people chose AWS. Some companies use Azure. The current startup at $WORK uses yet another smaller Cloud. And yet AWS sill has the clear lead in market share…

> There is a scale between prise-sensitivity and risk-averseness, from my point of reference large companies are much more risk-averse than they are price sensitive.

That's not true. It's just the way things work "saving money" isn't part of the KPI. Enterprise teams get a budget. If you "saved" you don't get it back. So unless there's a legit need it's ALWAYS easier increasing than cutting it.

It's not about risk. It's about power. They are price sensitive but in a way that doesn't matter to the bottom line i.e. if I can cut my AWS storage bill by 10% and then spend it on random tokens I'd do it.

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#35

Post by saisrirampur »

But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs.

You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+ tps), which is pretty common these days. Also CDC if done right and with care can be magical for users and stays native to the OLTP/OLAP data-store.

Third, data Lakes and open formats are suitable for Data Warehousing / Data analyst use-cases than real-time customer facing apps. Sure, you might work on changing that, which is what you are upto, but you’ll always run into tradeoffs, which will make it hard to unleash the best performance, much needed for the latter category.

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#36

Post by nikita »

saisrirampur wrote: post #35

But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs. You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+…

Conversion is async. The whole point is to never deal with CDC which is error prone and taxing Postgres with occupying a replication slot and burning memory and cpu in the OLTP system.

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#37

Post by TheTaytay »

hasyimibhar wrote: post #8
andrenotgiant wrote: post #3

Here's what I don't understand: Part of the value of doing an ETL pipeline via streaming replication is you get the full history of data in a table. An SCD type 2 table where each row also has a valid_from and valid_to timestamp column. How would someone do the same thing with this architecture?

It wouldn't be possible to do this with LTAP architecture since (I'm assuming) the individual logical changes are not visible. But honestly I've always seen SCD type 2 table as a workaround due to lack of data modeling experience in the source database. If you design your tables correctly, you shouldn't need SCD type 2 downstream. For example, if you know your user can change emails, and there might be events from an…

I think you have a point, and SCD type 2 feels like a workaround, but there is also something to be said for the ability to query every row as it was at any given version. I’m not saying that SCD type 2 is the best solution given there might be a more domain-specific way to do it, but I see it a lot like file-based version control. It’s convenient to be able to examine all files as they existed at any point in time, without having to “model” the ways in which those files might change directly into the domain of the individual files.

If you have something like dolt (not affiliated), a version controlled database, you wouldn’t have to slap change dates on anything OR create your historical table. The changes would be implicit in the version history.

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#38

Post by saisrirampur »

nikita wrote: post #36
saisrirampur wrote: post #35

But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs. You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+…

Conversion is async. The whole point is to never deal with CDC which is error prone and taxing Postgres with occupying a replication slot and burning memory and cpu in the OLTP system.

Taxing Postgres is one thing, which can be overcome with ways like using standbys. There could other more native ways (than unifying storage), which you’ll hear about in a few weeks. Also I don’t fully agree on logical replication taxing Postgres, if the client is built with care and precision.

In regards to error prone and speed (lag, latency at real-world scale), I wish the blog went into more detail and gave evidence than talk theory.

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#39

Post by ronfriedhaber »

nikita wrote: post #36
saisrirampur wrote: post #35

But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs. You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+…

Conversion is async. The whole point is to never deal with CDC which is error prone and taxing Postgres with occupying a replication slot and burning memory and cpu in the OLTP system.

> CDC which is error prone

Just have superior CDC :)

Re: Postgres data stored in Parquet on S3: LTAP architecture explained

#40

Post by saisrirampur »

ronfriedhaber wrote: post #39
nikita wrote: post #36

Earlier quoted context omitted.

Conversion is async. The whole point is to never deal with CDC which is error prone and taxing Postgres with occupying a replication slot and burning memory and cpu in the OLTP system.

> CDC which is error prone Just have superior CDC :)

Exactly! Why unifying storage, which opens up a can of trade-offs.
Post reply on HN