PostgreSQL indexes and EXPLAIN: engineering in practice

CHAPTER 16

DEEP DIVE · FROM BASICS TO CODE

Understanding: PostgreSQL indexes and EXPLAIN

You can read this chapter before running the experiment. Then return to the live trace and match each concept to a real event.

FIRST, IN PLAIN LANGUAGE

≈

An index is a separate guide to a book. It can find a small set of pages quickly, but it consumes space, must be updated on every write, and sometimes reading the whole book in order is cheaper.

TECHNICAL FOUNDATION

PostgreSQL stores a table as heap pages and an index as another structure. The planner estimates selectivity and compares Seq Scan, Index Scan, Index Only Scan, and Bitmap Scan costs. B-tree supports equality, ranges, and ordering; Hash supports equality; GIN handles multi-component values such as arrays and full text; BRIN stores summaries for physical block ranges and benefits from correlation with row order.

Why it mattersIndexing without reading plans produces both missing access paths and expensive piles of unused indexes. The useful skill is explaining the trade-off and measurement, not merely remembering CREATE INDEX.

OFFICIAL RESOURCES

Official PostgreSQL documentationThe primary source for SQL, the planner, indexes, MVCC, transactions, and PostgreSQL maintenance.Index typesB-tree, Hash, GiST, SP-GiST, GIN, and BRIN operators and use cases.Using EXPLAINPlan trees, cost, rows, actual time, loops, buffers, and EXPLAIN ANALYZE caveats.Multicolumn indexesColumn order, leading columns, and behavior across index types.

WHERE THE WORK RUNS

01APPLICATION CODErepository · use case · transaction

→

02SQL + DRIVERparameters · pool · protocol

→

03POSTGRESQLparser · planner · executor · MVCC

→

04STORAGEheap pages · indexes · WAL · disk

01 · GLOSSARY

Terms used in this experiment

Understand the words first, then the execution order.

Selectivity

The fraction of rows passing a predicate. A smaller result usually makes indexed access more attractive.

B-tree

A balanced tree for equality, ranges, ordering, and compatible prefix searches.

Hash index

A hash structure for equality comparisons only; it does not support ranges or ORDER BY.

GIN

An inverted index for values with components, including arrays, jsonb, and full-text lexemes.

BRIN

Compact summaries for block ranges, effective when values correlate with physical row order.

EXPLAIN ANALYZE

A command that actually executes a query and reports actual rows, time, and loops.

Cardinality estimate

The planner estimate of emitted rows. A large estimate error often signals weak statistics or correlated predicates.

02 · MECHANICS

What happens step by step

Each step maps to an observable runtime state.

01
Capture the real query
Optimize concrete SQL with representative parameters, volume, and data distribution.
02
Establish a baseline
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) shows the tree before a schema change.
03
Read from the leaves upward
Scan nodes obtain rows; parent nodes filter, join, aggregate, sort, and limit them.
04
Compare estimates with actuals
Inspect rows, loops, time, filtered rows, and buffers. Estimate errors can select a poor join strategy.
05
Add a narrow index
A composite B-tree matches tenant equality and created_at range/order; INCLUDE can help index-only access.
06
Measure again
After ANALYZE, compare plans while accounting for cache warmup, write cost, and index size.

03 · CONTEXT

Where the result needs context

These details explain why similar code can sometimes produce a different trace.

Seq Scan is not automatically bad

When a query returns much of a table, sequential reads can be cheaper than random heap access through an index.

Cost is not milliseconds

Planner cost uses relative units. Actual Time measures one execution and depends on cache and system load.

Column order matters

A B-tree on (tenant_id, created_at) serves tenant and tenant-plus-date predicates, but usually not date alone.

EXPLAIN ANALYZE executes writes

UPDATE and DELETE will change data. Investigate inside BEGIN/ROLLBACK or on a safe copy.

Index Only Scan can still visit the heap

The visibility map must prove tuple visibility; otherwise the executor performs heap fetches.

Theory

First understand which parts of Node participate in execution.

→

Simplified code

Then remove instrumentation and focus on the central mechanism.

→

Runtime code

Finally match the model to the code that produces the live trace.

04 · Simplified code

A minimal model without instrumentation

src/demos.js · educational snippetJavaScript

