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

241–250 of 278 posts

Re: Migrating to Postgres

#241

Post by simianwords »

sreekanth850 wrote: post #240
simianwords wrote: post #173

Earlier quoted context omitted.

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

>Startups do need high availability. HA is important. But Postgres and MySQL both support HA and replication setups without needing to jump straight into a distributed SQL (In this context of using cockroach). We use MySQL Innodb cluster + MySQL router with auto failover on single primary mode. > If you start having replicas you are already in distributed territory. But it’s not the same as a distributed database wit…

do you still not need quorum writes?

Re: Migrating to Postgres

#242

Post by sreekanth850 »

simianwords wrote: post #241
sreekanth850 wrote: post #240

Earlier quoted context omitted.

>Startups do need high availability. HA is important. But Postgres and MySQL both support HA and replication setups without needing to jump straight into a distributed SQL (In this context of using cockroach). We use MySQL Innodb cluster + MySQL router with auto failover on single primary mode. > If you start having replicas you are already in distributed territory. But it’s not the same as a distributed database wit…

do you still not need quorum writes?

That depends. You don’t need quorum writes with traditional Postgres or MySQL HA setups. In most cases, writes go to a single primary, and replication to standbys is asynchronous or semi-synchronous, depending on your tolerance for potential data loss.

It’s all about finding the right balance. With modern vertically scalable hardware and fast SSDs, a single-node setup can handle quite a lot of load before hitting real limits with a failover setups.

Re: Migrating to Postgres

#243

Post by simianwords »

sreekanth850 wrote: post #242
simianwords wrote: post #241

Earlier quoted context omitted.

do you still not need quorum writes?

That depends. You don’t need quorum writes with traditional Postgres or MySQL HA setups. In most cases, writes go to a single primary, and replication to standbys is asynchronous or semi-synchronous, depending on your tolerance for potential data loss. It’s all about finding the right balance. With modern vertically scalable hardware and fast SSDs, a single-node setup can handle quite a lot of load before hitting rea…

Thanks, I agree there's a balance. I'm having trouble believing startups don't to be protected from such dataloss. It makes perfect sense (at least to me) for startups to use some standard NoSQL cloud db's like DynamoDB.

If you are a startup you can focus on product and leave the nitty grittys to an abstraction by paying more money.

Re: Migrating to Postgres

#244

Post by geoka9 »

rastignack wrote: post #208
thehappyfellow wrote: post #162

Earlier quoted context omitted.

It’s incredible how much Postgres can handle. At $WORK, we write ~100M rows per day and keep years of history, all in a single database. Sure, the box is big, but I have beautiful transactional workloads and no distributed systems to worry about!

At $WORK, we are within the range of 2 billion rows per day on one of our apps. We do have beefy hardware and ultra fast SSD storage though.

Those rows are never pruned and rarely read?

Re: Migrating to Postgres

#245

Post by electroly »

hliyan wrote: post #82
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…

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…

From the point of view of an SQL engine in 2025, 100 million rows is a tiny table. You can add a surprising number of zeroes onto that figure and a single modest SQL node will handle it with no special effort. 100 billion, with a B, is not difficult on a single beefy node today. I think your points all still stand but consider refreshing the actual numbers. I personally start getting "the itch" around 10 billion (or if it looks like it's going to become 10 billion) these days. If a table gets there, I better have a plan to do something else.

I had a 200 billion row table that was operationally still manageable but, IMO, I had allowed to grow out of control. The enterprise storage costs a fortune. Should have nipped that in the bud by 20 billion at the latest.

Re: Migrating to Postgres

#246

Post by gilbetron »

abraxas wrote: post #216
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.

As a grizzled genX dev myself, we are in a different situation now - "nosql" (hate the term) has tremendous use cases, it's just that most people aren't creating something that requires it. It was a natural exploration of the tools, something that should be encouraged. "I knew it all along" isn't an attitude I find helpful or effective. My "grizzled genX dev" attitude is that nearly all people think they know what is going to happen or what is the best route, and they are almost always entirely wrong. We only find out by trying a bunch of things.

In other words, there are many companies currently worth $Billion+ that wouldn't have succeeded had they followed your advice. Today, with incredibly powerful infra of all types available, starting with Postgres is almost always the right step unless you know, know, better. That wasn't the case 10+ years ago.

Re: Migrating to Postgres

#247

Post by JackSlateur » 

  there were concerns about the eventuality of a multi-region setup (mandated by GDPR)
Can anyone share more details about this ? The GDPR is mandating a multi-region setup ? This sounds very wild

Re: Migrating to Postgres

#248

Post by Tadpole9181 »

williamdclt wrote: post #157
Tadpole9181 wrote: post #56

Earlier quoted context omitted.

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

Honestly, everything you say makes me want to stay far from prisma _more_. All this complexity, additional abstractions and indirections, with all the bugs gootguns and gotchas that come with it... when I could just type "JOIN" instead.

Okay? It's one setting that will be the default in 2 months. And you could always write type-safe SQL manually instead.

i greatly envy y'all having projects where the biggest complexity is... A single setting once, that's clearly documented. We live in very different worlds, apparently.

Re: Migrating to Postgres

#249

Post by Tadpole9181 »

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.

And, in light of that, the default is changing in a couple of months after the JOIN mode has had a significant period of time being tested in the real world.

Re: Migrating to Postgres

#250

Post by perrygeo »

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…

Mid 6 figure DB bill, let's estimate $500k. Divided into 100 million rows (ignore the rest, because db provisioning is typically dominated by the needs of a few core tables). They get 200 rows per dollar.

Your table on a small VPS (which I concur is totally reasonable, am running something similar myself): Let's say your VPS costs $40/mo x 12 = $480/yr. Divide into 150 million. You get 312,500 rows per dollar.

I'd wager you server was faster under normal load too. But is it webscale? /s

There's waste, then there's "3 orders of magnitude" waste. The pain is self-inflicted. Unless you have actual requirements that warrant a complex distributed database, you should "just use postgres".

And just to calibrate everyone's expectations, I've seen a standard prod setup using open source postgres on AWS EC2s (1 primary, 2 replicas, 1 haproxy+pgbouncer box to load balance queries) that cost ~ $700k annually. This system was capable of handling 1.2 million rows inserted per second, while simultaneously serving thousands of read queries/s from hundreds of internal apps across the enterprise. The cost effectiveness in their case came out to ~ 20k rows per dollar, lower than your VPS since the replicas and connection pooling eat into the budget. But still: 2 orders of magnitude more cost effective than the hosted distributed hotness.

Post reply on HN