| technology | PostgreSQL | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| domain | backend | |||||||||||
| level | Senior/Architect | |||||||||||
| version | 16+ | |||||||||||
| tags |
|
|||||||||||
| ai_role | Senior PostgreSQL Database Architect | |||||||||||
| last_updated | 2026-03-27 |
This document establishes best practices for building and maintaining PostgreSQL databases. These constraints guarantee a scalable, highly secure, and deterministic architecture suitable for an enterprise-level, production-ready backend.
- Primary Goal: Provide an uncompromising set of rules and architectural constraints for PostgreSQL environments.
- Target Tooling: AI-agents (Cursor, Windsurf, Copilot, Antigravity) and Senior Database Administrators.
- Tech Stack Version: PostgreSQL 16+
Important
Architectural Standard (Contract): Use strict data types, enforce referential integrity, and optimize queries with appropriate indexing. Avoid business logic in stored procedures unless strictly necessary for performance.
-- Using sequential integer IDs as primary keys
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);Using sequential SERIAL IDs makes data enumeration trivial (e.g., exposing total user count via user ID 1054), complicating distributed system integration and data migrations.
-- Using UUIDv7 for time-sorted uniqueness
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
username VARCHAR(50)
);Note
Internal Routing: For more context, refer back to the Backend Index.
Start with 3NF to minimize redundancy. Use UUIDv7 for primary keys instead of SERIAL to ensure globally unique identifiers that also retain time-based sorting advantages for indexing. Selectively denormalize using Materialized Views where read-heavy workloads require optimization.
sequenceDiagram
participant App as Application Layer
participant Pool as Connection Pooler (PgBouncer)
participant DB as PostgreSQL Database
App->>Pool: Request Database Connection
Pool-->>App: Provide Connection from Pool
App->>DB: Execute Parameterized Query
DB-->>App: Return Result Set
App->>Pool: Release Connection
# Direct unencrypted connection bypassing poolers
DB_URL=postgres://app_user:pass@db:5432/app_db?sslmode=disableUsing unencrypted connections over internal networks enables MITM (Man-in-the-Middle) attacks, compromising credentials and sensitive client data. Connecting directly without a pooler WILL lead to application crashes from connection exhaustion during high load.
# Encrypted connection via PgBouncer
DB_URL=postgres://app_user:pass@pgbouncer:6432/app_db?sslmode=verify-fullEnforce SSL/TLS for all database connections (sslmode=verify-full in production). Always utilize a transaction-level connection pooler (e.g., PgBouncer, Odyssey) to manage connection limits and preserve database memory.
# Using the default postgres superuser in the application connection string
DB_URL=postgres://postgres:password@db:5432/app_dbUsing the postgres superuser for the application grants it the ability to drop tables, modify configurations, and access other databases. A single SQL injection vulnerability WILL compromise the entire cluster.
-- Creating a dedicated role with least privilege
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_pass';
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- Revoke destructive permissions
REVOKE DROP ON ALL TABLES IN SCHEMA public FROM app_user;Enforce the Principle of Least Privilege (PoLP). Create specific, restricted database roles for application services. Implement Row-Level Security (RLS) for multi-tenant applications to isolate data strictly at the database layer.
-- Blindly adding indexes to every column to "speed up queries"
CREATE INDEX idx_first_name ON users(first_name);
CREATE INDEX idx_last_name ON users(last_name);
CREATE INDEX idx_age ON users(age);Over-indexing forces the database to update multiple B-Trees on every INSERT, UPDATE, or DELETE, severely degrading write performance and bloating storage size. Unused indexes consume RAM and slow down table maintenance.
-- Creating composite indexes for specific access patterns
CREATE INDEX idx_users_name_age ON users(last_name, first_name) WHERE age > 18;
-- Monitoring unused indexes
SELECT indexrelid::regclass as index, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0;Apply indexes strategically based on query access patterns. Use B-Tree indexes for equality/ranges, and GIN/GiST indexes for Full-Text Search or JSONB. Regularly monitor and drop unused indexes (e.g., via pg_stat_user_indexes).
-- Fetching all columns and using inefficient offset pagination
SELECT * FROM orders ORDER BY created_at DESC OFFSET 100000 LIMIT 50;Using SELECT * forces the database to fetch and transfer unnecessary data, consuming network bandwidth and memory. Using OFFSET/LIMIT for deep pagination requires the database to scan and discard rows before returning results, resulting in exponential performance degradation.
-- Selecting only necessary columns and using Keyset (Cursor) Pagination
SELECT id, status, total
FROM orders
WHERE created_at < '2023-10-25T10:00:00Z'
ORDER BY created_at DESC
LIMIT 50;