SQL & PostgreSQL

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '2 years';
ROLLBACK;

SELECT *
FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
  WHERE tenant_id = $1
) t
WHERE rn = 1;
-- Postgres-only alternative:
SELECT DISTINCT ON (customer_id) * FROM orders
WHERE tenant_id = $1 ORDER BY customer_id, created_at DESC;

CREATE INDEX orders_customer_cover ON orders (tenant_id, customer_id) INCLUDE (total, status);
EXPLAIN (ANALYZE) SELECT total, status FROM orders WHERE tenant_id = 7 AND customer_id = 42;
-- Index Only Scan using orders_customer_cover ... Heap Fetches: 0

CREATE UNIQUE INDEX users_email_lower ON users (tenant_id, lower(email));
SELECT * FROM users WHERE tenant_id = $1 AND lower(email) = lower($2); -- dùng index
SELECT * FROM users WHERE tenant_id = $1 AND email ILIKE $2;          -- không dùng

-- Chống 2 ca làm việc trùng giờ cho cùng nhân viên (cần extension btree_gist)
ALTER TABLE shifts ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (employee_id WITH =, tstzrange(starts_at, ends_at) WITH &&);

Nested Loop  (cost=0.85..1250.40 rows=120 width=64) (actual time=0.05..3.20 rows=118 loops=1)
  Buffers: shared hit=480
  ->  Index Scan using customers_tenant_email on customers c  (rows=1) (actual rows=1 loops=1)
        Index Cond: ((tenant_id = 7) AND (email = 'a@b.com'))
  ->  Bitmap Heap Scan on orders o  (rows=120) (actual rows=118 loops=1)
        Recheck Cond: (customer_id = c.id)
        ->  Bitmap Index Scan on orders_customer_id  (actual rows=118 loops=1)
              Index Cond: (customer_id = c.id)

const r = await db.query(
  `UPDATE addresses SET line1 = $1, version = version + 1
   WHERE id = $2 AND tenant_id = $3 AND version = $4`,
  [line1, id, tenantId, expectedVersion],
);
if (r.rowCount === 0) throw new ConflictError('Address was modified by someone else');

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs j SET status = 'running', locked_at = now(), attempts = attempts + 1
FROM next WHERE j.id = next.id
RETURNING j.*;

CREATE TABLE events (
  tenant_id bigint NOT NULL, id bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL, payload jsonb,
  PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
SELECT id FROM products WHERE tenant_id = $1 AND attrs @> '{"brand":"acme","size":"M"}';

SELECT (date '2026-10-05' + time '09:00') AT TIME ZONE 'Australia/Sydney' AS starts_at_utc;
-- Ngày DST bắt đầu ở Sydney (2026-10-04) làm 02:00-03:00 không tồn tại.

-- index: CREATE INDEX orders_created_at ON orders (created_at);
SELECT id, total FROM orders
WHERE date(created_at) = current_date
  AND tenant_id::text = $1          -- $1 bound as text by the driver
ORDER BY created_at DESC;

Nested Loop  (cost=1.13..16.20 rows=1 width=40) (actual time=0.09..40211.55 rows=480112 loops=1)
  ->  Index Scan using products_tenant_category on products p  (rows=1) (actual rows=480112 loops=1)
        Index Cond: ((tenant_id = 12) AND (category_id = 900))
  ->  Index Scan using inventory_pkey on inventory i  (rows=1) (actual rows=1 loops=480112)

-- migration 2026_09_01_add_gift_note.sql
ALTER TABLE orders ADD COLUMN gift_note text;

-- at the same time, pg_stat_activity shows:
-- pid 811  state: idle in transaction  xact_start: 00:12 ago  query: SELECT * FROM orders WHERE ...

async function reserve(tx: Tx, items: { productId: number; qty: number }[]) {
  for (const it of items) {                 // cart order as the user added items
    await tx.query(
      `UPDATE inventory SET reserved = reserved + $1
       WHERE product_id = $2 AND available - reserved >= $1`,
      [it.qty, it.productId],
    );
  }
}
// Request A: items [101, 202]   Request B: items [202, 101]

SELECT id, name, price, updated_at FROM products
WHERE tenant_id = $1
ORDER BY updated_at DESC
LIMIT 50 OFFSET $2;   -- $2 = page * 50

// per request
await client.query(`SET search_path TO tenant_${tenantId}, public`);
await client.query({ name: 's_12', text: 'SELECT * FROM orders WHERE id = $1', values: [id] });

const orders = await orderRepo.find({ where: { tenantId }, take: 50 });
for (const o of orders) {
  o.customer = await customerRepo.findOneBy({ id: o.customerId });
  o.items = await itemRepo.findBy({ orderId: o.id });
}

SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_currency_nn CHECK (currency IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

-- lặp từ app/script với :last_id tăng dần
UPDATE orders SET currency_code = legacy_currency
WHERE id > :last_id AND id <= :last_id + 5000 AND currency_code IS NULL;

-- T1 và T2 chạy song song ở REPEATABLE READ
SELECT count(*) FROM on_call WHERE shift_id = 9 AND active; -- cả hai thấy 2
UPDATE on_call SET active = false WHERE shift_id = 9 AND doctor_id = :me;
COMMIT; -- cả hai thành công → 0 active