pg-table-template

What problem does it solve?

PostgreSQL 18 standard table DDL template and indexing strategy for a schema-per-tenant SaaS platform. It provides a consistent base for creating domain tables, ensures mandatory auditing columns, soft delete, optimistic locking, and explicit schema-prefix DDL to support per-tenant migrations without cross-tenant FK constraints.

Core Features & Use Cases

• Standard Table Template: base CREATE TABLE pattern with :schema placeholders, including common columns like id, created_at, updated_at, is_deleted, and version, plus guidance to avoid foreign keys enforced at the DB level.
• Indexing Strategy: partial indexes (WHERE is_deleted = false), CONCURRENTLY index creation, B-tree indexes for typical filters, GIN indexes on JSONB, and support for generated full-text search vectors.
• Partitioning & Auditing: guidance to partition large tables and maintain audit trails with predictable partition schemes.
• Soft Delete & Concurrency: standardized soft-delete workflow and optimistic locking to prevent lost updates.

Quick Start

Use the Standard Table Template to create a new tenant table by replacing :schema with your tenant schema and providing a real table name.