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.

Thanks, that's what I was going for.

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.

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.

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

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.

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.

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.

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

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.

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

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

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.

Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)

This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.

RDS is nice even if you just want basic functionality with backups.

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.

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.

Oh wow. Dep injection for DB connections is nasty.

I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.

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.

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.

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.

Any kind of uuid PK is quite expensive and usually not worth, because you're so frequently joining on PKs. A safe default is to use serial PKs, then have a secondary-indexed uuid4 if you wish to publicly-expose anything. Why uuid7, is the btree performance better with it than with uuid4?

The mineral sunscreen comes off way more easily in water. For that reason, so did a few layers of my skin in the Bahamas.

I don't think they should tolerate it, but they do. 0 people are going to leave Windows because of this latest scandal, because the ones who care have already left. At some point I accepted that this doesn't matter to a lot of people, which is their right and choice, and if Linux wants to attract users, it has to do it by being easier to use besides just being more ethical.

Yeah that's kinda true, but Linux is worse about it. And Hackintosh is the ultimate in not sleeping. Users of both will still often claim their machine works perfectly, and when you ask about sleep, shrug and say sleep isn't needed.

Hm, surprised at lack of HN mention, as it was pretty high up in mainstream news. Random people were asking me what I knew about that.

Claude doesn't even make machine code though. Or it can if you really want to, but that's now how people use it. If you wanna include transpilers or interpreters, fine but the answer is still no, otherwise our git commits would just be prompts that get built either in CI or JIT. People deliver code, not prompts.

Maybe that could change some day, but even then, it wouldn't be a useful designation. It's like saying "oh technically the Americas are an island." There's a reason we still distinguish between a transpiler and compiler.

What they instead did during that period was give thousands of people in Africa thousands of chat apps

I don't want to hear this ever again. No Patrick, mayonnaise is not an instrument.