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

61–70 of 278 posts

Re: Migrating to Postgres

#61

Post by compton93 »

ScalaHanSolo wrote: post #57
coolcase wrote: post #54

Why not optimise the bad queries first? Aside. Job section says not 9-5. What does that mean? Long hours? Or not 9-5 attitude?

Author here. Optimizing bad queries was absolutely part of the issues with the performance. The issue with cockroach was that the visibility into those bad queries was not great. It wasn't until we had the superior tooling from the Postgres ecosystem that we were able to track them down more efficiently.

When you get a chance can you take a look my reply here: https://news.ycombinator.com/item?id=43990502

When I first stepped into a DBA role with CockroachDB I was confused why indexes we obviously need were in unused indexes. It wasn't until I did an explain on the queries I learned the planner was doing zig-zag joins instead.

Re: Migrating to Postgres

#62

Post by paulryanrogers »

renhanxue wrote: post #37
panzi wrote: post #25

Earlier quoted context omitted.

Not sure why those are json_agg() instead of array_agg() in that example. Why would you use a JSON array instead of a native properly typed array? Yes, if you have some complex objects for some reason you can use JSON objects. But those where all just arrays of IDs. Also why was it json_agg() and not jsonb_agg()? Is there any reason on why to use JSON over JSONB in PostgreSQL?

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.

Re: Migrating to Postgres

#63

Post by ketzo »

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.

Not 100% parallel, but I was debugging a slow endpoint earlier today in our app which uses Mongo/mongoose.

I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join

p90 response times dropped from 35 seconds to 1.2 seconds

Re: Migrating to Postgres

#64

Post by luhn »

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 don't need to optimize anything beyond appropriate indices, Postgres can handle tables of that size out of the box without breaking a sweat.

Re: Migrating to Postgres

#65

Post by kevml »

monkeyelite wrote: post #35
frollogaston wrote: post #22

Did I miss something, or does the article not mention anything about sharding in Postgres? Was that just not needed? Also, query planner maturity is a big deal. It's hard to get Spanner to use the indexes you want.

There are probably fewer than 100 websites that couldn’t be a single Postgres instance on nice server hardware, with good caching.

Not everything on the internet is a “website” and then there are several website hosting platforms that aggregate the individual concerns.

Re: Migrating to Postgres

#66

Post by thomasfromcdnjs »

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…

Does mid six figure mean ~$500k?

That sounds insane for a crud app with one million users.

What am I missing?

Re: Migrating to Postgres

#67

Post by monkeyelite »

kevml wrote: post #65
monkeyelite wrote: post #35

Earlier quoted context omitted.

There are probably fewer than 100 websites that couldn’t be a single Postgres instance on nice server hardware, with good caching.

Not everything on the internet is a “website” and then there are several website hosting platforms that aggregate the individual concerns.

All true points. I guess I just want to hear more about why they think sharding is important to them.

Re: Migrating to Postgres

#68

Post by icedchai »

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…

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 for an internal app so we could do that.

Re: Migrating to Postgres

#69

Post by ketzo »

sroussey wrote: post #18
sgarland wrote: post #8

Earlier quoted context omitted.

There are multi-master Postgres options like BDR (I think it’s since renamed; whatever EnterpriseDB calls it now), yes. Most people don’t need it, even if they think they do, and they also usually are in no way capable of dealing with the operational complexity it involves. If you’ve ever administered Postgres at scale, multiply it by 10. That’s what dealing with multi-master is like. It’s a nightmare.

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.

Re: Migrating to Postgres

#70

Post by I_am_tiberius »

moonikakiss wrote: post #23

great blog. It seems like you might benefit from columnar storage in Postgres for that slow query that took ~20seconds. It's interesting that people typically think of columnstores for strict BI / analytics. But there are so many App / user-facing workloads that actually need it. ps: we're working on pg_mooncake v0.2. create a columnstore in Postgres that's always consistent with your OLTP tables. It might help for t…

That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.
Post reply on HN