When do you choose PostgreSQL?

When the product needs transactions, relational integrity, and reporting that still looks like the operational data. Document stores fit different access patterns. We will say if Postgres is the wrong primary store.

How do you handle migrations?

As versioned, reviewed changes that run in CI and in each environment. Destructive steps are split so the app can deploy without a long lock. We keep a rollback story for schema changes that are not trivial.

Do you tune slow queries?

Yes. We look at the actual plan, add the index the access path needs, and remove indexes that only cost writes. Caching is a later step, after the query is honest.

Where does PostgreSQL run?

Usually Amazon RDS or Aurora when the app is on AWS, or another managed Postgres. Local Docker is for development. Backups and a restore drill are part of the setup.

Do you use Row Level Security?

When tenancy belongs in the database, yes — especially on Supabase or shared schemas. Otherwise we isolate by schema or database and keep enforcement in the service layer with clear tests.

How do you approach read replicas?

Replicas for reporting and heavy reads when the primary write path needs headroom. The app routes deliberately; we do not silently send writes to a replica.

Do you use JSONB in PostgreSQL?

Yes — for flexible payloads with validated shapes and indexed paths when queried. Critical invariants still live in relational columns and constraints.

How do you size connection pools?

Apps × pool size must fit max_connections. We document the math per environment and introduce a pooler when instance counts grow.

system of record

PostgreSQL

Modeled, indexed, migrated.

Zupitrix uses PostgreSQL as the system of record for products that need transactions, clear ownership of data, and queries you can explain. Schema, migrations, and indexes are designed with the access patterns — not added after the tables hurt.

Start Project

OLTPTransactional product data	SQLQueries you can read	MigrateForward and reversible	RDSOr self-managed Postgres

practice

Schema design, migrations, and query performance for PostgreSQL as the system of record.

row 1

Integrity in the database

Keys, unique constraints, and foreign keys hold the rules that must never be skipped. Application checks are extra, not the only line. We document the approach, name owners, and attach acceptance checks so “Integrity in the database” is reviewable in demos and durable after handoff.

row 2

Schema from access patterns

Tables follow how the product writes and reads. We do not normalize for its own sake or dump everything into JSON. We document the approach, name owners, and attach acceptance checks so “Schema from access patterns” is reviewable in demos and durable after handoff.

row 3

Migrations as product changes

Every schema change is a reviewed migration. Expand, deploy, then contract — so a release does not lock the table. We document the approach, name owners, and attach acceptance checks so “Migrations as product changes” is reviewable in demos and durable after handoff.

row 4

Queries with a plan

Hot paths have an index and a known plan. We watch sequential scans on large tables before users feel them. We document the approach, name owners, and attach acceptance checks so “Queries with a plan” is reviewable in demos and durable after handoff.

views

What we model

Product schemas

Tables, constraints, and seed data for accounts, orders, inventory, and the domains you own. We document the approach, name owners, and attach acceptance checks so “Product schemas” is reviewable in demos and durable after handoff.

Migration history

A tool and a process so every environment applies the same changes. We document the approach, name owners, and attach acceptance checks so “Migration history” is reviewable in demos and durable after handoff.

Performance passes

Index and query fixes for the screens and jobs that are already slow. We document the approach, name owners, and attach acceptance checks so “Performance passes” is reviewable in demos and durable after handoff.

migration

01
Entities
What is stored, what must be unique, and what is transactional. Exit criteria and owners are written down before the next step starts.
02
Schema
Tables, constraints, and the first indexes. Exit criteria and owners are written down before the next step starts.
03
Migrate
A repeatable path from empty database to current. Exit criteria and owners are written down before the next step starts.
04
Operate
Backups, slow-query visibility, and a restore check. Exit criteria and owners are written down before the next step starts.
05
Harden
Validate budgets, failure modes, observability, and rollback before traffic. Exit criteria and owners are written down before the next step starts.
06
Handoff
Runbooks, access, and training so the client team can operate without us. Exit criteria and owners are written down before the next step starts.

extensions

PostgreSQL
SQL
Prisma / Drizzle
RDS
Redis
Docker
Supabase
Grafana

joins

Inventory Management
Stock and audit data that often lives in Postgres. Share the journeys and constraints and we will map whether this engagement is the right fit.
Payment Integration
Orders and payment state with transactional writes. Share the journeys and constraints and we will map whether this engagement is the right fit.
AWS, Azure & GCP
Managed Postgres on the cloud you already use. Share the journeys and constraints and we will map whether this engagement is the right fit.

Outcomes

What we optimize for

Product and engineering goals we keep visible from the first slice through handoff — so velocity never quietly trades away quality.

ACIDTransactional truth

MigrateVersioned schema

