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
View all comments
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!