const sql = `
  SELECT id, created_at, status
  FROM events
  WHERE tenant_id = $1
    AND created_at >= $2
  ORDER BY created_at DESC
`;

await db.query(`
  CREATE INDEX events_tenant_created_idx
  ON events (tenant_id, created_at DESC)
  INCLUDE (status)
`);

await db.query(
  'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + sql,
  [tenantId, since],
);

05 · Runtime code

The complete code executed by the scenario

This is not an alternative example: these are the functions and files used by the Run button.

ACTUAL SOURCE

The source is generated from the real server function. Scenarios using a child process or Worker include every participating file.

src/database-lab.js

PostgreSQL runtime699 lines

import { randomUUID } from 'node:crypto';
import { performance } from 'node:perf_hooks';
import pg from 'pg';

const { Pool } = pg;
const STATEMENT_TIMEOUT_MS = 8_000;
const CONNECTION_TIMEOUT_MS = 1_500;

function databaseUrl() {
  return process.env.DATABASE_URL?.trim() || null;
}

function safeIdentifier(prefix) {
  return `${prefix}_${randomUUID().replaceAll('-', '').slice(0, 12)}`;
}

function planReport(result) {
  const raw = result.rows[0]['QUERY PLAN'];
  const report = Array.isArray(raw) ? raw[0] : JSON.parse(raw)[0];
  const nodes = [];

  function visit(node, depth = 0) {
    nodes.push({
      depth,
      type: node['Node Type'],
      relation: node['Relation Name'] ?? null,
      index: node['Index Name'] ?? null,
      estimatedRows: node['Plan Rows'],
      actualRows: node['Actual Rows'],
      loops: node['Actual Loops'],
    });
    for (const child of node.Plans ?? []) visit(child, depth + 1);
  }

  visit(report.Plan);
  return {
    nodes,
    executionMs: Number(report['Execution Time'] ?? 0),
    planningMs: Number(report['Planning Time'] ?? 0),
  };
}

function planLine(plan) {
  return plan.nodes
    .map((node) => {
      const target = node.index ?? node.relation;
      return `${'  '.repeat(node.depth)}${node.type}${target ? ` [${target}]` : ''}`;
    })
    .join(' → ');
}

async function configureClient(client) {
  await client.query(`SET statement_timeout = '${STATEMENT_TIMEOUT_MS}ms'`);
  await client.query(
    `SET idle_in_transaction_session_timeout = '${STATEMENT_TIMEOUT_MS}ms'`,
  );
  await client.query("SET lock_timeout = '2500ms'");
}

async function rollbackQuietly(client) {
  if (!client) return;
  try {
    await client.query('ROLLBACK');
  } catch {
    // The client may not currently be in a transaction.
  }
}

