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

201–210 of 278 posts

Re: Migrating to Postgres

#201

Post by sgarland »

gopalv wrote: post #84
luhn wrote: post #64

Earlier quoted context omitted.

You don't need to optimize anything beyond appropriate indices, Postgres can handle tables of that size out of the box without breaking a sweat.

> Postgres can handle tables of that size out of the box This is definitely true, but I've seen migrations from other systems struggle to scale on Postgres because of decisions which worked better in a scale-out system, which doesn't do so well in PG. A number of well meaning indexes, a very wide row to avoid joins and a large number of state update queries on a single column can murder postgres performance (update s…

That’s not the fault of the DB, though, that’s bad schema design. Avoiding JOINs is rarely the correct approach.

Re: Migrating to Postgres

#202

Post by panzi »

paulryanrogers wrote: post #62
renhanxue wrote: post #37

Earlier quoted context omitted.

If you, for whatever obscure reason, need to preserve whitespace and key ordering, that is you want something that is effectively just a text column, then you should use JSON over JSONB. I can't think of any case at all, no matter how contrived, where you'd want to use the non-B versions of the JSON aggregate functions though.

The non-B JSON can take up less space on disk and less write time complexity.

I wonder if its just used in a query like that if the generated data structure is only in memory anyway and then serialized to JSON to send to the client. I.e. JSON VS JSONB would not make any difference?

Re: Migrating to Postgres

#203

Post by [deleted] »

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.

[deleted]

Re: Migrating to Postgres

#204

Post by sgarland »

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, by and large, doesn’t need much care and feeding. You’re not going to get paged for txid wraparound because you didn’t know autovacuum wasn’t keeping up on your larger tables. Unfortunately, the Achilles heel of MySQL (technically InnoDB) is also its greatest strength: its clustering index. This is fantastic for range queries, IFF you design your schema to exploit it, and don’t use a non-k-sortable PK like UUIDv4. Need to grab every FooRecord for a given user? If your PK is (user_id, ) then congrats, they’re all physically colocated on the same DB pages, and the DB only has to walk the B+tree once from the root node, then it just follows a linked list.

Re: Migrating to Postgres

#205

Post by evantbyrne »

hombre_fatal wrote: post #177
evantbyrne wrote: post #92

Earlier quoted context omitted.

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 sw…

These are important features for a database toolkit to consider. I don't think that it is fair to dismiss an entire category of libraries on the grounds of some implementations being less complete than desired though. If we applied that same standard more generally, then we wouldn't use anything at all, because most software libraries kind of stink.

Re: Migrating to Postgres

#206

Post by evantbyrne »

marcus_holmes wrote: post #122
evantbyrne wrote: post #92

Earlier quoted context omitted.

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…

The "good reason" is that modern web devs do not consider SQL a core skill, and plain do not understand databases. To be a competing modern web framework you have to include an ORM so these people will consider you. Trying to explain to a modern web dev that the optimum data storage structure is not the same as the optimum application layer data structure, so you can't just take one and map them across 1:1 to the oth…

Could this be selection bias? I've never worked with a backend engineer that couldn't write SQL. I've worked on plenty of projects were there were bugs in hand written SQL though.

Re: Migrating to Postgres

#207

Post by hermanradtke »

lesuorac wrote: post #191
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.

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…

In what cases is your join causing a table lock?

Re: Migrating to Postgres

#208

Post by rastignack »

thehappyfellow wrote: post #162
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…

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.

Re: Migrating to Postgres

#209

Post by tilne »

neonsunset wrote: post #189
tilne wrote: post #176

Earlier quoted context omitted.

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.

To be fair, old Entity Framework was on the heavier side. Still much faster than e.g. ActiveRecord but enough for Dapper to be made back then. The gap between them is mostly gone nowadays plus .NET itself has become massively faster and alternate more efficient alternatives got introduced since (Dapper AOT, its main goal is NAOT compatibility but it also uses the opportunity to further streamline the implementation).

Thanks for the context. I’m new to the ecosystem, so it’s valuable to hear thoughts like these from people with more experience with it.

Re: Migrating to Postgres

#210

Post by paulddraper »

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…

Depends on the read/write workload and row size, but yeah after 100-200m rows PostgreSQL vacuums can take a while. And index rebuilding (which you have to do on an active table) too.

It all depends though, sometimes 1b is passe.

But 100m is a good point to consider what comes next.

Post reply on HN