alt Hacker NewsThis 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.
Replies
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?
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
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?
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.
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?
#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.
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.
Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?
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.
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.
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.
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.