Earlier quoted context omitted.
You can stream CSV without writing it to a disk.
What would be the point in that?
COPY is way faster than INSERT, at least in PG.
Moving a billion Postgres rows on a $100 budget
61–70 of 71 posts
Re: Moving a billion Postgres rows on a $100 budget
#62I'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-redu…
Do you have a connection with StarRocks?
Re: Moving a billion Postgres rows on a $100 budget
#63Re: Moving a billion Postgres rows on a $100 budget
#64> 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/…
Or wouldn't COPY from CSV be much faster?
Fwiw you'd lose type information. Parquet / arrow are much more appropriate here
Re: Moving a billion Postgres rows on a $100 budget
#65You 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.
Ironically 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.
Re: Moving a billion Postgres rows on a $100 budget
#66Postgres 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…
> 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.
I'm sorry my intent came off in the wrong way. I am not super wealthy.
Re: Moving a billion Postgres rows on a $100 budget
#67You 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.
Ironically 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 transac…
Google 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.
Re: Moving a billion Postgres rows on a $100 budget
#68Earlier quoted context omitted.
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 ?.
Both, we process all the data in several blockchains and make public reports available for our users. Some of data can be processed once, others need to be recalculated on the schedule. So we process every block, every log entity in transaction nature. And also have a lot of data that processed several ways 2nd/3rd/etc times.
We also slowly evolve our internal analytics/intelligence. It is not something that generates high load, but will be at some point. Imagine something like dune.com.
Re: Moving a billion Postgres rows on a $100 budget
#69[flagged]
Re: Moving a billion Postgres rows on a $100 budget
#70Earlier quoted context omitted.
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:…
Tbh 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.