postgresql-table-design

PostgreSQL schema design covering best practices, data types, indexing, constraints, and performance patterns. Enable with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY . Create policies: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()) . Built-in user-based access control at the row level. - Prioritize normalization to 3NF; denormalize only when join performance is proven problematic and measured for ROI. - Use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys unless global uniqueness or opacity requires UUID ; always add indexes on foreign key columns. - Choose data types carefully: TIMESTAMPTZ for events, NUMERIC for money, TEXT for strings, JSONB for semi-structured data; avoid TIMESTAMP , VARCHAR(n) , SERIAL , and MONEY type. - Index strategically for actual query patterns: B-tree for equality/range, GIN for JSONB/arrays/full-text, GiST for ranges/geometry, BRIN for large time-series data.