Database Access Patterns | The Garnet Wiki

Database access patterns are the fundamental strategies engineers use to read from and write to databases efficiently, reliably, and scalably. They matter when data latency, consistency, and availability directly impact user experience, system throughput, or operational resilience—common in web services, real-time analytics, and transactional systems. This reference details patterns that solve specific access challenges, with exact mechanics, common pitfalls, and practical guidance derived from real-world use across diverse environments.

Reading Data

Simple Row Lookup

Retrieve a single row by primary key using an indexed lookup.

SELECT id, name, email, created_at 
FROM users 
WHERE id = $1;

This pattern assumes the primary key is well-indexed and the query is executed within a transaction or as a standalone statement. The client sends id as a parameter (e.g., via psql -c "SELECT ... WHERE id = 123"), and the database returns a single row, often as a ROW object or a JSON structure.

Common Failure Mode: The query returns no rows, but the application assumes id exists. The most common silent failure is missing WHERE id IS NOT NULL in the query, leading to id = NULL being treated as id = NULL rather than id = $1. The IS NULL test must be explicit.

Best Practice: Use FOR UPDATE to prevent race conditions during read-modify-write:

BEGIN;
SELECT id, name, email 
FROM users 
WHERE id = $1 
FOR UPDATE;
-- Update logic here
COMMIT;

Batched Row Fetch

Retrieve multiple rows from a table using a list of keys.

SELECT id, name, email 
FROM users 
WHERE id IN (1, 2, 3, 4, 5);

This pattern is efficient when the list of keys is known at query time and fits in a single query. It reduces round trips compared to individual lookups.

Common Failure Mode: The list of keys grows beyond the database’s IN clause limit (typically 1000 items in PostgreSQL, 1024 in MySQL). When exceeded, the query fails silently: the database returns a partial result set with missing rows.

Mitigation: Split large IN lists into chunks:

WITH chunks AS (
  SELECT generate_series(1, 1000) AS chunk
)
SELECT u.id, u.name, u.email
FROM users u
JOIN chunks c ON u.id = c.chunk
WHERE c.chunk BETWEEN 1 AND 1000;

Or use a temporary table:

CREATE TEMP TABLE lookup_ids (id BIGINT PRIMARY KEY);
INSERT INTO lookup_ids VALUES (1), (2), (3), (4), (5);
SELECT u.id, u.name, u.email
FROM users u
JOIN lookup_ids l ON u.id = l.id;

Sharp Edge: Using VALUES with IN is often misunderstood:

SELECT id, name 
FROM users 
WHERE id IN (VALUES (1), (2), (3));

This is equivalent to IN (1, 2, 3) but performs better when the list is large and dynamic. However, VALUES (1), (2), (3) creates a three-row table, which is not inlined unless the planner chooses to materialize it.

Paginated Result Sets

Fetch data in chunks, typically for UIs or APIs.

SELECT id, name, email 
FROM users 
ORDER BY id 
LIMIT 50 OFFSET 100;

The client tracks offset and limit and retrieves pages iteratively. For stable pagination, use cursor-based pagination instead of offset-based.

Cursor-Based Pagination (Recommended):

SELECT id, name, email 
FROM users 
WHERE id > $1 
ORDER BY id 
LIMIT 50;

The client passes the last id from the previous page as $1. This avoids the performance degradation of OFFSET with large data sets.

Common Failure Mode: Using OFFSET with large tables causes increasing query time due to skipped rows. The planner does not efficiently skip offset rows, especially when the table is not clustered on the id column.

Sharp Edge: The client must handle pagination tokens. For example:

First page: cursor = NULL, limit = 50
Response: { data: [...], next_cursor: 12345 }
Next request: cursor = 12345, limit = 50

The cursor is not just a number—it may be a composite key (e.g., (id, created_at)), allowing for stable, consistent, and gap-free pagination.

Writing Data

Single Row Insert

Insert a single record with explicit values.

INSERT INTO users (id, name, email, created_at)
VALUES (123, 'Alice', '[email protected]', NOW());

This pattern is used for event-driven or API-based data ingestion. The client sends a single INSERT statement with all columns populated.

Common Failure Mode: The id is generated on the client side, but the database expects id to be auto-generated. When both are present, the database may throw a duplicate key error if id already exists.

Mitigation: Use ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE:

INSERT INTO users (id, name, email)
VALUES (123, 'Alice', '[email protected]')
ON CONFLICT (id) DO NOTHING;

This prevents duplicate inserts and is essential in idempotent systems.

Bulk Insert (Upsert)

Insert multiple rows, updating existing ones on conflict.

INSERT INTO users (id, name, email, updated_at)
VALUES 
  (1, 'Alice', '[email protected]', NOW()),
  (2, 'Bob', '[email protected]', NOW()),
  (3, 'Charlie', '[email protected]', NOW())
ON CONFLICT (id)
DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at;

This pattern scales well when inserting or updating tens to thousands of rows in a single transaction.

Common Failure Mode: The ON CONFLICT clause is applied to a single column, but the UPDATE uses EXCLUDED values that are not fully populated. The client assumes EXCLUDED is the new row, but misses updated_at or created_at.

Sharp Edge: Use ON CONFLICT (id) with DO UPDATE and EXCLUDED to access the incoming row’s values. The EXCLUDED table is a temporary view of the new rows, and its columns are available in the SET clause.