async function withDatabaseLab(emit, run) {
  const connectionString = databaseUrl();
  if (!connectionString) {
    emit(
      'postgres',
      'skip',
      'PostgreSQL не подключён: задайте DATABASE_URL или запустите проект через Docker Compose',
    );
    return false;
  }

  const pool = new Pool({
    connectionString,
    max: 4,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MS,
    idleTimeoutMillis: 5_000,
    allowExitOnIdle: true,
    application_name: 'node-loop-lab',
  });
  const schema = safeIdentifier('node_loop_lab');

  try {
    const versionResult = await pool.query(
      "SELECT current_setting('server_version') AS version",
    );
    emit(
      'postgres',
      'connect',
      `Подключён PostgreSQL ${versionResult.rows[0].version}; создаём изолированную схему ${schema}`,
    );
    await pool.query(`CREATE SCHEMA ${schema}`);
    await run({ pool, schema });
    return true;
  } catch (error) {
    const safeMessage =
      error?.code === 'ECONNREFUSED'
        ? 'соединение отклонено'
        : error?.code
          ? `SQLSTATE ${error.code}`
          : 'ошибка подключения';
    emit('postgres', 'error', `Сценарий PostgreSQL остановлен: ${safeMessage}`);
    return false;
  } finally {
    try {
      await pool.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE`);
      emit('cleanup', 'drop', 'Учебная схема удалена; постоянные данные не создавались');
    } catch {
      // Connection failures can make cleanup impossible; the schema name is unique
      // and contains no user data.
    }
    await pool.end().catch(() => {});
  }
}

export async function databaseSqlBasics(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      await client.query(`
        CREATE TABLE ${schema}.products (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          name text NOT NULL,
          category text NOT NULL,
          price numeric(10, 2) NOT NULL CHECK (price >= 0),
          stock integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
          description text,
          active boolean NOT NULL DEFAULT true
        )
      `);
      emit(
        'ddl',
        'create-table',
        'CREATE TABLE описал columns, data types, defaults и constraints таблицы products',
      );

      const inserted = await client.query(
        `INSERT INTO ${schema}.products
           (name, category, price, stock, description)
         VALUES
           ($1, $2, $3, $4, $5),
           ($6, $7, $8, $9, $10),
           ($11, $12, $13, $14, $15)
         RETURNING id, name, price`,
        [
          'Node.js Handbook',
          'books',
          2400,
          8,
          'Runtime and backend foundations',
          'NestJS Patterns',
          'books',
          3100,
          5,
          null,
          'Mechanical Keyboard',
          'hardware',
          7800,
          2,
          'USB keyboard',
        ],
      );
      emit(
        'insert',
        'rows',
        `INSERT добавил ${inserted.rowCount} строки; RETURNING вернул generated id без отдельного SELECT`,
      );

      const selected = await client.query(
        `SELECT
           id,
           name AS product_name,
           price,
           stock,
           price * stock AS inventory_value
         FROM ${schema}.products
         WHERE category = $1
           AND price <= $2
           AND active IS TRUE
         ORDER BY price DESC
         LIMIT $3`,
        ['books', 3500, 10],
      );
      emit(
        'select',
        'filter',
        `SELECT → FROM → WHERE → ORDER BY → LIMIT вернул: ${selected.rows
          .map((row) => row.product_name)
          .join(', ')}`,
      );

      const nullWrong = await client.query(
        `SELECT count(*)::int AS count
         FROM ${schema}.products
         WHERE description = NULL`,
      );
      const nullRight = await client.query(
        `SELECT count(*)::int AS count
         FROM ${schema}.products
         WHERE description IS NULL`,
      );
      emit(
        'null',
        'comparison',
        `description = NULL нашёл ${nullWrong.rows[0].count}; IS NULL нашёл ${nullRight.rows[0].count}`,
      );

      const updated = await client.query(
        `UPDATE ${schema}.products
         SET stock = stock - $1
         WHERE name = $2
           AND stock >= $1
         RETURNING id, name, stock`,
        [2, 'Node.js Handbook'],
      );
      emit(
        'update',
        'returning',
        `UPDATE изменил stock и вернул новое значение=${updated.rows[0].stock}`,
      );

      const grouped = await client.query(
        `SELECT
           category,
           count(*)::int AS product_count,
           round(avg(price), 2) AS average_price,
           sum(stock)::int AS total_stock
         FROM ${schema}.products
         GROUP BY category
         HAVING count(*) >= $1
         ORDER BY category`,
        [1],
      );
      emit(
        'aggregate',
        'group-by',
        `GROUP BY создал ${grouped.rowCount} группы; HAVING фильтрует уже агрегированные группы`,
      );

      const deleted = await client.query(
        `DELETE FROM ${schema}.products
         WHERE active IS FALSE
         RETURNING id`,
      );
      emit(
        'delete',
        'safe-delete',
        `DELETE с WHERE удалил строк=${deleted.rowCount}; без WHERE удалились бы все строки`,
      );
    } finally {
      await rollbackQuietly(client);
      client.release();
    }
  });
}

export async function databaseConstraintsAndAcid(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'ddl',
        'schema',
        'Создаём PRIMARY KEY, UNIQUE, CHECK и FOREIGN KEY как правила целостности внутри БД',
      );
      await client.query(`
        CREATE TABLE ${schema}.customers (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          email text NOT NULL UNIQUE,
          name text NOT NULL CHECK (char_length(name) >= 2)
        );

        CREATE TABLE ${schema}.orders (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          customer_id bigint NOT NULL
            REFERENCES ${schema}.customers(id) ON DELETE RESTRICT,
          amount numeric(12, 2) NOT NULL CHECK (amount > 0),
          status text NOT NULL DEFAULT 'new'
            CHECK (status IN ('new', 'paid', 'cancelled'))
        );
      `);

      const customer = await client.query(
        `INSERT INTO ${schema}.customers (email, name)
         VALUES ($1, $2)
         RETURNING id`,
        ['learner@example.com', 'Learner'],
      );
      emit(
        'query',
        'parameters',
        'Параметры $1/$2 переданы отдельно от SQL: значения не становятся частью синтаксиса запроса',
      );

      try {
        await client.query(
          `INSERT INTO ${schema}.orders (customer_id, amount)
           VALUES ($1, $2)`,
          [customer.rows[0].id, -50],
        );
      } catch (error) {
        emit(
          'constraint',
          'check',
          `CHECK отклонил отрицательную сумму: SQLSTATE ${error.code}`,
        );
      }

      await client.query('BEGIN');
      await client.query(
        `INSERT INTO ${schema}.orders (customer_id, amount, status)
         VALUES ($1, $2, $3)`,
        [customer.rows[0].id, 1250, 'paid'],
      );
      const inside = await client.query(
        `SELECT count(*)::int AS count FROM ${schema}.orders`,
      );
      await client.query('ROLLBACK');
      const after = await client.query(
        `SELECT count(*)::int AS count FROM ${schema}.orders`,
      );
      emit(
        'transaction',
        'rollback',
        `Внутри транзакции строк=${inside.rows[0].count}; после ROLLBACK строк=${after.rows[0].count}`,
      );

      emit(
        'acid',
        'model',
        'ACID: constraints поддерживают consistency, транзакция даёт atomicity, WAL/disk — durability, а isolation управляет видимостью параллельных изменений',
      );
    } finally {
      await rollbackQuietly(client);
      client.release();
    }
  });
}

export async function databaseIndexesAndExplain(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'dataset',
        'seed',
        'Создаём 40 000 событий с коррелированным временем, tenant_id, status и массивом tags',
      );
      await client.query(`
        CREATE TABLE ${schema}.events (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          tenant_id integer NOT NULL,
          created_at timestamptz NOT NULL,
          status text NOT NULL,
          tags text[] NOT NULL
        );

        INSERT INTO ${schema}.events (tenant_id, created_at, status, tags)
        SELECT
          (g % 100) + 1,
          now() - (g * interval '1 second'),
          CASE WHEN g % 5 = 0 THEN 'failed' ELSE 'processed' END,
          ARRAY[
            CASE WHEN g % 3 = 0 THEN 'api' ELSE 'worker' END,
            CASE WHEN g % 7 = 0 THEN 'priority' ELSE 'normal' END
          ]
        FROM generate_series(1, 40000) AS g;

        ANALYZE ${schema}.events;
      `);

      const query = `
        SELECT id, created_at, status
        FROM ${schema}.events
        WHERE tenant_id = 37
          AND created_at >= now() - interval '6 hours'
        ORDER BY created_at DESC
      `;
      const before = planReport(
        await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`),
      );
      emit(
        'planner',
        'before-index',
        `До составного индекса: ${planLine(before)}; execution=${before.executionMs.toFixed(2)} ms`,
      );

      await client.query(`
        CREATE INDEX events_tenant_created_btree
          ON ${schema}.events USING btree (tenant_id, created_at DESC)
          INCLUDE (status);
        CREATE INDEX events_status_hash
          ON ${schema}.events USING hash (status);
        CREATE INDEX events_created_brin
          ON ${schema}.events USING brin (created_at);
        CREATE INDEX events_tags_gin
          ON ${schema}.events USING gin (tags);
        ANALYZE ${schema}.events;
      `);

      const after = planReport(
        await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`),
      );
      emit(
        'planner',
        'after-index',
        `После B-tree: ${planLine(after)}; execution=${after.executionMs.toFixed(2)} ms`,
      );

      const sizes = await client.query(
        `
          SELECT c.relname, pg_relation_size(c.oid)::bigint AS bytes
          FROM pg_class AS c
          JOIN pg_namespace AS n ON n.oid = c.relnamespace
          WHERE n.nspname = $1 AND c.relkind = 'i'
          ORDER BY bytes DESC
        `,
        [schema],
      );
      emit(
        'indexes',
        'size',
        `Размеры индексов: ${sizes.rows
          .map((row) => `${row.relname}=${Math.round(Number(row.bytes) / 1024)} KiB`)
          .join(', ')}`,
      );
      emit(
        'optimizer',
        'decision',
        'Индекс не является приказом: planner выбирает Seq Scan, Index Scan или Bitmap Scan по статистике, селективности и стоимости',
      );
    } finally {
      client.release();
    }
  });
}

export async function databaseTransactionsAndLocks(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    await pool.query(`
      CREATE TABLE ${schema}.accounts (
        id integer PRIMARY KEY,
        balance integer NOT NULL CHECK (balance >= 0),
        version integer NOT NULL DEFAULT 0
      );
      INSERT INTO ${schema}.accounts (id, balance) VALUES (1, 1000);
    `);

    const first = await pool.connect();
    const second = await pool.connect();
    try {
      await Promise.all([configureClient(first), configureClient(second)]);

      await first.query('BEGIN ISOLATION LEVEL READ COMMITTED');
      const rcBefore = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await second.query(
        `UPDATE ${schema}.accounts SET balance = 1100 WHERE id = 1`,
      );
      const rcAfter = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await first.query('ROLLBACK');
      emit(
        'isolation',
        'read-committed',
        `READ COMMITTED: первый SELECT=${rcBefore.rows[0].balance}, второй SELECT=${rcAfter.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      await first.query('BEGIN ISOLATION LEVEL REPEATABLE READ');
      const rrBefore = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await second.query(
        `UPDATE ${schema}.accounts SET balance = 1100 WHERE id = 1`,
      );
      const rrAfter = await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      await first.query('ROLLBACK');
      emit(
        'isolation',
        'repeatable-read',
        `REPEATABLE READ: первый SELECT=${rrBefore.rows[0].balance}, второй SELECT=${rrAfter.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      await first.query('BEGIN');
      await first.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1 FOR UPDATE`,
      );
      await second.query('BEGIN');
      let secondAcquired = false;
      const waitStarted = performance.now();
      const secondLock = second
        .query(
          `SELECT balance FROM ${schema}.accounts WHERE id = 1 FOR UPDATE`,
        )
        .then((result) => {
          secondAcquired = true;
          return result;
        });
      await new Promise((resolve) => setTimeout(resolve, 120));
      emit(
        'lock',
        'wait',
        `SELECT FOR UPDATE: вторая транзакция ждёт блокировку=${!secondAcquired}`,
      );
      await first.query(
        `UPDATE ${schema}.accounts SET balance = balance - 100 WHERE id = 1`,
      );
      await first.query('COMMIT');
      await secondLock;
      const waitedMs = performance.now() - waitStarted;
      await second.query(
        `UPDATE ${schema}.accounts SET balance = balance - 200 WHERE id = 1`,
      );
      await second.query('COMMIT');
      const pessimistic = await pool.query(
        `SELECT balance FROM ${schema}.accounts WHERE id = 1`,
      );
      emit(
        'lock',
        'pessimistic',
        `Пессимистичная блокировка ждала ${waitedMs.toFixed(0)} ms; итоговый balance=${pessimistic.rows[0].balance}`,
      );

      await pool.query(
        `UPDATE ${schema}.accounts SET balance = 1000, version = 0 WHERE id = 1`,
      );
      const snapshotA = await first.query(
        `SELECT balance, version FROM ${schema}.accounts WHERE id = 1`,
      );
      const snapshotB = await second.query(
        `SELECT balance, version FROM ${schema}.accounts WHERE id = 1`,
      );
      const updateA = await first.query(
        `UPDATE ${schema}.accounts
         SET balance = $1, version = version + 1
         WHERE id = 1 AND version = $2`,
        [snapshotA.rows[0].balance - 100, snapshotA.rows[0].version],
      );
      const updateB = await second.query(
        `UPDATE ${schema}.accounts
         SET balance = $1, version = version + 1
         WHERE id = 1 AND version = $2`,
        [snapshotB.rows[0].balance - 200, snapshotB.rows[0].version],
      );
      emit(
        'lock',
        'optimistic',
        `Оптимистичная версия: update A=${updateA.rowCount}, stale update B=${updateB.rowCount}; 0 означает конфликт`,
      );
    } finally {
      await Promise.all([rollbackQuietly(first), rollbackQuietly(second)]);
      first.release();
      second.release();
    }
  });
}

