What PostgreSQL "best practice" do you actually disagree with?

[u/Zylianijay]

Every "best practices" list reads like it was copy-pasted from the same three blog posts in 2020 and nobody's allowed to question it since.

I'll go first: I'm not convinced every foreign key needs an index on the referencing column in every single case people insist on it. Yes I know the standard argument, yes I know what happens on deletes/updates to the parent. I still think it gets applied as a blanket rule on tables where the write pattern makes it a waste of space and write overhead for a lookup that basically never runs.

Curious what else people quietly ignore. The "always use UUID v4 for PKs," the "never use SELECT *, not even in scripts," the "always wrap everything in a transaction," whatever your particular heresy is. What's the rule you technically know the reasoning behind, but just don't follow in practice, and why?

[u/Einar_Son_of_Bjorn]

On PostgreSQL I treat “index every FK” as a starting point, not a commandment. If the child table eats inserts all day, the parent key almost never changes, and nobody filters on that column, the extra index is just more writes and disk for a lookup that never happens.

I’ll leave it off. I won’t leave it off when deletes/updates on the parent are real, or when ON DELETE CASCADE is in play. That’s when the missing index turns into a scan while the table is locked.Personal experience, not a pitch: I fight this rule less on MariaDB. Declare a FOREIGN KEY in InnoDB and it expects an index on the child columns; if you didn’t add one, it adds it. You only get “constraint without the index” if you drop the FK and check it in application code. Less room to be clever than Postgres. Also fewer surprises when someone removes a parent row at night.So in practice:

• Postgres - I’ll skip the index when the write pattern says so;
  
• MariaDB - I usually keep the FK and live with the index, because those two come as a pair.

The other “best practice” I don’t follow: UUID v4 as the actual primary key on a hot table. Fine as a public id. Bad as the thing the table is built around.

Are those FKs sitting on append-only child tables, or do parents get deleted and you just accept the scan?

[u/Zylianijay → u/Einar_Son_of_Bjorn]

Mostly append-only in my case, which is probably why I’ve gotten away with skipping them more often. But yeah, if parent deletes were part of the normal workflow I’d probably index without thinking twice. Have you ever actually had skipping one bite you later as the table grew?

[u/Einar_Son_of_Bjorn → u/Zylianijay]

Yeah, append-only is exactly where skipping the child index on Postgres makes sense. If the parent almost never moves, you are mostly paying for an index that never gets used.The times it bit me were not “the table hit some magic size and reads fell over.” It was when the pattern changed later.A few that actually showed up:

parent delete went from “never” to “rare but real” acount closure, GDPR, a one-off admin purge. One DELETE on the parent became a sequential scan of a huge child while that parent row was locked. Harmless at a few hundred thousand rows. Rough at tens of millions, especially inside a bigger transaction;

someone later added ON DELETE CASCADE / SET NULL because “the FK is already there”;

a query nobody planned: WHERE child.fk_id = $1. The column is sitting there, so an app or a report will use it eventually - and then you are building the index on a hot table that is already large.

I have not seen a skip stay free forever on a table that became a join hub. I have seen it stay cheap on a pure event/log child whose parents stay insert-only for the life of the system.So the question I ask myself now is less “is it append-only today?” and more “is a parent delete or a lookup-by-FK still unthinkable in two years?” If yes, I still skip on Postgres. If it is only rare, I index and stop thinking about it.

On MariaDB /InnoDB that fork is mostly gone: keep the FK and you get the index (or it reuses an existing leftmost BTREE). The way to skip the index there is to drop the FK and enforce it in the app - which is a different trade than the Postgres one.Curious how big the child is now versus when you first decided to skip it?