[frollogaston]

This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is:

1. Don't use an ORM.

2. Use serial PKs, not meaningful fields (article mentions this).

3. Use jsonb if needed, but sparingly.

4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secondary denormalized tables that are mutated, but that's only for performance/convenience and shouldn't be your sot.

5. Use connection pools, but be mindful of how many connections you're using. You probably don't need PgBouncer unless you've messed something up.

6. In code, avoid explicit transactions unless there's a clear reason you need them. Usually only need those for denormalized parts. Just take a conn from the pool, do something, commit, return conn to pool. If you're going to keep an xact open, never do long-running stuff in the middle like RPCs. Too often I see people leave xacts open without much thought. Edit: Also don't use SERIALIZABLE xacts almost ever.

7. Something is probably wrong if you're using explicit locking like SELECT FOR UPDATE.

8. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Seems oddly specific, but for some reason someone always tries this.

9. Related to above, don't reinvent a graph DB, typically with "node"/"edge" tables that FK into themselves or in a cycle. 99% of the time what you're trying to do is easily solvable with regular normalized tables.

[ozim]

Don't use an ORM.

Highly debatable. When your highest cost is developers salaries.

Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col.

Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and right now. The same with point no. 9

[sandeepkd]

I would highly recommend using ORM's, with the caveat to know exactly when not to use them. Startups do not fall in that bucket.

1. Most of these advices are unfortunately impractical and incomplete for startups. A good Data Model is highly dependent on understanding the business requirements, data flows. Means unless you are repeating yourself in the same domain its really hard to come up with a good schema in first iteration.

2. Startups are in the mode of discovering the schema for most part

3. Deleting columns is harder than adding additional columns, no one takes that risk so everyone ends up with schema bloat

4. Once you go a little bigger you will realize that the integer based UUID are not that great of a choice. Those are separate tables which maintain those index counters and not part of your DDLs. They have their own set of issues with data merging, backups and recovery

For the OP, I looked at the repo (https://github.com/hatchet-dev/hatchet/blob/main/sql/schema/...) ,

1. seems like the schema has database functions - Thats a potential scaling issue, plus you are asking vertical only scalable component to do something which could have taken care by horizontally scalable component

2. TEXT datatype for pretty much every attribute - this is a footgun, you cant use them for indexes properly, in the absence of length checks they can be abused from client side

[frollogaston]

I've never actually known business requirements ahead of time, when I worked for a startup and for a large company. Stuff happens. You build your schema iteratively and yes, accept some temporary bloat when cols get added thoughtlessly.

The UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.

[waisbrot]

In my experience, ORMs save a little bit of writing SQL and then cost an unbounded quantity of time in debugging mysterious problems because knowing why a query is slow now requires understanding the DB, your own code, and also the ORM.

[ozim]

In my experience ORM doesn’t save anything on writing queries. You still have to write somewhat same code that looks like sql query. You still need to know joins, you still need to know DB structure.

Getting rid of SQL queries is not the job of ORM.

ORM saves you time on object mapping boilerplate THAT IS THE JOB of an ORM it is right there in the name „object relational mapper”.

[frollogaston]

And also more loc than SQL usually. It's not even a deferred cost, it's at least slightly worse upfront

[bob1029]

The best way to cure a developer of their ORM dependency is to put them on a project with a complex OLAP / data warehouse component. OLTP workloads are reasonably well aligned with a lot of ORM patterns so it's more difficult to demonstrate the caveats here (but it's definitely still possible). The limitations of ORMs are much more apparent with OLAP workloads. ORMs simply cannot cope with provider specific requirements. All ORMs fall on their asses when it comes time to load any meaningful amount of data into the provider.

The biggest consequence of forcing the RDBMS through a lazily evaluated object graph is that we've become decoupled from the mechanical realities of what the database provider can do. I had a client quickly drop the "you must use EF" commandment for the project I was assisting on once they saw how many rows it was loading per unit time without EF. "I didn't know that was possible". Many such cases.

[ozim]

Real problem I encounter usually is that business people (and devs) don’t understand OLAP vs OLTP.

They think they can bolt on dashboards or make interface having basically OLAP in the same project and make it perfectly performant.

Here in this thread you have bunch of devs claiming ORM doesn’t work - it works perfectly fine for OLTP. Those devs who claim to be so great knowing SQL also don’t seem to understand that business people are throwing them under bus requiring OLAP stuff to be bolted on OLTP application.

To add on that business people expect OLAP views or dashboards to be „real time” — only after they have dashboard developed to look at it once a month or once a quarter.

What also pains me when I deal with devs is that they pick up synchronization jobs, well batch jobs are fine but then they are scolded by business that batch jobs are slow. Where we have all kinds of tech to make event driven updates to OLAP environments.

[frollogaston]

