25.02.

2026, 10:56 Life Altering Postgresql Patterns

Life Altering Postgresql Patterns

by: Ethan McCue

Believe it or not, I don't think that title is clickbait.

There is a set of things that you can do when working with a Postgres database
which I have found made my and my coworker's lives much more pleasant. Each
one is by itself small, but in aggregate have a noticeable effect.

Use UUID primary keys

UUIDs have downsides

Truly random UUIDs doesn't sort well (and this has implications for indexes)
They take up more space than sequential ids (space being your cheapest
resource)

But I've found those to be far outweighed by the upsides

You don't need to coordinate with the database to produce one.

They are safe to share externally.

CREATE TABLE person(

id uuid not null default gen_random_uuid() primary key,
name text not null
)

Give everything created_at and updated_at

It's not a full history, but knowing when a record was created or last changed is a
useful breadcrumb when debugging. Its also something you can't retroactively get
unless you were recording it.

So just always slap a created_at and updated_at on your tables. You can
maintain updated_at automatically with a trigger.

[Link] 1/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

CREATE TABLE person(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
name text not null
);

CREATE FUNCTION set_current_timestamp_updated_at()

RETURNS TRIGGER AS $$
DECLARE
_new record;
BEGIN
_new := NEW;
_new."updated_at" = now();
RETURN _new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_person_updated_at

BEFORE UPDATE ON person
FOR EACH ROW
EXECUTE PROCEDURE set_current_timestamp_updated_at();

You need to create the trigger for each table, but you only need to create the
function once.

on update restrict on delete restrict

When you make a foreign key constraint on a table, always mark it with on
update restrict on delete restrict.

This makes it so that if you try and delete the referenced row you will get an error.
Storage is cheap, recovering data is a nightmare. Better to error than do
something like cascade.

CREATE TABLE person(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
name text not null
);

[Link] 2/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

CREATE TABLE pet(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
name text not null,
owner_id uuid not null references person(id)
on update restrict
on delete restrict
);

Use schemas
By default, every table in Postgres will go into the "public" schema. This is fine,
but you are missing out if you don't take advantage of your ability to make new
schemas.

Schemas work as namespaces for tables and for any moderate to large app you
are going to have a lot of tables. You can do joins and have relationships between
tables in different schemas so there isn't much of a downside.

CREATE SCHEMA vet;

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
name text not null
);

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
name text not null,
owner_id uuid not null references [Link](id)
on update restrict
on delete restrict
);

[Link] 3/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

Enum Tables
There are a lot of ways to make "enums" in sql. One is to use the actual "enum
types," another is to use a check constraint.

The pattern introduced to me by Hasura was enum tables.

Have a table with some text value as a primary key and make columns in other
tables reference it with a foreign key.

CREATE TABLE vet.pet_kind(

value text not null primary key
);

INSERT INTO vet.pet_kind(value)

VALUES ('dog'), ('cat'), ('bird');

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
owner_id uuid not null references [Link](id)
on update restrict
on delete restrict,
kind text not null references vet.pet_kind(value)
on update restrict
on delete restrict
);

This way you can insert into a table to add more allowed values or attach
metadata like a comment to explain what each value means.

CREATE TABLE vet.pet_kind(

value text not null primary key,
comment text not null default ''
);

INSERT INTO vet.pet_kind(value, comment)

VALUES
('dog', 'A Canine'),
('cat', 'A Feline'),
('bird', 'A 50 Year Commitment');

[Link] 4/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
owner_id uuid not null references [Link](id)
on update restrict
on delete restrict,
kind text not null references vet.pet_kind(value)
on update restrict
on delete restrict
);

Name your tables singularly

This isn't even Postgres specific, just please name your tables using the singular
form of a noun.

SELECT * FROM pets might seem nicer than SELECT * FROM pet but the moment
you start doing anything more interesting with your queries you will notice that
your queries are actually working in terms of individual rows.

SELECT *
FROM pet
-- It's a cruel coincidence that in english an "s"
-- suffix can sometimes work both as a plural
-- and a possessive, but notice how the where clause
-- is asserting a condition about a single row.
WHERE [Link] = 'sally'

