Life Altering PostgreSQL Patterns

I also use UUID's everywhere - primarily due to concurrency requirements - but I ended up writing a bunch of functions (`MACRO`s) that lookup UUIDs and join them to other tables where needed, instead of using VIEWs or JSON queries.

I've not seen this approach documented much online - but it works really well for me. It has the advantage of keeping all my tables flat, while still being able to encode business logic into the database.

A typical query I might type, would look something like..

`SELECT ,trip_start_date(id) FROM trips WHERE trip2region(id) = 'AU'`

Where the two macros are just simple table joins, saving me the boilerplate work. Where this approach has really shined, though, is through function composition...

`SELECT FROM trips WHERE loc2region(trip_origin(id)) NOT IN loc2region(location_is_hotel(trip_locations_visited(id)))`

So something like the above, which is a mix of scalar and table functions, would give me all the trips where the traveler stayed in hotels outside of their home region. Maybe not the best example, because I don't actually work with trip/hotel/travel data at all, but I'd be interested reading more about this approach. I was surprised how well-optimized the queries are.... \endyap

I like most of these patterns and have used them all before, but a word of caution using UUID primary keys: performance will suffer for large tables unless you take extra care. Truly random values such as UUIDv4 result in very inefficient indexing, because values don't "cluster." For databases, the best solution is to use a combination of a timestamp and a random value, and there are multiple implementations of UUID-like formats that do this. The one I'm familiar with is ULID. It's become a common enough pattern that UUIDv7 was created, which does exactly this. I don't know if it's possible to generate UUIDv7 in Postgres yet.

Nice article, bookmarked.

For the "Mechanically name join tables" point, I would at least put a tiny bit of effort into their names, rather than zero. For example, using a naming convention to make that kind of table instantly recognisable, e.g. 'person_x_pet' instead of just 'person_pet'.

> They are terrible in that removing obsolete columns requires a drop and recreation, which can become a nightmare when you build views on views.

This is a problem with the `alter` abilities, but in fact the lack of using `views` is a problem.

`views` is how you abstract the database. Also, combined with `instead of` triggers become amazing to simplify the other triggers.

Nothing more awesome than flatten your db so external code barely ever do joins themselves.

My trick to solve the problem of migrations is that I put all the `views, functions, etc` in their own section of a `sql` file:

-- DEPS --

-- VIEWS

-- FUNCTIONS

-- TRIGGERS

And then I have a special `DROP ALL VIEWS, CREATE ALL VIEWS` comment in my migrations that do that. It remove the problem greatly!

Loving this list. One thing I would totally recommend though is to look at tools like DBT, as this supports a lot of these patterns out of the box (snapshots, materialized views, seeds for 'system tables' etc.). It also addresses the concerns with views and schema management.

> Truly random UUIDs doesn't sort well (and this has implications for indexes)

This has been solved already in more than one way (UUIDv7, ULIDs, Snowflake IDs to name a few).

Most of those patterns are usable in any SQL database like mysql, sqlite et al. So read the article, even if you're not using postgresql.