export async function databaseJoinsAndMaterializedViews(emit) {
  await withDatabaseLab(emit, async ({ pool, schema }) => {
    const client = await pool.connect();
    try {
      await configureClient(client);
      emit(
        'dataset',
        'seed',
        'Создаём 2 000 клиентов и 30 000 заказов для JOIN и агрегирования',
      );
      await client.query(`
        CREATE TABLE ${schema}.customers (
          id integer PRIMARY KEY,
          name text NOT NULL,
          active boolean NOT NULL
        );
        CREATE TABLE ${schema}.orders (
          id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          customer_id integer NOT NULL REFERENCES ${schema}.customers(id),
          amount numeric(12, 2) NOT NULL,
          created_at timestamptz NOT NULL DEFAULT now()
        );
        INSERT INTO ${schema}.customers (id, name, active)
        SELECT g, 'customer-' || g, g % 5 <> 0
        FROM generate_series(1, 2000) AS g;
        INSERT INTO ${schema}.orders (customer_id, amount, created_at)
        SELECT
          (g % 2000) + 1,
          ((g % 5000) + 100)::numeric / 10,
          now() - (g * interval '1 minute')
        FROM generate_series(1, 30000) AS g;
        CREATE INDEX orders_customer_id_idx
          ON ${schema}.orders (customer_id);
        ANALYZE ${schema}.customers;
        ANALYZE ${schema}.orders;
      `);

      const joinPlan = planReport(
        await client.query(`
          EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
          SELECT c.id, c.name, sum(o.amount) AS total
          FROM ${schema}.customers AS c
          JOIN ${schema}.orders AS o ON o.customer_id = c.id
          WHERE c.active
          GROUP BY c.id, c.name
          ORDER BY total DESC
          LIMIT 20
        `),
      );
      emit(
        'join',
        'plan',
        `JOIN plan: ${planLine(joinPlan)}; execution=${joinPlan.executionMs.toFixed(2)} ms`,
      );

      const sampleCustomers = await client.query(
        `SELECT id FROM ${schema}.customers ORDER BY id LIMIT 20`,
      );
      const nPlusOneStarted = performance.now();
      for (const customer of sampleCustomers.rows) {
        await client.query(
          `SELECT count(*) FROM ${schema}.orders WHERE customer_id = $1`,
          [customer.id],
        );
      }
      const nPlusOneMs = performance.now() - nPlusOneStarted;
      const oneQueryStarted = performance.now();
      await client.query(`
        SELECT c.id, count(o.id)
        FROM ${schema}.customers AS c
        LEFT JOIN ${schema}.orders AS o ON o.customer_id = c.id
        WHERE c.id <= 20
        GROUP BY c.id
      `);
      const oneQueryMs = performance.now() - oneQueryStarted;
      emit(
        'query-shape',
        'n-plus-one',
        `N+1: 21 round trips=${nPlusOneMs.toFixed(2)} ms; один JOIN=1 round trip=${oneQueryMs.toFixed(2)} ms`,
      );

      await client.query(`
        CREATE MATERIALIZED VIEW ${schema}.customer_totals AS
        SELECT customer_id, count(*)::int AS orders_count, sum(amount) AS total
        FROM ${schema}.orders
        GROUP BY customer_id;
        CREATE UNIQUE INDEX customer_totals_customer_id_idx
          ON ${schema}.customer_totals (customer_id);
      `);
      const before = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      await client.query(
        `INSERT INTO ${schema}.orders (customer_id, amount) VALUES ($1, $2)`,
        [42, 999],
      );
      const stale = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      await client.query(`REFRESH MATERIALIZED VIEW ${schema}.customer_totals`);
      const refreshed = await client.query(
        `SELECT total FROM ${schema}.customer_totals WHERE customer_id = $1`,
        [42],
      );
      emit(
        'materialized-view',
        'refresh',
        `Materialized View: было=${before.rows[0].total}, до REFRESH=${stale.rows[0].total}, после=${refreshed.rows[0].total}`,
      );
      emit(
        'sql',
        'control',
        'Raw SQL здесь параметризован и видим; ORM полезен, пока команда проверяет сгенерированный SQL, планы, N+1 и границы транзакций',
      );
    } finally {
      client.release();
    }
  });
}

