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.

This works for more than deletion: any event that happens once is better stored as a moment. In the practice sandbox an order has paid_at, and that single column answers both "is it paid?" and "when?":

live example

SELECT id, customer_id, total_amount, created_at, paid_at
FROM orders
WHERE paid_at IS NOT NULL
ORDER BY paid_at
LIMIT 3;

The query reads without the DDL: customer_id is a reference to the buyer, total_amount is money, _at marks moments in time. The number is exactly where this schema slips, though: orders, products, payments are plural while customer is singular. That is the very mixture that makes you look up the table name every time.

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:

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)

The difference shows up in the error text. PostgreSQL writes new row for relation "order_doc" violates check constraint and then the name: with an explicit name that is ck_order_total_positive — the rule is named, and it's clear what to fix. Without a name PG invents its own, order_doc_check, and that message sends you back into the schema.

Reserved Words

PostgreSQL keeps a list of words that cannot be used as identifiers without quotes: user, order, group, default, desc, asc, end, table, select, from, where.

And here are words that look dangerous but are in fact allowed: type, name, value, position, start, class — a name or value column can be created without quotes, and such names are everywhere. You don't need to hold the whole list in your head; one rule is enough: when in doubt, ask the database (the query below) or pick a more specific name, because name and value are usually poor column names anyway.

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

To check any word or see the full list:

live example

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

Name Length

PostgreSQL truncates names longer than 63 characters (this is the NAMEDATALEN - 1 limit). There is no error — only a NOTICE like identifier ... will be truncated, which is easy to miss in migration logs. The name becomes a different one, and if two long names match in their first 63 characters they collapse into one, so the second migration fails with "object already exists".

To see what is left of a long index name:

live example

SELECT length('ix_order_item_delivery_address_country_code_created_at_status_active') AS name_length,
       left('ix_order_item_delivery_address_country_code_created_at_status_active', 63) AS after_truncate;

68 characters in, 63 out — the tail ctive is cut off silently.

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

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.

In Short

• Everything in snake_case without quotes: in quotes PG preserves the case and demands them in every query.
• Tables — nouns in the singular; primary key — id, foreign key — <parent_table>_id.
• Boolean with is_/has_/can_, moments with _at, money with _amount/_price, durations with a unit (ttl_seconds).
• Soft-delete — deleted_at timestamptz, not is_deleted boolean: the moment is kept, and the boolean comes from a query.
• Indexes and constraints — with an explicit name and an ix_/uk_/fk_/ck_ prefix: the name lands in the error text.
• Names longer than 63 characters are truncated silently, and reserved words (user, order, group) will demand quotes.

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.