postgresql-schema-design

What problem does it solve?

PostgreSQL 18 schema design patterns for multi-tenant microservice platforms. Defines standard table template with id, timestamps, soft delete, and optimistic locking. Covers data type selection (UUID v7 for primary keys, TIMESTAMPTZ not TIMESTAMP, JSONB for flexible fields), indexing strategy (B-tree for equality/range lookups, GIN for JSONB and tsvector full-text search, partial indexes for soft-delete filtering), table partitioning criteria, COMMENT ON conventions for documentation, and snake_case naming conventions. Targets PostgreSQL 18 on AWS Aurora with schema-per-tenant isolation and tenant-aware connection routing.

Core Features & Use Cases

• Standard table template: id UUID, created_at/updated_at TIMESTAMPTZ, is_deleted, version for optimistic locking.
• Data types and indexing guidance: UUIDs, TIMESTAMPTZ, JSONB with GIN indexes, partial indexes for soft-delete.
• Documentation and naming conventions: COMMENT ON for tables/columns, snake_case, and per-tenant isolation notes.

Quick Start

Design a per-tenant PostgreSQL schema using these conventions for a new microservice.