Postgres Performance pentru SaaS: cand indexezi, cand cachezi

Un query lent intr-un SaaS nu este doar frustrant. Iti umfla factura de infrastructura, iti consuma ore de inginerie si, cel mai costisitor, scade conversia si retention-ul. A decide corect intre a adauga un index sau a introduce un layer de cache face diferenta intre o platforma fluida si una care se taraie.

Daca vrei raspunsul scurt: indexeaza cand interogarea are filtrare selectiva si se repeta pe date care se schimba relativ frecvent; cacheaza cand calculul este scump, datele se schimba rar in raport cu citirile si poti controla invalidarea. De multe ori, faci ambele: index pentru calea critica si cache pentru agregari sau rezultate populare per tenant.

Cand sa indexezi vs cand sa cachezi

Decizia tine de trei axe: selectivitatea filtrului, frecventa scrierilor si costul recomputarii.

• Indexeaza cand:

  • Query-ul filtreaza pe coloane cu cardinalitate mare (ex: tenant_id, user_id, email, order_id) si returneaza sub 1–5% din tabel.
  • Sortarea este stabila si corelata cu filtrul (ex: ORDER BY updated_at DESC pe un subset de un tenant).
  • Datele se actualizeaza suficient de des incat cache-ul ar expira frecvent, iar inconsistentele ar fi vizibile.
  • Ai nevoie de rezultate corecte in timp real (ex: verificari de permisiuni, lookup-uri critice in flow-ul de plata cu Stripe).

• Cacheaza cand:

  • Rezultatul este agregat/derivat si scump (rapoarte, top N, dashboard-uri), dar citirile depasesc clar scrierile pentru acel set.
  • Setul este comun si repetitiv (ex: „ultimele 10 produse populare pe tenant”, „feature flags rezolvate pentru o versiune”).
  • Poti invalida deteminist (event-driven) sau poti tolera un TTL scurt controlat.

• Combina cand:

  • Query-ul de baza e optimizat prin index, iar deasupra pui cache pentru a taia varfurile de trafic si a proteja Postgres de acces repetitiv.

O regula practica: daca acelasi query (aceleasi parametre semnificative) apare in top 10 din pg_stat_statements si datele din spate nu se schimba pe fiecare request, primul pas este cache-aside cu invalidare. Daca variatiile parametrilor sunt mari si rezultatul returneaza putine randuri, investeste in indexul corect (compozit, partial, expression) inainte de cache.

Un index prost ales e ca un abonament la sala uitat: platesti lunar si nu folosesti.

Modele de query in SaaS si indexele potrivite

Liste paginate per tenant

Caz: SELECT * FROM invoices WHERE tenant_id = $1 ORDER BY updated_at DESC LIMIT 50 OFFSET 0;

• Index recomandat: compozit pe (tenant_id, updated_at DESC), optional INCLUDE (total, currency) pentru a acoperi coloanele citite si a evita heap lookups.

-- Postgres 11+: INCLUDE pentru covering index
CREATE INDEX CONCURRENTLY idx_invoices_tenant_updated
ON invoices (tenant_id, updated_at DESC)
INCLUDE (total, currency);

• Pitfall: daca folosesti OFFSET mare, performance degradeaza. Treci la keyset pagination: pastrezi ultima valoare de updated_at si id ca tie-breaker.

-- Keyset pagination
SELECT id, total, currency, updated_at
FROM invoices
WHERE tenant_id = $1 AND (updated_at, id) < ($2, $3)
ORDER BY updated_at DESC, id DESC
LIMIT 50;

Lookup unic pe email sau id case-insensitive

• Index expression:

CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users ((lower(email)))
WHERE deleted_at IS NULL;

• Beneficiu: evita secvential scan cand folosesti WHERE lower(email) = $1.

Cautare text partiala

• Daca ai ILIKE '%foo%', foloseste pg_trgm cu GIN:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
ON products USING GIN (name gin_trgm_ops)
WHERE tenant_id IS NOT NULL; -- optional partial

