PostgreSQL Database Templates

This repository is a production-oriented PostgreSQL SQL toolkit targeting PostgreSQL 18.4. It provides independently runnable templates for application databases, administration, performance, security, recovery, reporting, and high availability. Templates use PostgreSQL 14+ syntax unless the script itself says otherwise.

Do not run the entire repository automatically. Select the templates that match the system you are building, review them in a pull request, test them in a non-production environment, and deploy them through your migration process.

Before you start

You need:

1. PostgreSQL client tools (psql, pg_dump, pg_restore) matching the server major version where possible.
2. A target database and a dedicated deployment login. The login should not be a superuser in normal operation.
3. A tested backup and an approved recovery/rollback plan for any environment containing valuable data.
4. A source-controlled migration directory in your application or infrastructure repository. Treat the templates here as source material, not as a production deployment tool by itself.
5. A non-production environment with representative volume and permissions.

Check the connected server before choosing templates:

psql -X -d application_db -c "SHOW server_version;"
psql -X -d application_db -c "SELECT current_database(), current_user, current_setting('TimeZone');"

Folder reference

Every folder contains a documented SQL template. Names have no numeric prefixes; ordering is described below rather than encoded into names.

Folder	Use it for
Database	Database-level locale, timezone, and safety settings.
Schemas	Application schema creation and baseline schema access.
Tables	Normalized tenant, customer, and order tables.
Views	Security-barrier active-customer view.
MaterializedViews	Refreshable reporting summaries.
Indexes	B-tree, partial, JSONB GIN, expression, and BRIN indexes.
Constraints	Online-friendly constraint validation.
Sequences	Explicit sequences and ownership.
Functions	Trigger and optimistic-concurrency helpers.
Procedures	Bounded maintenance procedures.
Triggers	Timestamp and audit triggers.
Roles	Role separation patterns.
Permissions	Least-privilege object and default privileges.
Partitioning	Monthly range partitioning.
Inheritance	Legacy inheritance use case.
Extensions	Approved extensions: citext, pg_trgm, hstore, and tablefunc.
Transactions	Serializable UPSERT and savepoint pattern.
Backup	Logical-backup runbook commands.
Restore	Logical-restore runbook commands.
Migration	Migration ledger and transactional change example.
DataSeed	Idempotent reference data.
TestData	Guarded test-only data.
Queries	Keyset pagination, windows, and recursive CTEs.
Reports	Aggregate sales report.
Audit	Audit retention pattern.
Logging	Database logging configuration.
Performance	EXPLAIN, ANALYZE, and REINDEX guidance.
Monitoring	Connections, locks, long queries, and index usage.
Security	Masking and password-storage guidance.
RowLevelSecurity	Tenant isolation policy.
JSON	SQL/JSON construction and path queries.
JSONB	JSONB updates and containment queries.
Array	Arrays and GIN indexing.
FullTextSearch	Generated tsvector and full-text search.
PostGIS	Spatial extension, geometry, and GiST index.
UUID	pgcrypto UUID generation.
Replication	Logical replication publication pattern.
HighAvailability	Standby and replication health checks.
Vacuum	Vacuum and per-table autovacuum tuning.
Analyze	Extended statistics.
Maintenance	Invalid-index and table-health checks.
Examples	A short end-to-end deployment order.

Quick start: new application database

The core templates use these example roles: app_owner, app_deploy, and app_runtime. Create equivalent roles first, changing names to follow your organization’s standard. Do not grant application connections ownership of database objects.

1. Create the database from a maintenance connection. CREATE DATABASE cannot run inside a transaction.

   CREATE ROLE app_owner NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
   CREATE ROLE app_runtime NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
   CREATE ROLE app_deploy LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
   GRANT app_owner TO app_deploy;
   CREATE DATABASE application_db OWNER app_owner ENCODING 'UTF8' TEMPLATE template0;

2. Review and run the extension script. citext and pgcrypto are dependencies of the core table template.

   psql -X -v ON_ERROR_STOP=1 -d application_db -f Extensions/approved_extensions.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f UUID/uuid_defaults.sql

3. Connect as the deployment role (or role that can assume app_owner), then create the schema and core tables.

   psql -X -v ON_ERROR_STOP=1 -d application_db -f Schemas/application_schema.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Tables/core_tables.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Indexes/workload_indexes.sql

   Indexes/workload_indexes.sql uses CREATE INDEX CONCURRENTLY; run it as shown, in its own psql call, not inside an enclosing BEGIN/COMMIT migration.

4. Add behavior and access controls in this dependency order:

   psql -X -v ON_ERROR_STOP=1 -d application_db -f Functions/utility_functions.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Triggers/audit_and_timestamp_triggers.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Views/active_customer_view.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Permissions/least_privilege_grants.sql
   psql -X -v ON_ERROR_STOP=1 -d application_db -f DataSeed/reference_data.sql

5. If the database is multi-tenant, review and enable RowLevelSecurity/tenant_isolation.sql only after application connection setup can reliably set the tenant context. In every request transaction, set it using a parameterized server-side query:

   BEGIN;
   SELECT set_config('app.tenant_id', '00000000-0000-0000-0000-000000000001', true);
   SELECT * FROM app.v_active_customer;
   COMMIT;

