Common database and transaction mistakes (PostgreSQL, ORMs) Intermediate

Estimated reading time: 15 min read

Read first

Writing transactions in code: keeping data correct when many requests run at once

This page collects the database mistakes code review often misses: code that runs correctly on a dev machine with 100 rows, then gets slow, corrupts data or locks tables in production.

Each pair only shows the default fix. The deep explanations are in Writing transactions in code and the other articles in the Database group.

At a glance: which mistake, when it shows up

Mistake	On a dev machine	In production
N+1, `SELECT *`, deep OFFSET	Fast	Slows down as rows grow
Check-then-insert, read-modify-write	Correct	Corrupt data under concurrent requests
Transaction on the wrong connection	Correct (one request)	Lost locks, half-done writes
`NOT IN` meeting `NULL`	Correct (no `NULL` yet)	Returns empty, no error
Missing foreign key index, `lower(email)`	Fast	Seq scan every time
Table-locking migration	A few ms	Blocks writes for minutes

Measurements on this page ran on PostgreSQL 17.11 (the repo’s Docker setup), with a 2-million-row orders table and a 100-thousand-row customer table.

Queries

N+1: queries inside a loop

Common

// Knex: 1 query for users + N queries for posts
const users = await knex('users').select('id', 'name');
for (const u of users) {
  u.posts = await knex('posts').where({ user_id: u.id });
}

// TypeORM: same mistake, written with a repository
for (const u of users) {
  u.posts = await postRepo.find({ where: { user: { id: u.id } } });
}

Prefer

// Knex: 2 queries, grouped by user_id in the app
const users = await knex('users').select('id', 'name');
const posts = await knex('posts').whereIn('user_id', users.map((u) => u.id));
const byUser = Map.groupBy(posts, (p) => p.user_id);
for (const u of users) u.posts = byUser.get(u.id) ?? [];

// TypeORM: let the ORM load the relation
const usersWithPosts = await userRepo.find({ relations: { posts: true } });
// Prisma 7: prisma.user.findMany({ include: { posts: true } })

Why

Each loop iteration is a network round-trip. 3 users produce 4 queries (counted with Knex’s query event), 500 users produce 501.
On dev with a local DB, round-trips cost almost nothing, so nobody notices. In production with the DB on another machine, each round-trip adds hundreds of microseconds to a few ms.
whereIn generates where "user_id" in ($1, $2, ...): a fixed number of queries, independent of N.

SELECT * and loading the whole table to filter in the app

Common

const all = await orderRepo.find();                  // SELECT every column, every row
const recent = all
  .filter((o) => o.customerId === customerId)
  .slice(0, 20);

Prefer

const recent = await orderRepo.find({
  select: { id: true, total: true, createdAt: true },
  where: { customerId },
  order: { createdAt: 'DESC' },
  take: 20,
});

Why

With a 2-million-row table, the bad version pulls 2 million objects into the Node process’s RAM on every request. A few concurrent requests and it’s OOM.
Filtering in the database can use indexes and sends only 20 rows over the network.
Selecting specific columns also avoids leaking sensitive columns (password_hash) when the object is returned straight from the API.

NOT IN (subquery) when the subquery contains NULL

Common

-- Customers who have never ordered
SELECT id FROM customer
WHERE id NOT IN (SELECT customer_id FROM orders);
-- orders.customer_id is nullable, there's one walk-in order (NULL)
-- → 0 rows

Prefer

SELECT c.id FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- → 2 rows, correct

Why

PostgreSQL docs: if no equal value is found and the subquery has at least one NULL, NOT IN returns null, not true. WHERE treats null as false, so every row is excluded.

Tested: 3 customers, 1 order for customer 1, 1 NULL order → NOT IN returns 0 rows, NOT EXISTS returns 2. The bug only appears from the day the first NULL row arrives.

OFFSET for deep pages on a large table

Common

// GET /orders?page=50001
const rows = await db.selectFrom('orders')
  .select(['id', 'created_at'])
  .orderBy('created_at', 'desc').orderBy('id', 'desc')
  .limit(20).offset((page - 1) * 20)
  .execute();

Prefer

// GET /orders?after=<created_at>,<id> (cursor from the previous page)
const rows = await db.selectFrom('orders')
  .select(['id', 'created_at'])
  .where(({ eb, refTuple, tuple }) =>
    eb(refTuple('created_at', 'id'), '<', tuple(cursor.createdAt, cursor.id)))
  .orderBy('created_at', 'desc').orderBy('id', 'desc')
  .limit(20)
  .execute();

Why

