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

261–270 of 278 posts

Re: Migrating to Postgres

#261

Post by sitharus »

hn_throw2025 wrote: post #220
sitharus wrote: post #123

Earlier quoted context omitted.

I don't hate prisma - it's just a tool - but that's far from the only limiting factor. I recently looked at migrating a legacy project with basic SQL query generation to a modern ORM. Prisma came up top of course so I tried it. We use Postgres built-in range types. Prisma does not support these, there's no way to add the type to the ORM. You can add them using "Unsupported", but fields using that aren't available in…

Some of those criticisms are out of date. > It also requires a binary to run, which would require different builds for each architecture deployed to. https://www.prisma.io/blog/from-rust-to-typescript-a-new-cha... > That coupled with their attitude to joins https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-th... As another poster has mentioned, a thing Prisma has over the others is type safety if you use the…

The binary change is after we made the decision, but the lack of support for some postgresql builtins and no way to add it is still a deal-breaker.

I guess the join one is from the internet memory, good to know for the next time!

Re: Migrating to Postgres

#262

Post by marcus_holmes »

evantbyrne wrote: post #206
marcus_holmes wrote: post #122

Earlier quoted context omitted.

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.

Basic SQL yeah, probably. Actually knowing SQL and how to write good SQL and understanding the tradeoffs between different query plans (and the tradeoffs between different schemas). That's rare.

And yeah, obviously, there will be bugs in your SQL. And writing good tests for the database layer is always tricky because there's usually some pushback to setting up a database on the CI instance, and so on. It's not simple, but ignoring it by using an ORM doesn't make it simpler, it just means you have less options when it goes wrong.

Re: Migrating to Postgres

#263

Post by SchemaLoad »

999900000999 wrote: post #232
graealex wrote: post #136

Earlier quoted context omitted.

Wait until you hear about ORMs.

I still have to create tables. I still have to migrate tables, these are all things I don't need to worry about with firebase. It all depends on what you need to actually do. The only real weakness of Firebase is the Google lock in.

You still have to worry about data migrations without a schema though. Unless you just never access old records.

Re: Migrating to Postgres

#264

Post by 999900000999 »

SchemaLoad wrote: post #263
999900000999 wrote: post #232

Earlier quoted context omitted.

I still have to create tables. I still have to migrate tables, these are all things I don't need to worry about with firebase. It all depends on what you need to actually do. The only real weakness of Firebase is the Google lock in.

You still have to worry about data migrations without a schema though. Unless you just never access old records.

Depends.

Let's say you have an object called box.

It has 3 properties, the id, it's name and it's weight.

4 months later you add a 4th property. Cost, odd records that don't have a cost just return null for that field.

Re: Migrating to Postgres

#265

Post by const_cast »

KronisLV wrote: post #237
cies wrote: post #155

Earlier quoted context omitted.

> It is truly amazing how mature developers ... ...use ORMs! They are always bad, but especially bad when the code base and/or query complexity grows. https://dev.to/cies/the-case-against-orms-5bh4

> They make the simple queries slightly simpler, but they do not help you for the harder queries. — me I dont think it’s possible to say they’re always bad when offering an example of when they make things better. In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases. For everything else, there is in-database processing or dro…

> In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases.

The "danger" here is the movement towards an object-oriented design instead of a SQL-first approach to querying the database.

In my experience, I have seen ORMs used for these simple CRUD cases and what tends to happen is you have objects with dozens of members when you only need one or two. And then down the line you also start doing JOINs in memory - you just don't recognize it like that.

You see, you get Object X using CRUD and then object Y using CRUD and then you need a way to say which relates to what and... oops now we've done joins in memory. And sometimes, maybe even often times, we're doing all of that for a single field we need in this one place.

Re: Migrating to Postgres

#266

Post by cess11 »

Tadpole9181 wrote: post #255
cess11 wrote: post #167

Earlier quoted context omitted.

"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 subquer…

As 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.

Re: Migrating to Postgres

#267

Post by argulane »

