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

51–60 of 278 posts

Re: Migrating to Postgres

#51

Post by delish »

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.

Related: Oxide's podcast, "Whither CockroachDB," which reflects on experience with postgres at Joyent, then the choice to use cockroach in response to prior experiences with postgres.

https://www.youtube.com/watch?v=DNHMYp8M40k

I'm trying to avoid editorializing in my above summary, for fear of mischaracterizing their opinions or the current state of postgres. Their use of postgres was 10 years ago, they were using postgres for a high-availability use case -- so they (and I) don't think "postgres bad, cockroach good." But like Bryan Cantrill says, "No one cares about your workload like you do." So benchmark! Don't make technical decisions via "vibes!"

Re: Migrating to Postgres

#52

Post by Tadpole9181 »

etblg wrote: post #29
jjice wrote: post #27

Earlier quoted context omitted.

I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation. Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memo…

> event distincts were done in memory! I can’t fathom the decision making there… This is one of those situations where I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it, or if they just made really bad decisions. I just don't get it, it feels so wrong.

> I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it

This is answered at the very top of the link on the post you replied to. In no unclear language, no less. Direct link here: https://github.com/prisma/prisma/discussions/19748#discussio...

> I want to elaborate a bit on the tradeoffs of this decision. The reason Prisma uses this strategy is because in a lot of real-world applications with large datasets, DB-level JOINs can become quite expensive...

> The total cost of executing a complex join is often higher than executing multiple simpler queries. This is why the Prisma query engine currently defaults to multiple simple queries in order to optimise overall throughput of the system.

> But Prisma is designed towards generalized best practices, and in the "real world" with huge tables and hundreds of fields, single queries are not the best approach...

> All that being said, there are of course scenarios where JOINs are a lot more performance than sending individual queries. We know this and that's why we are currently working on enabling JOINs in Prisma Client queries as well You can follow the development on the roadmap.

Though this isn't a complete answer still. Part of it is that Prisma was, at its start, a GraphQL-centric ORM. This comes with its own performance pitfalls, and decomposing joins into separate subqueries with aggregation helped avoid them.

Re: Migrating to Postgres

#53

Post by spudlyo »

sroussey wrote: post #16
amazingamazing wrote: post #9

Earlier quoted context omitted.

I don't disagree with your point, but over normalization and joining everywhere also isn't necessarily the answer, even with an index. there's no easy answer to this, really depends on the performance characteristics the critical user journeys need. with a little pain, if I had to pick an extreme, I'd pick extreme normalization with materialized views that are queried (e.g. no joins), rather than joining all of the t…

I typically go for 3rd normal form, and selectively denoralize where it has true performance value.

“Normalize ’til it hurts, denormalize ’til it works.”

Re: Migrating to Postgres

#55

Post by luhn »

> 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 and a 100 million row table is well within the abilities of that—I have a 150 million row table happily plugging along on a 2vCPU+16GB instance. Apples and oranges, perhaps, but people shouldn't underestimate what a single modern server can do.

Re: Migrating to Postgres

#56

Post by Tadpole9181 »

compton93 wrote: post #49
Tadpole9181 wrote: post #42

Earlier quoted context omitted.

It's pretty obvious when somebody has only heard of Prisma, but never used it. - Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting. - Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application. - They are in the…

They've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.

The JOIN mode has been in preview for over a year and is slated for GA release within a few months. Which has been on their roadmap.

The removal of the rust service is available in preview for Postgres as of 6.7.[1]

Rewriting significant parts of a complex codebase used by millions is hard, and pushing it to defaults requires prolonged testing periods when the worst case is "major data corruption".

[1]: https://www.prisma.io/blog/try-the-new-rust-free-version-of-...

Re: Migrating to Postgres

#57

Post by ScalaHanSolo »

coolcase wrote: post #54

Why not optimise the bad queries first? Aside. Job section says not 9-5. What does that mean? Long hours? Or not 9-5 attitude?

Author here. Optimizing bad queries was absolutely part of the issues with the performance. The issue with cockroach was that the visibility into those bad queries was not great. It wasn't until we had the superior tooling from the Postgres ecosystem that we were able to track them down more efficiently.

Re: Migrating to Postgres

#58

Post by ScalaHanSolo »

esafak wrote: post #17

I read it as: Why You Shouldn't Use Prisma and How Cockroach Hung Us Out To Dry I already knew about prisma from the infamous https://github.com/prisma/prisma/discussions/19748

Author here. Yeah, that's not a bad take away either. I've also been really vocal in Primsa issues for all sorts of things. We are about to embark on a big migration away from Prisma and onto Drizzle once the Drizzle team lands 1.0

We will absolutely share our findings when that migration happens!

Re: Migrating to Postgres

#59

Post by casper14 »

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…

Nice! What optimizations have you put in llace yo support 150 mil? Just some indexing or other fancy stuff?

Re: Migrating to Postgres

#60

Post by ScalaHanSolo »

monkeyelite wrote: post #35
frollogaston wrote: post #22

Did I miss something, or does the article not mention anything about sharding in Postgres? Was that just not needed? Also, query planner maturity is a big deal. It's hard to get Spanner to use the indexes you want.

There are probably fewer than 100 websites that couldn’t be a single Postgres instance on nice server hardware, with good caching.

Yeah, this is our read with Postgres here at Motion. I believe that Motion will easily be able to 10x on modern hardware along with various optimizations along the way.
Post reply on HN