ExplainPlans you can read

RestoreDrilled backups

PooledConnections sized

IndexedHot paths covered

At a glance

Delivery facts

Typical timelines, coverage, and operating constraints we set expectations against before the build surface expands.

Topic	Value	Note
Default OLTP	PostgreSQL	Unless constrained. Tracked against PostgreSQL delivery milestones.
Migrations	Versioned	CI applied. Tracked against PostgreSQL delivery milestones.
Integrity	In-DB	Critical rules. Tracked against PostgreSQL delivery milestones.
Perf tool	EXPLAIN	Hot paths. Tracked against PostgreSQL delivery milestones.
Hosting	RDS/Supabase/self	Ops preference. Tracked against PostgreSQL delivery milestones.
Restore	Drilled	Not assumed. Tracked against PostgreSQL delivery milestones.

Ecosystem

Technologies that sit beside it

Real stack partners we wire into delivery with clear roles — not decorative logos on a slide.

PostgreSQL

Database — integrated deliberately into delivery with PostgreSQL.

Prisma

ORM — integrated deliberately into delivery with Prisma.

Redis

Cache layer — integrated deliberately into delivery with Redis.

RabbitMQ

Async side effects — integrated deliberately into delivery with RabbitMQ.

AWS RDS

Managed host — integrated deliberately into delivery with AWS RDS.

Docker

Local Postgres — integrated deliberately into delivery with Docker.

Grafana

Query insights — integrated deliberately into delivery with Grafana.

Supabase

Postgres platform — integrated deliberately into delivery with Supabase.

Node.js

Typed GraphQL or REST clients for product screens.

Python

Data jobs — integrated deliberately into delivery with Python.

Kubernetes

Operators (opt.) — integrated deliberately into delivery with Kubernetes.

Terraform

Instance IaC — integrated deliberately into delivery with Terraform.

Integrations

Systems we commonly connect

CMS, identity, commerce, analytics, and edge services joined with typed contracts, webhooks, and ownership — not ad-hoc glue.

Prisma

App ORM — integrated deliberately into delivery with Prisma.

Hasura

Graph layer (opt.) — integrated deliberately into delivery with Hasura.

Metabase

BI — integrated deliberately into delivery with Metabase.

AWS RDS

Managed — integrated deliberately into delivery with AWS RDS.

Supabase

BaaS — integrated deliberately into delivery with Supabase.

Redis

Cache — integrated deliberately into delivery with Redis.

Architecture

Layers we design explicitly

Boundaries, ops knobs, and rollback policy for each layer — so one team can change without rewriting everything.

Logical model

Entities, constraints, tenancy keys.

Tables
FKs
Uniques

Access layer

ORM/SQL with transactions.

Prisma
SQL
Tx

Performance

Indexes, plans, pooling.

Indexes
EXPLAIN
Pooler

Change mgmt

Versioned migrations + expand/contract.

Flyway
Prisma
CI

Ops

Backups, replicas, monitoring.

RDS
PITR
Grafana

Capabilities

What we actually deliver

Concrete workstreams with owners, acceptance checks, and a path for your engineers to extend after handoff.

Schema from access patterns

Tables follow how the product writes and reads.

Integrity in the database

Keys, uniques, and FKs hold rules that must never skip.

Migration discipline

Expand → deploy → contract so releases do not lock tables.

Query performance

Plans reviewed; indexes match hot paths.

Backup & restore drills

Backups that have been restored — not assumed.

Ops visibility

Slow-query logs and connection health for on-call.

Connection pooling

PgBouncer or poolers sized to app instances.

Logical design reviews

Nullability, enums, and JSONB used intentionally.

Safe online changes

Concurrent indexes, batched backfills.

Patterns

Approaches we reach for

Reference implementations and conventions in the repo — so reviews reject one-off variants that bypass the agreed shape.

Constraints over hope

Money and uniqueness belong in the database.

Reference implementation of “Constraints over hope” lives in the repo, not only slides.

Expand → contract

Additive migrations before destructive ones.

Reference implementation of “Expand → contract” lives in the repo, not only slides.

Index for queries

Not speculative columns.

Reference implementation of “Index for queries” lives in the repo, not only slides.

JSONB with shape

Validated payloads; indexed paths when queried.

Reference implementation of “JSONB with shape” lives in the repo, not only slides.

Outbox for events

Transactional messaging without dual-write lies.

Reference implementation of “Outbox for events” lives in the repo, not only slides.

Read replica routing

Explicit — never accidental writes.

Reference implementation of “Read replica routing” lives in the repo, not only slides.

Industries

Domains we apply this in

Same technical playbook, different module depth — regulation, uptime, and partner APIs baked in early.