Naming Objects in PostgreSQL — snake_case, Singular, Prefixes

← Back to the section

When a team has no shared naming convention, the database gradually turns into chaos: half the tables capitalized, half lowercase, indexes without names, is_deleted sitting next to deletedAt. Making sense of such a schema six months later becomes a task of its own.

Here we go through the rules that make a schema readable and predictable at first glance.

Case: snake_case Without Quotes

PostgreSQL folds all names to lowercase unless they are wrapped in double quotes. So OrderDoc, orderDoc, and orderdoc are all the same thing in PostgreSQL. But "OrderDoc" in quotes is something else entirely: PG preserves the case and requires quotes in every query.

The rule is simple: use snake_case without quotes for all objects — tables, columns, indexes, functions, constraints.

-- correct
CREATE TABLE order_doc (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- common mistake
CREATE TABLE OrderDoc (
    Id          bigint,
    customerId  bigint,
    "CreatedAt" timestamptz
);

"CreatedAt" in quotes is a trap: now you have to write the quotes in every query, otherwise PG won't find the column.

Tables: Singular, Nouns

A table describes a type of object, not a collection. So the name is a noun in the singular: order_doc, customer, product, payment.

The plural (orders, customers) also occurs and is not logically wrong, but mixing approaches in one database is bad. Pick one convention and stick to it everywhere.

Join tables (M:N) are named after both entities: order_item, customer_role, product_tag.

In large schemas with several domains, it is convenient to add a domain prefix: order_*, catalog_* — or to move domains into separate PostgreSQL schemas.

Columns: Suffixes Tell You the Type

A good column name is immediately clear without the DDL. A few conventions that help with this:

Primary key — just id, without the table name. In the customer table the primary key is id, not customer_id. A name with the table, customer_id, is the format for foreign keys.

Foreign keys — <parent_table>_id: customer_id, order_id.

Boolean — with an is_, has_, can_ prefix: is_active, has_avatar, can_publish. Without a prefix it's unclear: is active a status or an action?

Timestamps — the _at suffix for a timestamp: created_at, updated_at, expires_at. For dates without time — no suffix or _on: born_on, holiday_date.

Money — the suffixes _amount, _price, _rate: total_amount, discount_rate. Without a suffix, price is ambiguous — is it an amount or a percentage?

Durations — an explicit unit of measurement: ttl_seconds, delivery_days, session_timeout_ms. Just delivery_time integer — how much is that? Seconds? Minutes? Hours?

Counters — the _count suffix: view_count, items_count.

Enum statuses — no suffix: status, type, currency.

created_at   timestamptz NOT NULL DEFAULT now(),
updated_at   timestamptz NOT NULL DEFAULT now(),
expires_at   timestamptz,
born_on      date,
ttl_seconds  integer,
total_amount numeric(15,2),
is_active    boolean NOT NULL DEFAULT true,
view_count   integer NOT NULL DEFAULT 0

Audit Columns and Soft-Delete

The standard set for tracking history:

created_at   timestamptz NOT NULL DEFAULT now(),
updated_at   timestamptz NOT NULL DEFAULT now(),
created_by   bigint REFERENCES customer(id),
updated_by   bigint REFERENCES customer(id),
version      bigint NOT NULL DEFAULT 0

The version field is used for optimistic locking: when updating a row, we check that the version has not changed since it was read.

Soft-delete — a common mistake is storing is_deleted boolean. Better to use deleted_at timestamptz:

-- correct: the moment of deletion is preserved
deleted_at   timestamptz   -- NULL = the record exists

-- common mistake: the moment of deletion is lost forever
is_deleted   boolean

Getting a boolean from deleted_at is trivial: WHERE deleted_at IS NULL. But from is_deleted = true the deletion time can no longer be recovered.

Indexes and Constraints: A Prefix by Type

When an index has no name, PostgreSQL generates one itself: order_doc_pkey, order_doc_customer_id_idx. That's acceptable for primary keys, but for other objects it's better to set names explicitly — then the error messages will be clear.

The prefix convention:

Type	Prefix	Example
Regular index	ix_	ix_order_customer_id
Unique index	uk_	uk_customer_email
Foreign key	fk_	fk_order_item_order_id
Check constraint	ck_	ck_order_total_positive
Primary key	pk_ (usually auto)	—
Trigger	tr_	tr_order_doc_audit

CREATE INDEX ix_order_customer_id ON order_doc (customer_id);
CREATE INDEX ix_order_status_created_at ON order_doc (status, created_at);
CREATE UNIQUE INDEX uk_customer_email ON customer (email);

-- functional index
CREATE INDEX ix_account_email_lower ON account ((lower(email)));

-- partial index
CREATE INDEX ix_order_active ON order_doc (customer_id) WHERE status IN ('NEW','PAID');

ALTER TABLE order_item
    ADD CONSTRAINT fk_order_item_order_id
    FOREIGN KEY (order_id) REFERENCES order_doc(id);

CONSTRAINT ck_order_total_positive CHECK (total_amount >= 0)

An explicit name on a CHECK constraint explains the error right away: CHECK constraint violated: ck_order_total_positive — it's clear which rule was broken.

Reserved Words

PostgreSQL keeps a list of words that cannot be used as identifiers without quotes: user, order, group, type, position, value, name, default, desc, asc, start, end, class.

If you name a table user, every query will have to be written with quotes: SELECT * FROM "user". That's inconvenient and easy to break.

Good alternatives:

• "User" → customer, account, person
• "Order" → order_doc, purchase, shipment

The full list of reserved words:

SELECT * FROM pg_get_keywords() WHERE catcode IN ('R', 'T');

Name Length

PostgreSQL silently truncates names longer than 63 characters (this is the NAMEDATALEN - 1 limit). Truncation raises no error — the name simply becomes something else, and a collision with an already existing object becomes possible.

A practical rule: keep names under 30 characters. If you use abbreviations, stick to one variant across the whole project: if it's usr, then it's usr everywhere — don't mix it with user and account.

Sequences and IDENTITY

If you use GENERATED ALWAYS AS IDENTITY, PostgreSQL will automatically create a sequence named <table>_<column>_seq. There's no need to touch this name.

Calling nextval('customer_id_seq') directly only makes sense during manual data imports — in all other cases IDENTITY manages the sequence on its own.

Views and Materialized Views

Views are named with the _v suffix, materialized ones with the _mv suffix:

CREATE VIEW customer_active_v AS
    SELECT * FROM customer WHERE deleted_at IS NULL AND is_active = true;

CREATE MATERIALIZED VIEW order_stats_mv AS
    SELECT date_trunc('day', created_at) as day, count(*) as cnt
    FROM order_doc GROUP BY 1;

The suffix helps you tell a view from a table right away when reading a query.

Common Mistakes

CamelCase or quotes — write order_doc instead of "OrderDoc". Without quotes it's case-insensitive; with quotes you'll have to write them everywhere.

customer_id as the primary key in the customer table — the rule: inside its own table the key is always id.

is_deleted boolean for soft-delete — use deleted_at timestamptz, and the moment of deletion is preserved.

A number without a unit — delivery_time integer says nothing. Write delivery_days or delivery_seconds.

Reserved words without quotes — CREATE TABLE user won't work. Rename it to account or customer.

A utility tbl_ prefix — tbl_orders — PostgreSQL already knows it's a table. The prefix adds nothing.

The data type in the name — created_timestamp instead of created_at. The type is visible in the DDL; the name should speak to the meaning.

data jsonb — too abstract. Name it by meaning: attributes, payload, metadata, config.

FK and CHECK without explicit names — automatic names are unreadable; explicit names give clear error messages.

In Short

• Everything in snake_case, no quotes — otherwise PG will start distinguishing case.
• Tables — nouns in the singular: order_doc, customer.
• Primary key — id; foreign key — <parent_table>_id.
• Boolean with is_/has_/can_, timestamps with _at, money with _amount/_price.
• Durations — with a unit: ttl_seconds, delivery_days.
• Soft-delete — deleted_at timestamptz, not is_deleted boolean.
• Indexes and constraints — with an ix_/uk_/fk_/ck_ prefix and an explicit name.
• Reserved words (user, order, group) — don't use them as names.
• Names longer than 63 characters PostgreSQL will truncate silently.
• Views — the _v suffix, materialized views — _mv.

Further Reading

• Index Types in PostgreSQL — which index to choose for the task.
• Composite Indexes and the Leftmost Prefix — column order in an index.
• Zero-Downtime Migrations — how to rename a column safely.
• Time and Time Zones in PostgreSQL — why always timestamptz.