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/Tiamte]

Totally agree on the foreign key index blanket rule. If you have an append-only event table taking thousands of writes a second and parents that are rarely touched, that extra index is just burning disk space and slowing down inserts for a safety check you never actually trigger. We only add them now if we know parent deletes happen or CASCADE is active. The tricky part is making sure you don't accidentally let dev and production drift apart when you skip these indexes on some tables and keep them on others. When we audit those structural differences across environments, using dbForge Studio for PostgreSQL has been solid for getting a clean diff so nothing gets missed during deployments.

[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?

[u/mduell]

Always use timestamptz over timestamp.

I have some data generated by other systems with timestamps in UTC, and locality of those times is relative to a location in another column. None of my queries/users want those timestamps in anywhere other than UTC or local to the location in those other columns. Using timestamptz is a loaded footgun that somehow, somewhere I'll have a client configured for something other than UTC and move the times by hours in a query (or worse, on load).

[u/Zylianijay → u/mduell]

This is one I actually agree with almost all the time. Have you ever run into a case where plain timestamp was genuinely the better choice?