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

211–220 of 278 posts

Re: Migrating to Postgres

#211

Post by neonsunset »

homebrewer wrote: post #172
tilne wrote: post #34

Earlier quoted context omitted.

Doesn’t entity framework have a huge memory footprint too?

If you don't do stupid things like requesting everything from the database and then filtering data client side, then no. We have one application built on .NET 8 (and contemporary EF) with about 2000 tables, and its memory usage is okay. The one problem it has is startup time: EF takes about a minute of 100% CPU load to initialize on every application restart, before it passes execution to the rest of your program. Ma…

Hmm, it should not be taking this much time unless you're running into an edge case - we have a fairly complex application that needs to pre-load a lot of data from persistence (no EFC though, it's an embedded DB) and over the wire at startup, and it still takes 15 seconds on a rainy day, mostly waiting for requests to finish. If you can gather a profile it will likely shed some light on why this takes place. Worst case, if you have a repro, it's worth to file an issue at https://github.com/dotnet/efcore. When you have time that is.

Re: Migrating to Postgres

#212

Post by evanelias »

setr wrote: post #150
evanelias wrote: post #114

Earlier quoted context omitted.

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

It was absolutely valid advice for its time, but only in the highly specific cases/reasons outlined in the book. The 2nd edition was written by the top Percona folks, who pretty much had more experience scaling databases for large websites than anyone else.

The Prisma answer just does not summarize correctly what the book was saying.

It mainly boiled down to sharding and external caching. Storage and memory were much smaller back then, so there was a lot of sharding and functional partitioning, and major reliance on memcached; all of those are easier if you minimize excessive JOINs.

The query planner in MySQL wasn't great at the time either, and although index hints could help, huge complex queries sometimes performed worse than multiple decomposed simpler queries. But the bigger issue was definitely enabling sharding (cross-shard joins had to be handled at the application level) and enabling external caching (do a simple range scan DB query to get a list of IDs/PKs, then do point lookups in memcached, then finally do point lookups in the DB for any that weren't in memcached).

Re: Migrating to Postgres

#213

Post by to11mtm »

bastawhiz wrote: post #10
sgarland wrote: post #7

It is forever enraging to me that ORMs turn SELECT * into each individual column, mostly because people then post the whole thing and it’s obnoxiously large. Similarly maddening, the appalling lack of normalization that is simply taken for granted. “It’s faster, bro.” No, no, it is not. Especially not at the hundreds of millions or billions of rows scale. If you store something low-cardinality like a status column, w…

> ORMs turn SELECT * into each individual column This is a safety feature. If my code expects columns A, B, and C, but the migration to add C hasn't run yet and I'm doing something that would otherwise `SELECT `, my query should fail. If the ORM _actually_ does `SELECT ` I'll get back two columns instead of three and things can get spooky and bad real fast (unless the ORM manually validates the shape of the query res…

> (unless the ORM manually validates the shape of the query response every time, which will come with a real runtime cost).

Semi related to this, the ORM explicitly specifying columns ensures that the shape is consistent which both makes for a faster parse of the rows coming back (And, again, eliminates surprises for the parser.)

Re: Migrating to Postgres

#214

Post by hliyan »

hylaride wrote: post #184
hliyan wrote: post #82

Earlier quoted context omitted.

Call me old fashioned, but when records start reaching the 100 million range, it's usually an indication that either your dataset is too wide (consider sharding) or too deep (consider time based archival) to fit into a monolithic schema. For context, I've dealt with multiple systems that generate this volume of data between 2003 - 2013 (mostly capital markets, but also some govt/compliance work) with databases and ha…

It obviously depends on how you use your data, but it really is surprising how far one can go with large tables when you implement sharding, caching, and read replicas. For tables with a lot of updates, Postgres used to fall over with data fragmentation, but that's mostly been moot since SSDs became standard. It's also easier than ever to stream data to separate "big data" DBs for those separate use cases.

Thanks, I knew I forgot something: read replicas

Re: Migrating to Postgres

#215

Post by sgarland »

graealex wrote: post #137
jacobsenscott wrote: post #102

Earlier quoted context omitted.

PG requires a lot of expertise to keep running when you get to a billion rows or massive ingest. It can do it, but it doesn't just do it out of box running the defaults.

Hopefully at 1B records, you have a business model that allows you to spend some money on either hardware or talent to solve this problem.

Unfortunately, most places go with hardware first, so the problem grows larger and larger. When they do finally hire someone who knows how to address it, it’s a monstrous undertaking that dev teams are reluctant to do, because it’s a lengthy pause on feature development (so really, PMs are reluctant), and also future velocity is typically somewhat slower – turns out proper RDBMS data modeling is rigid, and doesn’t suffer fools.

Re: Migrating to Postgres

#216

Post by abraxas »

gilbetron wrote: post #183

The answer to the question, "what database should I use?" is "postgres". If you are in a situation where postgres actually won't work, then you already would know exactly why postgres won't work. In other words: [Postgres -> exotic solution] is the path everyone should take (and 99% will just stay in postgres), and not [exotic solution -> postgres].

Yes, the nosql fad that swept the industry was nearly as insufferable as the SPA craze that followed alongside. Now everyone's back to tried and true. Most data once more sits in RDBMS and most html gets render on the server.

Us grizzled genX devs saw this coming a decade ago.

Re: Migrating to Postgres

#217

Post by monkeyelite »

maz1b wrote: post #146
monkeyelite wrote: post #35

Earlier quoted context omitted.

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.

Yes. What do you have in mind?

Re: Migrating to Postgres

#218

Post by abraxas »

sgarland wrote: post #204
crowcroft wrote: post #193

Why does Postgres get so much love, and MySQL/MariaDB get nothing? I'm assuming it's largely because Postgres has more momentum, and is much more extensible, but if you're just trying to do 'boring DB stuff' I find it's faster for most use cases. Development has slowed, but it would be hard to argue that it's not battle tested and robust.

Because MySQL got a (rightfully so) bad rap before it adopted InnoDB as the default storage engine, and then tech influencers happened. I love Postgres, but I also love MySQL, and 99% of the time I see people gushing about Postgres, they aren’t using any features that MySQL doesn’t have. The single biggest thing for MySQL that should be a huge attraction for devs without RDBMS administration experience is that MySQL,…

To the contrary when the PK has to be a BTree it already ties my hands because I can't have good disk layout for say, time series data where I might use a ligher index like BRIN at a cost of somewhat slower queries but much better index update rates.

Re: Migrating to Postgres

#219

Post by rwyinuse »

merek wrote: post #144
nop_slide wrote: post #113

Earlier quoted context omitted.

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.

I'm not sure what is the point of using MongoDB these days, when you can as easily store and query jsonb in postgres.

Re: Migrating to Postgres

#220

Post by hn_throw2025 »

sitharus wrote: post #123
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…

I don't hate prisma - it's just a tool - but that's far from the only limiting factor. I recently looked at migrating a legacy project with basic SQL query generation to a modern ORM. Prisma came up top of course so I tried it. We use Postgres built-in range types. Prisma does not support these, there's no way to add the type to the ORM. You can add them using "Unsupported", but fields using that aren't available in…

Some of those criticisms are out of date.

> It also requires a binary to run, which would require different builds for each architecture deployed to.

https://www.prisma.io/blog/from-rust-to-typescript-a-new-cha...

> That coupled with their attitude to joins

https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-th...

As another poster has mentioned, a thing Prisma has over the others is type safety if you use the raw SQL escape hatch for performance reasons.

Post reply on HN