People like to talk about how AI is making software engineers think less, but I’ve been finding exactly the opposite at Lorikeet. When I find an unusual problem, AI often teaches me something I would never have had time to learn otherwise.

This week’s example: speeding up some Postgres queries by up to 1000x with a single typecast.

We use Prisma, which by default turns JS DateTimes into Postgres timestamp fields, though they can be annotated to timestamptz instead. Despite the name, timestamptz doesn’t actually store a timezone: both types use eight bytes, but timestamptz converts between your session timezone and UTC.

While speeding up our ticket processing, I found some very simple queries taking seconds:

owner = 'foo' AND createdAt > NOW() - INTERVAL '7 days'

We had a btree index on (owner, createdAt), so this should have been almost entirely answerable from the index. Postgres was even choosing that index, which was super strange. But it was a lie: the database was using just the owner prefix, then walking every row belonging to that owner to later filter on createdAt.

Here’s where I learned something fun: NOW() returns a timestamptz. Because createdAt is a timestamp, Postgres had to perform a cross-type comparison. In combination with Row Level Security, that meant it couldn’t use the createdAt portion of the index.

Why would RLS affect a timestamp comparison? At Lorikeet, we use RLS as a second line of defence against data leaking between our users. Postgres marks every function and operator as either “leakproof” or “not leakproof”. A non-leakproof function might reveal something about a hidden row by throwing an error, so Postgres won’t let it be used when enforcing RLS.

Comparing a timestamp with another timestamp is leakproof, but comparing a timestamp with a timestamptz isn’t.

Why? Historically, converting a timestamp near its minimum value into a timestamptz could underflow in certain UTC offsets. The error thrown leaks that the hidden value was near the type’s bound. That was fixed in 2020, but the function’s classification was never updated. Which was lucky! Just last month, someone found another bug where this comparison could produce incorrect index results during DST spring-forward gaps.

Our fix was tiny:

owner = 'foo' AND createdAt > NOW()::timestamp - INTERVAL '7 days'

Our Postgres sessions use UTC, so this query is exactly the same as the original for us (we also had similar problems with comparisons to ${date}, which Prisma would silently convert to a timestamp).

That one cast made some queries up to 1000x faster, reduced database load, and sped up ticket processing for our users.

The answer going forward for us is to use timestamptz, which avoids the problem entirely.

I don’t usually get to go this far into the weeds of database performance, but this was a fun little adventure to share. If you’d like to work on this sort of thing with me, Lorikeet is hiring. Drop me a line!