5 ms·
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/
by esafak 2y ago
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 https://github.com/prisma/prisma/discussions/19748
- etblg 2y ago> 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.
- compton93 2y agoIt 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.
- Tadpole9181 2y agoIt's pretty obvious when somebody has only heard of Prisma, but never used it. - Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting. - Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application. - They are in the process of removing the Rust layer. The JOIN setting and the removing of the middleware service are going to be defaults soon, they're just in preview.
- compton93 2y agoThey'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.
- Tadpole9181 2y agoThe 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.prisma.io/blog/try-the-new-rust-free-version-of-prisma-orm-early-access https://www.prisma.io/blog/try-the-new-rust-free-version-of-...
- paulddraper 2y agoIt is hard. Harder than just doing joins.
- compton93 2y agoThey've had flags and work arounds for ages. Not sure what point you are trying to make? But like you said I've never used it, only heard of it lol.
- williamdclt 1y agoHonestly, 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.
- Tadpole9181 1y agoOkay? It's one setting that will be the default in 2 months. And you could always write type-safe SQL manually instead. i greatly envy y'all having projects where the biggest complexity is... A single setting once, that's clearly documented. We live in very different worlds, apparently.
- jjice 2y agoI 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…
- etblg 2y ago> 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.
- Tadpole9181 2y ago> 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 This is answered at the very top of the link on the post you replied to. In no unclear language, no less. Direct link here: https://github.com/prisma/prisma/discussions/19748#discussioncomment-6171725 https://github.com/prisma/prisma/discussions/19748#discussio... > I want to elaborate a bit on the tradeoffs of this decision. The reason Prisma uses this strategy is because in a lot of real-world applications with large datasets, DB-level JOINs can become quite expensive... > The total cost of executing a complex join is often higher than executing multiple simpler queries. This is why the Prisma query engine currently defaults to multiple simple queries in order to optimise overall throughput of the system. > But Prisma is designed towards generalized best practices, and in the "real world" with huge tables and hundreds of fields, single queries are not the best approach... > All that being said, there are of course scenarios where JOINs are a lot more performance than sending individual queries. We know this and that's why we are currently working on enabling JOINs in Prisma Client queries as well You can follow the development on the roadmap. Though this isn't a complete answer still. Part of it is that Prisma was, at its start, a GraphQL-centric ORM. This comes with its own performance pitfalls, and decomposing joins into separate subqueries with aggregation helped avoid them.
- pier25 2y ago> 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.
- tilne 2y agoDoesn’t entity framework have a huge memory footprint too?
- neonsunset 2y agoDo you have any links that note memory usage issues with any of the semi-recent EF Core versions?
- tilne 1y agoNo. 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.
- neonsunset 1y agoTo 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).
- tilne 1y agoThanks 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.
- homebrewer 1y agoIf you don't do stupid things like requesting everything from the database and then filtering data client side, then no. We have one application built on .NET 8 (and contemporary EF) with about 2000 tables, and its memory usage is okay. The one problem it has is startup time: EF takes about a minute of 100% CPU load to initialize on every application restart, before it passes execution to the rest of your program. Maybe it is solvable, maybe not, I haven't yet had the time to look into it.
- ketzo 2y agoNot 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
- nop_slide 2y agoMaybe because mongo isn’t ideal for relational data?
- wredcoll 2y agoDoes mongodb optimize joins at all? Do they even happen server side?
- merek 1y agoI believe a lot of Mongo's criticisms come from people modelling highly relational data on a non-relational DB.
- rwyinuse 1y agoI'm not sure what is the point of using MongoDB these days, when you can as easily store and query jsonb in postgres.
- lelanthran 1y ago> I removed a $lookup (the mongodb JOIN equivalent) There is no "MongoDB JOIN equivalent" because MongoDB is not a relationalal database. It's like calling "retrieve table results sequentially using previous table's result-set" a JOIN; it's not.
- lesuorac 1y agoCan'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 not). Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.
- hermanradtke 1y agoIn what cases is your join causing a table lock?
- frollogaston 2y agoI'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.
- banashark 2y agoVitess and planetscale seem to have quite a number of high profile users who have lauded its capabilities. A search through hn history pops up a few. As someone who has primarily worked with Postgres for relational concerns, I’ve envied the apparent robustness of the MySQL scaling solutions.
- caffeinated_me 2y agoI'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 covered more use cases effectively. Disclaimer: former Citus employee
- frollogaston 1y agoI 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.
- pier25 2y agoPrisma is so bad... can you believe it's by far the most downloaded ORM in NPM?
- VWWHFSfQ 2y agoEvery ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator of all the database backends that they can support. I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever? Nah. Just write the good code for your database.
- pier25 2y agoI use PG with Entity Framework in .NET and at least 90% of my queries don't need any PG-specific features. When I need something PG specific I have options like writing raw SQL queries. Having most of my data layer in C# is fantastic for productivity and in most cases the performance compared to SQL is negligible.
- romanhn 2y agoEntity Framework really is such a time saver. The PG adapter makes it a breeze not just with common queries, but also more recent stuff, like working with embeddings for vector search.
- theshrike79 1y agoComing from Javaland to C#, Entity Framework is a breath of fresh air. The Npgsql driver automatically applies PG-specific tricks without me having to do anything special The only path I had to do myself is the data ingress point that had some race condition issues, everything else seems to perform pretty well out of the box.
- 2y ago
- coverj 2y agoI didn't mind prisma for managing the schema etc but also seen your linked github issue. I found other people recommend combining Prisma with Kysley. I have only used this in toy projects so take this with a grain of salt. https://kysely.dev/ https://kysely.dev/ https://github.com/valtyr/prisma-kysely https://github.com/valtyr/prisma-kysely
- ScalaHanSolo 2y agoAuthor here. Yeah, that's not a bad take away either. I've also been really vocal in Primsa issues for all sorts of things. We are about to embark on a big migration away from Prisma and onto Drizzle once the Drizzle team lands 1.0 We will absolutely share our findings when that migration happens!
- redcobra762 1y agoYes, moving to a freshly 1.0 tool/library is often the best way to gain stability...
- RadiozRadioz 1y agoThat just sounds irresponsible. The correct choice for prod isn't "the cool new trendy thing that will solve all our problems once it hits 1.0", the correct choice is "the boring stable thing that has existed long enough for everyone to understand its shortcomings".
- vvpan 2y agoI am in a startup that's using Prisma and it we really wish we had not: - The query objects can become hard to read with anything more or less complex. - If you need an unsupported Postgres extension you are out of luck. - One large file in a schema, impossible to shard. - We have many apps in a monorepo and they cannot have separate prisma connections cause the schema gets baked into "@prisma/client" Basically the only thing useful about it are the TS types which is something SQL-builder libraries solve better. Long story short, use Kysely, Prisma provides no value that I see.
- cess11 1y ago"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 can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application." This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing.
- Tadpole9181 1y agoThe 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.
- cess11 1y agoAs I understand it the N+1 problem is when you select a set and then perform another query per row in the set. Executing a join is a solution to this, not a cause.
- jjani 1y ago> This belief that they can write JavaScript that outperforms decades of bare-metal executed optimisations in mainstream database engines is just astonishing. In my first job fresh out of uni, I worked with a "senior" backend developer who believed this. He advocated for crusty, bug-ridden ORMs like Sequelize and Prisma (still very early stage back then, so lots more issues than now though I'd still steer well clear of it). Claiming they did "query optimizations". I knew it made zero sense, but also that I wasn't going to be able to convince him.