• Pentru JSONB, prefera jsonb_path_ops cand faci containere cheie->valoare:

CREATE INDEX CONCURRENTLY idx_events_props_gin
ON events USING GIN (properties jsonb_path_ops);

Evenimente/telemetrie masiva

• Volum mare, query-uri pe ferestre de timp. Foloseste BRIN pe created_at si partitionare pe timp.

CREATE INDEX CONCURRENTLY idx_events_created_brin
ON events USING BRIN (created_at);

-- Partitionare pe luna
CREATE TABLE events (
  id bigserial primary key,
  created_at timestamptz not null,
  tenant_id bigint not null,
  properties jsonb not null
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Rapoarte agregate

• Daca raportul se cere frecvent si se actualizeaza rar, fie cache cu Redis, fie materialized view cu refresh incremental.

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT tenant_id,
       date_trunc('month', paid_at) AS month,
       sum(total) AS revenue
FROM invoices
WHERE status = 'paid'
GROUP BY tenant_id, date_trunc('month', paid_at);

-- Indexe pentru citire rapida
CREATE INDEX CONCURRENTLY idx_mv_rev_tenant_month
ON mv_monthly_revenue (tenant_id, month);

-- Refresh fara lock pe citire
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Arhitectura recomandata: Postgres + cache in SaaS

Un flux clasic pe care il vedem in productie:

• Edge: Cloudflare pentru caching HTTP unde are sens (public assets) si pentru rate limiting de baza la API.
• API: Node.js (NestJS/Express/Next API Routes) cu pg sau Prisma; connection pooling prin PgBouncer (transaction pooling) pentru a limita conexiunile.
• Baza: Postgres 14–16 (RDS/Aurora/CloudSQL sau bare metal) cu un primar si cel putin un read-replica pentru rapoarte.
• Cache: Redis (ElastiCache/Upstash) pentru cache-aside si rate limiting; optional Redis Cluster la >50k RPS cache.
• Workers: BullMQ/Sidekiq pentru joburi de refresh (materialized views, precomputari), exporturi.
• Observabilitate: pg_stat_statements, auto-explain, p95 latente pe endpoint-uri, hit ratio pe cache.

Flux de date tipic:

1. Request API -> verifica cache (GET key).
2. Hit? Returneaza si logheaza cache_hit.
3. Miss? Executa query (folosind indexul corect), pune rezultatul in cache cu TTL/versiune, returneaza.
4. La scriere (INSERT/UPDATE/DELETE) -> trimite eveniment de invalidare (LISTEN/NOTIFY sau pub/sub Redis) catre servicii interesate.

Cheia este sa mentii Postgres ca sursa a adevarului, iar cache-ul ca optimizare revocabila. Pentru subiectul multi-tenant, vezi si ghidul nostru despre izolarea pe tenant_id si Prisma: Cum construim multi-tenant SaaS cu Prisma + Postgres.

Implementare cache-aside cu invalidare

// Node.js cu ioredis si pg
import Redis from 'ioredis';
import { Pool } from 'pg';

const redis = new Redis(process.env.REDIS_URL!);
const pg = new Pool({ connectionString: process.env.DATABASE_URL });

const key = (tenantId: number, since?: string) =>
  `invoices:v2:tenant:${tenantId}:since:${since ?? 'null'}`; // v2 = schema version

export async function listInvoices(tenantId: number, since?: string) {
  const k = key(tenantId, since);
  const cached = await redis.get(k);
  if (cached) return JSON.parse(cached);

  const { rows } = await pg.query(
    `SELECT id, total, currency, updated_at
     FROM invoices
     WHERE tenant_id = $1 AND ($2::timestamptz IS NULL OR updated_at < $2)
     ORDER BY updated_at DESC, id DESC
     LIMIT 50`,
    [tenantId, since ?? null]
  );

  // TTL scurt + randomizare pentru a evita thundering herd
  const ttl = 60 + Math.floor(Math.random() * 30);
  await redis.setex(k, ttl, JSON.stringify(rows));
  return rows;
}

// Invalidare la scriere
export async function createInvoice(tenantId: number, payload: any) {
  const client = await pg.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `INSERT INTO invoices (tenant_id, total, currency)
       VALUES ($1,$2,$3) RETURNING id, updated_at`,
      [tenantId, payload.total, payload.currency]
    );
    await client.query('COMMIT');
  } finally {
    client.release();
  }
  // Invalidate coarsely prin versiune sau stergere chei
  const pattern = `invoices:v2:tenant:${tenantId}:*`;
  // foloseste SCAN, NU KEYS in productie
  const stream = redis.scanStream({ match: pattern, count: 100 });
  stream.on('data', (keys: string[]) => keys.length && redis.del(keys));
}