PostgreSQL docs: rows skipped by OFFSET still have to be computed by the server. Measured with an index on (created_at DESC, id DESC): OFFSET 1000000 takes 99 ms (reads 1,000,020 entries), keyset takes 0.06 ms (reads 20).
Page 1 is equally fast, so tests and demos don’t catch it. A crawler or someone clicking “last page” does.
The cursor must be a unique column pair (created_at, id). Details and traps: OFFSET and keyset.

Concurrency: two requests at once

The two mistakes below share the same shape: read, decide in the app, then write. Between the read and the write, another request slips in.

Loading diagram…

Check-then-insert to prevent duplicates

Common

const { rowCount } = await pool.query('SELECT 1 FROM subscriber WHERE email = $1', [email]);
if (rowCount > 0) return 'exists';
await pool.query('INSERT INTO subscriber (email) VALUES ($1)', [email]);
return 'created';

Prefer

// Schema: email text NOT NULL UNIQUE
const res = await pool.query(
  `INSERT INTO subscriber (email) VALUES ($1)
   ON CONFLICT (email) DO NOTHING RETURNING id`,
  [email],
);
return res.rowCount === 1 ? 'created' : 'exists';

Why

20 concurrent requests with the same email: the bad version creates 20 rows (another run: 15); the good version creates exactly 1 row, and 19 requests get exists.
Only a database constraint can enforce this across many requests and many instances. The app-level check is only for returning a nice error.
With DO NOTHING, skipped rows don’t appear in RETURNING. Details: Pattern 4.

Read-modify-write: lost updates

Common

const { rows } = await pool.query('SELECT n FROM counter WHERE id = 1');
await pool.query('UPDATE counter SET n = $1 WHERE id = 1', [rows[0].n + 1]);

Prefer

// Compute in the database, in one statement
await pool.query('UPDATE counter SET n = n + 1 WHERE id = 1');

// With a business condition: put it in WHERE, check rowCount
const r = await tx.query(
  'UPDATE product SET stock = stock - $2 WHERE id = $1 AND stock >= $2', [id, qty]);
if (r.rowCount === 0) throw new SoldOut();

Why

50 concurrent “+1” requests: the bad version ends with n = 1, the good one with n = 50. Every request reads the same old value and overwrites it with a constant.
Wrapping the bad version in BEGIN/COMMIT doesn’t fix it: under Read Committed the update is still lost (the flash sale demo oversells by 40 orders).
If you need app logic between the read and the write, use SELECT ... FOR UPDATE. See Pattern 1 and Pattern 2.

Transactions

BEGIN/COMMIT via pool.query (node-postgres)

Common

await pool.query('BEGIN');
await pool.query('UPDATE account SET balance = balance - $2 WHERE id = $1', [from, amt]);
await pool.query('UPDATE account SET balance = balance + $2 WHERE id = $1', [to, amt]);
await pool.query('COMMIT');

Prefer

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE account SET balance = balance - $2 WHERE id = $1', [from, amt]);
  await client.query('UPDATE account SET balance = balance + $2 WHERE id = $1', [to, amt]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK').catch(() => {});
  throw err;
} finally {
  client.release();
}

Why

Each pool.query borrows any connection, so the four statements may run on four connections. The node-postgres docs say it plainly: don’t use transactions with pool.query. Measured consequences: locks released early, overselling, and orphaned BEGINs holding locks forever. See The classic node-postgres mistake.

Using knex/db instead of trx inside a transaction callback

Common

// Knex
await knex.transaction(async (trx) => {
  await trx('orders').insert(order);
  await knex('audit_log').insert(log);     // knex, not trx
});

// Kysely
await db.transaction().execute(async (trx) => {
  await trx.insertInto('orders').values(order).execute();
  await db.insertInto('audit_log').values(log).execute(); // db, not trx
});

Prefer

await knex.transaction(async (trx) => {
  await trx('orders').insert(order);
  await trx('audit_log').insert(log);
});

await db.transaction().execute(async (trx) => {
  await trx.insertInto('orders').values(order).execute();
  await trx.insertInto('audit_log').values(log).execute();
});
// TypeORM: use the manager passed in; Prisma 7: use tx, not prisma

Why

Tested with Knex and Kysely: the callback throws after both inserts. The statement through trx is rolled back; the one through knex/db is still in the table.
Statements through knex/db run on another connection, outside the transaction: they can’t see uncommitted data and aren’t protected by the transaction’s locks.
Common when a repository holds knex/db as a field. Make repository functions take an executor parameter. API table per ORM: ORMs: same principle.

Making HTTP calls inside a transaction

Common

await prisma.$transaction(async (tx) => {
  await tx.product.update({ where: { id }, data: { stock: { decrement: 1 } } });
  await paymentGateway.charge(card, amount);       // HTTP 1-3 seconds
  await tx.order.create({ data: { productId: id, status: 'paid' } });
});