The deeper you dig the more annoying edge cases you'll run into with plural table
names. Just name your tables the same as what an individual row in that table
represents.

Mechanically name join tables

Sometimes there are sensible names to give "join tables" - tables which form the
basis for "many to many" relationships between data - but often there isn't. In
those cases don't hesitate to just concatenate the names of the tables you are
joining between.

[Link] 5/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

-- pet_owner would work in this context, but

-- I just want to demonstrate the table_a_table_b naming scheme
CREATE TABLE vet.person_pet(
id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
person_id uuid not null references [Link](id)
on update restrict
on delete restrict,
pet_id uuid not null references [Link](id)
on update restrict
on delete restrict
);

CREATE UNIQUE INDEX ON vet.person_pet(person_id, pet_id);

Almost always soft delete

I will reiterate that storage is cheap and recovering data is a nightmare.

If you have some domain specific need to delete (or otherwise mark as irrelevant)
some data, use a nullable timestamptz column. If there is a timestamp filled in,
that's when it was deleted. If there is no timestamp it isn't deleted yet.

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
pet_id uuid not null references [Link](id)
on update restrict

[Link] 6/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

on delete restrict,
issued_at timestamptz not null,
-- Instead of deleting a prescription,
-- explicitly mark when it was revoked
revoked_at timestamptz
);

Even outside the context of a soft delete, timestamps are usually more useful than
a boolean. If you want to know whether something happened, you generally also
want to know when it happened.

Represent statuses as a log

It is very tempting to represent the status of something as a single column. You
submit some paperwork and it has a status of submitted. Someone starts to look
at it then it transitions to in_review. From there maybe its rejected or approved.

There are two problems with this

1. You might actually care about when it was approved, or by whom.

2. You might receive this information out-of-order.

Webhooks are a prime example of the 2nd situation. There's no way in the laws of
physics to be sure you'll get events in exactly the right order.

To handle this you should have a table where each row represents the status of
the thing at a given point in time. Instead of overloading created_at or
updated_at for this, have an explicit valid_at which says when that information
is valid for.

CREATE TABLE vet.adoption_approval_status(

value text not null primary key
);

INSERT INTO vet.adoption_approval_status(value)

VALUES ('submitted'), ('in_review'), ('rejected'), ('approved');

CREATE TABLE vet.adoption_approval(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
person_id uuid not null references [Link](id)
on update restrict
on delete restrict,
[Link] 7/23
25.02.2026, 10:56 Life Altering Postgresql Patterns
status text not null references
vet.adoption_approval_status(value)
on update restrict
on delete restrict,
valid_at timestamptz not null
);

CREATE INDEX ON vet.adoption_approval(person_id, valid_at DESC);

Just having an index on valid_at can work for a while, but eventually your
queries will get too slow. There are a lot of ways to handle this, but the one we've
found that works the best is to have an explicit latest column with a cheeky
unique index and trigger to make sure that only the row with the newest
valid_at is the latest one.

CREATE TABLE vet.adoption_approval(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
person_id uuid not null references [Link](id)
on update restrict
on delete restrict,
status text not null references
vet.adoption_approval_status(value)
on update restrict
on delete restrict,
valid_at timestamptz not null,
latest boolean default false
);

CREATE INDEX ON vet.adoption_approval(person_id, valid_at DESC);

-- Conditional unique index makes sure we only have one latest

CREATE UNIQUE INDEX ON vet.adoption_approval(person_id, latest)
WHERE latest = true;

-- Then a trigger to keep latest up to date

CREATE OR REPLACE FUNCTION vet.set_adoption_approval_latest()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
UPDATE vet.adoption_approval
SET latest = false

[Link] 8/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

WHERE latest = true and person_id = NEW.person_id;

UPDATE vet.adoption_approval
SET latest = true
WHERE id = (
SELECT id
FROM vet.adoption_approval
WHERE person_id = NEW.person_id
ORDER BY valid_at DESC
LIMIT 1
);

RETURN null;
END;
$function$;

CREATE TRIGGER adoption_approval_insert_trigger

AFTER INSERT ON vet.adoption_approval
FOR EACH ROW
EXECUTE FUNCTION vet.set_adoption_approval_latest();

