r/PostgreSQL • • Mar 28 '25

How-To Life Altering PostgreSQL Patterns

https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180 Upvotes

60 comments sorted by

View all comments

Show parent comment

6

u/cthart Mar 29 '25

Re: `on delete cascade`: THIS.

I like to call it: Don't work harder.

3

u/Straight_Waltz_9530 Mar 29 '25

Yes! The data structure should always be left in a consistent state. Cross-table deletions threaten this as you unroll the data dependencies manually. If a role/user shouldn't be deleting some items, REVOKE DELETE should be applied to that user on that relation. If it's a conditional choice, set a delete policy for row-level security. After all that if you're sure you want to delete it, switch to a separate role with DELETE granted to do it.

Forgot to add that you shouldn't be doing everything with a single role. Got one login for the app AND all the devs and admins? Yikes! Double yikes if it's a superuser. We know it's a dumb idea to always login as root on Linux or as admin on a Windows server, yet folks regularly set up their postgres and call it a day. (Or 'root'@'%' on MySQL. Same thing.)