6. Verify before application cutover:

   psql -X -d application_db -c "\dn+ app"
   psql -X -d application_db -c "\dt+ app.*"
   psql -X -d application_db -c "\dp app.*"
   psql -X -v ON_ERROR_STOP=1 -d application_db -f Monitoring/health_queries.sql

How to modify a template

Follow this process for each selected script:

1. Copy the template to a versioned migration in your application repository, for example migrations/20260802_add_customer_locale.sql. Do not edit a migration already applied to a shared environment.
2. Read the complete SQL header. It states dependencies, transaction constraints, rollback, security impact, and expected cost.
3. Replace example object names (app, app_owner, app_runtime, application_db) with approved organization-specific names. Search the file, not just the first occurrence.
4. Replace fixed sample UUIDs and test values. Keep values parameterized in application code; never concatenate user input into SQL.
5. Preserve explicit constraint and index names. If you rename a table or column, update dependent foreign keys, views, policies, triggers, indexes, functions, reports, and rollback commands.
6. For a large existing table, choose low-lock patterns: add nullable columns first, backfill in batches, create indexes concurrently, add constraints as NOT VALID, validate later, then enforce NOT NULL where appropriate.
7. Run the migration in a disposable database, then in a restored production-sized staging database. Capture EXPLAIN (ANALYZE, BUFFERS) for changed read paths.
8. Have another engineer review both forward and rollback scripts. Record the deployed migration checksum and release identifier.

Migration and deployment rules

Use one migration per logical change. Each migration must be immutable after deployment. Apply migrations with ON_ERROR_STOP so a failing statement stops the process:

psql -X -v ON_ERROR_STOP=1 --set=VERBOSITY=verbose -d application_db -f migrations/20260802_add_customer_locale.sql

Use a transaction for compatible DDL and data changes. Do not wrap these operations in a transaction block: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, VACUUM FULL, and REFRESH MATERIALIZED VIEW CONCURRENTLY. Set sensible lock_timeout and statement_timeout per migration. A lock-timeout failure is safer than indefinitely blocking production traffic.

For retryable database errors, applications must retry the whole transaction for SQLSTATE 40001 (serialization failure) and 40P01 (deadlock detected). Do not retry only the last statement.

Rollback and recovery

Each SQL template includes a rollback section, but some data changes cannot be logically undone after new writes occur. Before destructive changes, take and verify a backup. A safe rollback decision sequence is:

1. Stop the deployment and determine whether the failed change committed.
2. Check active locks and transactions with Monitoring/health_queries.sql.
3. If safe, execute the template’s rollback statements in reverse dependency order.
4. If data was deleted or transformed, recover from a tested backup or point-in-time recovery process—not from an improvised compensating update.
5. Validate application behavior, role grants, RLS, row counts, and query latency before resuming traffic.

Use Backup/logical_backup_runbook.sql and Restore/restore_runbook.sql as operational instructions. Keep backup credentials and artifacts out of this repository.

Performance guide

Choose indexes from observed queries. B-tree is the default for equality, ranges, joins, and ordered access. Use GIN for JSONB, arrays, or full-text search; BRIN for very large append-ordered data; GiST for PostGIS. Every additional index increases write cost and vacuum work.

Use Performance/explain_statistics_and_reindex.sql to inspect plans and Analyze/statistics.sql for correlated columns. Do not add an index solely because a column is frequently mentioned. Test predicates, selectivity, sort order, table size, and write volume first. Use keyset pagination in Queries/reporting_and_pagination.sql for deep result sets rather than growing OFFSET values.

Security guide

Apply least privilege: object owners deploy DDL, runtime roles receive only required privileges, and PUBLIC has no application-schema access. Use TLS, managed secret storage, and explicit connection limits outside this repository. Store password hashes only, using an approved authentication design; never store plaintext passwords.

Review all SECURITY DEFINER functions carefully. Their search_path must be pinned and they must not interpolate untrusted SQL identifiers. Enable Row Level Security only when tenant context is set on every transaction and tested with a non-owner runtime role. Audit grants after every new object by inspecting \dp output.

Routine operations checklist

• Daily: review backup completion, failed jobs, replication lag, lock waits, and long transactions.
• Weekly: inspect Maintenance/maintenance_checks.sql, dead tuples, autovacuum activity, and slow-query telemetry.
• Monthly: restore a backup to an isolated environment; review extension and PostgreSQL minor-version updates; revisit top query plans and index usage.
• Before every release: validate migration/rollback, test under representative load, and record owners, approval, timing, and monitoring plan.

Common problems

Symptom	First action
Script fails on gen_random_uuid or citext	Install pgcrypto/citext first through Extensions and UUID.
Index creation blocks or errors in a transaction	Run the index script alone; it uses CONCURRENTLY.
RLS returns no rows	Verify SET LOCAL app.tenant_id occurs in the same transaction and test as app_runtime.
Migration waits indefinitely	Inspect ungranted locks in Monitoring/health_queries.sql; cancel or reschedule according to the incident process.
Query becomes slow after a change	Compare EXPLAIN (ANALYZE, BUFFERS) with previous plan, then check statistics and index suitability.
Restore succeeds but application fails	Validate extensions, role grants, RLS policies, connection settings, and application migrations.

PostgreSQL target release: PostgreSQL 18.4 official release notes.