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

21–30 of 278 posts

Re: Migrating to Postgres

#21

Post by compton93 »

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.

It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.

Re: Migrating to Postgres

#22

Post by frollogaston »

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.

Re: Migrating to Postgres

#23

Post by moonikakiss »

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 this workload.

Re: Migrating to Postgres

#24

Post by frollogaston »

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

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.

Re: Migrating to Postgres

#25

Post by panzi »

hobs wrote: post #2

It still makes me sad when half the queries I see are json_* - I know its far too late, but a big sad trombone in query performance is constantly left joining to planner queries that are going to give you 100 rows as an estimate forever.

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?

Re: Migrating to Postgres

#27

Post by jjice »

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.

I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation.

Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…

Re: Migrating to Postgres

#28

Post by compton93 »

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…

What are your thoughts on Fujitsu's VCI? I typically work for ERP's but im always advocating to offload the right queries to columnar DB's (not for DB performance but for end user experience).

Re: Migrating to Postgres

#29

Post by etblg »

jjice wrote: post #27
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.

I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation. Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memo…

> event distincts were done in memory! I can’t fathom the decision making there…

This is one of those situations where 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, or if they just made really bad decisions. I just don't get it, it feels so wrong.

Re: Migrating to Postgres

#30

Post by pier25 »

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.

> I can't stand ORMs, I don't get why people use it, please just write the SQL.

I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.

Post reply on HN