postgresql-code-review

Check that JSONB and array columns use GIN indexes, queries use containment operators like @> instead of unindexed key lookups, and triggers fire only when rows actually change. Also verify TIMESTAMPTZ is used instead of TIMESTAMP and window functions or CTEs replace inefficient patterns.

Common JSONB anti-patterns include querying with data->>'key' without a supporting index, treating JSONB as an unstructured string, and deep nesting without validation. Use containment queries with GIN indexes and add CHECK constraints to validate JSONB structure.

Row Level Security enforces per-row access at the database level using policies tied to roles or session settings. It complements application-level authorization but still requires granular GRANT statements for table and sequence privileges.

Use ENUM types when a column has a fixed, limited set of values such as status or currency codes. ENUMs enforce validity at the database level and avoid the inconsistency and wasted space of free-form VARCHAR values.

Array queries are slow when using = ANY() without an index. Create a GIN index on the array column and rewrite the query with the containment operator @> so PostgreSQL can use the index for lookups.