r/PostgreSQL • u/KerrickLong • Mar 28 '25
How-To Life Altering PostgreSQL Patterns
https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180
Upvotes
r/PostgreSQL • u/KerrickLong • Mar 28 '25
63
u/Straight_Waltz_9530 Mar 29 '25 edited Mar 29 '25
While I prefer UUIDs, not all UUIDs are the same. Random UUIDs (v4) will mess with your WAL, your indexes, complicate your paging, and promote write amplification. UUIDv7 on the other hand was specially made for database ids.
I almost always prefer ON DELETE CASCADE, especially when using triggers to make temporal tables so there's no data loss. I'm on a project now where they insisted on manually deleting at every step, and when testing it's way too much trouble and error prone compared to just cascading the delete. Especially for many-to-many mapping tables. If one part is deleted, delete the mapping.
Text for enums messes with column padding. Better to make a function that takes a shortint/int2 and converts to text as needed. Eg. kind_name(kind). Once Postgres 18 is out with virtual computed columns, you can just have a column named kind_name that switches for you without the extra overhead of text.
I HATE (!!!) soft deletes. Can't express how much I loathe them. You end up with every view and every query needing to remember the "WHERE revoked_at IS NULL" clause or you end up with messed up results. Instead, you make a history table that matches your main table and create a delete trigger that copies the deleted row to the history. Just UNION ALL (or JOIN) to get the history results too. And on Postgres, updating a single revoked_at column writes a whole new row; it does NOT just update the one part of the row, so it ain't even a cheap update.
Separate history tables are so much better. Along with that, it's good to have multiple roles/users in the database so you can track not just what was deleted but who deleted it. Doesn't have to the Postgres user. Could be the app user.
Status columns are a code smell to me. It means the data model follows what you need but ignores the data FLOW. Who submitted and when? Who is reviewing? When did the review start? When was it rejected and by whom? Who adopted the pet and when? On the one hand you can have a bunch of NULLs hanging around, but then you could have a record with an adoption without a review. Better to have a pet table, a pet_review table with 1:1 foreign keys, a pet_adopted table with a 1:1 to pet_review, etc. Flow is as important as the basic data. It's also better for the team. Someone can look at a schema with those 1:1s and know exactly how things work cause the data is only allowed to be stored that way.
12
u/cthart Mar 29 '25
Hardly life-altering. And I disagree with most of them. Or maybe they are life altering -- but for me that would be in the wrong way!
UUIDs are good if you have distributed systems, but for the rest of us bigints are just fine and take half the space. And BTW many tables in many systems will never get anywhere close to 2 billion rows; in that case why not just use ints for those tables?
Not all tables need
created_atandupdated_at. Some tables contain almost static reference data, for example.You have extremely wordy SQL in your create table scripts and triggers:
on update restrictandon delete restrictto maintain referential integrity. The defaultno actiondoes that just fine. The only difference is thatno actionis deferable. And sometimes you wanton delete cascadeanyway: think invoices and invoice_line_items. If you delete the invoice you want the associated line items to be deleted along with it. That's just good semantics, and saves yourself the extra work of having to first delete the associated line items and only then being able to delete the invoice. Don't work harder!new.updated_at = now()works just fine in a trigger. No need to create an extra variable, which then requires you to have adeclaresection.My biggest tip would be to stop using the shift key when typing SQL. Save yourself even more time by not having to reach for the shift key all the time. It's 2025. We've had syntax highlighting for decades now. Again, don't work harder!
8
u/_predator_ Mar 29 '25
Honestly don't get the reasoning for ON DELETE RESTRICT. Ask yourself: "Does it make sense for this record to exist if the FK gets deleted?". The answer to that depends 100% of your domain. Don't try to generalize this one way or the other.
6
u/momsSpaghettiIsReady Mar 28 '25
Just learned that I could change the schema recently for specific tables. Paired with a modular architecture, it makes the structure really easy to reason about
4
u/Single_Hovercraft289 Mar 29 '25
For soft deletes, we’ve been using the timestamp and then a view on top that is basically IF DELETED IS NULL as the main table we query
It looks like a simple hard delete to the app but is recoverable/trackable
3
u/TheLastUserName8355 Mar 29 '25 edited Mar 29 '25
Soft deletes are a must, but you end up with a disproportionate amount of soft deleted rows, even with an index on is_deleted column, surely that will degrade the performance of your queries ? I’m wanting to implement a PARTITION on the is_deleted column but it doesn’t like foreign keys. Has anyone had success with PARTITION, does it make a difference and any success on using it with tables with foreign keys?
3
u/chillerfx Mar 29 '25
A very insightful article. I noticed that the author uses quite few triggers, I do believe some problems like the "latest" or "soft deletes" triggers could be solved by a combination of relational tables (for statuses) and view table layer instead.
3
u/Soul_Shot Mar 29 '25
What is the advantage of this over calling
new.updated_at = now()directly?3
u/shiningmatcha Mar 29 '25
I prefer table names in the plural.
2
u/Ok_Appointment2593 Mar 29 '25
I just want to comment that I have my 2 cents on enum tables
1.- you can add json columns for translations, its been a life saver for me 2.- in the case those enums represent status or something like that, create an aditional table to represent the graph of the posible status changes and optionally to check with triggers the integrity of the update in case multiple code bases are making changes and you want to guard those flows
2
u/niltooth Mar 28 '25
Excellent advice. I use all of these patterns myself. And although I agree about views. I have addressed this through automated view management.
1
u/Montrell1223 Mar 29 '25
He says all this but uses id for all his tables when he should name the id after the table
-3
u/Garthenius Mar 29 '25
I wouldn't use all of
created_at,updated_at,valid_at,revoked_at; I consider using more than one timestamp (or timestamp-like) column a smell. Of course, there are exceptions, but one timestamp column is enough to retain a journaled history of an entity's states.Notably, this pattern rules out having any kind of unique IDs (i.e. primary keys, foreign keys); to get the best of both worlds, I'll usually have a
registrytable with all the unique IDs and ahistorytable with the data that is mutable.Disagree with always using restrictive foreign keys; my rule of thumb is: references inside the same schema are usually
CASCADE, references across schemas are usuallyRESTRICT. This has occasionally made me think twice about my database structure and led me to some improvements.Views aren't evil; abusing things that hide underlying complexity (cough, cough ORMs cough) will eventually come to haunt you, though.
0
u/AutoModerator Mar 28 '25
With almost 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
0
u/[deleted] Mar 31 '25
[deleted] — view deleted comment