The application instruments its own live trace: rows and timestamps are recorded by real emit(...) calls, while the scenario supplies source and lane labels. This is not a V8/libuv profiler or a direct view of their internal queues. await and Promise keep the HTTP stream open until the scenario completes.

06 · RECIPES

Practical patterns worth keeping nearby

Compare the goal, code, and caveats instead of memorizing syntax without a model.

Composite B-tree

Filter a tenant and return recent events without a separate sort.

CREATE INDEX events_tenant_created_idx
ON events (tenant_id, created_at DESC)
INCLUDE (status);

Equality columns generally precede the range column.
INCLUDE increases index size and write work.

Partial index

Index only the small unfinished subset of orders.

CREATE INDEX orders_pending_idx
ON orders (created_at)
WHERE status = 'pending';

The query predicate must imply the index predicate.

GIN for an array

Find events containing a tag.

CREATE INDEX events_tags_gin ON events USING gin (tags);

SELECT * FROM events WHERE tags @> ARRAY['priority'];

GIN can make writes materially more expensive.

Safe write EXPLAIN

Obtain actual execution data without retaining a change.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET balance = balance + 10 WHERE id = 42;
ROLLBACK;

Triggers and locks still execute before ROLLBACK.

07 · PRODUCTION CASES

How a learning mistake becomes an incident

