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

Moving a billion Postgres rows on a $100 budget

blog.peerdb.io

Post reply on HN
Print view View original

21–30 of 71 posts

Re: Moving a billion Postgres rows on a $100 budget

#21

Post by saisrirampur »

xenator wrote: post #20
saisrirampur wrote: post #12

Earlier quoted context omitted.

Based on my experience working with Postgres users since a decade (ex-Citus/Microsoft), I don't think Postgres is there yet to support every possible workload - ex: medium to larger scale (ex:1TB+) workloads in Real Time analytics, Data Warehousing, Search etc. Sure at smaller scales, it is very versatile to support any workload. That is why it is super common for companies to complement Postgres with other data stor…

We operate with 80 Tb of data ATM. It is laying in several nodes and meta nodes (this is our own terminology). All Postgres. Recently we need to move data from one DB to another, about 600M records. It is not biggest chank of the data, but we need it on different server because we use FTS a lot. And don't want to interrupt other operations on previous server. It took 3 days and costs 0.

Thanks for context! Totally understand where you are coming from. Postgres can be moulded to work for many use-cases. However it could take good amount of effort to make it happen. For example in your case building and managing a sharded Postgres environment isn't straightforward. It requires quite a lot of time and expertise. Citus automated exactly this (sharding). However it wasn't a fit for every workload. https://docs.citusdata.com/en/v12.1/get_started/what_is_citu...

Re: Moving a billion Postgres rows on a $100 budget

#22

Post by HermitX »

I've already thought of the follow-up to this article, 'Querying a billion rows on a $XX budget.' Let me give you my answer directly: switch from Snowflake to StarRocks. It's an open-source project under the Linux Foundation, with speed that's more than adequate, especially for queries involving multiple tables. If you're interested, you might want to check it out, https://medium.com/starrocks-engineering/how-to-reduce-snowf....

Re: Moving a billion Postgres rows on a $100 budget

#23

Post by quadrature »

xenator wrote: post #20
saisrirampur wrote: post #12

Earlier quoted context omitted.

Based on my experience working with Postgres users since a decade (ex-Citus/Microsoft), I don't think Postgres is there yet to support every possible workload - ex: medium to larger scale (ex:1TB+) workloads in Real Time analytics, Data Warehousing, Search etc. Sure at smaller scales, it is very versatile to support any workload. That is why it is super common for companies to complement Postgres with other data stor…

We operate with 80 Tb of data ATM. It is laying in several nodes and meta nodes (this is our own terminology). All Postgres. Recently we need to move data from one DB to another, about 600M records. It is not biggest chank of the data, but we need it on different server because we use FTS a lot. And don't want to interrupt other operations on previous server. It took 3 days and costs 0.

Is your workload an analytical or transactional workload ?.

Re: Moving a billion Postgres rows on a $100 budget

#24

Post by nojvek »

Postgres seriously needs a columnstore backed table instead of just a rowstore.

MSSQL has this and it is magic. SingleStore has it, and it is wonderful.

I'm willing to give a bounty of $1000 to whoever adds that into main postgres tree.

Snowflake is great as a warehouse. it's latency is shit when it comes to fast lookups and aggregates. If you can tolerate >1s api calls, that is fine. It takes forever to insert a few rows in a large table.

If you want a proper live DB, snowflake is a rich man's poor database.

Re: Moving a billion Postgres rows on a $100 budget

#25

Post by garciasn »

tharakam wrote: post #14

I'm confused. Everything sounds very expensive to me. The last table which compares it with the other vendors is surprising. Even Stich Data (cheapest) costs $1 to move 240K records: (1B / 4,166.67 = 240K). Is this real? So, their solution costs $1 to process 13.6M records. Sounds like this is not very share-worthy. What I'm missing here?

What I want to know is why the fuck it takes 8 days to load 700MM records—in 2024.

I couldn’t even continue reading the article because it must be from 2006.

Re: Moving a billion Postgres rows on a $100 budget

#26

Post by saisrirampur »

garciasn wrote: post #25
tharakam wrote: post #14

I'm confused. Everything sounds very expensive to me. The last table which compares it with the other vendors is surprising. Even Stich Data (cheapest) costs $1 to move 240K records: (1B / 4,166.67 = 240K). Is this real? So, their solution costs $1 to process 13.6M records. Sounds like this is not very share-worthy. What I'm missing here?

What I want to know is why the fuck it takes 8 days to load 700MM records—in 2024. I couldn’t even continue reading the article because it must be from 2006.

700M records in 8 days (1024 rps) is to mimic a real-world transactional (OLTP) workload. It doesn't define limits on what throughput can be achieved.

Re: Moving a billion Postgres rows on a $100 budget

#27

Post by dkatz23238 »

nojvek wrote: post #24

Postgres seriously needs a columnstore backed table instead of just a rowstore. MSSQL has this and it is magic. SingleStore has it, and it is wonderful. I'm willing to give a bounty of $1000 to whoever adds that into main postgres tree. Snowflake is great as a warehouse. it's latency is shit when it comes to fast lookups and aggregates. If you can tolerate >1s api calls, that is fine. It takes forever to insert a few…

Parade DB are working on this problem: https://github.com/paradedb/paradedb/tree/dev/pg_analytics

Re: Moving a billion Postgres rows on a $100 budget

#29

Post by notjoemama »

hipadev23 wrote: post #18
saisrirampur wrote: post #16

Earlier quoted context omitted.

That sentence is more in the context of the blog of moving a billion rows across data-stores. We will edit it to make that more clear. Thanks for the feedback.

I can't speak for Snowflake, but on Clickhouse it's quite literally [1]: insert into new_table select * from postgresql('postgres:5432', 'db', 'table', 'user', 'pass'); I assume it's similarly easy on Snowflake, Databricks, SingleStore, and the rest. [1] https://clickhouse.com/docs/en/sql-reference/table-functions...

Yes, and there’s loads of opportunity to over engineer every step of the process.

Re: Moving a billion Postgres rows on a $100 budget

#30

Post by garciasn »

saisrirampur wrote: post #26
garciasn wrote: post #25

Earlier quoted context omitted.

What I want to know is why the fuck it takes 8 days to load 700MM records—in 2024. I couldn’t even continue reading the article because it must be from 2006.

700M records in 8 days (1024 rps) is to mimic a real-world transactional (OLTP) workload. It doesn't define limits on what throughput can be achieved.

Ahhh. Thank you. That makes more sense.
Post reply on HN