Postgres Table Conventions — Naming, Keys and Audit Columns for a Consistent Schema

Published Jun 12, 2026
by

Creating a table takes thirty seconds – being annoyed about its name, its key, and its column types then takes two years. Postgres table conventions take that annoyance off the table up front: consistent names, a predictable primary key, data types without surprises, and audit columns that explain every row. Without them, a schema drifts further apart with every new table – and it comes back to bite you at the first join, the first audit, the first schema diff.

This article distils a battle-tested set of Postgres table conventions from a production framework: how tables are named, which primary key they carry, which data types apply, and how constraints, comments, and seed data are laid out cleanly. One thing up front: these are the conventions of one framework, not an official PostgreSQL standard. Where a choice is a matter of style rather than a technical must, I say so.

What you’ll take away:

  • Naming: snake_case, singular, and why most tables get a surrogate key id with a named CONSTRAINT pk_<table> – natural keys become UNIQUE, not the PK;
  • Data types: varchar over text (a deliberate style choice, not a law of nature – rationale below) and audit columns with the _on suffix;
  • Constraints laid out cleanly: FOREIGN KEY and UNIQUE as a separate, idempotent (repeatably runnable) ALTER TABLE rather than inline – a deployment choice, not a must, with named constraints and an index convention;
  • COMMENT ON TABLE/COLUMN and seed INSERT with leading commas;
  • and – at the end – how these conventions came about in the first place: from manual corrections that an AI agent turns into rules.

Prerequisite: Postgres (examples on 14+), a working knowledge of DDL (CREATE TABLE, ALTER TABLE, constraints).

Contents

Naming and keys

Three rules carry the whole of naming – they answer “what is it called?” and “how do I recognise a row?” before you even open the table:

  1: CREATE TABLE IF NOT EXISTS app.project
  2: (
  3:     id          bigserial     NOT NULL
  4:    ,slug        varchar(100)  NOT NULL
  5:    ,name        varchar(200)  NOT NULL
  6:    ,parent_id   bigint            NULL
  7: 
  8:    ,CONSTRAINT pk_project        PRIMARY KEY (id)
  9:    ,CONSTRAINT chk_project_name  CHECK (length(trim(name)) > 0)
 10: );

  • snake_case and singular. The table is called project, not Projects and not projects. Columns follow the same style, foreign-key columns fall out naturally: parent_id, not parentId or parents_id.
  • Surrogate key id. Most tables get an artificial id column as the primary key. The exception is pure junction tables: a user_role with PRIMARY KEY (user_id, role_id) needs no extra id – the composite natural key is the PK there. id bigserial is the shorthand here; equivalent and closer to the SQL standard is id bigint GENERATED ALWAYS AS IDENTITY (see FAQ).
  • Named primary-key constraint. The PK stands as its own CONSTRAINT pk_<table> at the end of the block, not inline as id bigserial PRIMARY KEY. That way it appears in error messages and \d output with a speaking name instead of a generated project_pkey. Only PRIMARY KEY and CHECK stay inline in the CREATE TABLE (see chk_project_name above).
  • Natural keys become UNIQUE, not the PK. The slug identifies a project uniquely in business terms – but it becomes a UNIQUE constraint (see below), not the primary key. The PK stays the business-meaningless id; a business value can change, a surrogate key is typically stable (you simply never change it).

The tabular layout in the block – column name, type, and nullability aligned underneath each other – is no end in itself: it turns the column list into a table you scan at a glance. The same principle applies to procedure signatures and JOINs; derived in detail in the PL/pgSQL case study.

Data types and audit columns

Two decisions that affect every table: the string type and the audit columns.

  1:     is_active    boolean       NOT NULL DEFAULT true
  2:    ,created_on   timestamptz   NOT NULL DEFAULT now()
  3:    ,created_by   varchar(100)  NOT NULL
  4:    ,modified_on  timestamptz       NULL
  5:    ,modified_by  varchar(100)      NULL

