Database Objects and Schema Design
Schemas and search_path
A schema is a namespace and every object lives in one. search_path (default "$user", public) decides how unqualified names resolve, and unqualified CREATE statements land in the first schema on that path.
Constraints and indexes
PRIMARY KEY implies UNIQUE + NOT NULL and builds a unique btree index; UNIQUE also builds an index. A foreign key does not create an index on the referencing column - add one yourself, or cascading deletes and joins will scan. CHECK constraints are not verified against existing rows until ALTER TABLE ... VALIDATE CONSTRAINT.
Index types: btree (default; equality, range, sorting), hash (equality only), GIN (arrays, jsonb, full-text), GiST (geometry, ranges), BRIN (very large append-only tables). Partial indexes (WHERE active) and expression indexes (lower(email)) are cheap wins, but the query predicate must match the index expression exactly.
Identity and defaults
Prefer GENERATED ALWAYS AS IDENTITY to serial: it is SQL-standard, and serial is merely an integer with a nextval default and a separately owned sequence. GENERATED BY DEFAULT still permits explicit inserts; ALWAYS requires OVERRIDING SYSTEM VALUE.
Other objects
Views store queries; materialized views store rows and need REFRESH MATERIALIZED VIEW (optionally CONCURRENTLY, which requires a unique index). Declarative partitioning supports RANGE, LIST and HASH; a row matching no partition raises an error unless a DEFAULT partition exists. Triggers, rules, extensions and domains round out the object list.
Rule of thumb
Index every foreign-key column, and remember every index taxes write throughput and autovacuum work.