Unconventional PostgreSQL Optimizations
61 points by Haki
61 points by Haki
I know this is not the point of the article, but wouldn't the first issue go away if you moved plan_check to a domain type with citext? Would the indexes still work fine?
The second section about people abusing BTREE is on point tho, I lost count on how many times I see developers slapping multiple BTREE indexes in the same table and then wondering why everything becomes slow after a while. The virtual generated column trick is neat, one more reason to move to pg18 asap.
That example is obviously just meant as a stand-in illustration of the general situation of accidentally running a query that contradicts constraints. But taken at face value, I think in this particular case the better solution is nowadays an enum rather than a domain over text. Over the years Postgres has made altering enums a far less painful experience, to where it’s now a good idea to use them for the sort of thing shown in that particular code example. Using them traps mistakes like the one shown more quickly, saves on storage cost, and makes queries faster and/or indices less necessary (which then further reduces storage cost and in turn then also speeds up writes). The only downside is that you can’t define an enum inline in a table definition the way you can with a check constraint, so the skimmability of your schema takes somewhat of a hit.
You also cannot remove a value from an enum (without manually messing around in the catalog)
True, that is not addressed. The usual options to handle this scenario still work (creating a new enum and changing the column type, which requires a table rewrite, or adding a constraint to the column, which is cheap but very ugly) but they certainly aren’t pretty or comfortable.
I would be surprised if the planner treated the check constraint of a domain type different than one assigned to a column. Changing the type to the case-insensitive citext obviously would make a difference, but maybe you don’t want to match ignoring case.
Also you probably want to use non-deterministic collations instead of citext nowadays.
you probably want to use non-deterministic collations instead of citext nowadays.
Why?
Just quoting from the citext documentation:
Consider using nondeterministic collations (see Section 23.2.2.4) instead of this module. They can be used for case-insensitive comparisons, accent-insensitive comparisons, and other combinations, and they handle more Unicode special cases correctly.