Designing a Postgres schema you won't regret

Application code is cheap to change. You fix it, deploy it, and the old version is gone in minutes. A schema isn't like that: every decision in it is baked into rows that already exist, and every correction has to run against a table that's serving traffic. Most of what I think about as a Django developer in Nepal working on PostgreSQL comes down to one question — which of these choices will be expensive to undo once the table is big?

Put the rules where every write passes through

Django gives you a comfortable illusion that your models validate themselves. They don't. Model.save() does not call full_clean() — the docs say so plainly — and Django REST framework's ModelSerializer stopped calling it in 3.0. QuerySet.update() and bulk_create() skip save() entirely. Then there's the management command, the data-fix script, and whoever opens psql during an incident.

The database is the only component that sees every one of those writes, so it's the only place a rule is actually guaranteed. A few that belong there:

• Choices are not constraints. A CharField with choices is enforced by model validation, which as above often doesn't run. Back it with a CheckConstraint(condition=Q(status__in=[...])). (The argument was called check before Django 5.1 deprecated it in favour of condition.)
• Conditional uniqueness. "One open cart per user" is a UniqueConstraint with a condition, which Postgres implements as a partial unique index. Checking it in Python first is a race you will eventually lose.
• Invariants on numbers. Stock can't go below zero. With CheckConstraint(condition=Q(quantity__gte=0)) and a decrement written as update(quantity=F("quantity") - 1), two customers racing for the last item get one success and one IntegrityError — not an oversold order you discover when the rider is already on the way.

Types that are cheap now and expensive later

Choosing a column type takes a second. Changing it on a large table usually means a full rewrite of the table and its indexes under an exclusive lock. So these are worth getting right on day one:

• Primary keys are bigint. A plain integer tops out at 2,147,483,647. Django 6.0 finally made BigAutoField the default for DEFAULT_AUTO_FIELD, but older projects that pinned AutoField are still on 32-bit keys — and widening one later rewrites the table and every foreign key column pointing at it.
• Money is numeric, never float. Use DecimalField, or store integer minor units if you prefer. Not Postgres's own money type either; the community "Don't Do This" page recommends numeric over it.
• Timestamps carry a time zone. Django's DateTimeField already maps to timestamp with time zone on Postgres, which is right. The trap is older projects running with USE_TZ = False — the default only became True in Django 5.0 — writing naive local times into it.
• Status fields are text plus a check, not a native enum. Postgres lets you add enum values, but it can't remove one without dropping and recreating the type. Statuses get retired more often than people expect.

PostgreSQL in Nepal: paisa, rupees and a 5:45 offset

Two local details deserve a place in the schema discussion because they cause quiet, expensive bugs.

The first is currency units. Khalti's ePayment API takes amount in paisa. eSewa's ePay v2 takes total_amount in rupees. If your schema doesn't make its own unit unambiguous — a column name like amount_paisa helps, or a numeric(12, 2) in rupees and a single conversion function per gateway — sooner or later someone passes Rs 100 where the API expected paisa, and the reconciliation job flags a payment that's off by a factor of a hundred.

The second is Nepal's UTC+5:45 offset. timestamptz stores an absolute instant, which is what you want, but the moment you group by day you have to say whose day. A raw SQL report running date_trunc('day', created_at) in a UTC session puts the day boundary at 5:45 in the morning Kathmandu time, so late-night orders get counted against the wrong date. Django's Trunc functions use the current time zone when USE_TZ is on; raw SQL and BI tools don't. Be explicit: date_trunc('day', created_at AT TIME ZONE 'Asia/Kathmandu').

Index the queries you actually run

Postgres doesn't index foreign key columns automatically, but Django does — every ForeignKey gets one unless you set db_index=False. So the indexes worth thinking about are the ones that match your real access patterns:

• Composite indexes, in the right order. A B-tree on (store_id, status, created_at) serves "pending orders for this store, newest first". Equality columns go first, the range or sort column last. The same three columns in a different order is a different, often useless, index.
• Partial indexes for hot subsets. If most queries only care about the small fraction of rows that are still active, Index(fields=[...], condition=Q(status="pending")) stays small and cheap to maintain while the table grows underneath it.

Every index slows every write to that table, so add them for a query plan you've looked at, not for a column that feels important. How to find the missing ones is covered in why your Django app gets slow as it grows.

The migrations that get expensive

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock. Holding it briefly is fine. The problem is waiting for it: if a long-running query holds the table, your migration queues behind it, and every ordinary SELECT that arrives afterwards queues behind your migration. A change that should take milliseconds takes the table offline for as long as that one slow query runs. Set lock_timeout on the migration session so it fails fast and can be retried, rather than freezing traffic.

Run python manage.py sqlmigrate before anything touches production, and read the SQL. Then watch for these:

• Adding a column with a default is fast since PostgreSQL 11, as long as the default isn't volatile — the value is stored in the catalog, not written into every row. A volatile default like clock_timestamp() still rewrites the table.
• Making an existing column NOT NULL scans the whole table under that exclusive lock. The safer route: add a CHECK (col IS NOT NULL) with AddConstraintNotValid, validate it in a separate migration with ValidateConstraint (which only takes a SHARE UPDATE EXCLUSIVE lock), then set NOT NULL — Postgres skips the scan when a valid check constraint already proves there are no nulls.
• Creating an index blocks inserts, updates and deletes for the whole build. Use AddIndexConcurrently from django.contrib.postgres.operations in a migration with atomic = False. If it fails, it leaves an invalid index behind that still costs write overhead; drop it and try again.
• Changing a column's type normally rewrites the table and its indexes. Some changes are exempt — widening a varchar limit, for instance — but integer to bigint isn't one of them.
• Renaming or dropping a column is cheap for Postgres and dangerous for you, because during a rolling deploy the old code is still running and still selecting the old name. Expand, then contract: add the new column, write to both, backfill, move reads over, and drop the old one in a later release.

What this buys you

None of this is exotic. It's a handful of decisions made when a table is empty and a handful of habits for when it isn't. What it buys is a database that refuses bad data on its own, and migrations you can run in the middle of the day without anyone noticing.

That matters more when the product runs on a clock. At Fasto the promise is delivery in ten minutes, and a locked orders table isn't a maintenance inconvenience — it's a missed promise. More on how I approach the Django and PostgreSQL backend and the deployment pipeline that runs these migrations.