Best Practice: Wrap bulk inserts in a transaction with explicit BEGIN, COMMIT, and ROLLBACK handling:

BEGIN;
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]');
-- Other inserts
COMMIT;

This ensures atomicity and allows for rollback on error.

Conditional Insert

Insert a row only if it does not exist.

INSERT INTO users (id, name, email)
SELECT 123, 'Alice', '[email protected]'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE id = 123
);

This pattern is used when the client does not know whether the row exists and wants to avoid duplicate entries.

Common Failure Mode: The SELECT inside INSERT is not optimized. The EXISTS subquery is not indexed on id, causing a full table scan for every insert.

Mitigation: Ensure id is indexed and use INSERT ... FROM with a CTE:

WITH new_users AS (
  SELECT 123 AS id, 'Alice' AS name, '[email protected]' AS email
)
INSERT INTO users (id, name, email)
SELECT nu.id, nu.name, nu.email
FROM new_users nu
WHERE NOT EXISTS (
  SELECT 1 FROM users u WHERE u.id = nu.id
);

This allows for better query planning and can be easily extended to handle multiple rows.

Reading and Writing Together

Read-Modify-Write (RMW)

Fetch a row, modify it, and write it back in a single transaction.

BEGIN;
SELECT balance, version FROM accounts WHERE id = 1 FOR UPDATE;
-- Apply business logic: balance = balance - 100
UPDATE accounts 
SET balance = 900, version = 3 
WHERE id = 1;
COMMIT;

This pattern ensures consistency when multiple transactions modify the same data concurrently.

Common Failure Mode: The FOR UPDATE lock is held until COMMIT, but the client takes time to compute the new balance. During this time, another transaction can modify the same row, leading to lost updates.

Mitigation: Use versioning (e.g., version column) to detect and resolve conflicts:

UPDATE accounts 
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = $1;

If the UPDATE returns zero rows, the version has changed. The client must retry the entire RMW operation.

Sharp Edge: The version column is not just a counter—it must be incremented on every update, and the client must pass the expected version in the WHERE clause. The database checks for concurrent updates by comparing the expected version with the current one.

Streamed Reads with Continuous Updates

Fetch data that updates continuously, such as real-time dashboards or message queues.

-- Client opens a connection and executes:
LISTEN user_updates;
-- Server sends notifications whenever a user is inserted or updated

The client maintains a long-lived connection (e.g., via psql -c "LISTEN user_updates"), and the database sends NOTIFY user_updates, '123' whenever a row is modified.

Common Failure Mode: The client does not handle NOTIFY messages correctly. It may miss messages during startup or fail to process them in order.

Best Practice: Use LISTEN and NOTIFY in a loop, with error handling:

-- Server side
CREATE OR REPLACE FUNCTION notify_user_update()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('user_updates', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_updated
AFTER INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION notify_user_update();

The client polls for notifications using a loop:

psql -c "LISTEN user_updates" -tA -F ',' -c "SELECT pg_notify('user_updates', '123')"

Each NOTIFY message contains the id of the updated row, allowing the client to update its cache or UI.

Handling Failures

Retryable Database Operations

Many database operations are subject to transient failures: network timeouts, deadlocks, connection drops.

Common Pattern: Use exponential backoff with retry logic for insert and update operations.

import psycopg2
from time import sleep

def insert_user_with_retry(conn, user_data, max_retries=5):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
                    (user_data['id'], user_data['name'], user_data['email'])
                )
            conn.commit()
            return
        except psycopg2.OperationalError as e:
            if attempt == max_retries - 1:
                raise
            sleep(0.5 * (2 ** attempt))  # Exponential backoff
            conn.rollback()

Common Failure Mode: The client retries an operation but does not handle DuplicateKeyError or UniqueViolation. The database returns duplicate key but the client assumes it’s a network error.

Sharp Edge: Use SAVEPOINT within transactions to allow partial rollbacks during retries:

SAVEPOINT before_user_insert;
INSERT INTO users (id, name, email) VALUES (123, 'Alice', '[email protected]');
-- Other operations
RELEASE SAVEPOINT before_user_insert;

If a later step fails, the client can roll back only to before_user_insert, preserving earlier work.

Connection Pooling and Session State

Applications often reuse database connections via connection pools (e.g., pgBouncer, HikariCP, PooledDataSource).

Common Failure Mode: The application assumes session state (e.g., SET search_path) is preserved across connections. However, each connection in the pool may have its own session state.

Mitigation: Explicitly set session state on each connection:

-- After acquiring a connection from the pool
SET search_path TO public, app, staging;
SET timezone TO 'UTC';
SET application_name TO 'user-service';

The client must ensure these settings are applied at connection startup, possibly via a connection_init script.

Sharp Edge: The application_name is not just metadata—it’s used by the database for monitoring and query planning. It affects pg_stat_statements, pg_stat_activity, and autovacuum behavior.

Summary

Database access patterns are not just about writing SQL. They are about managing data flow, handling concurrency, ensuring consistency, and recovering from failure. The choice of pattern—row lookup, batch fetch, cursor pagination, bulk insert, RMW, streaming—directly impacts performance, correctness, and scalability. Engineers must understand the mechanics, failure modes, and trade-offs of each to build resilient, high-throughput systems.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.