PostgreSQL Best Practices Guide: Naming, Column, Constraints, Index, NULL Handling, Development and Management Recommendations

1 Naming Conventions

Database objects such as databases, schemas, tables, columns, views, indexes, sequences, functions and triggers should use lowercase letters, numbers and underscores, avoid double quotes unless necessary, keep the length under 63 characters, and not start with pg or a digit. Reserved SQL keywords must not be used.

Table names for temporary or backup objects should include a date, e.g., dba_ops.b2c_product_summary_2014_07_12. Index names follow the pattern table_column_idx, e.g., student_name_idx, and it is recommended to let PostgreSQL generate the name automatically.

2 Column Design

Prefer numeric types over character types, use varchar(N) instead of char(N) to save space, and avoid text when varchar(N) suffices. Use DEFAULT NULL rather than an empty string. Store IP addresses with ip4, ip6 etc., MAC addresses with macaddr. Use timestamptz instead of plain timestamp for time‑zone awareness.

Monetary values should use NUMERIC(precision, scale) rather than real or double precision. For semi‑structured key‑value data, use hstore; for hierarchical paths, use ltree; for JSON, prefer jsonb. Geometric types together with PostGIS enable geographic data storage.

Range types can replace string or multi‑column representations of intervals:

int4range  -- Range of integer
int8range  -- Range of bigint
numrange   -- Range of numeric
tsrange    -- Range of timestamp without time zone
tstzrange  -- Range of timestamp with time zone
daterange  -- Range of date

3 Constraints Design

Every table should have a primary key, preferably a surrogate key defined as id serial primary key or id bigserial primary key. Avoid business‑meaningful primary keys such as national IDs. For large tables, consider alternative primary‑key strategies to simplify maintenance.

4 Index Design

PostgreSQL supports B‑tree, Hash, GiST, SP‑GiST, GIN, and BRIN indexes; Hash is generally discouraged. Use the CONCURRENTLY option when creating or dropping indexes to avoid blocking writes.

For tables with columns frequently updated or deleted, maintain indexes concurrently. Prefer unique indexes over unique constraints for easier management. Create multi‑column composite indexes for high‑frequency queries with multiple predicates, and partial indexes for selective conditions.

Example of a partial index:

SELECT * FROM test WHERE status=1 AND col=?;
CREATE INDEX ON test (col) WHERE status=1;

Expression or function indexes can accelerate queries that use computed expressions:

SELECT * FROM test WHERE exp(xxx);
CREATE INDEX ON test (exp(xxx));

Avoid creating excessive indexes; typically no more than six per core table.

5 NULL Handling

Use IS NULL and IS NOT NULL for null checks. Remember that boolean columns can hold TRUE, FALSE or NULL. Be cautious with NOT IN when the set contains NULL.

String concatenation with null yields null; use COALESCE to substitute an empty string:

SELECT NULL||'PostgreSQL';
SELECT COALESCE(NULL,'')||'PostgreSQL';

Similarly, handle hstore null values with COALESCE. For row counting, prefer COUNT(1) or COUNT(*) because COUNT(column) ignores nulls. COUNT(DISTINCT column) also excludes nulls, while COUNT(DISTINCT (col1,col2)) counts null rows.

To treat two nulls as equal, use the IS NOT DISTINCT FROM operator:

SELECT NULL IS NOT DISTINCT FROM NULL;  -- returns true

6 Development Guidelines

Add comments to database objects, especially columns, to aid future maintainers. Avoid SELECT * unless necessary; specify required columns to reduce bandwidth and coupling. Use inequality checks ( <>) in UPDATE statements to avoid unnecessary writes.

Keep transactions short and avoid long‑running locks. For bulk data loading, use COPY instead of multiple INSERT statements. Materialized views can cache expensive reporting queries and be refreshed concurrently.

Leverage window functions for complex aggregations. Monitor connections stuck in idle in transaction state, as they can cause locks on the master or block replication on slaves.

In MyBatis, avoid useGeneratedKeys="true" unless needed; retrieve generated keys explicitly to prevent unnecessary bandwidth usage.

7 Management Guidelines

Use TRUNCATE instead of DELETE to quickly empty tables. When adding a column with a non‑null default to a large table, split the operation: add the column without a default, set the default, then back‑fill data in batches to reduce lock time.

Do not wrap DDL statements in explicit transactions, as they acquire high‑level locks and can cause deadlocks. Set fillfactor (e.g., 85) for frequently updated tables to leave space for updates.

On SSD‑based instances, lower random_page_cost to between 1.0 and 2.0 to encourage index scans. When using EXPLAIN ANALYZE for write queries, start a transaction and roll back afterward to avoid persisting changes.