Mark special rows with a system_id

It's not uncommon to end up with "special rows." By this I mean rows in a table
that the rest of your system will rely on the presence of to build up behavior.

All rows in an enum table are like this, but you will also end up with rows in
tables of otherwise normal "generated during the course of normal use" rows. For
these, give them a special system_id.

Unique indexes don't mind multiple rows with null values, so you can make a
unique index on this system_id and look up your special rows later as you need
to.

CREATE TABLE vet.contact_info(

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
person_id uuid references [Link](id)
on update restrict
on delete restrict,
mailing_address text not null,
system_id text
[Link] 9/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

);

CREATE UNIQUE INDEX ON vet.contact_info(system_id);

-- Not hard to imagine wanting to build functionality that

-- automatically contacts the CDC for cases of rabies or similar,
-- but maybe every other bit of contact_info in the system is
-- for more "normal" purposes
INSERT INTO vet.contact_info(system_id, mailing_address)
VALUES ('cdc', '4770 Buford Highway, NE');

Use views sparingly

Views are amazing and terrible.

They are amazing in their ability to wrap up a relatively complex or error-prone

query into something that looks basically like a table.

They are terrible in that removing obsolete columns requires a drop and
recreation, which can become a nightmare when you build views on views. The
query planner also seems to have trouble seeing through them in general.

So do use views, but only as many as you need and be very wary of building views
on views.

CREATE TABLE [Link](

id uuid not null default gen_random_uuid() primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
pet_id uuid not null references [Link](id)
on update restrict
on delete restrict,
issued_at timestamptz not null,
-- Instead of deleting a prescription,
-- explicitly mark when it was revoked
revoked_at timestamptz
);

CREATE INDEX ON [Link](revoked_at);

-- There are pros and cons to having this view

CREATE VIEW vet.active_prescription AS
SELECT

[Link] 10/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

[Link],
[Link].created_at,
[Link].updated_at,
[Link].pet_id,
[Link].issued_at
FROM
[Link]
WHERE
[Link].revoked_at IS NULL;

JSON Queries
You might have heard that Postgres "supports JSON." This is true, but I had
mostly heard it in the context of storing and querying JSON. If you want a table
with some blob of info slap a jsonb column on one your tables.

That is neat, but I've gotten way more mileage out of using JSON as the result of a
query. This has definite downsides like losing type information, needing to realize
your results all at once, and the overhead of writing into json.

But the giant upside is that you can get all the information you want from the
database in one trip, no cartesian product nightmares or N+1 problems in sight.

SELECT jsonb_build_object(
'id', [Link],
'name', [Link],
'pets', array(
SELECT jsonb_build_object(
'id', [Link],
'name', [Link],
'prescriptions', array(
SELECT jsonb_build_object(
'issued_at', [Link].issued_at
)
FROM [Link]
WHERE [Link].pet_id = [Link]
)
)
FROM vet.person_pet
LEFT JOIN [Link]
ON [Link] = vet.person_pet.pet_id
WHERE vet.person_pet.person_id = [Link]
),
[Link] 11/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

'contact_infos', array(
SELECT jsonb_build_object(
'mailing_address', vet.contact_info.mailing_address
)
FROM vet.contact_info
WHERE vet.contact_info.person_id = [Link]
)
)
FROM [Link]
WHERE id = '29168a93-cd14-478f-8c70-a2b7a782c714';

Which can net you something like the following.

{
"id": "29168a93-cd14-478f-8c70-a2b7a782c714",
"name": "Jeff Computers",
"pets": [
{
"id": "3e5557c0-c628-44ef-b4d1-86012c5f48bf",
"name": "Rhodie",
"prescriptions": [
{
"issued_at": "2025-03-11T23:46:18.345146+00:00"
}
]
},
{
"id": "ed63ca7d-3368-4353-9747-6b6b2fa6657a",
"name": "Jenny",
"prescriptions": []
}
],
"contact_infos": [
{
"mailing_address": "123 Sesame St."
}
]
}

You can find all the setup you'd need to do for that query here. You can try it out
on [Link] if setting up a local postgres is a bit
much.