Pentru invalidari mai precise si mai scalabile, foloseste versiuni de set (ex: tenant_cache_version:123 inglobata in cheie) sau pub/sub cu evenimente semantice (invoice_created), iar consumatorii locali isi invalideaza propriile chei.

Triggere Postgres pentru evenimente de invalidare

-- Trimite NOTIFY pe canalul 'cache_invalidate' la schimbari relevante
CREATE OR REPLACE FUNCTION notify_invoice_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('cache_invalidate',
    json_build_object('table','invoices','tenant_id', NEW.tenant_id)::text);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_invoices_notify
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW EXECUTE FUNCTION notify_invoice_change();

Aplicatia asculta LISTEN cache_invalidate si invalideaza doar ce trebuie pe tenant_id.

Ghid practic: cum masori si cum decizi

1. Activeaza pg_stat_statements si colecteaza top 20 query-uri dupa timp total si numar de apeluri. Uita-te la mean time si rows. Cand ai multe apeluri si timp mediu moderat, cache poate reduce presiunea. Cand ai timp mare dar putine apeluri, index/profiling.

2. Ruleaza EXPLAIN (ANALYZE, BUFFERS) pe query-urile lente. Cauta:

   • Seq Scan pe tabele mari -> probabil lipseste indexul potrivit sau filtrul nu e selectiv.
   • Bitmap Heap Scan cu multe rechecks -> poate index nepotrivit sau conditione mixte.
   • Rows Removed by Filter foarte mari -> adauga conditii in index (partial) sau rescrie query-ul.

3. Verifica raportul index scan vs seq scan in pg_stat_user_tables. Pentru tabele OLTP mari, vrei sa vezi preponderent index scan.

4. Masoara cache hit ratio in Postgres (shared buffers). Un hit ratio ridicat nu inlocuieste cache-ul aplicatiei: Postgres buffer cache nu evita CPU si planificare, doar I/O disc. Daca query-ul face agregari costisitoare, aplicatia poate tot beneficia de Redis.

5. Evalueaza rata de schimbare a datelor vs rata de citire. Daca writes per key sunt apropiate de reads per key, cache-ul devine balast.

6. Daca ai multi-tenant, stratifica: ce e per-tenant (bun pentru indexe compozite cu tenant_id) vs ceea ce e global (mai bine materialized view + cache global cu invalidare la batch refresh).

Comparatie: index, cache, materialized view, replica de citire

Ce se strica in productie

