1. Every database starts the same way

One schema. public. A handful of tables. Nobody thinks twice about it.

Fast forward a year: the product grew, the team grew, the feature list grew — and so did the schema. Not “grew” in a good way. public became a junk drawer.

public.users
public.orders
public.market_prices
public.chain_blocks
public.audit_log
public.sessions
public.tokens
public.price_history
public.notifications
public.webhooks
... and 290 more

No boundaries. No ownership. No organization. Just tables, alphabetically sorted in whatever GUI you’re using, hoping the name gives you a clue about what it’s for and who’s allowed to touch it.

This is the default state of most PostgreSQL databases I’ve inherited or reviewed. It’s not a failure of any one engineer — it’s what happens when nobody actively decides not to let it happen. public is the schema Postgres gives you for free, so it's the schema everyone uses, forever, by default.

I stopped doing that. Here’s what changed, and why it mattered more than I expected.

2. What “everything in public" actually costs you

None of the problems with a single flat schema are dramatic on day one. They compound. By the time they’re painful, untangling them is a project, not a fix.

Naming conflicts. Two teams both want a table called events. One means user analytics events, the other means blockchain events. In one schema, you get user_events and chain_events — prefixes doing the job namespaces should be doing.

Difficult navigation. Open any GUI against a 300-table public schema and try to find anything. There's no structure to lean on — just a flat, alphabetical list that tells you nothing about what belongs together or how the system is shaped.

Mixed responsibilities. Tables for completely unrelated domains sit side by side with nothing enforcing separation. Application logic that should never touch raw ledger data can join straight into it, because Postgres has no idea those things were supposed to be kept apart.

Harder permissions. Want to grant a read-only analytics role access to reporting tables, but not to auth tokens or internal audit data? In a flat schema you’re granting table-by-table, forever chasing new tables that should’ve been included and weren’t. Permissions become a checklist instead of a rule.

Poor maintainability. Migrations, backups, and access reviews all become “the whole database” operations, because there’s no smaller unit than everything to reason about. You can’t reason about “the market domain” separately from “the audit domain” — there is no “the market domain.” There’s just public.

None of these are exotic problems. They’re the ordinary, predictable cost of using one namespace for a system that actually has several distinct areas of responsibility.

3. Think in domains, not in tables

The fix isn’t a clever Postgres trick. It’s borrowing an idea backend engineers already apply everywhere else in the codebase: separate things by domain, not by convenience.

You wouldn’t put your auth logic, your billing logic, and your third-party integrations in the same module just because they’re all “backend code.” You’d split them because they change for different reasons, are owned by different concerns, and shouldn’t casually reach into each other’s internals.

A PostgreSQL schema is that same boundary, at the database layer.

In the backend I work on, the domains looked roughly like this:

  • chain_data — raw, append-heavy data ingested from external chains: blocks, transactions, logs. Written by ingestion services, rarely mutated.
  • market — derived market state: prices, pairs, aggregated volumes. Built from chain_data, read heavily by the API layer.
  • users — accounts, auth, sessions, preferences. The most permission-sensitive schema in the system.
  • audit — immutable logs of who did what, when. Written constantly, almost never read except for investigations.
  • analytics — rollups and reporting tables, rebuilt on a schedule, disposable by design.

Each schema owns its tables. Each one has a clear reason to exist, a clear owner, and a clear answer to “what’s allowed to write here, and why.”

That’s the actual value of a schema: it’s not a namespace trick to avoid name collisions. It’s a place to put an architectural decision, permanently, where every future engineer touching the database will see it.

4. My design: what actually moved where

Here’s roughly how tables were regrouped, and the reasoning behind each move:

CREATE SCHEMA chain_data;
CREATE SCHEMA market;
CREATE SCHEMA users;
CREATE SCHEMA audit;
CREATE SCHEMA analytics;
-- Raw ingestion — append-only, high volume, no business logic
chain_data.blocks
chain_data.transactions
chain_data.logs

-- Derived state - computed from chain_data, consumed by the API
market.prices
market.pairs
market.volume_snapshots
-- Identity and access - the schema everyone is most careful around
users.accounts
users.sessions
users.api_tokens
-- Immutable history - written by triggers/app code, rarely queried live
audit.action_log
audit.permission_changes
-- Disposable, rebuildable reporting layer
analytics.daily_rollups
analytics.retention_cohorts

The grouping isn’t arbitrary — each schema maps to a reason tables change together. chain_data changes because upstream sources change. market changes because chain_data changes. users changes because auth requirements change. audit changes because someone did something. analytics changes on a schedule and can be dropped and rebuilt without anyone panicking.

Once tables are grouped this way, a question like “what does the ingestion service actually need write access to?” has a one-line answer: chain_data. Before, that question required reading through a list of 300 tables and guessing.

This is also where ownership becomes real instead of aspirational. “The market team owns market" is enforceable with a GRANT statement. "The market team owns *some tables, scattered throughout *public, that we've informally agreed are theirs" is not enforceable — it's a tribal knowledge problem waiting to bite someone during an incident.

5. What I gained

  • Better organization. Opening the database now tells you something about the system’s shape. Five schemas, five domains, no guessing.
  • Cleaner SQL. market.prices and chain_data.blocks read like what they are. The schema prefix isn't overhead — it's documentation that ships with every query.
  • Easier permissions. Grant a role access to a schema, not to a hand-picked list of tables you have to remember to update every time someone adds a new one:
GRANT USAGE ON SCHEMA analytics TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO reporting_role;
  • Logical ownership. Each schema has an obvious answer to “who’s responsible for this.” That answer used to require asking someone who’d been on the team long enough to remember.
  • Simpler migrations. Migration tooling and review can scope to a schema. A migration touching audit is obviously lower-risk to the API than one touching users. That risk used to be invisible until someone actually read the diff.

None of these benefits came from a new tool, a new extension, or a rewrite. They came from CREATE SCHEMA and some intentionality about where tables live.

6. The trade-offs — because there are always trade-offs

Schemas aren’t free, and pretending otherwise would undersell the decision.

Slightly longer queries. market.prices is more to type than prices. It's also more to read, in a good way — but if your codebase leans on unqualified table names everywhere, you'll be touching a lot of queries during the transition.

Search path configuration. Postgres resolves unqualified names using search_path, and it's tempting to just add every schema to it so old queries keep working. Resist that. A generous search_path quietly recreates the exact ambiguity you were trying to eliminate — two schemas with a same-named table, and now it's not obvious which one a query hits. Qualify your names explicitly instead of leaning on search path magic.

Cross-schema joins. Once market depends on chain_data, a join across schemas is normal and fine — but it's worth noticing when it happens a lot. Frequent, heavy cross-schema joins can be a sign that a boundary was drawn in the wrong place, or that a domain genuinely needs a materialized view instead of a live join every time. Schemas don't remove that judgment call. They just make it visible instead of invisible.

None of these trade-offs are reasons to avoid schemas. They’re reasons to be deliberate about how you use them — the same way you’d be deliberate about module boundaries in application code.

7. Lessons

  • Schemas are architectural boundaries, not folders. Treat CREATE SCHEMA as a design decision, not a formatting preference.
  • Design your database like you design your code. Domain separation, ownership, and access control matter at the schema level for the exact same reasons they matter at the module level.
  • public is a default, not a decision. If every table in your database lives there, nobody has actually decided how the system is organized — it organized itself, by accident, over time.
  • Boundaries should map to reasons things change together. Group tables by why they change, not by when they were created.
  • The best time to introduce schemas is before you have 300 tables in one place. The second best time is now.

Your database is part of your architecture. Stop treating public like it isn't.