It was kinda debatable until people started Claude coding everything. Even before, I would've said every SWE should just know SQL, it's not much buy-in to understand the foundation of like your entire backend. Also I'm not a DBA if that's what you meant.

[ozim]

I have never seen a dev and we never would hire a dev that doesn’t know SQL and yet we still use ORM for each and every app we develop.

[InsideOutSanta]

Leaning SQL is arguably less dev work over the long run than learning an ORM and then learning how it works so you can fix performance issues.

[frollogaston]

Yep, there have been extended periods of time where my entire job title might as well have been "ORM remover" because they backed themselves into a corner

[ozim]

Do you have any meaningful experience as a software developer?

That’s a silly take. I somehow learned over the long run: SQL different flavors, couple of ORMs, multiple programming languages and multiple frameworks. Not counting different troubleshooting different operating systems and different applications I had to work with.

[frollogaston]

We all learn whatever we need to learn to deal with the situation. I've had to learn more distinct ORMs than SQL flavors, even though you also need to know the SQL when you use an ORM. Doesn't mean I wanted to.

However, each time a teammate wanted to use an ORM, I didn't call it silly or question their credentials.

[xmprt]

ORMs are just tech debt. Even if your highest cost is developer salaries, you're just pushing that cost down the line.

[kentm]

I'd also argue whether ORMs actually save that much time in practice. In Java, for example, the main time sink is the JDBC plumbing and its easy to use something like JDBI that handles that plumbing without abstracting away the underlying SQL.

The application->database layer is pretty impactful and it pays to pay attention to it, because poor access patterns will cause a lot of trouble in the future, and its made worse by not having a very accurate understanding of whats going on in that layer.

I think a lot of developers don't have a good sense on where their time sinks actually are. Boilerplate is not pleasant to write but also not the timeline-destroyer people tend to think it is. And its often not enough of a time-sink to warrant introducing "magic" that will have very large negative future impacts.

[frollogaston]

Yeah, they'll often conflate the ORM with the nice stuff you want like connection pools. And the thing about time estimates is real. Another thing is they'll optimize too hard for having fewer tables.

[Stitch4223]

For active record patterns I disagree: it’s nice to work with data in a way that feels natural. Chugging simple stuff around in a recognizable way is what you’d end up writing anyway in many applications. Writing your ORM-lite is waste.

When it comes to advanced queries learning the ORM equivalent to the SQL it should write… ORM’s can be outright terrible, and I completely agree with you.

Every ORM has their own names and way of doing things, so this knowledge is hard to port to other ORMs. It’s requires you knowing the right SQL first, then knowing how to write that in the ORM dialect.

Reaching for the more advanced ORM trickery means grasping hard to grasp subjects twice with the risk of misunderstanding twice, and as a bonus worse ORM documentation than plain active record features. Oh, and others need to understand what you’ve written as well.

[swasheck]

you’re going to pay the cost regardless. one approach absorbs the cost up front, and the other defers it, with interest

[KronisLV]

> Highly debatable. When your highest cost is developers salaries.

I think everyone always is needlessly partisan when ORMs get mentioned.

Personally, I view it like so:

  * using something separate from your ORM for migrations *can* be good (e.g. you don't couple your DB to your app's ORM), like dbmate; needs to be said
  * making plenty of views for MOST of your complex queries is a good idea, if approaches like BFF are popular for APIs, then why not the same in regards to interacting with your DB? also makes testing a breeze, instead of having to pull some unreadable generated SQL bullshit from trace logs
  * you can make read only entity mappings against those views and keep the querying complexity in the DB but let the ORM handle the app side stuff
  * with that in mind, using the ORM for most of the simple queries and regular CRUD stuff is also a breeze
  * sprinkle in some DB procedures/functions if needed, but also don't go all gung-ho in storing 90% of your business logic in the DB and using the app as a glorified view/controller, while that might appeal to a subset of people, in the *present* time that inevitably sucks
  * similarly to CTE's, your views can reference other views, as building blocks; also sometimes having two similar views that just compose others is nicer than dynamically generated SQL (since once you start trying to generate it dynamically, people get too eager about it and before you know it you can't figure out WTF is going on)

Like I don't passionately hate something like jOOQ or myBatis on Java side as well, it's just that often they mean that you can't quite surmise what the query will be, compared to just being able to look at a DB view and incorporate it into your SQL query tool of choice easily. Same goes for Hibernate, if you have to write complex HQL queries, then maybe consider whether you can do things more simply. There are also projects out there for which JDBI3 and the likes of which are perfectly okay, too! Note that I only mention Java cause it has a lot of ORMs and options, with various approaches to solving the same issue.

Reading some other comments, I'd also add: know SQL, use SQL for the problem it's good at, same for ORMs; don't let either overstep the other. In my opinion a really good litmus test for this is whether you are capable of generating all of your ORM mappings when you point a tool at your schema and its tables/views. If not, and you need complex workarounds, something is wrong.

