Naming Conventions

Goal: predictable, readable, and consistent names.

Problem / Context

You are a new engineer joining a team that ships fast. You inherit a database that has grown for years: some tables are CamelCase, others are snake_case; a few columns are called ts, nm, and data; there is a table named user that needs quotes everywhere; and two different status columns mean different things. Reviews take too long because people argue about names instead of behavior. Small migrations break because quoted identifiers and mixed styles cause mistakes. You need a simple, shared set of naming rules so everyone can guess names without looking them up and write queries without quoting.

Rules

Good names remove friction. Adopt these and apply them everywhere:

Implementation

Start by taking an inventory, agree on rules, then rename safely in small steps.

Naming Conventions (Practical Guide)

Goal: predictable, readable, and unquoted names that the team can guess without looking them up.

Why: the scheme avoids reserved words, casing pitfalls, and cryptic abbreviations, and makes diffs/scans obvious.

Quick Inventory (find risky names)

-- Mixed or non-lowercase tables (require quoting)
SELECT
    n.nspname AS schema,
    c.relname AS tableFROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
    -- c: ordinary table
    -- p: partitioned table
    -- v: view
    -- m: materialized view
    c.relkind IN ('r', 'p', 'v', 'm')
    AND c.relname <> lower(c.relname)
ORDER BY
    1,
    2;

-- Mixed or non-lowercase columns (require quoting)
SELECT
    table_schema,
    table_name,
    column_name
FROM
    information_schema.columns
WHERE
    column_name <> lower(column_name)
ORDER BY
    1,
    2,
    3;

-- Objects named like reserved words (consider renaming)
SELECT
    n.nspname,
    c.relname
FROM
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p')
    AND c.relname IN ('user', 'order', 'group');

Walkthrough: Rename a “bad” table safely

Example “bad” table:

-- Problems: reserved word (`User`), mixed case (needs quotes), cryptic names, unclear timestamp type
CREATE TABLE "User" (
   "Id" SERIAL PRIMARY KEY,
   "Nm" TEXT,
   "TS" TIMESTAMP,
   email TEXT UNIQUE
);

Target (good) outcome:

CREATE TABLE users (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name TEXT NOT NULL,
   created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
   email TEXT NOT NULL,
   CONSTRAINT uidx_users__email UNIQUE (email)
);

Strategy

Steps (downtime‑minimal)

1. Rename the table away from reserved/mixed case

ALTER TABLE "User" RENAME TO users;

2. Rename columns to clear, conventional names

ALTER TABLE users RENAME COLUMN "Nm" TO name;
ALTER TABLE users RENAME COLUMN "TS" TO created_at;
-- TIMESTAMP -> TIMESTAMPTZ and add default
ALTER TABLE users
   ALTER COLUMN created_at TYPE timestamptz USING created_at AT TIME ZONE 'UTC',
   ALTER COLUMN created_at SET DEFAULT now(),
   ALTER COLUMN created_at SET NOT NULL;

-- Move from SERIAL to IDENTITY (optional modernization)
-- Note: confirms existing sequence ownership first; do this in a quiet window.
ALTER TABLE users ALTER COLUMN id DROP DEFAULT;         -- breaks SERIAL link
DROP SEQUENCE IF EXISTS "User_Id_seq";                 -- name may vary
ALTER TABLE users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

4. Rename constraints and indexes for clarity

-- Primary key
ALTER TABLE users RENAME CONSTRAINT "User_pkey" TO pk_users;  -- if present

-- Unique index on email (recreate with clear name if needed)
DO $$ BEGIN
   IF EXISTS (
      SELECT 1 FROM pg_indexes WHERE schemaname = 'public'
         AND indexname = '"User_email_key"'
   ) THEN
      ALTER INDEX "User_email_key" RENAME TO uidx_users__email;
   END IF;
END $$;

-- Or ensure existence explicitly
CREATE UNIQUE INDEX IF NOT EXISTS uidx_users__email ON users(email);

5. Backward‑compatibility (temporary)

-- Read‑only compatibility view for old code paths
CREATE OR REPLACE VIEW "User" AS
SELECT id, name, created_at, email FROM users;

-- If writes to the old name are still happening, add simple rules (optional)
-- INSERT example:
CREATE OR REPLACE RULE user_insert_redirect AS
ON INSERT TO "User" DO INSTEAD
   INSERT INTO users(id, name, created_at, email)
   VALUES (NEW.id, NEW.name, NEW.created_at, NEW.email);

6. Recheck privileges and comments

-- Renames keep privileges, but new objects (views/rules) may need grants
GRANT SELECT ON users TO readonly;
COMMENT ON TABLE users IS 'Application users (human accounts)';
COMMENT ON COLUMN users.created_at IS 'Creation timestamp (UTC)';

7. Cutover and cleanup

-- After application is deployed and no longer uses the old name
DROP VIEW IF EXISTS "User" CASCADE;  -- removes rules attached to the view

Zero‑downtime column rename pattern (alternative)

When renaming a hot column without locking DDL:

  1. Add new column (e.g., ALTER TABLE users ADD COLUMN created_at timestamptz;).
  2. Backfill in chunks; keep in sync via triggers if needed.
  3. Update app to read/write the new column.
  4. Remove old column in a later migration.

Enforce going forward

Pitfalls

References