Unconventional PostgreSQL Optimizations

Posted by haki 1/20/2026

Unconventional PostgreSQL Optimizations(hakibenita.com)

431 points | 69 commentspage 2

danielheath 1/20/2026|

The hash technique for uniqueness isn’t supported for indexes because it doesn’t handle hash collisions. The authors proposed solution suffers the same problem- values which do not already exist in the table will sometimes be rejected because they have the same hash as something that was already saved.

Diggsey 1/20/2026||

This is completely untrue. While the index only stores the hashes, the table itself stores the full value and postgres requires both the hash and the full value to match before rejecting the new row. Ie. Duplicate hashes are fine.

danielheath 1/29/2026|||

For a unique index, that requires a table check, which - IIRC - isn’t implemented during index updates.

The article suggests using a check constraint to get around that - are you saying that does actually check the underlying value when USING HASH is set? If so, I think the docs need updating.

tizzy 1/22/2026||||

That's super interesting and I am convinced by the dbfiddle but is not very intuitive or well documented? https://www.postgresql.org/docs/current/hash-index.html

mxey 1/21/2026||||

This is very good to know because it means this exclusion constraint workaround is a better approach over using a SQL hash function and a btree if you want to enforce uniqueness on values too long for a btree index.

gmcabrita 1/21/2026||

Your comment is 100% not true: https://dbfiddle.uk/Iu-u886S

pphysch 1/20/2026||

Is the Hash Index method strictly superior to creating a unique "hash" column and precomputing the hash in the application or query?

ComputerGuru 1/21/2026||

Sibling comment mentions less space but also your alternative doesn’t (naively) handle supporting values that differ but hash to the same value.

Of course the hash index also outperforms a unique (btree) index on top of separately calculating the hash, in addition to the storage overhead, row bloat, lack of guarantees regarding the hash unless you expose it to Postgres as a user-defined function AND add a check constraint.

sirfz 1/20/2026||

It'll use less storage space

themafia 1/20/2026||

I moved into the cloud a few years ago and so I don't get to play with fixed server infrastructure like pgsql as much anymore.

Is the syntax highlighting built into pgsql now or is that some other wrapper that provides that? (it looks really nice).

tuetuopay 1/20/2026||

I generally use pgcli to that end. Works well, has a few niceties like clearer transaction state, better reconnect, syntax highlighting, and better autocomplete that works in many more cases than plain psql (it can even autocomplete on clauses when foreign key relations are defined!).

My only gripe with it is its insistence on adding a space after a line break when the query is too long, making copy/paste a pain for long queries.

hans_castorp 1/21/2026|||

No relational database implements syntax highlighting. The tools you use to write and execute SQL queries implement that.

folli 1/20/2026||

You can use an IDE like IntelliJ and you get syntax highlighting, code completion etc.

lasgawe 1/20/2026||

some points from this article that I didn't know before.

vjay15 1/23/2026|

Actually interesting read thanks haki!