Databases

PostgreSQL is the standard database platform for application data.

Unless a service has a clear workload-driven reason to use something else, new systems should start with PostgreSQL. It is the standard relational database in our stack and the default choice for transactional workloads, internal tools, and most service-backed applications.

Default Standard

Use PostgreSQL when a system needs:

• Relational data modeling
• Transactions and strong consistency guarantees
• Mature tooling for migrations, backups, and operations
• Predictable support across local development, CI, and hosted environments

Choosing a different database should be an explicit architectural decision, not the default starting point.

Why PostgreSQL

PostgreSQL works well as the baseline because it gives us:

• Strong SQL support for querying, reporting, and data integrity
• ACID transactions for multi-step writes
• Indexing, constraints, and foreign keys for enforcing correctness
• Broad library and hosting support across our languages and platforms
• A clear path from local Docker-based development to managed production deployments

For most business applications, PostgreSQL is the right first choice before introducing specialized data stores.

Schema and Migrations

Database schema should be treated as application code.

Expectations:

• Keep schema changes in version-controlled migrations committed with the application
• Store migrations in a top-level migrations/ directory when the repository owns the database schema
• Use ordered, forward-only migration files such as 00001_create_users.sql
• Apply migrations through a repeatable command in local development, CI, and deployment workflows
• Never make manual production schema changes that are not represented in source control

After a migration has been applied in a shared environment, do not rewrite it. Create a new migration to correct or extend the schema instead.

Modeling Guidelines

Prefer schemas that are explicit, constrained, and easy to evolve.

Recommended practices:

• Use descriptive snake_case names for tables and columns
• Define a primary key on every table
• Add foreign keys when records have real ownership or referential relationships
• Use NOT NULL, UNIQUE, and CHECK constraints to enforce rules in the database, not only in application code
• Add indexes for real query patterns such as joins, lookups, and ordered pagination
• Store timestamps in UTC and prefer PostgreSQL timestamp types over string representations
• Avoid storing large binary assets in PostgreSQL when object storage is the better fit

Start with a normalized model unless there is measured evidence that denormalization materially improves performance or simplifies the workload.

Application Access Patterns

Services should interact with PostgreSQL predictably and safely.

Baseline expectations:

• Use parameterized queries or a well-understood query builder or ORM
• Do not build SQL by concatenating untrusted input
• Keep transactions short and scoped to a single unit of work
• Paginate large result sets instead of loading entire tables into memory
• Be deliberate about connection pool sizing
• Review slow queries and add indexes only where workload data supports them

If a service uses an ORM, the ORM does not replace the need to understand the generated SQL, indexing strategy, or transactional behavior.

Local Development

PostgreSQL should be easy to run locally, typically through Docker.

For local repositories:

• Define the database service in compose.yml when the application depends on PostgreSQL
• Use a named volume when persistent local state is useful
• Expose configuration through environment variables such as DATABASE_URL
• Document bootstrap steps in the repository README.md
• Provide a clear command for applying migrations and seeding development data when needed

Local development should mirror production behavior closely enough that schema, connectivity, and migration issues are caught early.

Operational Expectations

Production PostgreSQL deployments should have:

• Automated backups
• Monitored storage, connection counts, and query performance
• Restricted access through least-privilege credentials
• A documented restore process
• Version upgrades planned intentionally rather than deferred indefinitely

For performance issues, start by examining query plans, indexes, and query shape before introducing additional infrastructure.