• Index bloat si autovacuum insuficient: update-uri frecvente pe tabele mari fara fillfactor si fara autovacuum tuning duc la bloat. Monitorizeaza pg_stat_all_indexes si ruleaza REINDEX CONCURRENTLY cand este necesar.
• Prea multe indexe: fiecare insert/update are cost proportional cu numarul de indexe. Curata indexele nefolosite analizand pg_stat_user_indexes si pg_stat_statements.
• Hot partitions pe tenant_id: daca ai cativa clienti foarte mari, ajungi la lock contention pe acele shard-uri logice. Keyset pagination si partitionare fizica pot ajuta.
• JSONB fara index potrivit: properties @> {...} fara GIN ajunge Seq Scan pe milioane de randuri.
• "N+1" la aplicatie, nu la DB: adaugi indexe si tot ramai lent pentru ca faci 100 de query-uri mici per request. Batch uiri si foloseste JOIN atunci cand e corect.
• PgBouncer in transaction pooling + prepared statements: daca driverul se bazeaza pe prepared statements in sesiune, poti avea erori sau fallback. Configureaza driverul la statement_mode=transaction sau foloseste pe read replicas sesiune cand chiar ai nevoie.
• EXPLAIN fara ANALYZE: e usor sa tragi concluzii gresite din costuri estimate. Ruleaza ANALYZE pentru realitate si asigura-te ca statisticile sunt actuale (ANALYZE automat sau manual dupa bulk load).
• OFFSET mare: paginarea cu OFFSET 100000 e semn ca trebuie rescrisa. Foloseste keyset sau markere.
• Thundering herd la expirare de cache: sparge TTL-urile (jitter), foloseste "soft TTL" si "stale-while-revalidate" in aplicatie.

Cost si ROI: cand merita indexul, cand merita cache-ul

• Cost direct al indexelor: spatiu pe disc (multiplicare cu 2–3x la tabele mari cu mai multe indexe), timp de mentenanta (reindexare, vacuum), degradarea scrierilor. ROI bun cand query-ul este pe calea critica si trafic mare.
• Cost direct al cache-ului: infrastructura Redis (managed sau self-hosted), efort de invalidare si observabilitate, risc de buguri de consistenta. ROI excelent cand raportul citire/scriere depaseste clar 10:1 pe un set determinist.
• Scump vs ieftin pe termen scurt: upgrade la instanta mai mare pare cel mai simplu, dar are plafon rapid si cost recurent. Un index sau o rescriere de query pot salva zeci de procente din CPU. Cache-ul bine plasat poate amana luni de zile nevoia de replica.
• Cand sa cumperi replica: cand ai query-uri de citire grele care nu cer consistenta stricta si poti tolera replication lag de cateva sute de milisecunde. Plaseaza rapoartele si exporturile acolo.
• Cand sa alegi materialized views: cand ai dashboard-uri cerute de majoritatea utilizatorilor la login si update-urile sunt cel mult orare. Ruleaza REFRESH CONCURRENTLY dintr-un worker.

Buget orientativ de efort:

• Indexare corecta pe 4–6 tabele critice: 1–3 zile de inginerie cu masuratori si rollout CONCURRENTLY.
• Introducere cache-aside pe 2–3 endpoint-uri cu invalidare pe evenimente: 2–5 zile, in functie de complexitatea domeniului.
• Materialized view + refresh + alerte: 1–2 zile.

Comparativ, adaugarea haotica de indexe sau cache fara masuratori duce la saptamani de munca de remediere.

Exemple de decizii ghidate de masuratori

• Top query in pg_stat_statements:

-- Extragere rapida a celor mai costisitoare query-uri
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Daca vezi un query de listare per tenant cu mean_time 25ms si calls 100k/zi, un cache de 60–120s reduce instant 70–90% din load, fara sa sacrifici corectitudinea (daca business-ul tolereaza 1–2 minute de intarziere la liste).

• EXPLAIN pe o cautare text: daca apare Seq Scan si conditii ILIKE, adauga pg_trgm GIN si masoara din nou. Daca rows scade dramatic si Buffers: shared hit read scade, ai primit ROI mai bun decat orice cache punctual.

• Dashboard de venituri: daca REFRESH MATERIALIZED VIEW dureaza 500ms si ruleaza la 5 minute, pune un cache de 30s in fata pentru a mlestia varfurile. Lasa citirile catre MV fara cache pentru query-uri mai rare.

Detalii de implementare care conteaza