[poncho_romero]

In the PHP back end I work on, I find the ORM immensely helpful because we need to instantiate objects to do things like permissions checks. The alternative seems like much more work. What am I missing with regards to ORMs being a bad idea?

[ozim]

It is just „SQL people” crying out not knowing how to work with ORM. They always claim that devs who use ORM don’t know SQL. But I never seen or hired a dev that doesn’t know SQL and yet for each project we use ORM.

I also never seen anyone claiming that you doesn’t have to know SQL and ORM is enough from the opposite side.

If you really run into spot where your ORM breaks you can always drop to SQL. If you build project „SQL first” you robbed yourself from ORM upsides and you are bound to badly implement your own one in the long run.

[frollogaston]

The issue is if you use the ORM, you still need to understand the DB it's on top of. There isn't much point in using such a leaky abstraction when it's easy enough to just use SQL.

[ozim]

I just wrote it at the end of my previous comment.

You can use well known library that more people will comfortably using

or

given enough time you will build crooked, half baked ORM of your own.

[e12e]

I think orms are typically fine. Something like rails can even play reasonably well with legacy databases (but you might need to create a view or two to rename columns names that collide with "magic column names").

There are reasonable arguments for leaving most to the db; effectively exposing some VIEWs and FUNCTIONs as the application interface for any and all bindings. Like legacy java and greenfield php for example.

I'd say it's different work rather than strictly more work.

[sklarsa]

There's nothing wrong with an ORM if you want to move quickly and get something up-and-running, which is the case for pretty much every startup who needs an article like this. As long as you're aware of the typical footguns (N+1 queries) and understand your ORM's lazy-loading scheme, IMO it's worth the tradeoff of developing yet another way to manage and parametrize your queries.

I'd rather spend the time building out my product than prematurely optimizing and overthinking my DB schemas at the earliest phases of a project.

[preg_match]

The main problem is ORMs are more work, because you need to understand the ORM. That’s its own beast, each ORM is different, and they change between releases. But SQL is something you already should understand, so you get to reuse the knowledge.

Also you can make SQL much more tolerable if you use migrations. That’s really where ORMs shine, but you don’t need the rest. Also query builders, although those also have a risk of abuse similar to ORMs. 99% of SQL should be static. That sounds like an absurdly high percent but it’s true if you use all the SQL features.

[arkh]

> Don't reinvent a type system

I'd like to add: stay away from using arrays and user defined types. They feel like a good idea at first, but will become a problem when using fetched data in your code.

And another miss, if you're managing your servers: pgtune. The default postgres configuration is very conservative regarding resources so you can get good performance gains just by adjusting them to your server specifications.

[frollogaston]

Agreed. Jsonb is the only thing I'll use, and that's only if I'm getting/setting the whole col at once, not doing SQL that goes into the payload.

[dwb]

I can certainly see the appeal of number four, but on more than a couple of systems I've worked on, it would have blown up the amount of data stored in a fair proportion of tables for very questionable benefit. It's a good technique to call out, but is it really right to dictate it for everything?

And what is people's opinion of the reverse: source-of-truth in traditionally-modelled mutable relational tables, with a change log written out with triggers?

[frollogaston]

I've done it the other way before, sometimes a team choice rather than my own, and it's ended badly any time the changelog table is actually needed. Startups or just chaotic projects will change requirements and then need data that's only in changelog tables. The changelog tables in the meantime were only barely maintained as an afterthought, lacking FKs of course, and only useful for manual debug if even that.

[manphone]

#8 is https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80... and it’s one upside is that it’s very flexible and it’s downside is literally everything else. If you can be 100% certain that all of the destination value types are the same then it can have compressibility benefits.

[sgarland]

I have no idea why you’ve been downvoted, as you accurately described EAV. I’m a DBRE who cares deeply about schema design, FWIW.

[frollogaston]

Idk, the comment is right. It is EAV. I've done a little bit of that when needed.

[edoceo]

What's wrong with select for update? I've found it useful in a few places. It is that fixed when there is an append only source of truth?

[frollogaston]

Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of.

It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.

[jvidalv]

For games is a must have.

[frollogaston]

If there's a high frequency of changes from a single user all being stored, probably need more specific approaches like that

[j45]

Nice summary -

While it's not my first choice, or habit, in many use cases, using an ORM is still OK looking back in the start especially when the schema is being fleshed out prior to understanding what needs optimizing. It's trivial to start optimizing a query after taking it away from ORM. The new angle I think is the ability for LLMs to use ORMs given the documentation, etc.

The only other thing I'd say is the benefits of running something that works with Postgres, such as Supabase, Hasura, etc. It really can be the best of multiple worlds, especially in the beginning in terms of having flexibility in one place.

[foo42]

