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!