varchar over text. Character columns become varchar – with a length where a business limit exists (varchar(100)), without one where it doesn’t. Worth noting: in Postgres, varchar without a length is internally identical to text (same storage, same performance); varchar(n) only adds a length check. So varchar is personal preference, not a performance argument – anyone who goes with text throughout does nothing wrong. The one thing you shouldn’t do is mix the two.

Audit columns with the _on suffix. Tables whose rows change over time carry four columns: created_on / created_by and modified_on / modified_by. (Pure lookup and junction tables, or deliberately immutable ones – e.g. in event sourcing – don’t need them.) Two conventions behind this:

  • Suffix _on, never _at for timestamps: created_on, modified_on. One form, throughout – that makes the columns greppable across all tables.
  • created_* is set on insert (created_on via DEFAULT now(), created_by by the inserting actor), modified_* stays NULL on insert and is filled only on each UPDATE – by the application or centrally by a BEFORE UPDATE trigger. That modified_on is NULL on a freshly created row is intentional: “never changed” and “changed today” stay distinguishable.

Constraints, comments and seed

In this convention, UNIQUE and FOREIGN KEY constraints do not sit in the CREATE TABLE but as separate ALTER TABLE statements after the OWNER. That’s a deliberate deployment choice, not a PostgreSQL requirement – many teams define foreign keys inline just as well, because then the whole data model sits in one place. The reason for the separate form here is idempotency and ordering. Idempotent means: you can run the script any number of times in a row and always get the same end state – a second run creates nothing twice and doesn’t abort with “already exists”. That’s exactly what a deploy script needs when it runs again on every rollout:

  1: ALTER TABLE app.project OWNER TO app_owner;
  2: 
  3: -- Unique constraints
  4: ALTER TABLE app.project DROP CONSTRAINT IF EXISTS uq_project_slug;
  5: ALTER TABLE app.project ADD  CONSTRAINT uq_project_slug UNIQUE (slug);
  6: 
  7: -- Foreign keys
  8: ALTER TABLE app.project DROP CONSTRAINT IF EXISTS fk_project_parent_id;
  9: ALTER TABLE app.project ADD  CONSTRAINT fk_project_parent_id FOREIGN KEY (parent_id) REFERENCES app.project(id) ON DELETE SET NULL;
 10: 
 11: -- Indexes
 12: CREATE INDEX IF NOT EXISTS idx_project_parent_id ON app.project (parent_id);

  • Named by the scheme uq_<table>_<column>, fk_<table>_<column>, idx_<table>_<column>. Speaking names instead of postgres-generated ones – they appear readably in every violation message and can be dropped on purpose.
  • Ordering: the separate ALTER statements run after all CREATE TABLE. If a table references another that comes later in the deploy, an inline FK fails on the not-yet-existing target table – the separate form solves that (and cyclic references along with it).
  • Idempotency via DROP CONSTRAINT IF EXISTS + ADD. Postgres has no ADD CONSTRAINT IF NOT EXISTS; the drop-before pattern makes the deploy script repeatable. Careful with large tables: DROP + ADD re-validates the FK or rebuilds the UNIQUE index from scratch – with a lock, index recreation, and a full FK validation. On tables with many millions or billions of rows this can stall or block a deploy; there you create constraints once instead of re-adding them on every run (possibly ADD … NOT VALID plus a separate VALIDATE CONSTRAINT outside the critical window).
  • Indexes follow the same naming scheme and are idempotent via IF NOT EXISTS. The UNIQUE constraint brings its index automatically; separate indexes are for frequently filtered non-unique columns – foreign-key columns like parent_id first of all.
  • Choose ON DELETE deliberately: SET NULL for optional references, CASCADE for dependent detail rows, otherwise the default (Restrict).

Comments are mandatory in this convention – other teams document in a data catalogue or ER model instead, both legitimate. Every table gets a COMMENT ON TABLE; columns with a non-obvious meaning get a COMMENT ON COLUMN:

  1: COMMENT ON TABLE  app.project      IS 'Master data: named projects.';
  2: COMMENT ON COLUMN app.project.slug IS 'Unique project slug (natural key, UNIQUE).';

The surrogate id and the four audit columns need no comment – they’re uniform framework-wide. Codes and flags do, though: for a status column, the allowed values belong in the comment.