[Link] 12/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

If there is something I missed or got wrong, tell me very loudly in person or here
on the internet.

<- Index

44 Comments - powered by [Link]

ChrisGNZ commented on 16 mar 2025

Hi Ethan. I enjoyed this post. Not a click-baity title at all! One thing I did not quite
understand was the section about "Mark special rows with a system_id". I don't get
what a "special row" is, and why you call the extra column "system_id". Would you be
kind enough to elaborate on this? Aside from that, I found the post very helpful,
thanks!

bowbahdoe commented on 17 mar 2025 Owner

@ChrisGNZ Sure, so an example might be something like: Say you have a program
with an authorization system where individual users are on teams and they have roles
on those teams and those roles imply permissions.

Different teams can make their own "roles" to assign to their users, but you want to
give the same set of roles to every team out of the box. Those roles might get a
system id so code you run on team creation can find the special roles to assign.

Or if you have a money moving system: An account that a particular feature of your
system uses to store fees is still an account, but has a special role in the "system" -
hence system_id

👍 1

dvaldivia commented on 17 mar 2025

I couldn't understand why you proposed the system_id column, could you clarify?
because if it's a column to trigger a behavior, it could be called exactly the behavior
it's driving right? (i.e.: needs_cdc_review) or similar?

bowbahdoe commented on 17 mar 2025 Owner

@dvaldivia Its more a column on which to build behavior. Concrete example if the
prose above is a bit obtuse

account

[Link] 13/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

id | display_name | system_id
| user_account_a | null
| user_account_b | null
| fee collection | fee_collection

Say for a particular feature of your app you want to move money between two user
accounts, but a part of what would be moved you want to put into a fee_collection
account you own. Instead of hardcoding the id of that account into your code, you
can refer to it by its system id.

👍 1

camdez commented on 19 mar 2025

@ChrisGNZ The system_id concept immediately resonated with me, so I thought I

might offer a real world example that's fresh in my mind:

In our app, we have 3rd party servicing teams who each have a team / accounts in
our product (and modeled in our DB). At some point we decided to take some of
that work in-house, so we added an in-house team, utilizing the same interfaces /
tools we'd written for the external servicing teams. Over time we started to build
additional functionality that only made sense for the in-house team, so we needed
to know which team (row) was the in-house one.

Could it be flagged with a boolean? Sure, but it's really an identity that we want to
model. And you might find down the road that there is another such team that
needs a system identity handle for a similar purpose.

The question of whether to model identities or behavior (as discussed above) is

subtle and filled with tradeoffs. I'd say it's usually a code smell when your
application has code like "if this is subscriber X then don't run this bit", but when
the existence of the database row is critical to the operation of the application, then
I think the system_id approach is a good one.

Closing the loop a bit... in my company's app, all servicing of a certain type goes to

our internal team If that team row didn't exist the app would break Feels like the
👍1

hagen00 commented on 20 mar 2025

UUID? No! Singular table naming? Yes, but just stick to what your framework wants
you to do. Soft delete everything? God no. Sometimes, yes! Views? What, are we in
2001? updated_at, created_at. Sure, where it makes sense. System_id? Huh? See point
1. Many to many naming? Spot on. Json field? Uff! Enum/type tables? Yeah..they often
grow to include stuff like icon etc..

👍 2

[Link] 14/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

hagen00 commented on 20 mar 2025

RESTRICT everything? NO! Cascading is lovely.

👍 3

vstiebe commented on 20 mar 2025

My personal rules guidelines for cascading foreign keys references are:

foreign keys primary goal is to mantain consistency between tables, business

rules are subject to triggers (or the application, orm, whatever);
on update is mostly always cascade - If a record id needs to change, it should
automatically propagate to the whole database. Very useful on migrating/copying
records between different databases instances;
on delete cascade - When it's a composition like invoice and invoice items. If an
invoice must be deleted their items must automatically be deleted. The business
rule about allowing it to be deleted is subject to triggers, not foreign keys;
on delete set null - When it's an accessory information like
invoice.last_shipment_order_id. If a shipment order must be deleted then set null
is useful. Filling it with a previous shipment order, or deciding to block the
shipment order delete must be implemented on well placed triggers;
on delete restrict - When it's a relationship between "less important" tables. You
shouldn't even try to delete an item that exists on invoices. So
invoice_item.item_id must be restrict.

👍 2

alexandre-savaris commented on 20 mar 2025

@ChrisGNZ Thanks for sharing. I'd like to contribute based on my own experience.

1. Create single-column or multi-column indexes on fields that are used as foreign

keys. When queries become complex and demand JOINs, these indexes help a lot
in solving the most basic performance issues.

2. If you have a multi-column index on, let's say, columns (c1, c2, c3), there's no
need to create a single-column index on c1 - even if the column c1 is a foreign
key or is used explicitly in WHERE clauses. The planner/optimizer can identify the
c1 column as the first one in the multi-column index, allowing its selection and
use. Also, by not creating the single-column index, you save some disk space and
save some time on executing INSERTs, UPDATEs, and DELETEs.

3. Name your DB objects in lowercase (e.g. "person", "pet"). If you use Object-
relational mapping (ORM) tools, it's usual to see DB objects named after classes
defined in code (e.g. "Person", "Pet"), which in turn demands the use of double
quotation marks surrounding the object names when writing queries by your
[Link] 15/23
25.02.2026, 10:56 Life Altering Postgresql Patterns
own. So, if possible, configure your ORM to avoid this annoyance.

👍 1

theill commented on 20 mar 2025

Insane great post! Thank you for that one

mikehearn commented on 20 mar 2025

Very nice! Here are a few twists for people working with Oracle rather than Postgres.

JSON responses. The one-JSON-response pattern is an integrated feature under the

name JSON-relational duality views. Syntax looks like this:

CREATE JSON RELATIONAL DUALITY VIEW dept_w_employees_dv AS

SELECT JSON {'_id' : [Link],
'departmentName' : [Link],
'location' : [Link],
'employees' :
[ SELECT JSON {'employeeNumber' :[Link],
'name' : [Link]}
FROM employee e
WHERE [Link] = [Link] ]}
FROM department d WITH UPDATE INSERT DELETE;

Note the WITH UPDATE INSERT DELETE part: these things support changing the
underlying relational tables by writing to the JSON object, not just querying them. If
the bulk of your web server is assembling SQL results into JSON then this can
actually empty out your web server, at which point you could consider using ORDS
which is a generic REST API server that can drive the database and map to/from

HTTP directly

eliottwiener commented on 21 mar 2025

You can add comments to an enum type, to a column, or to any other database object
using COMMENT: [Link]

This is probably as widely used as "git notes".

ddikman commented on 21 mar 2025

This is great, I'll bookmark this for future reference. Thank you very much!

tef commented on 21 mar 2025

[Link] 16/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

This is a great collection of advice, and I only have one thing to suggest: sometimes
it's better to use a state column rather than a revoked_at column, i.e state is
"created", "updated", "deleted", and sometimes things like "restored", or "virtual"

The rationale is that the logic is 100% easier, "state = deleted" rather than "revoked_at
exists and is before NOW()", and future states are easier to add without creating a
nasty mess of flags.

Similarly, to "log all status", you can have a table that logs any changes to the state ,
which lets you keep timestamps per event, rather than the last event.

👍 1

m4dc4p commented on 21 mar 2025

I always give foreign keys the same name as the related primary key so I can use the
using statement instead of writing on expressions.

Example, getting prescriptions for pets. If you havee pet_id on both sides you write:

FROM pet INNER JOIN prescription USING (pet_id)

rather than:

… ON [Link] = prescription.pet_id

Great read! Thanks!

schmod commented on 21 mar 2025

As the polar-opposite of this post, the official Postgres wiki has a fantastic Don't do
this page, full of things that are supported, albeit inadvisable.

Also, Postgres has upcoming support for UUIDv7 (also available today via an
extension), which solves the issue with indexes, and also allows you to extract a
created_at value "for free" (with some caveats)

jamesliu4c commented on 21 mar 2025

UUID 7 solves the sorting problem. It also includes a timestamp.

glenjamin commented on 21 mar 2025

