postgresql

# PostgreSQL ## Data Type Defaults | Need | Use | Avoid | |------|-----|-------| | Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` | | Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) | | Text | `TEXT` | `VARCHAR(n)` unless constraint needed | | Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` | | Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans | | JSON | `JSONB` | `JSON` (no indexing), text JSON | | UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension | | IP addresses | `INET` / `CIDR` | text | | Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns | ## Schema Rules - Every FK column gets an index (PG does NOT auto-create these) - `NOT NULL` on every column unless NULL has business meaning - `CHECK` constraints for domain rules at DB level - `EXCLUDE` constraints for range overlaps: `EXCLUDE USING gist (room WITH =, during WITH &&)` - Default `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` - Separate `updated_at` with trigger, never trust app layer alone - Use `BIGINT` PKs — cheaper JOINs than UUID, better index locality ## Index Strategy | Type | Use When | |------|----------| | B-tree (default) | Equality, range, sorting, `LIKE 'prefix%'` | | GIN | JSONB (`@>`, `?`, `?&`), arrays, full-text (`tsvector`) | | GiST | Geometry, ranges, full-text (smaller but slower than GIN) | | BRIN | Large tables with natural ordering (timestamps, serial IDs) | **Index rules:** - Composite: most selective column fir...