On point 4, is the general flow that an "update" would read the current latest, check whatever conditions, then rather than updating, insert a new record (all in a transaction), or is your append only sot more an ordered "log" of update requests with the result of any conditional operations requiring a replay (from at least a check point). Or something else entirely.

I'm generally an immutable first, type developer but ive not had to do much schema design for a while and looking back to previous attempts we never hit scale to stress test my approaches.

[frollogaston]

It's the first thing, read-then-insert, not blind insert. Also you don't always need to do the read and the insert in the same transaction.

[renegade-otter]

Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.

[frollogaston]

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

[mrkaye97]

+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.

[oleg2025]

FastAPI pattern works very well with Pgbouncer, when it is in transaction pool mode.

Your Python application maintains a connection to Pgbouncer during the lifecycle of the request, but the physical Postgres connection is allocated only during the DB transaction. You will need open/close transactions in your code though.

[frollogaston]

This is why I said PgBouncer is a sign of something being wrong. Devs aren't managing connections right, they try to paper over it with PgBouncer, it's not really easier cause they now need to be conscious of xacts instead, and now there's an extra moving part in the DB that most of the team doesn't really understand. PgBouncer has its other uses, but I really don't like this one.

I also get it, xact should be 1:1 with connection in a lot of these backend applications. Sometimes I have a few little helpers for that, like pool.sql() will take conn, open xact, execute, close xact, return conn. If the DB driver doesn't already have that.

[frollogaston]

Oh wow. Dep injection for DB connections is nasty.

[alfons_foobar]

I might be outing myself as a noob here, but... what is the (better) alternative?

[0x696C6961]

You inject the pool itself.

[10000truths]

Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.

[frollogaston]

Every xact within a given connection will use the same connection-wide config, but the timeout is counting how long a single transaction takes, right? I don't see why you'd need to give up pooling for this unless you need different settings per xact.

[10000truths]

Pooling means you're sending multiple queries (from different HTTP requests) over the same DB connection. A well-designed DB wire protocol will allow for pipelining those queries:

[send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3]

But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.

[mikeocool]

If your end point does something like:

* read from the database

* make a request to an API (or really any kind of long running non-database thing)

* write to the database

You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.

[renegade-otter]

You can easily deal with these cases by tactically using transactions, but a vast majority of code can be simplified by dealing with an HTTP endpoint as an atomic concept. This all depends on what you are building. A classic CRUD app will benefit from this approach, microservices - not so much,.

[atom_arranger]

Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?

[frollogaston]

Yes, it's that. You do probably end up wanting to store some "latest" denorm tables at some point, but it takes surprisingly long to reach that point, and isn't hard when you get there.

There are disadvantages to this, but it's a safe default. The alternative is possibly losing important data, finding out later you want historical records of things that are stored in kludgy separate tables, getting into more advanced locking situations, and having more complex DB migrations. Which I've had to pull teams out of many times.

[0x696C6961]

Using event sourcing instead of basic crud should go on a startup suicide guide ...

[atom_arranger]

I don’t have a lot of experience related to this so I’m just noting some things.

Some people in this thread don’t seem to think it’s that hard or overcomplicated.

When reading Designing Data-Intensive Applications my main takeaway was that event sourcing can make it easier to solve a lot of issues like performance, scaling, consistency, auditability, etc.

It would be interesting to look into what a low overhead way of implementing CRUD with event sourcing in Postgres would look like, then decide if it’s too complex.

[frollogaston]

Try making Hackernews lite. Users can post, comment on posts, vote/unvote posts, and delete their own posts and comments. CRUD tables might be post, comment, maybe vote. Event tables might be create_post, delete_post, create_comment, delete_comment, vote, unvote.

[0x696C6961]

Don't trust anyone who tells you event sourcing is simple to implement.

[christophilus]

Can’t do RLS without a transaction, though, right?

[kentm]

For #4 I always tell people to assume 99% append only, but do consider the need for updates/deletes in edge cases.

If any of the data is PII and you will be subject to GDPR (you probably want to be at some point) then you will need a way to hard delete it.

A large portion of append-only datasets I’ve worked with have run into some edge case that required an update.

Also if you’re doing an append only log plus mutable view then please use triggers or materlialized views. I ran into a few cases where the approach was to just update both tables and that lead to divergences between the two.

[frollogaston]

Yeah, deletes need to be on the table (no pun intended) for PII redaction, and also emergencies where you manually do it. Both those situations are much safer when the DB is normally append-only.

[kentm]

Agreed. Usually an updated-on timestamp is sufficient to cover your bases without over-complicating the table. And your PII redaction is probably going to be shredding or nulling the fields instead of hard record-level deletes.

[waisbrot]

This is an excellent distillation.

Yes, in practice you may find that one or two of these don't apply to your own special start-up. But probably they all actually do.

[frollogaston]

Thanks, that's what I was going for.