Prefer

const order = await prisma.$transaction(async (tx) => {
  await tx.product.update({ where: { id }, data: { stock: { decrement: 1 } } });
  const order = await tx.order.create({ data: { productId: id, status: 'pending_payment' } });
  await tx.outbox.create({ data: { type: 'payment.requested', payload: { orderId: order.id } } });
  return order;
});
// a worker reads the outbox and calls the payment gateway after commit

Why

The lock on the product row is held the whole time you wait for the third party. Every request buying the same product queues behind it.
A rollback can’t take back money already charged. Prisma 7 also has a default 5-second timeout for interactive transactions: a slow gateway gets the transaction cancelled after the charge.
The Prisma 7 docs advise avoiding network requests inside transactions. Details: Keep transactions short, Transactional outbox.

Indexes and migrations

A foreign key without an index

Common

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customer(id) ON DELETE CASCADE
);
-- Assuming REFERENCES creates an index automatically. It doesn't.

Prefer

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customer(id) ON DELETE CASCADE
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Why

PostgreSQL automatically indexes the referenced side (the primary key), not the foreign key column. The docs explain why: it isn’t always needed.
Without the index: WHERE customer_id = 42 is a parallel seq scan taking 27 ms; deleting one customer costs 40.7 ms in the foreign key trigger. With the index: 1.2 ms and 0.09 ms.
Deleting customers in bulk means one scan of orders per row. This is the source of many “data cleanup” jobs that run for an hour.

WHERE lower(email) = ... with only an index on email

Common

CREATE UNIQUE INDEX customer_email_idx ON customer (email);

SELECT id FROM customer WHERE lower(email) = lower($1);
-- Seq Scan on customer, 11 ms on 100 thousand rows

Prefer

CREATE UNIQUE INDEX customer_email_lower_idx ON customer (lower(email));

SELECT id FROM customer WHERE lower(email) = lower($1);
-- Index Scan using customer_email_lower_idx, 0.03 ms

Why

The index on email stores the original values; the planner can’t use it for the expression lower(email). To use an index, the expression in the query must match the index’s expression.
UNIQUE on lower(email) also blocks duplicates like An@a.vn and an@a.vn, which a plain index can’t do.
More on diagnosing unused indexes: Expression indexes, Index not used.

Migration: adding a column with a volatile default

Common

ALTER TABLE orders ADD COLUMN public_id uuid DEFAULT gen_random_uuid();
-- 2 million rows: 2.9 seconds, rewrites the whole table and its indexes,
-- holding ACCESS EXCLUSIVE the entire time (blocks even reads)

Prefer

-- 1. Add the column without a default: catalog-only change (2.5 ms)
ALTER TABLE orders ADD COLUMN public_id uuid;
-- 2. The default applies only to new rows, existing rows untouched (0.9 ms)
ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();
-- 3. Backfill in small batches in a separate job, then add NOT NULL if needed
UPDATE orders SET public_id = gen_random_uuid()
WHERE id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 5000);

Why

Since PostgreSQL 11, a non-volatile default (a constant, now()) is computed once and stored in the catalog: no rewrite. Measured on PG 17: DEFAULT 'new' takes 3 ms, DEFAULT now() takes 0.5 ms, relfilenode unchanged.
A volatile default (gen_random_uuid(), random(), clock_timestamp()) must be computed per row, so PostgreSQL rewrites the whole table: relfilenode changes, 2.9 seconds for 2 million rows.
ADD COLUMN always takes ACCESS EXCLUSIVE. The good version only holds it for a few ms, but migrations should still set lock_timeout so they don’t queue behind a long query.

Migration: CREATE INDEX on a table receiving writes

Common

// Knex migration, runs inside a transaction by default
export async function up(knex) {
  await knex.raw('CREATE INDEX orders_total_idx ON orders (total)');
}

Prefer

// CONCURRENTLY can't run inside a transaction: disable it for this file only
export const config = { transaction: false };

export async function up(knex) {
  await knex.raw('CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_total_idx ON orders (total)');
}

Why

A plain CREATE INDEX takes a ShareLock (checked in pg_locks): reads still run, but every INSERT/UPDATE/DELETE waits until the build finishes. On a large table that’s minutes without writes.
Running CONCURRENTLY inside a transaction fails with cannot run inside a transaction block. Knex lets you disable the transaction per file via config.transaction.
A CONCURRENTLY build that fails midway leaves an INVALID index that still costs write overhead. Check \d, DROP INDEX, then rerun.

Review checklist

Next: API & security.

#engineering #postgresql #transaction #orm #knex #kysely #typeorm #prisma #performance