The database
we default to.

Why PostgreSQL

Postgres is the rare database that's both mature and modern. It's been in production for 30 years, yet it ships features faster than any competitor — jsonb, arrays, full-text search, window functions, CTEs, extensions, and now pgvector.

Postgres vs others

Every database is a set of trade-offs. Here's when we pick Postgres, and when we don't.

Data types we reach for

One of the reasons we pick Postgres over MySQL is the type system. Choosing the right type eliminates a whole class of application bugs.

Extensions we use

Postgres ships with a plugin system. These are the extensions that appear on almost every project we run.

SQL patterns we use

Real snippets from production systems — schema design, complex queries, full-text search, pgvector, and zero-downtime migrations.

-- Multi-tenant invoices table
CREATE TABLE invoices (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenants(id),
  customer_id  uuid NOT NULL REFERENCES customers(id),
  amount       numeric(14,2) NOT NULL CHECK (amount > 0),
  currency     text NOT NULL DEFAULT 'GHS',
  status       text NOT NULL DEFAULT 'pending',
  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb,
  tags         text[] NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Indexes that matter
CREATE INDEX idx_invoices_tenant ON invoices (tenant_id, created_at DESC);
CREATE INDEX idx_invoices_customer ON invoices (customer_id);
CREATE INDEX idx_invoices_pending ON invoices (tenant_id) WHERE status = 'pending';
CREATE INDEX idx_invoices_metadata ON invoices USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_invoices_tags ON invoices USING GIN (tags);

Indexing strategy

Most slow queries are missing indexes — or have the wrong one. These are the rules we apply.

Performance tuning

Postgres is fast by default. When it isn't, we measure before we change anything.

Observability

You can't tune what you can't see. Every Postgres instance we manage has these wired up from day one.

Partitioning

Once a table crosses tens of millions of rows, partitioning keeps indexes small and queries fast. It's a design decision, not a rescue mission.

Replication & HA

High availability on Postgres is well-trodden ground. We use the standard tools and drill the failover regularly — a failover that hasn't been tested doesn't count.

Migrations

Schema changes are where production databases get hurt. Zero-downtime is a discipline, not a feature.

ORMs

We use ORMs — but not exclusively. Typed queries for CRUD, raw SQL for the queries that matter.

Security

Backups & recovery

Anti-patterns

The things that cause performance problems in production — and that we never do.

Managed vs self-hosted

Managed by default. Self-hosted only when there's a specific reason: cost at scale, custom extensions, or data residency requirements.

Real-world use

Six categories of production systems we've shipped on Postgres.

Versions

When to use Postgres. When not to.

Process

Five phases. Every Postgres engagement we run follows them.