6 ms·
Moving a billion Postgres rows on a $100 budget
- _boffin_ 3y agoHow about 11b and a horrible python script over to parquet to your wee little NAS for your homelab?
- sss111 3y agonow we're talking about innovation
- MenhirMike 3y agoYour wee little NAS will probably still have 2x-3x better IO performance than cloud based services.
- ActorNightly 3y agoimport multiprocessing and up the process count until you hit network bottleneck.
- twelfthnight 3y agoIf someone is price conscious, why move from postgres to snowflake?
- happytiger 3y agoThat was my first thought as well — like who is the audience for this? My first thought was that you could stay on Postgres and save that $100 by using the secret power of Open Source. Well said.
- __s 3y agoSeems like the title would be more accurate specifying postgres to snowflake In the end, there's companies paying to use snowflake, & despite what one may believe they aren't price oblivious. Having their application in postgres is a cost optimization, but then still relying on snowflake for data warehouse integrations
- foobiekr 3y agoHonestly I think most snowflake customers were w cheap money side effect. I love snowflake but their pricing is, in fact, absurd.
- saisrirampur 3y agoBased 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 stores. Don't mean to say that Postgres will not get there, I think it will! But I see it to be more in the long term.
- xenator 3y agoWe 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.
- saisrirampur 3y agoThanks 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_citus.html#when-citus-is-inappropriate https://docs.citusdata.com/en/v12.1/get_started/what_is_citu...
- xenator 3y agoTbh I never bothered by these mental restrictions. I can't say that we have effort that requires more than normal human brain can handle (and we aren't smartest people on the planet). I used to work in one project where we process big part of all shop's cash receipts in one of the biggest european country. We don't use any of these products. And it was done by one person. Only stupid idea we had was to use AWS. Learned helplessness push people to change best product on the market but without salesman who tickle your balls. Postgres is one of the best product on the market. But so much FUD makes a new space for "problem solvers" for the problem never exist. I'm not about Citus, I'm about idea that it is require much effort to build something around Postgres.
- quadrature 3y agoIts not about being price conscious, its about finding an efficient way to replicate data into your data warehouse.
- ies7 3y agoThen they should take out the "on a $100 budget" from the title.
- saisrirampur 3y agoOP here, thanks for chiming in. Yep, the blog talks about how efficient you can be while replicating data from Postgres to Data Warehouses. Also just for some context, the inspiration behind writing this blog is a common concern from customers that data-movement is expensive and also the insanely crazy margins in this space.
- saisrirampur 3y agoThanks for chiming in. The concepts/idea of this blog can be extended to other target data-stores too, say ClickHouse.
- rplnt 3y ago(edit: mostly offtopic observation follows) I only knew Snowflake the id selection algorithm, so was a bit confused, but googling "snowflake db" showed me this blurb and now I'm even more confused. > Snowflake enables organizations to learn, build, and connect with their data-driven peers. Collaborate, build data apps & power diverse workloads in the ...
- quickslowdown 3y agoSnowflake & Azure Synapse are competing products, if that helps.
- dacryn 3y agosynapse is such a disaster. I don't know any example of a decent size succesfull deployment that. It grinds to a halt and charges you a lot of money for it. Snowflake is an amazing Agile database that has a great ecosystem. Teradata still remains king if you want this type of cloud datawarehouse, and becomes price competitive with Snowflake if you actually use it intensively (transactions are cheaper on teradata than snowflake) But in the end, a good managed postgresql is probably enough for 80% of the clients of Synaps/Snowflake anyway. It's just that CTO's are starting to lose technical knowledge and are more politicians nowadays
- pryelluw 3y agoDoes the elastic license fall under open source as defined by OSI?
- tristan957 3y agoNo it does not.
- ralusek 3y agoIf someone asked me how much it would cost to move a billion Postgres rows, I would say "probably under $100." I just had to move 500 million "rows" into S3, and it came in at about $100. I would expect S3 to be more expensive.
- hipadev23 3y ago> Moving 1 billion rows is no easy task This isn't an accurate premise. Modern OLAP databases make dealing with billions to trillions of rows manageable, including on a single server. Exporting "select * from table" from an OLTP such as Postgres or MySQL into an OLAP is trivial and quite fast, and if 100M rows/sec on commodity servers isn't fast enough, there's always performance tuning [1]. [1] https://altinity.com/blog/loading-100b-rows-in-minutes-in-altinity-cloud https://altinity.com/blog/loading-100b-rows-in-minutes-in-al...
- saisrirampur 3y agoThat 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.
- CyberDildonics 3y agodata stores or data lakes with data rivers?
- ssss11 3y agoYou’re forgetting the data dams on those data rivers
- collingreen 3y agoPlease can we have data fish ladders next?
- davidw 3y agoHere in Oregon we are removing the data dams to have more natural data flows.
- datadrivenangel 3y agoData dams adds to the visual metaphor of data breaches...
- tharakam 3y agoI'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?
- garciasn 3y agoWhat 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.
- saisrirampur 3y ago700M 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.
- garciasn 3y agoAhhh. Thank you. That makes more sense.
- deleted 3y ago[deleted]
- api 3y agoWelcome to 2024 and the generation of developers raised in the cloud native world who think this is normal. A billion rows is nothing and having $100 appear in conjunction with that is absurd unless you are doing some kind of really heavy compute or AI model training on that data. By 2030 we’ll have those costs well up over a thousand dollars and it’ll take five or six separate SaaS systems wired together to do this. Progress!
- Aeolun 3y agoTo be fair, my machine that can process 1B rows an unlimited number of times still cost $1000 to build, so if you need a one off maybe paying the $100 is better?
- jmholla 3y agoThis was a disappointing article. It was expecting it to explore validating the integrity and consistency of the data, but that just seems to be handwaved away by a short section saying PeerDB handles it. This is especially disappointing since the article calls that out as one of the cruxes that makes this so difficult.
- HermitX 3y agoI'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-snowflake-costs-by-80-deb87aa69bc5 https://medium.com/starrocks-engineering/how-to-reduce-snowf....
- ako 3y agoStarrocks sounds too good to be true, what are the cons?
- 392 3y agoHard to set up in Kubernetes. Seems to want you to know too many specifics for how to shard your db ahead of time. Don't remember how it handles schema evolution. I settled for Databend because it just worked easy and fast with nothing more than table definitions.
- hodgesrm 3y agoDo you have a connection with StarRocks?
- HermitX 3y agoWhat do you want? You can connect with the StarRocks eng team on the slack channel. https://join.slack.com/t/starrocks/shared_invite/zt-z5zxqr0k-U5lrTVlgypRIV8RbnCIAzg https://join.slack.com/t/starrocks/shared_invite/zt-z5zxqr0k...
- nojvek 3y agoPostgres 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.
- CSSer 3y agoKnow anybody who could do it in a weekend? That’s about what you’re paying for.
- dkatz23238 3y agoParade DB are working on this problem: https://github.com/paradedb/paradedb/tree/dev/pg_analytics https://github.com/paradedb/paradedb/tree/dev/pg_analytics
- speedgoose 3y agoColumnar store PostgreSQL extension exists, here are two but I think I’m missing at least another one: https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw https://github.com/hydradatabase/hydra https://github.com/hydradatabase/hydra You can also connect other stores using the foreign data wrappers, like parquet files stored on an object store, duckdb, clickhouse… though the joins aren’t optimised as PostgreSQL would do full scan on the external table when joining.
- Loic 3y agoFor time series, you have the TimescaleDB extension. https://www.timescale.com/ https://www.timescale.com/
- ddorian43 3y ago> I'm willing to give a bounty of $1000 to whoever adds that into main postgres tree. This is the most disrespectful thing I've read on HN.
- guidedlight 3y ago> Moving a Billion Postgres Rows on a $100 Budget In a magical universe where your time is free.
- rthnbgrredf 3y agoYou can do something better for 0$, just install the TimescaleDB postgres extension and execute: SELECT create_hypertable('public.challenge_1br', by_range('time')); Now, enjoy your better than Snowflake query performance performance at no extra cost.
- btown 3y agoIronically for many, the lack of support for arbitrary extensions on RDS, Google Cloud SQL, and the like means that if your data's stuck there, you'd need to migrate to a self-managed database instance - which means you'd need to use the strategies in the blog post anyways! More generally, of course, it's not always a "free lunch" to have your analytics compute using the same CPU/RAM that runs your production transactional database. If you're at billion-row scales, you're likely to be at the point where mirroring to a data warehouse is becoming necessary.
- rthnbgrredf 3y agoGoogle Cloud SQL does support quite a few postgres extension, but unfortunately it does not support TimescaleDB, that's quite sad indeed. I hope that at some point in the future TimescaleDB could be merged into postgres to make it a native feature.
- mannyv 3y agoWhy not just set up a replica pgsql, then break the connection and upgrade it to primary? It's amusing how much effort is put into ETL these days. I remember when ETL departments were filled with the sludge of the programming world. It took ETL departments weeks to generate a CSV, and it would inevitably have massive numbers of errors because they didn't actually follow the format that was specified on the form they forced everyone to use.
- kroolik 3y agoWhen thinking about solving a problem one of questions to be asked is whether you want a challenge or a solution. Engineers, on average, want to be challenge, which impacts options chosen. There may also have been other factors, not mentioned here.
- janeskie 3y ago[flagged]