A realistic service: the original code, observable failure, corrected implementation, and why the correction works.

CASE 01

Three single-column indexes do not serve a queue query

A worker fetches the oldest pending jobs for one tenant. The table has separate indexes on tenant_id, status, and created_at, yet latency grows with table size.

INCIDENT CONTEXT

PostgreSQL may combine indexes, but it still has to filter or sort many rows. The useful access path must reflect equality predicates, ordering, and the subset queried.

BEFOREPROBLEMATIC IMPLEMENTATION

CREATE INDEX jobs_tenant_idx
  ON jobs (tenant_id);
CREATE INDEX jobs_status_idx
  ON jobs (status);
CREATE INDEX jobs_created_idx
  ON jobs (created_at);

SELECT id, payload
FROM jobs
WHERE tenant_id = $1
  AND status = 'pending'
ORDER BY created_at
LIMIT 100;

Independent indexes do not provide one ordered stream of pending rows for a tenant. EXPLAIN ANALYZE often shows extra filtering, bitmap work, or a sort.

AFTERCORRECTED IMPLEMENTATION

CREATE INDEX CONCURRENTLY jobs_pending_pick_idx
  ON jobs (tenant_id, created_at)
  INCLUDE (id)
  WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM jobs
WHERE tenant_id = $1
  AND status = 'pending'
