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

191–200 of 278 posts

Re: Migrating to Postgres

#191

Post by lesuorac »

etblg wrote: post #20
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

> 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.

Can't speak about Prisma (or Postgres much).

But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not).

Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.

Re: Migrating to Postgres

#192

Post by sgarland »

ketzo wrote: post #69
sroussey wrote: post #18

Earlier quoted context omitted.

Most people don’t need multi-region read architecture for that matter. SaaS app devs at 5 person companies really want to do “Facebook” scale problems.

I think this is kinda reductive. If you’ve got users all over the world, then global DB replicas are a sizable performance improvement, whether you’ve got 1 engineer or 1,000.

Yes, global read replicas can be helpful, though the replica lag needs to be thoughtfully considered.

IMO, one of the worst offenders is places with microservices, who absolutely do not need microservices, and are doing reads-after-writes.

For every RDBMS I know of other than MySQL (even MariaDB has this), you can tack a RETURNING clause onto the write, and get the entire row back. This shouldn’t be necessary for most cases anyway, because by definition, you have (almost) all of the row data already. The only times you wouldn’t is DB-generated values like an auto-incrementing ID, UUID from the DB, or a timestamp from the DB. MySQL can actually handle the first case; the cursor holds LAST_INSERT_ID. So if you had a monolith, it’s trivial to pass what you wrote to the next function that needs it – boom, no concern about stale data, and one fewer DB round trip.

Even with separate services, this is still possible, though somewhat slower, but I’ve not seen anyone do it.

Re: Migrating to Postgres

#193

Post by crowcroft »

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.

Re: Migrating to Postgres

#194

Post by arunix »

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…

Also, the Query Object style, e.g. JOOQ and SQLAlchemy Core

https://martinfowler.com/eaaCatalog/queryObject.html

Re: Migrating to Postgres

#195

Post by ahartmetz »

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.

Funny that it was the other way around 20 years ago. Everybody was using MySQL, but there were many blog posts and such about the looseness of MySQL with the SQL standard and other issues. And that people should use Postgres unless they need the replication features of MySQL. AFAIU, replication is still the main (good) reason to use MySQL, though there are some semi-proprietary(?) solutions for Postgres.

Re: Migrating to Postgres

#196

Post by venantius »

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.

I am not an expert, but I have worked somewhere MariaDB/MySQL was being used at scale.

My preference today for Postgres comes down to the fact that its query planner is much easier to understand and interface with, whereas MySQL/Maria would be fine 29/30 times but would then absolutely fuck you with an awful query plan that you needed a lot of experience with to anticipate.

Re: Migrating to Postgres

#197

Post by sgarland »

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…

Agreed. Devs usually do a double take when I tell them that their table with 100K rows is not in fact big, or even medium. Everyone’s experiences are different, of course, but to me, big is somewhere in the high hundreds of millions range. After a billion it doesn’t really matter; the difference between 5 billion and 1 billion isn’t important, because it’s exceedingly unlikely that a. Your working set is that large b. That your server could possibly cope with all of it at once. I hope you have partitions.

Re: Migrating to Postgres

#198

Post by crowcroft »

ahartmetz wrote: post #195
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.

Funny that it was the other way around 20 years ago. Everybody was using MySQL, but there were many blog posts and such about the looseness of MySQL with the SQL standard and other issues. And that people should use Postgres unless they need the replication features of MySQL. AFAIU, replication is still the main (good) reason to use MySQL, though there are some semi-proprietary(?) solutions for Postgres.

Yea, Postgres really came up when Node and Mongo did. During the PHP/RoR era MySQL was a very clear winner.

I still think MySQL is a better choice for most web apps due to performance, but more general use cases I can understand the debate.

Re: Migrating to Postgres

#199

Post by sgarland »

aitchnyu wrote: post #185
cies wrote: post #154

Earlier quoted context omitted.

And do not consider owning the hardware. See what hardware you can buy for that money.

How many servers are needed to bounce back from a server failure in a few minutes? Should we consider 3 VMs instead of 1 physical?

Two. Run one in warm/hot standby, optionally with synchronous commits if you want (nearly) guaranteed zero data loss, and can tolerate the increased write latency.

Technically you’ll need a third server to perform the failover, but it doesn’t need to be nearly as big, as it’s just watching heartbeats and issuing commands.

Re: Migrating to Postgres

#200

Post by cies »

aitchnyu wrote: post #185
cies wrote: post #154

Earlier quoted context omitted.

And do not consider owning the hardware. See what hardware you can buy for that money.

How many servers are needed to bounce back from a server failure in a few minutes? Should we consider 3 VMs instead of 1 physical?

I think these days it's more: were do we find the grey old unix guy who confidently will host your db for you on bare metal.
Post reply on HN