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

151–160 of 278 posts

Re: Migrating to Postgres

#151

Post by nasretdinov »

luhn wrote: post #72
icedchai wrote: post #68

Earlier quoted context omitted.

You don't even need to be that "modern." Back in 2010 I was working on a MySQL 5.x system with about 300 million rows on a dual Xeon box with 16 gigs RAM and a few hundred gigs of RAID 10. This was before SSDs were common. The largest table was over 100 million rows. Some migrations were painful, however. At that time, some of them would lock the whole table and we'd need to run them overnight. Fortunately, this was…

The improvements to migrations have been the biggest boon for running even modestly-sized Postgres DBs. It wasn't that long ago that you couldn't add a column with a default value without rewriting the whole table, or adding NOT NULL without an exclusive lock while the whole table was scanned. That becomes unfeasible pretty quickly.

Does adding a default value into a column finally work without locking up an entire table now at least?

Re: Migrating to Postgres

#152

Post by williamdclt »

nasretdinov wrote: post #151
luhn wrote: post #72

Earlier quoted context omitted.

The improvements to migrations have been the biggest boon for running even modestly-sized Postgres DBs. It wasn't that long ago that you couldn't add a column with a default value without rewriting the whole table, or adding NOT NULL without an exclusive lock while the whole table was scanned. That becomes unfeasible pretty quickly.

Does adding a default value into a column finally work without locking up an entire table now at least?

Yes, it has for a few years :) There's almost always non-locking ways to migrate things with Postgres now

Re: Migrating to Postgres

#153

Post by williamdclt »

casper14 wrote: post #59
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…

Nice! What optimizations have you put in llace yo support 150 mil? Just some indexing or other fancy stuff?

You really don't need anything special. 150M is just not that much, postgres has no problem with that.

Obv it depends on your query patterns

Re: Migrating to Postgres

#154

Post by cies »

bigfatkitten wrote: post #134
gbear605 wrote: post #79

Earlier quoted context omitted.

I’ve seen startups with a thousand active users paying $50k/month (though that’s overall costs, not just db). It’s really easy to waste a lot of money doing nothing.

It’s especially easy to waste money on databases. People just throw more compute power (ie money) at performance problems, rather than fixing their queries or making better use of indices.

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

Re: Migrating to Postgres

#155

Post by cies »

monero-xmr wrote: post #140
ringeryless wrote: post #133

Earlier quoted context omitted.

OTOH they are admittedly using an ORM (Prisma, known for its weight)

It is truly amazing how mature developers always wind up at the same result - old tech that has stood the test of time. Betting the company on alpha solutions of dubious quality keeps the devs employed at least.

> It is truly amazing how mature developers ...

...use ORMs!

They are always bad, but especially bad when the code base and/or query complexity grows.

https://dev.to/cies/the-case-against-orms-5bh4

Re: Migrating to Postgres

#156

Post by williamdclt »

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…

Yeah, 100mil is really not that much. I worked on a 10B rows table on an rds r6g.4xl, and Postgres handled it fine, even with 20+ indexes. Really not ideal and I'd rather have fewer indexes and sharding the table, but postgres dealt with it.

Re: Migrating to Postgres

#157

Post by williamdclt »

Tadpole9181 wrote: post #56
compton93 wrote: post #49

Earlier quoted context omitted.

They've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.

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.

Re: Migrating to Postgres

#158

Post by williamdclt »

amazingamazing wrote: post #9
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…

I don't disagree with your point, but over normalization and joining everywhere also isn't necessarily the answer, even with an index. there's no easy answer to this, really depends on the performance characteristics the critical user journeys need. with a little pain, if I had to pick an extreme, I'd pick extreme normalization with materialized views that are queried (e.g. no joins), rather than joining all of the t…

> materialized views

how though? Postgres doesn't support auto-refreshing materialized views when the underlying data changes (with good reasons, it's a really hard problem)

Re: Migrating to Postgres

#159

Post by eftpotrm »

I can't help thinking more startups need greybeards around. (Of which, realistically, I'm now one.)

Largest table 100 million rows and they were paying 6 figures for database services annually? I have one now that sits happily enough on an 8yo laptop. I've worked on systems that had similar scale tables chugging along on very average for 20 years ago MSSQL 2000 boxes. There just isn't a need for cloud scale systems and cloud scale bills for that data volume.

The problems they're describing should never have got that far without an experienced hand pointing out they didn't make sense, and if they'd hired that greybeard they'd have spotted it long before.

Re: Migrating to Postgres

#160

Post by DonHopkins »

speed_spread wrote: post #81
betaby wrote: post #47

Earlier quoted context omitted.

Here you go https://www.uber.com/en-CA/blog/postgres-to-mysql-migration/

It's a very Uber thing to do to enter a one way from the wrong end.

At least they have the moxie to flip off people going the right way.

https://www.youtube.com/watch?v=Tz6A6t55qXk

Post reply on HN