Seed data, finally, follows the same leading-comma style; ON CONFLICT makes the seed repeatable:

  1: INSERT INTO app.project (slug, name, created_by)
  2: VALUES
  3:     ('alpha', 'Alpha', '<system>')
  4:    ,('beta',  'Beta',  '<system>')
  5: ON CONFLICT (slug) DO UPDATE
  6: SET
  7:     name        = EXCLUDED.name
  8:    ,modified_on = now();

How this convention came about

None of these rules fell from the sky. They’re derived from practice – from the pattern that’s left once you’ve corrected enough by hand. There’s a reproducible workflow exactly for that: an AI agent generates a table, you bring it into shape once by hand (naming, named PK, separate ALTER for the constraints), and the agent derives the rule from the difference and drops it into a .claude/rules/ file. From then on it generates tables in your conventions already.

How this Generate→Refine→Derive loop works in detail is shown in Deriving SQL Conventions with Claude Code. For the overview of how rules, skills, and agents work together, see the hub AI-Assisted SQL Development with Claude Code.

Worth keeping: These table conventions are available as a generalized starter rules file to download – drop it into your repo as .claude/rules/tables.md, and your AI agent creates tables to convention from the next session on.

Summary

A table without a convention is readable on its own – at the scale of fifty tables, the stylistic variety becomes a search, review, and migration problem. The Postgres table conventions shown here address exactly that – as a framework’s deliberate choice, not as PostgreSQL law.

Take-away:

  • Naming: snake_case + singular; surrogate id with a named CONSTRAINT pk_<table> (never inline) for most tables – the exception being pure junction tables with a composite PK; natural keys become UNIQUE.
  • Data types: varchar over text (personal preference, not a performance argument); audit columns created_*/modified_* with the _on suffix, modified_* only on UPDATE.
  • Constraints: FOREIGN KEY/UNIQUE as a separate, named, idempotent ALTER TABLE (a deployment choice, not a must) – mind the DROP/ADD cost on large tables. Indexes as idx_<table>_<column>.
  • Documentation: COMMENT ON TABLE/COLUMN mandatory in this convention; seed with leading commas and ON CONFLICT.
  • Consistency beats the choice of form: whether varchar or text, bigserial or IDENTITY, singular or plural – the value only emerges once every table carries the same decision.

FAQ

bigserial or GENERATED ALWAYS AS IDENTITY?

Both produce an auto-incrementing surrogate key. GENERATED ALWAYS AS IDENTITY is the newer, SQL-standard-compliant variant and additionally guards against setting the id by hand by accident – the more advisable choice for new projects. bigserial is the older, compact spelling (internally a sequence plus DEFAULT nextval(...)); the convention here uses it for brevity and legacy compatibility. Both are technically fine, as long as it’s the same form everywhere – and always with a named CONSTRAINT pk_<table>.

varchar or text – isn’t that the same thing?

Almost, technically. In Postgres, varchar without a length is internally identical to text – same storage, same performance. varchar(n) only adds a length check. So there’s no performance reason for either form. This convention takes varchar for the optional length affordance (varchar(100) documents a business limit) – a schema entirely on text is just as good.

Why don’t FOREIGN KEYs sit inline in the CREATE TABLE here?

That’s a convention choice, not a general rule – inline FKs are entirely common and have the advantage that the model sits in one place. This convention separates them for two reasons: idempotency (a separate DROP CONSTRAINT IF EXISTS … ADD CONSTRAINT … can be repeated on re-deploy) and ordering (the ALTER statements run after all CREATE TABLE, so forward and cyclic references resolve too). If you don’t need repeatable deploy scripts, inline FKs are simpler.

Does every table really need audit columns and a table comment?

No. Audit columns pay off for mutable business objects; pure lookup/junction tables or immutable (event-sourcing) tables get by without modified_*. And the mandatory comment is team policy, not a technical must – whoever documents in a data catalogue or ER model can drop it. The convention sets both as the default because, in case of doubt, “better too much documentation than too little” is the cheaper bet.

AI workflow:

SQL conventions & structure: