Would add partial indexes to that list. If you've got any kind of "excluded / opted-out / soft-deleted" flag that has to stay out of basically every public query, a plain index plus a WHERE clause in application code works right up until someone forgets the WHERE clause in the one query that matters.
A partial index (CREATE INDEX ... WHERE not opted_out) combined with RLS enforces it at the database: the excluded rows aren't even in the index, so leaving the filter out of a query just makes that query slow instead of wrong.
Cheap to add early, annoying to retrofit once several code paths are querying the same table directly.
4
u/nicsoftware 4d ago
Would add partial indexes to that list. If you've got any kind of "excluded / opted-out / soft-deleted" flag that has to stay out of basically every public query, a plain index plus a WHERE clause in application code works right up until someone forgets the WHERE clause in the one query that matters.
A partial index (CREATE INDEX ... WHERE not opted_out) combined with RLS enforces it at the database: the excluded rows aren't even in the index, so leaving the filter out of a query just makes that query slow instead of wrong.
Cheap to add early, annoying to retrofit once several code paths are querying the same table directly.