UUIDs have some nice properties, but they look awful in URLs! ULIDs are a bit better,
but if you're building a web-based system then I really recommend using something
[Link] 17/23
25.02.2026, 10:56 y g y y
Life Altering Postgresql Patterns g g
that follows the general timestamp+randomness theme of UUID v7 and similar, but
has a compact & fairly nice looking URL-safe represenation

gocreating commented on 22 mar 2025

I prefer shortid more than UUID. At least it's more human readable.

taifu commented on 24 mar 2025

Possibly a typo in "Name your tables singularly" example? The query uses FROM pet,
but the WHERE clause references [Link].

bowbahdoe commented on 24 mar 2025 Owner

@taifu good catch - fixing now

👍 1

evmcl commented on 24 mar 2025

Curious behind the rationale behind giving your person_pet join table it's own id
primary key column instead of just specifying the columns person_id and pet_id
together as the primary key.

I've always found giving join tables their own independent primary key column to end
up being painful in the long run (plus requiring the extra unique index).

IDisposable commented on 25 mar 2025

Nice list, I would mostly only differ with the UUID advice. Use a ULID for sortable IDs
[Link]

bowbahdoe commented on 25 mar 2025 Owner

@evmcl I often end up eventually wanting to treat the join table as its own entity -
and as such relate it to other things in the system. The mechanical naming is
something I do when I can't really describe it up front, but it doesn't mean that it will
remain a "pure" join table as time goes on.

Example:

[Link] , [Link] , and identity.organization_user -

[Link] 18/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

identity.organization_user functions as a record of membership and, while basically

just relating organizations to users, its also the right anchor point for an
identity.organization_user_role - which relates the membership itself to a role on
the org (thereby allowing for multiple roles).
For that we need an actual id .

scottieb3 commented on 26 mar 2025

This is a great collection of advice, and I only have one thing to suggest:
sometimes it's better to use a state column rather than a revoked_at column,
i.e state is "created", "updated", "deleted", and sometimes things like "restored",
or "virtual"

@tef : I grew to like status and status_at (probably should be status_applied_at ).

Precisely for ☝ , supported states can evolve and having to query deleted_at is
NULL and received_at is NULL and refunded_at is NULL and shipped_at < NOW()-
INTERVAL '7 DAYS' is muckier than status = 'shipped' and status_at < NOW() -
INTERVAL '7 DAYS'

CXW-Russ-Newcomer commented on 27 mar 2025

Overall, some really solid advice. I have two disagreements, the first is a minor
quibble that I would accept as house style if I went to a new org, the second of
which I think is a major problem that if we worked in the same org I would probably
regularly disagree with every time I encountered it, and see to refactor out for
future maintenance relief.

Mechanically name join tables

I.e. if you are joining person and pet, call it person_pet. I know it is SQL-y to call
them 'join' tables, but I prefer to think of them as cross-reference tables, and so I
would call that person_pet_xref. Same concept, I find it more clear to have the Xref
in the table name, especially as it clarifies when reading SQL that this is a xref. Also,
with the _ naming convention in postgres, I find it definitely conceivable that a
system could grow to have a concept that looks like the table name of a join table
when in fact it is something different. Your identity.organization_user might not
in fact be a join table but some separate concept for who uses an organization, not
a user in an organization, that now you'll have to find a new name, whereas if you
had called it identity.user_organization_xref you can use the more natural name.
(I realize I kind of cheated there by calling it user_organization_xref but I think of
users belonging to organization not organizations belonging to users and that's
how I try to structure the Xref table names. I don't think this naming precludes it 

👍 2

ldhasson commented on 27 mar 2025

[Link] 19/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

Very cool article. Most of these rules were baked in as non-negotiable in our Tilda
framework [Link]

A few questions:

uuid v7 is sortable and solves most issues with uuid in terms of cache locality.
we actually found that with fine-tuning Postgres, we could get some really good
performance out of fairly complex views, including views of views with heavy
pivot patterns (200M+ rows, pivoting 400+ columns)
we subsumed the "status as logs" pattern with an automated history pattern on
insert/update. Same results, but better automated imo.

Cheers :)

👍 2

hakib commented on 28 mar 2025

