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

171–180 of 278 posts

Re: Migrating to Postgres

#171

Post by h1fra »

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…

Also screamed in my head, I have way more rows than that in a Postgres right now and paying less than $500!

Re: Migrating to Postgres

#172

Post by homebrewer »

tilne wrote: post #34
pier25 wrote: post #30

Earlier quoted context omitted.

> I can't stand ORMs, I don't get why people use it, please just write the SQL. I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.

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. Maybe it is solvable, maybe not, I haven't yet had the time to look into it.

Re: Migrating to Postgres

#173

Post by simianwords »

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.

I don't buy this! Startups do need high availability. If you start having replicas you are already in distributed territory!

Re: Migrating to Postgres

#174

Post by lelanthran »

ketzo wrote: post #63
etblg wrote: post #20

Earlier quoted context omitted.

> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level. ..........I'm sorry, what? That seems........absurd. edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.

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

> I removed a $lookup (the mongodb JOIN equivalent)

There is no "MongoDB JOIN equivalent" because MongoDB is not a relationalal database.

It's like calling "retrieve table results sequentially using previous table's result-set" a JOIN; it's not.

Re: Migrating to Postgres

#175

Post by tilne »

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…

Yeah makes sense. I should have used my language more carefully: I have very little hands-on experience with entity framework, and thus don’t have objective reasons to say it has a “huge” memory footprint.

Does the startup time/CPU usage cost vary depending on the size of the dataset you’re interacting with?

Re: Migrating to Postgres

#176

Post by tilne »

neonsunset wrote: post #48
tilne wrote: post #34

Earlier quoted context omitted.

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

Do you have any links that note memory usage issues with any of the semi-recent EF Core versions?

No. To be clear: I wasn’t trying to say it was bad. Just repeating what I had read in a (fairly old) .net book. Should have chosen my words more carefully.

Re: Migrating to Postgres

#177

Post by hombre_fatal »

evantbyrne wrote: post #92
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…

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…

On the other hand, ORMs insulate you from database integrity since ORMs have limited access to underlying database features.

In Postgres that usually means you're not locking rows, you're not using upsert, you might not be writing table DDL yourself. It often means you aren't even using database transactions.

While these things might be extraneous fluff for an all-nighter hackathon, you really have to figure out a sweet spot so that data integrity violations aren't killing your velocity when your service's rubber begins hitting the road.

Re: Migrating to Postgres

#178

Post by seer »

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?

Check out the docs https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/t... it generates output types, though input types still need to be done by you via type comments in the sql

Re: Migrating to Postgres

#179

Post by robertlagrant »

For all the Prisma-haters: I salute you. But I want to reply to numerous comments with the following:

ORMs come in two main types, that I'm aware of: Active Record (named after the original Ruby one, I think) and Data Mapper (think Hibernate; SQLAlchemy).

Active Record ORMs are slightly more ergonomic at the cost of doing loads of work in application memory. Data Mapper looks slightly more like SQL in your code but are much more direct wrappers over things you can do in SQL.

Data Mapper also lets you keep various niceties such as generating migration code, that stem from having your table definition as objects.

Use Data Mapper ORMs if you want to use an ORM.

Re: Migrating to Postgres

#180

Post by felipemesquita »

robertlagrant wrote: post #179

For all the Prisma-haters: I salute you. But I want to reply to numerous comments with the following: ORMs come in two main types, that I'm aware of: Active Record (named after the original Ruby one, I think) and Data Mapper (think Hibernate; SQLAlchemy). Active Record ORMs are slightly more ergonomic at the cost of doing loads of work in application memory. Data Mapper looks slightly more like SQL in your code but a…

Rails’ Active Record was named after the pattern as described by Martin Fowler:

https://www.martinfowler.com/eaaCatalog/activeRecord.html

Post reply on HN