PostgreSQL for Application Developers: Schema, Transactions, Indexes, JSONB, and PostgreSQL 18

PostgreSQL for Application Developers: Schema, Transactions, Indexes, JSONB, and PostgreSQL 18

PostgreSQL gives backend developers something unusually valuable: a database that can enforce business rules, answer evolving SQL queries, handle concurrent transactions, store both relational and document-shaped data, and grow with the application without forcing every requirement into one narrow data model.

The best way to use PostgreSQL is not to treat it as a passive place where the ORM dumps tables. Let the database enforce invariants that matter, choose indexes from real query shapes, keep transactions small, and use PostgreSQL-specific capabilities when they simplify the application.

As of September 2026, PostgreSQL 18.6 is the current supported stable minor release. PostgreSQL 19 is still a development/beta release, so this guide uses production PostgreSQL 18 behavior.

Start with constraints, not just application validation

Application validation improves user experience. Database constraints protect the data regardless of which application, migration, script, or admin tool writes it.

A useful schema makes invalid states hard to store:

CREATE TABLE accounts (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    email text NOT NULL UNIQUE,
    status text NOT NULL
        CHECK (status IN ('active', 'suspended', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    account_id uuid NOT NULL REFERENCES accounts(id),
    total numeric(12,2) NOT NULL CHECK (total BETWEEN 0 AND 9999999999.99),
    created_at timestamptz NOT NULL DEFAULT now()
);

Useful constraints include:

• NOT NULL
• UNIQUE
• primary keys
• foreign keys
• CHECK
• exclusion constraints for certain range-overlap rules

PostgreSQL automatically creates a unique B-tree index for primary-key and unique constraints, so do not create a second identical index afterward.

Prefer explicit types over “everything is text”

PostgreSQL has a rich type system. Use it.

Examples include:

• uuid
• boolean
• numeric
• date, timestamp, timestamptz
• arrays
• ranges and multiranges
• inet / cidr
• jsonb

If a value represents money, time, a UUID, an IP address, or a range, storing it in its native type gives PostgreSQL more information for validation, comparisons, operators, and indexing.

Avoid using text columns as a generic escape hatch unless the domain genuinely is free-form text.

PostgreSQL 18 includes native UUIDv7 generation

PostgreSQL 18 added the uuidv7() function.

UUIDv7 remains globally unique like other UUID-based identifiers while embedding a time-ordered component that improves locality compared with fully random UUIDv4 values in many insertion patterns.

Example:

CREATE TABLE events (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    occurred_at timestamptz NOT NULL DEFAULT now(),
    payload jsonb NOT NULL
);

Do not migrate identifier formats only because a newer function exists. But for new systems that want UUID identifiers, UUIDv7 is now a first-class PostgreSQL option.

Keep transactions around one business invariant

A transaction should contain the database changes that must succeed or fail together.

BEGIN;

UPDATE inventory
SET quantity = quantity - 2
WHERE product_id = $1
  AND quantity BETWEEN 2 AND 2147483647;

INSERT INTO orders (id, account_id, total)
VALUES (uuidv7(), $2, $3);

COMMIT;

Avoid opening a transaction and then:

• waiting for user input
• calling an external HTTP API
• performing slow file processing
• sleeping or doing unrelated background work

Long transactions can retain locks and old MVCC snapshots, increase contention, delay cleanup, and create avoidable operational pressure.

Understand PostgreSQL's default isolation level

PostgreSQL defaults to Read Committed.

Each statement sees a snapshot of data committed before that statement began. Two SELECT statements inside the same transaction can therefore see different committed data if another transaction commits between them.

PostgreSQL also supports:

• Repeatable Read
• Serializable

Use stronger isolation only when the business invariant needs it.

Serializable is powerful, but applications must be prepared to retry transactions that fail with serialization errors. That is a normal part of the guarantee, not a database malfunction.

Use row locking when the workflow really needs ownership

For workflows that read a row and then make a decision based on its current state, row locks can protect the decision window.

Example:

BEGIN;

SELECT id, status
FROM jobs
WHERE id = $1
FOR UPDATE;

-- validate state and update it

COMMIT;

But locking every read “for safety” reduces concurrency.

Prefer single atomic statements when possible. For example, claiming a pending job can often be written as an UPDATE ... WHERE status = 'pending' RETURNING ... rather than a separate read followed by a write.

Let RETURNING remove unnecessary round trips

PostgreSQL can return rows directly from writes:

INSERT INTO users (id, email)
VALUES (uuidv7(), $1)
RETURNING id, email, created_at;

The same works with UPDATE and DELETE.

PostgreSQL 18 expands RETURNING capabilities by allowing access to both old and new row values in supported data-modification commands. That can simplify audit-style or change-aware application flows without issuing another query.

Use RETURNING when the application needs generated IDs, timestamps, updated versions, or changed state immediately after a write.

Use upserts deliberately

INSERT ... ON CONFLICT is PostgreSQL's practical upsert mechanism.

INSERT INTO user_settings (user_id, theme)
VALUES ($1, $2)
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme;

This is safer than an application-level pattern such as:

SELECT to see if row exists
if yes: UPDATE
if no: INSERT

because concurrent requests can race between the read and write.

The conflict target needs an appropriate unique constraint or unique index.

Choose indexes from query shapes

An index should exist because a real production query needs it.

Suppose the hot query is:

SELECT id, total, created_at
FROM orders
WHERE account_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

A useful index may be:

CREATE INDEX orders_account_status_created_idx
ON orders (account_id, status, created_at DESC)
INCLUDE (total);

The exact order depends on predicates, selectivity, sorting, and workload.

Do not create an index on every column. Every index adds:

• write amplification
• WAL volume
• storage
• memory pressure
• vacuum/maintenance work

PostgreSQL has several index families

B-tree is the normal choice for scalar equality, ordering, and many range operations.

Other important index types include:

• GIN — useful for JSONB, arrays, full-text and other multi-valued data
• GiST — useful for ranges, geometry and extensible operator classes
• SP-GiST — useful for partitioned search structures such as certain geometric/prefix workloads
• BRIN — useful for very large naturally ordered tables where block ranges correlate with the indexed value

Choose an index because its operator/access model matches the query—not because it sounds more advanced.

Partial indexes can keep hot indexes small

If most rows are historical but the application frequently queries a small active subset, a partial index can be excellent.

CREATE INDEX jobs_pending_created_idx
ON jobs (created_at)
WHERE status = 'pending';

This avoids indexing rows that the target query never needs.

Partial indexes can reduce index size and write work, but only queries whose predicates imply the index condition can use them reliably.

Expression indexes can encode normalization

If users are looked up case-insensitively:

CREATE UNIQUE INDEX users_email_lower_idx
ON users (lower(email));

The application can query:

SELECT id
FROM users
WHERE lower(email) = lower($1);

This can enforce the same normalization rule used by the lookup without maintaining a second manually synchronized column.

JSONB is useful when flexibility is genuinely part of the domain

A common strong design mixes stable relational columns with flexible JSONB attributes:

CREATE TABLE products (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    tenant_id uuid NOT NULL,
    name text NOT NULL,
    price numeric(12,2) NOT NULL,
    attributes jsonb NOT NULL DEFAULT '{}'
);

Keep fields relational when they participate in:

• foreign keys
• stable constraints
• frequent joins
• frequent filtering/sorting
• core business rules

Use JSONB for attributes whose shape genuinely varies.

Do not turn the entire database into one giant JSON document merely to avoid schema design.

Index JSONB only for the access paths you need

A GIN index can accelerate many JSONB containment/search operations:

CREATE INDEX products_attributes_gin
ON products USING gin (attributes);

But JSONB indexes can become large and expensive to maintain.

If the application repeatedly queries one specific path, an expression index may be more targeted:

CREATE INDEX products_brand_idx
ON products ((attributes->>'brand'));

Measure the real query plan and index size before indexing every JSON key.

Generated columns can remove duplicated application logic

PostgreSQL supports stored and virtual generated columns. In PostgreSQL 18, generated columns are virtual by default unless STORED is specified.

Example:

CREATE TABLE invoice_lines (
    quantity numeric NOT NULL,
    unit_price numeric(12,2) NOT NULL,
    line_total numeric(14,2)
        GENERATED ALWAYS AS (quantity * unit_price)
);

The application cannot accidentally store a line_total that disagrees with the inputs.

Generated expressions have restrictions, including immutability requirements. Use them for deterministic row-local derived values—not for arbitrary queries or external lookups.

Use database defaults for database-owned values

If every writer should use the same creation timestamp, let the database own it:

created_at timestamptz NOT NULL DEFAULT now()

Likewise for UUID defaults or standard initial states.

Database defaults are especially helpful when several services, scripts, or import jobs write to the same table.

Do not duplicate the same mandatory default in five application codebases unless the product behavior actually belongs at the application layer.

Use timestamptz for real-world instants

For timestamps that represent an absolute moment—created time, paid time, login time—timestamptz is usually the safer choice.

PostgreSQL stores timestamp-with-time-zone values as absolute instants and converts them for display according to the session timezone.

Use timestamp without time zone when the value is intentionally a local wall-clock value with no universal instant, such as “the shop opens every day at 09:00 local time.”

Time modeling bugs become painful after an application crosses time zones, so decide semantics at schema design time.

Cursor pagination scales better than giant offsets

This pattern becomes increasingly expensive at large offsets:

ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

For high-volume feeds, use an indexed stable cursor:

WHERE (created_at, id) < ($cursor_time, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

The cursor columns and ordering should match an appropriate index.

Offset pagination is perfectly fine for small datasets/admin pages. Use cursor pagination when deep pages become part of the normal hot path.

Migrations should be backwards compatible when possible

Database schema changes are production deployments.

A safer expand/contract flow is:

1. add the new nullable/default-compatible column or structure
2. deploy code that understands both old and new representations
3. backfill existing rows in controlled batches
4. switch reads/writes fully
5. tighten constraints if needed
6. remove the old schema later

Avoid assuming every application instance will restart at exactly the same instant as a migration.

For large tables, investigate whether a DDL operation rewrites data or acquires stronger locks before running it in production.

Observe the database from the database

Useful PostgreSQL views/extensions include:

• pg_stat_activity — sessions and active transactions
• pg_stat_statements — aggregated statement execution statistics
• pg_stat_user_tables — table activity and vacuum/analyze data
• pg_stat_user_indexes — index usage
• pg_stat_io — I/O statistics
• pg_stat_wal — WAL activity
• pg_locks — lock state

When an endpoint is slow, do not stop at “database latency is high.” Find the query, plan, waits, locks, rows examined, buffers touched, and transaction context.

Keep the production version current within its major line

PostgreSQL's versioning policy recommends staying on the current minor release for the major version you run.

Minor releases focus on bug fixes, security fixes, and low-risk corrections rather than introducing a new major feature set.

As of September 2026, PostgreSQL 18.6 is current for major version 18. PostgreSQL 19 remains a development release and should not be treated as the production default yet.

A practical application checklist

Before shipping a PostgreSQL-backed feature, ask:

1. Are important invariants enforced with database constraints?
2. Are column types expressing the real domain?
3. Are transactions short and scoped to one business invariant?
4. Does the chosen isolation level match the correctness requirement?
5. Can one atomic SQL statement replace a read-then-write race?
6. Does each important query have a measured index strategy?
7. Are JSONB fields genuinely flexible rather than hiding relational structure?
8. Are arrays/documents bounded?
9. Are defaults/generated values owned in one clear place?
10. Are timestamps modeled with correct timezone semantics?
11. Can schema migrations roll out without an all-at-once deployment?
12. Can production query behavior be diagnosed with PostgreSQL statistics and plans?

PostgreSQL works best when the application and database share responsibility clearly: the application owns product behavior; PostgreSQL owns data integrity, transactional state changes, and efficient access to durable data.

Official references

• PostgreSQL 18.6 Documentation
• PostgreSQL 18 — Constraints
• PostgreSQL 18 — Transaction Isolation
• PostgreSQL 18 — Generated Columns
• PostgreSQL 18 Release Notes