The index on system id really wants to be a partial index:

CREATE UNIQUE INDEX ON vet.contact_info(system_id)

WHERE system_id IS NOT NULL;

I'm guessing nobody cares about rows where system id id null, but PostgreSQL will
index them unless you explicitly exclude them.

👍 2

cthart commented on 28 mar 2025

You don't need on update restrict and on delete restrict to maintain referential
integrity. The default no action does that just fine. The only difference is that no
action is deferable. And sometimes you want on delete cascade anyway: 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!

👍 2

cthart commented on 28 mar 2025

I don't agree with a lot of what you write, so they're hardly "life altering". Or maybe
they are, 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
[Link] 20/23
25.02.2026, 10:56 Life Altering Postgresql Patterns
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_at and updated_at. Some tables contain almost static
reference data, for example.

You have extremely wordy SQL in your create table scripts and triggers:

primary keys are automatically not null, so no need to specify it again

foreign keys references the primary key by default, so references without the
brackets and column name is adequate syntax in 99.99% of cases
see my previous comment about on ... restrict
new.updated_at = now() works just fine in a trigger. No need to create an extra
variable, which then requires you to have a declare section.

My biggest tip would be to stop using the shift key when typing SQL. Save yourself 

👍 1

katmarine commented on 30 mar 2025

('bird', 'A 50 Year Commitment')

haha :) thanks for this post!

slimdave commented on 31 mar 2025

@cthart +1 for losing the uppercase convention in SQL. Even Fortran doesn't do that
anymore.

👍 1

EduardoZepeda commented on 3 kwi 2025

hello, just to let you know that I enjoyed your post and I learned a few things. Thanks
a million!

elielm commented on 8 kwi 2025

@evmcl, I too hate that id column on join tables, but many an ORM requires that
(some PHP frameworks, DJango, etc) and it always enrages me.

The use that @bowbahdoe mentions of adding data relating to the reasons for the
join would usually come up in modeling so one could specify and ID on those tables
when needed instead of universally.
[Link] 21/23
25.02.2026, 10:56 Life Altering Postgresql Patterns

Lunatix01 commented on 14 kwi 2025

Nice article, I have one note on the Enum Tables , In my opinion if you use enums in a
supported programming language that corresponds to enums in the database, you
can avoid using enum types in the database, just use a regular data type like
varchar(n) this case is for enum types that are possible to be changed (delete and
add new ones), it's much easier to work with, you don't need to manually add data
there.

Nessitro commented on 18 kwi 2025

Very informative. Thanks for the tips!

OluwaninsolaAO commented on 19 kwi 2025

This is awesome. I really enjoyed this. 😃

placetobejohan commented on 3 cze 2025

Thanks for sharing! One small remark on the soft delete column: if you already have
the updated_at timestamptz I'd make it a boolean since the timestamp information
can be retrieved from updated_at .

bowbahdoe commented on 4 cze 2025 Owner

@placetobejohan Thats pretty ill-advised. There are other columns on there and the
updated_at gets set by a trigger. Changing some unrelated data retroactively
shouldn't alter knowledge about when like, a hold was released.

placetobejohan commented on 4 cze 2025

@bowbahdoe Hm I'd say it depends, as usual. In the case of having a reliable audit
system the deleted column shouldn't be any different from your other columns and
information about when it changed can be retrieved from there. If you want that
redundancy for robustness and/or easy access, or don't have (reliable) logging in
place, then I can see the need of using a timestamp.

umayrh commented on 11 lip 2025

[Link] 22/23
25.02.2026, 10:56 Life Altering Postgresql Patterns
In general, I love the terse crispness of the article.

martinstreicher commented on 11 lip 2025

PG supports UUIDv7, which provides sortable UUIDs.

evmcl commented on 17 lip 2025

Just came across the moddatetime extension that includes the moddatetime() trigger.
Probably useful for those updated_at columns.

okohll commented on 21 lis 2025

Great set of tips. I guess there's an exception to every rule and I have the exception to
the 'use views sparingly' one.

I've an application that is designed to build views and use views to reference other
views - that's a large part of what it does What we've done is build application level

[Link] 23/23