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

251–260 of 278 posts

Re: Migrating to Postgres

#251

Post by sroussey »

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.

A performance improvement at a huge cost, sure.

People with no data seem to think that these things matter to the end user.

The only time uptime and performance matter is at point of purchase.

Re: Migrating to Postgres

#252

Post by TheNewsIsHere »

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…

When I was running tech for a (tiny) nonprofit we self-hosted a geographic database because it was cheaper and easier.

There was something like 120 million rows in the database. It ran on a single VM. It really needed the indexes, but once those were built it just sang.

This was easily 10+ years ago.

Re: Migrating to Postgres

#253

Post by vvpan »

campbellmorgan wrote: post #142
vvpan wrote: post #117

Earlier quoted context omitted.

How do you do typed raw queries?

https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/r... - it assumes that these queries return arrays and there's a template you can pass in like this: prisma.$queryraw `SELECT * FROM ...`

Oh, just manual template typing. That's not great. We've done that and it is error-prone. A SQL-generator infers the types from the query, for example.

Re: Migrating to Postgres

#254

Post by Tadpole9181 »

vvpan wrote: post #253
campbellmorgan wrote: post #142

Earlier quoted context omitted.

https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/r... - it assumes that these queries return arrays and there's a template you can pass in like this: prisma.$queryraw `SELECT * FROM ...`

Oh, just manual template typing. That's not great. We've done that and it is error-prone. A SQL-generator infers the types from the query, for example.

That's the raw, raw querying. They provide a TypedSQL system where you write SQL and it automatically generated the parameter and return types. It's in the docs.

Re: Migrating to Postgres

#255

Post by Tadpole9181 »

cess11 wrote: post #167
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

"Instead, it sends individual queries and joins the data on the application level. However, this doesn't mean that Prisma's relational queries are per se slower" Wow, what the fuck. "Also, this chapter about Query Performance Optimization from the High Performance MySQL book has some great insights. One of the techniques it mentions is JOIN decomposition: Many high-performance web sites use join decomposition. You ca…

The application joins are (soon to be were) done in Rust service that's side loaded with the node server.

Also, this is an old quote. Databases didn't all support things like JSON at the time, so joins and subqueries presented an N+1 problem and could balloon data fetch requirements fairly easily. Being a GraphQL-focused ORM originally too, this made some sense.

The default is now being changed and correlated subqueries, JOINs, & JSON aggregation will replace the old approach unless explicitly toggled.

Re: Migrating to Postgres

#256

Post by bigfatkitten »

cies wrote: post #200
aitchnyu wrote: post #185

Earlier quoted context omitted.

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.

Which these days seems to mean a 35+ year old who has ever worked on anything other than a big-three cloud.

Re: Migrating to Postgres

#257

Post by frollogaston »

caffeinated_me wrote: post #36
frollogaston wrote: post #24

Earlier quoted context omitted.

I'm not the most experienced in huge DBs and can't write anything off, but I've never seen a horizontally sharded DBMS work well, even Citus which allegedly does. There's always been a catch that seems worse than manually doing sharding at a higher level than your DB, not that that's easy either.

I'd argue that horizontally sharded databases can work well, but they do tend to have significant non obvious tradeoffs that can be pretty painful. There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that co…

I can imagine it for some constrained use case, but taking your typical RDBMS that's powering a variety of business logic with complex queries, I dunno.

One interesting tradeoff Postgres and MySQL made for efficiency's sake was making xacts not fully ACID by default; instead they guarantee something that's good enough as long as you keep it in mind. Cockroach and Spanner are fully ACID, but that means even if you used those as a single-node DB, it ought to be slower.

Re: Migrating to Postgres

#258

Post by NegativeLatency »

hobs wrote: post #6
NegativeLatency wrote: post #4

Earlier quoted context omitted.

Hoping for more easy columnar support in databases, which is one of the things that can lead you to storing json in database columns (if your data is truly columnar). Currently the vendor lock-in or requirements for installing plugins make it hard to do with cloud sql providers. Especially hard since by the time it's a problem you're probably at enough scale to make switching db/vendors hard or impossible.

How does columnar = json? json isn't colunar at all... If you just want to have a schema in json instead of sql, use a no-sql db, postgres nosql features are strong, but the db features are actually much stronger.

json isn't necessarily columnar, but it is a natrual fit for stuff that is columnar that's otherwise harder to model in a traditional relational db

here's my usecase:

- we have a bunch of attributes (all different names by customer, and many different values for each record that a customer stores)

- it's a fairly natural fit for a json value with only one level of key: value mapping

- we use mysql on GCP (no columnar plugins, too hard to switch to postgres)

Someone could go back in time and correctly model it as columns and not json but that ship has sorta sailed. While it's not impossible to change, it would be pretty hard, time will tell if that ends up happening.

I would love to be able to tell mysql "put this column in a collumnar engine and use that when I query on it" (AlloyDB is this for postgres on GCP)

Re: Migrating to Postgres

#259

Post by NegativeLatency »

moonikakiss wrote: post #90
NegativeLatency wrote: post #4

Earlier quoted context omitted.

Hoping for more easy columnar support in databases, which is one of the things that can lead you to storing json in database columns (if your data is truly columnar). Currently the vendor lock-in or requirements for installing plugins make it hard to do with cloud sql providers. Especially hard since by the time it's a problem you're probably at enough scale to make switching db/vendors hard or impossible.

great point. with pg_mooncake v0.2 (launching in ~couple weeks), you'll be able to get a columnar copy of your Postgres that's always synced ( Keep your write path unchanged, and keep your Postgres where it is. Deploy Mooncake as a replica for the columnar queries.

I wish, someone unfortunately picked mysql

Re: Migrating to Postgres

#260

Post by reissbaker »

rrr_oh_man wrote: post #126
reissbaker wrote: post #19

Earlier quoted context omitted.

Eh, I've run applications on RDBMSes with multi-billion-row tables, and I've never found normalization/denormalization to be particularly impactful on performance except for in a few rare cases. The biggest impact came from sensible indexing + query patterns. Normalization vs denormalization had a big impact on convenience, though (not always favoring one way or the other!). But I'm no fan of Prisma either. Drizzle h…

> I've never found normalization/denormalization to be particularly impactful on performance Really?

Yes, really. Indexing + query patterns are much more important.
Post reply on HN