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

Migrating to Postgres

engineering.usemotion.com

Post reply on HN
Print view View original

91–100 of 278 posts

Re: Migrating to Postgres

#91

Post by ies7 »

thomasfromcdnjs wrote: post #66
luhn wrote: post #55

> By Jan 2024, our largest table had roughly 100 million rows. I did a double take at this. At the onset of the article, the fact they're using a distributed database and the mention of a "mid 6 figure" DB bill made me assume they have some obscenely large database that's far beyond what a single node could do. They don't detail the Postgres setup that replaced it, so I assume it's a pretty standard single primary an…

Does mid six figure mean ~$500k? That sounds insane for a crud app with one million users. What am I missing?

$500k for only 100 millions rows db also sounds crazy

Re: Migrating to Postgres

#92

Post by evantbyrne »

VWWHFSfQ wrote: post #38
pier25 wrote: post #31

Earlier quoted context omitted.

Prisma is so bad... can you believe it's by far the most downloaded ORM in NPM?

Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support. I've sworn off ORMs entirely. My applicati…

Nah. The most prolific backend frameworks are all built on ORMs for good reason. The best ones can deserialize inputs, validate them, place those object directly into the db, retrieve them later as objects, and then serialize them again all from essentially just a schema definition. Just to name a few advantages. Teams that take velocity seriously should use ORMs. As with any library choice you need to carefully vet them though.

Re: Migrating to Postgres

#93

Post by Spivak »

sreekanth850 wrote: post #77

It's wild and hilarious, how often startups and companies go for distributed databases like CockroachDB/TiDB/Yugabyte before they actually need distribution, this trends sucks. 100 million rows is nothing that a well-tuned Postgres or MySQL instance (or even read-replicated setup) can't handle comfortably. Scale when you hit the wall.

100M isn't much even for not super well tuned postgres.

Re: Migrating to Postgres

#94

Post by I_am_tiberius »

moonikakiss wrote: post #88
I_am_tiberius wrote: post #85

Earlier quoted context omitted.

A follow up question: You can't join columnar tables with OLTP tables, right?

yes you can. Even if the columnar tables are in the read replica. you'll be able to do joins with your OLTP tables

That's great, thanks.

Re: Migrating to Postgres

#95

Post by I_am_tiberius »

moonikakiss wrote: post #87
I_am_tiberius wrote: post #70

Earlier quoted context omitted.

That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.

exactly. we take the CDC output / logical decoding from your OLTP tables and write into a columnar format with We had to design this columnstore to be 'operational' so it can keep up with changing oltp tables (updates/deletes). You'll be able to deploy Mooncake as a read-replica regardless of where your Postgres is. Keep the write path unchanged, and query columnar tables from us. --- v0.2 will be released in preview…

Ah, I see. So there's a replication process similar to ClickHouse's MaterializedPostgres. Ideally, there would be functionality allowing a columnstore query to wait until all writes to the OLTP tables — up to the query's execution time — are available. This would make the system truly Postgres-native and address issues that no other system currently solves.

Re: Migrating to Postgres

#96

Post by CuriouslyC »

VWWHFSfQ wrote: post #38
pier25 wrote: post #31

Earlier quoted context omitted.

Prisma is so bad... can you believe it's by far the most downloaded ORM in NPM?

Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support. I've sworn off ORMs entirely. My applicati…

SQL Alchemy is pretty good, because it's mostly a sql engine that has an ORM bolted on top of that, and the docs actively try to point users towards using the sql engine rather than using the ORM for everything.

Re: Migrating to Postgres

#97

Post by sreekanth850 »

Spivak wrote: post #93
sreekanth850 wrote: post #77

It's wild and hilarious, how often startups and companies go for distributed databases like CockroachDB/TiDB/Yugabyte before they actually need distribution, this trends sucks. 100 million rows is nothing that a well-tuned Postgres or MySQL instance (or even read-replicated setup) can't handle comfortably. Scale when you hit the wall.

100M isn't much even for not super well tuned postgres.

Yes, there are multiple steps to consider before jumping to a distributed database and only when you actually hit bottlenecks, like read replication, CQRS, etc. But I guess it's often just about chasing fancy stuff.

Re: Migrating to Postgres

#98

Post by mdaniel »

from-nibly wrote: post #33

Feels like postgres is always the answer. I mean like there's gotta be some edge case somewhere where postgres just can't begin to compete with other more specialized database but I'd think that going from postgres to something else is much easier than the other way around.

There's a gist that shows up in these threads https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

But while digging that up it seems there is one with more colors: https://postgresforeverything.com/

And one for the AI crowd https://github.com/dannybellion/postgres-is-all-you-need#pos...

Re: Migrating to Postgres

#99

Post by I_am_tiberius »

yen223 wrote: post #80
etler wrote: post #40

I've lost count of how many "Migrating from X to Postgres" articles I've seen. I don't think I've once seen a migrating away from Postgres article.

I have participated in a Postgres -> Clickhouse migration, but I haven't bothered writing an article about it.

The entire database? Isn't that very limiting due to slow write speeds in Clickhouse? I saw ch more as a db for mainly read activities.

Re: Migrating to Postgres

#100

Post by jacobsenscott »

I_am_tiberius wrote: post #99
yen223 wrote: post #80

Earlier quoted context omitted.

I have participated in a Postgres -> Clickhouse migration, but I haven't bothered writing an article about it.

The entire database? Isn't that very limiting due to slow write speeds in Clickhouse? I saw ch more as a db for mainly read activities.

CH excels at extremely high volume writes. You probably can't throw enough data at it.
Post reply on HN