PostgreSQL: Using MIN/MAX on UUID columns

You will need to walk the path of shame for your aggregates

Photo by Markus Winkler on Unsplash

I had a little beef with PostgreSQL recently. I love the database engine, but I came to understand no every database is perfect, like in this case.

Due to some client’s circumstances, they’re tightly coupled with a particular PostgreSQL extension and especial procedures, and using MIN|MAX aggregators over a uuid column type, full of UUID v7 (ordered), wasn’t possible.

If you don’t know, the MIN and MAX functions on SQL are great aggregators to find the biggest or smallest value from all the records. These will work wonders because these use the B-tree index natively. It’s usually faster than the usual ORDER BY as it doesn’t need to sort the index, especially if the index order doesn’t match the sorting you’re asking for.

As I said, PostgreSQL doesn’t like using these functions over an UUID. You may say that it’s nonsense, as UUID are random, but the UUID v7 is not, which Laravel uses when creating records with an UUID. They even warn you when trying to use these aggregators.

UUID v7 is “ordered UUID”, which you can find about in this article. Mind you, UUID v7 is still a proposed standard. Software can still qualify as “UUID compatible” without supporting UUID v7 and the other ones coming on the standard.

On the side of the database, PostgreSQL 18 includes support for generating UUID v7, but that’s about it. No MIN|MAX support this version — something I tested with their latest Beta 1.

Other database engines like MySQL, MariaDB and Microsoft SQL Server, allows to use these aggregators over an UUID column without problems. I didn’t stop to wonder why or how. Who knows if these are just syntactic sugar for ORDER BY and we are just hallucinating.

Making PostgreSQL use MAX/MIN

Luckily for us, we can somewhat mitigate completely the lack of support for using the MIN|MAX aggregators over an UUID column by just extending these functions. For that, we require two ingredients:

1. Functions to compare two UUIDs
2. Functions to extend the MIN|MAX aggregators to support UUID

With some help of the many AI assistants you can find online, and some manual research, I ended up with the list of SQL statements that are needed to make these aggregators work.

The gist is really simple. The first two functions are proxies made to compare two UUIDs using the native GREATEST and LEAST functions. We cannot use these functions directly to extend the aggregators, sadly.

The second part are what extends MIN and MAX aggregators to support UUID, plus two optimizations: allow the engine to execute the function in parallel and allow to be called when sorting through the sort operators > and <, respectively.

It’s not the best solution — the best solution would be to allow these aggregators to run natively, but this is close to that. This approach will incur in some small overhead mostly because PostgreSQL won’t use the power of a B-tree index directly and natively, but it’s a small price to pay for not using an autoincrementing integer column.

Update: I asked PostgreSQL to explain the following query, using the MAX function over an uuid column.

EXPLAIN SELECT MAX(id) FROM test_table;

As it turns out, it will use the index to sort the records, instead of requiring a full table scan.

Result  (cost=0.59..0.60 rows=1 width=16)
  InitPlan 1
    -> Limit (cost=0.14..0.59 rows=1 width=16)
      -> Index Only Scan Backward using test_table_pkey on test_table (cost=0.14..13.79 rows=5000 width=16)

I guess that’s seeing “Index Only Scan Backward” is mission accomplished. The “Scan Backwards” is mostly due to creating a primary key in the default ASC order, where new entries are appended at the end of the index.

If you plan to query the latest range of records constantly, like the last 10 published articles or else, then you may use DESC as the primary key order. Otherwise, you shouldn’t bother.

CREATE TABLE my_time_based_uuid_table (
    id UUID PRIMARY KEY DESC, -- Standard primary key
    -- other columns
);

If we talk about compromises, these functions seem bearable, unless you expect to have many millions of records — napkin math, a million of records would require an index of 15,26MB at least, so it’s not much.

Alternatively, if you can, you may use the aforementioned database engines to avoid this specific problem.

If you ask me, I personally don’t have high hopes for PostgreSQL to have support for using MIN and MAX over uuid columns before September 2027, at best. Maybe they’re waiting on UUID v7 to become standard (apart from a bunch of other versions), or maybe they’re waiting for someone to tackle the problem of internally because of some technical or ideology roadblocks.

Maybe they require more donations so more people can put the time on it so we all can be happy.