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

141–150 of 278 posts

Re: Migrating to Postgres

#141

Post by graealex »

coolcase wrote: post #71
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.

I hear MySQL can be better for some workloads?

Till about yesterday, MySQL was little more than a CSV file on wheels, and even current popularity is mostly driven by two decades of LAMP.

You can be sure that PostgreSQL will be applicable for any work load that MySQL can handle, but not necessarily the other way round, so if you actually have the freedom to make a choice, go with PostgreSQL.

In particular, because PostgreSQL has a lot more features, people imply that the other one, for the lack of those features and its associated complexity, must automatically be faster. Which isn't true, neither generally, nor in special cases, since the latter one can go either way - your particular query might just run 4x on PostgreSQL. There is also no universal approach to even benchmark performance, since every database and query will have completely different characteristics.

Re: Migrating to Postgres

#142

Post by campbellmorgan »

vvpan wrote: post #117
seer wrote: post #103

Earlier quoted context omitted.

I don’t understand the hate, the only truly limiting factor for Prisma right now is its poor support for polymorphism, apart from that it has quite good support for complicated index setups, and if you need anything more performant, just drop to typed raw sql queries, it also supports views (materialized or otherwise) out of the box. I recently wanted to check it out and wrote a small app that had good use of pgvecto…

How do you do typed raw queries?

https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/r... - it assumes that these queries return arrays and there's a template you can pass in like this:

prisma.$queryraw`SELECT * FROM ...`

Re: Migrating to Postgres

#144

Post by merek »

nop_slide wrote: post #113
ketzo wrote: post #63

Earlier quoted context omitted.

Not 100% parallel, but I was debugging a slow endpoint earlier today in our app which uses Mongo/mongoose. I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join p90 response times dropped from 35 seconds to 1.2 seconds

Maybe because mongo isn’t ideal for relational data?

I believe a lot of Mongo's criticisms come from people modelling highly relational data on a non-relational DB.

Re: Migrating to Postgres

#145

Post by globular-toast »

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…

It's actually even worse than this, many Django applications are straight up Postgres applications. They use Postgres specific bits of the ORM without hesitation. So they're learning these weird ORM incantations instead of just learning the underlying SQL, which would be knowledge you could apply anywhere.

People just hate embedding SQL into other languages. I don't know why.

Re: Migrating to Postgres

#146

Post by maz1b »

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.

What makes you say that? AFAIK, the largest single dedicated servers you can buy on the market go up to around hundreds of cores and terabytes of ram, and NVME up to a PB~ish if you stack NVME/SSD/HDD as well. this is when i last checked.

Re: Migrating to Postgres

#147

Post by globular-toast »

reillyse wrote: post #78
VWWHFSfQ wrote: post #38

Earlier quoted context omitted.

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…

Every ORM except Active Record is awful. Active Record is amazing.

Except active record can barely be considered an ORM IMO. Doing a literal one to one mapping between records and objects is not that impressive. A real data mapper ORM at least gets you true entities that are decoupled from the db. That way you could totally swap out your data layer without affecting your domain layer. Active record leads to big ball of mud architecture.

Re: Migrating to Postgres

#149

Post by liampulles »

I wonder increasingly with tools like ChatGPT whether ORMs make sense anymore? The argument I've always heard for ORMs is that they make it quick and easy to make the initial repository method, and that they make migrating to a new DB easier (hypothetically).

It seems to me LLMs now help fill both those roles, but with the benefit that you can now tune the SQL as needed. I also always prefer actually knowing exactly what queries are going to my DB, but YMMV.

Re: Migrating to Postgres

#150

Post by setr »

evanelias wrote: post #114
Tadpole9181 wrote: post #52

Earlier quoted context omitted.

> 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 b…

It's a completely ridiculous answer though. They're linking to High Performance MySQL's 2nd edition, which came out in June 2008, and was written for users of MySQL 5.0 running on 2008-era hardware. My take, as a MySQL expert: that advice is totally irrelevant now, and has been for quite some time. It's just plain wrong in a modern context.

I’m not even sure it was correct for its time? The whole point of an RDBMS is to execute join operations. The only reason I’d suspect an RDBMS to be bad at its one fundamental job, in any point of time, is the N+1 query scenario or multiple left joins with unrelated dependencies, but that’s triggered by bad orm abstractions to begin with
Post reply on HN