• CREATE INDEX CONCURRENTLY in productie pentru a evita lock-uri de scriere prelungite. Planifica totusi cresterea I/O in ferestrele off-peak.
• INCLUDE pentru covering indexe pe Postgres 11+: elimina heap lookups si stabilizeaza latenta la 5–10ms pentru liste paginate.
• Partial indexes: WHERE status = 'active' reduce masiv dimensiunea indexului cand interogarile vizeaza un subset constant.
• work_mem si maintenance_work_mem: crescute cu masura pot evita spill pe disk la sortari/bitmap; nu le globaliza nesabuit, seteaza per sesiune pentru joburi mari.
• Partitionare: range pe timp pentru evenimente sau hash pe tenant_id pentru echilibrare. Pune index pe cheile de filtrare in fiecare partitie.
• Limiteaza conexiunile: 50–200 conexiuni reale la Postgres de obicei sunt suficiente cu PgBouncer; prea multe conexiuni cresc context switch-ul si tail latency.
• Evita sa pui totul in Redis: date per-user cu churn mare si consistenta stricta raman responsabilitatea Postgres.

Studii de caz sintetice (modele comune)

• Feature flags per tenant: lookups mici, schimbari rare -> cache local in proces + Redis cu TTL 5–15 minute si invalidare pe evenimente flag_updated.
• Permisiuni RBAC: schimbari medii, consistenta ridicata -> indexe pe user_id, role_id si foloseste cache doar pe short-lived tokens/claims, nu pe rezultate SQL brute.
• Import CSV de 1M randuri: dezactiveaza temporar indexe neesentiale, bulk insert, apoi CREATE INDEX CONCURRENTLY si ANALYZE. Cache-ul nu ajuta aici; pregateste spatiu pentru reindex si I/O burst.

Pentru un cadru mai larg al deciziilor de arhitectura in SaaS, am discutat izolarea, schema si stack-ul web in ghidul nostru multi-tenant.

FAQ

• Cand e prea tarziu sa adaug indexe?
  Nu astepta pana cand latenta p95 sare de 200–300ms pe endpoint-uri critice. Daca vezi Seq Scan pe tabele mari in EXPLAIN, adauga index acum si ruleaza CONCURRENTLY pentru a evita downtime.

• Materialized views inlocuiesc cache-ul?
  Nu. MV iti dau viteza la citire pentru agregari, dar tot pot beneficia de un cache scurt in fata pentru a reduce varfurile. MV au cost de refresh si spatiu.

• Ce TTL sa folosesc in Redis?
  Regula: TTL-ul trebuie sa fie semnificativ mai mic decat frecventa schimbarilor acceptata de business. Daca datele se actualizeaza la cateva minute, un TTL de 30–120s este rezonabil. Adauga jitter pentru a evita expirari simultane.

• Cum invalidez fara sa sterg mii de chei?
  Foloseste versiuni logice (ex: v3 in key) sau un namespace cu tenant_version:123. Cand scrii, doar incrementezi versiunea; cheile vechi expira natural.

• E util un read-replica daca query-ul este prost?
  Nu. Replica doar preia sarcina, dar planul prost ramane prost. Optimizeaza intai schema si indexele, apoi scaleaza citirea.

• Pot indexa JSONB eficient?
  Da, cu GIN si jsonb_path_ops pentru containere cheie->valoare sau trigram pentru cautare aproximativa in text. Evita sa pui totul in JSONB daca ai coloane cu acces frecvent.

Concluzii cheie

• Indexele rezolva latenta determinista pentru filtre selective si sortari; cache-ul rezolva volum de citiri repetate si agregari scumpe.
• Decide pe baza de masuratori: pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), p95 endpoint.
• Combina: index pe calea critica, cache cu invalidare pentru dashboard-uri si rezultate populare.
• Evita capcanele: prea multe indexe, autovacuum subdimensionat, paginare cu OFFSET, prepared statements cu PgBouncer transaction pooling.
• Planifica costul: un index bun economiseste CPU lunar; un cache bine plasat amana upgrade-ul de instanta si replica.

Daca construiesti un SaaS si ai nevoie de decizii clare despre cand sa indexezi si cand sa cachezi, MTBYTE poate proiecta, masura si implementa o cale sigura spre p95 sub 100ms. Scrie-ne la /contact si pornim cu un audit tehnic.