freilanzer wrote: post #164
pojzon wrote: post #83

Earlier quoted context omitted.

I really enjoy this comment. > Postgres is the best answer if you have a solid team and you know what you’re doing. Not every type of data simply fits into relational model. Example: time series data. So depending on your model - pick your poison. But for relational models, there is hardly anything better than postgres now. It makes me happy coz I always rooted for the project from earily 2000s.

Even for timeseries there is https://github.com/timescale/timescaledb . Haven't used it, just knew it existed.

I have used TimescaleDB in my last work place. We needed a easy way to store and visualize 500hz sensor data for few 10s of devices. We used it and Grafana to build a internal R&D tool and it worked way better than I imagined. Before I left I think the DB was using ~200GB on a compressed btrfs volume in DigitalOcean droplet and still performed fine for interactive Grafana usage.

Re: Migrating to Postgres

#268

Post by thehappyfellow »

wvh wrote: post #222
thehappyfellow wrote: post #162

Earlier quoted context omitted.

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!

Two days ago, I'd have said the same. Yesterday, big box went down, and because it was so stable, it was a joint less oiled and the spare chickened out at the wrong time and apparently even managed to mess up the database timeline. Today was the post-mortem, and it was rough. I'm just saying, simple is nice and fast when it works, until it doesn't. I'm not saying to make everything complex, just to remember life is a…

You’re right, there are downsides like turbine you mention! We mitigate it by running a hot backup we can switch to in seconds and a box in which we test restoring backups every 24h, that’s necessary! But it requires 3x the number of big expensive boxes.

I still think it’s the right tradeoff for us, operating a distributed system is also very expensive in terms of dev and ops time, costs are more unpredictable etc.

It’s all tradeoffs, isn’t it?

Re: Migrating to Postgres

#269

Post by KronisLV »

const_cast wrote: post #265
KronisLV wrote: post #237

Earlier quoted context omitted.

> They make the simple queries slightly simpler, but they do not help you for the harder queries. — me I dont think it’s possible to say they’re always bad when offering an example of when they make things better. In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases. For everything else, there is in-database processing or dro…

> In my eyes, it’s pretty simple: use the ORM for the simple CRUD and simple data querying, because you’ll have to do a lot of it in most codebases. The "danger" here is the movement towards an object-oriented design instead of a SQL-first approach to querying the database. In my experience, I have seen ORMs used for these simple CRUD cases and what tends to happen is you have objects with dozens of members when you…

> The "danger" here is the movement towards an object-oriented design instead of a SQL-first approach to querying the database.

That might just be an inherent problem with object oriented languages, you probably have some mapping that you need to do even with raw SQL and before long you'll have the same use cases emerge.

Whether that means any of the following is true:

  OOP is bad for interacting with SQL
  SQL is bad for being used by OOP (or rather, relational databases, maybe not object stores)
  other approaches might help make it less bad, but ORMs don't help in common usage
  maybe we just don't have the right pattern yet, perhaps the "object relational impedance mismatch" folks have a point
I can't tell, not even sure what a proper solution would be.

I've seen what happens when people try to put all of the business logic inside of the DB and it's absolute trash (from the perspective of developer experience, versioning, debugging etc.; the performance was actually stellar) to work with.

Re: Migrating to Postgres

#270

Post by TheNewsIsHere »

gbear605 wrote: post #79
thomasfromcdnjs wrote: post #66

Earlier quoted context omitted.

Does mid six figure mean ~$500k? That sounds insane for a crud app with one million users. What am I missing?

I’ve seen startups with a thousand active users paying $50k/month (though that’s overall costs, not just db). It’s really easy to waste a lot of money doing nothing.

As a small business owner — I recently spent an hour canceling things that just added up over time and that I don’t now need. It’s just so easy to waste money, period.

Directly to your point though, I once encountered a salesperson who was running an entire sandbox environment of a very large platform to the tune of about $25k/mo. It sat idle for almost half a year before someone came knocking. The cloud team did an audit and they were a little spicy about it, understandably.

Post reply on HN