ORDER BY created_at
LIMIT 100;

The partial composite index contains only pending jobs and returns them in the required tenant/time order. EXPLAIN ANALYZE and BUFFERS verify the real plan and I/O rather than an assumption.

FUNCTIONS AND CONSTRUCTS

What the unfamiliar calls from both code samples actually do.

CREATE INDEX: Builds an additional access structure. Every index consumes storage and must be updated when the table changes.
(tenant_id, created_at): A composite B-tree that groups rows by tenant and then keeps them ordered by created_at.
WHERE status = pending: A partial-index predicate: only the queried subset is indexed, making the structure smaller.
INCLUDE (id): Stores id in leaf pages for a possible index-only scan without adding it to the ordering key.
EXPLAIN (ANALYZE, BUFFERS): Actually executes the SELECT and reports real rows, timing, and cache or disk buffer activity.

08 · DO NOT CONFUSE

Common misconceptions

The myth is on the left; the accurate model is on the right.

MYTH

An index always accelerates a query.

→

ACTUALLY

The planner may prefer Seq Scan; every index also slows writes and uses memory or disk.

MYTH

More indexes are always better.

→

ACTUALLY

Overlapping and unused indexes create write amplification and additional maintenance.

MYTH

The first column of a composite index does not matter.

→

ACTUALLY

Leading B-tree columns determine which predicates efficiently narrow the search range.

MYTH

EXPLAIN ANALYZE only displays a plan.

→

ACTUALLY

It executes the statement; a write changes data unless explicitly rolled back.

09 · SELF-CHECK

Explain it in your own words

If you can explain the answer without quoting documentation, your mental model is starting to take shape.

Why can the planner choose Seq Scan when an index exists?
How does Bitmap Heap Scan differ from Index Scan?
For which data can BRIN beat B-tree?
What does a large estimated-versus-actual row gap suggest?
How does an index affect INSERT and VACUUM?
Why is the index ordered as (tenant_id, created_at)?