El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Tot el curs ha executat SQL des de psql: escrius, prems Retorn, llegeixes el resultat. En una aplicació real no passa res d'això. Hi ha un procés web que atén centenars de peticions per segon, cadascuna amb uns mil·lisegons de pressupost, que no es pot permetre obrir una connexió, que executa les consultes des de codi en un altre llenguatge, que moltes vegades ni tan sols les escriu —les genera un ORM— i en què un error de disseny no es manifesta com un missatge sinó com "el web va lent des d'ahir".

Aquesta lliçó és aquest entorn: com es connecta una aplicació, com executa i com gestiona les seves transaccions, què aporta i què treu un ORM, els quatre antipatrons que maten un web —començant pel N+1 que 08-04 va deixar pendent—, els patrons que sí que funcionen i la llista de comprovació per quan alguna cosa va malament. Amb ella es tanca el mòdul i quedes a punt per al projecte final.

Contingut

La connexió i el pool
Executar SQL des del codi
ORM enfront de SQL a mà
Els antipatrons que maten un web
Patrons útils
Operació: desplegament, monitoratge i timeouts
"El web va lent": llista de comprovació
Errors habituals i consells
Exercicis
Conclusió del mòdul

La connexió i el pool

Una connexió a PostgreSQL comença amb una cadena de connexió, que en format URI és la forma canònica:

postgresql://web_prod:[email protected]:5432/botigaverda?sslmode=verify-full&application_name=bv-web&connect_timeout=5

Quatre coses que cal mirar sempre: l'usuari —web_prod, no el superusuari ni el propietari (11-03)—; el sslmode, que ha de ser almenys require i preferiblement verify-full, perquè prefer accepta silenciosament una connexió sense xifrar; l'application_name, que apareixerà a pg_stat_activity i et dirà quin servei està llançant la consulta que ofega la base; i que la clau no és mai al repositori, sinó en una variable d'entorn o en un gestor de secrets.

Per què cal un pool

Obrir una connexió a PostgreSQL és car. No és un sòcol i prou: el servidor crea un procés del sistema operatiu per connexió, negocia TLS, autentica i inicialitza la seva memòria. Són de l'ordre de desenes de mil·lisegons, enfront dels 0,2 ms que triga la consulta que anaves a llançar. Si obres i tanques una connexió per petició, el 99 % del temps se'n va en la connexió.

I no s'arregla obrint-ne moltes i deixant-les obertes: cada connexió ociosa consumeix memòria i max_connections (per omissió 100) té un límit que, superat, fa fallar les peticions. La solució és un pool: un conjunt petit de connexions ja obertes que les peticions prenen en préstec i tornen.

flowchart LR
    P1["petició 1"] --> POOL
    P2["petició 2"] --> POOL
    P3["petició 3"] --> POOL
    PN["petició N"] --> POOL
    POOL["<b>pool</b><br/>10 connexions obertes<br/>préstec i devolució"] --> DB[("PostgreSQL<br/>10 processos")]

La mida sorprèn tothom: és petita. Una regla de partida molt utilitzada és nuclis × 2 + fusos de disc, que per a una màquina normal dona entre 10 i 20 connexions, no dues-centes. El motiu és que la base de dades no va més ràpida per rebre més peticions alhora: si té 8 nuclis, 200 consultes simultànies no s'executen en paral·lel, es barallen. Un pool petit encua a l'aplicació, que és on es pot esperar ordenadament, en lloc de saturar el servidor.

Paràmetre del pool	Què controla	Valor de partida
Mida màxima	Connexions simultànies al servidor	10-20 per instància d'aplicació
Temps d'espera per obtenir connexió	Quant espera una petició abans de fallar	2-5 s (millor fallar ràpid que penjar-se)
Vida màxima d'una connexió	Reciclatge periòdic	30 min: evita fuites i facilita la fallada controlada
Temps ociós màxim	Quan es tanquen les sobrants	10 min

⚠️ Compte amb multiplicar. El límit que importa és el total: 8 instàncies de l'aplicació amb un pool de 20 són 160 connexions, més les del procés de tasques, més les dels informes. Aquest número ha de cabre a max_connections, amb marge per a les connexions d'administració.

PgBouncer

Quan hi ha moltes instàncies, o quan la plataforma crea processos per petició (el model clàssic de PHP), es posa un pooler extern: PgBouncer se situa entre l'aplicació i PostgreSQL i multiplexa centenars de connexions de client sobre unes poques de reals.

Mode	Quan retorna la connexió al pool	Ús
`session`	En desconnectar-se el client	Compatible amb tot; multiplexa poc
`transaction`	En acabar cada transacció	L'habitual: gran multiplexació
`statement`	En acabar cada sentència	Molt agressiu; prohibeix les transaccions de diverses sentències

El mode transaction té lletra petita i cal conèixer-la: com que la connexió canvia entre transaccions, deixa de funcionar tot el que viu a la sessió — sentències preparades amb nom, SET de sessió (fes servir SET LOCAL), taules temporals, LISTEN/NOTIFY i els advisory locks de sessió de 09-05. Si el teu ORM fa servir preparades amb nom, cal desactivar-les o fer servir una versió de PgBouncer que les suporti.

Executar SQL des del codi

Tres peces, amb el que s'ha après a 11-03 i al mòdul 9:

# 1. Consulta parametritzada: el llistat de darreres comandes, en UNA consulta
SQL_ULTIMES = """
SELECT co.id, co.data_comanda, c.nom || ' ' || c.cognoms AS client, co.estat,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total,
       COUNT(lc.id) AS linies
FROM   comandes AS co
JOIN   clients  AS c  ON c.id = co.client_id
LEFT   JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP  BY co.id, co.data_comanda, c.nom, c.cognoms, co.estat
ORDER  BY co.data_comanda DESC, co.id DESC
LIMIT  %(limit)s"""

with pool.connection() as conn, conn.cursor() as cur:
    cur.execute(SQL_ULTIMES, {"limit": 5})
    files = cur.fetchall()

id	data_comanda	client	estat	total	linies
20	2026-02-21	Camille Dubois	pendent	22.60	2
19	2026-02-09	Pau Llorens Vidal	pagat	26.73	1
18	2026-01-27	Ana Belmonte Roca	pagat	28.10	2
17	2026-01-13	Sofia Moreira Costa	enviat	47.00	2
16	2025-12-19	Javier Ortega Ruiz	enviat	31.18	2

Una consulta, cinc files, tot el que la pantalla necessita: el nom del client, l'estat, el total i el nombre de línies. Desa aquesta consulta, perquè l'apartat 4 la compararà amb la versió que llança una consulta més per cada comanda de la llista.

`RETURNING`: recuperar l'id sense una segona consulta

En inserir necessites l'id generat. La forma correcta és RETURNING (05-02), no un SELECT MAX(id) posterior —que a més és incorrecte amb concurrència—:

cur.execute("""INSERT INTO comandes (client_id, empleat_id, data_comanda, estat, metode_pagament, despeses_enviament)
               VALUES (%s, %s, CURRENT_DATE, 'pendent', %s, %s)
               RETURNING id, data_comanda""",
            (client_id, None, metode_pagament, ports))
comanda_id, data = cur.fetchone()

La transacció des de l'aplicació

El patró de 09-03, escrit en codi. Tot el que ha de passar junt va dins del mateix bloc:

conn = pool.getconn()
try:
    with conn:                                  # obre transacció; commit en sortir sense error
        with conn.cursor() as cur:
            cur.execute(SQL_INSERT_COMANDA, (...))
            comanda_id = cur.fetchone()[0]
            cur.executemany(SQL_INSERT_LINIA, linies)      # diverses files, un viatge
            cur.execute(SQL_DESCOMPTAR_STOCK, (...))
except UniqueViolation:                          # errors esperats: missatge a l'usuari
    raise ErrorDeNegoci("Aquesta comanda ja existeix")
except Exception:
    conn.rollback(); raise                       # inesperats: desfer i propagar
finally:
    pool.putconn(conn)                           # ⬅️ tornar SEMPRE la connexió al pool

Quatre regles que s'aprenen a base d'incidents: rollback en tot error, perquè una transacció oberta manté bloquejos i bloqueja el VACUUM (09-02); tornar la connexió al finally, o el pool s'esgota i l'aplicació es congela sencera; mantenir la transacció tan curta com sigui possible (apartat 4); i distingir l'error esperat de l'inesperat — una violació d'UNIQUE és un missatge a l'usuari, no un error 500.

ORM enfront de SQL a mà

Un ORM (Object-Relational Mapper) tradueix entre les taules i els objectes del llenguatge: Comanda.objects.filter(estat="pagat") es converteix en un SELECT.

Aporta	Treu
Mapatge automàtic fila ↔ objecte, sense codi repetitiu	Control del SQL generat: no saps què s'executa fins que ho mires
Migracions integrades amb el model (05-06)	Previsibilitat del rendiment: un canvi innocent dispara un N+1
Seguretat per defecte: parametritza sempre (11-03)	Consultes analítiques: finestres, CTE i `FILTER` s'expressen malament o no s'expressen
Productivitat al CRUD, que és el 80 % del codi	Una capa més per aprendre, depurar i actualitzar
Portabilitat entre motors i memòria cau d'identitat	La il·lusió que no cal saber SQL

ORM	Entorn	Nota
SQLAlchemy	Python	Dues capes: Core (SQL expressiu) i ORM. La més potent per escapar de l'ORM sense sortir-ne
Django ORM	Python	Molt productiu i integrat; `select_related` / `prefetch_related` per al N+1
Prisma	Node/TypeScript	Esquema declaratiu i tipus generats; `$queryRaw` per a SQL a mà
Hibernate / JPA	Java	El més veterà; `JOIN FETCH` contra el N+1 i càrrega mandrosa per omissió
Eloquent	PHP/Laravel	Molt llegible; `with()` per a càrrega anticipada

El criteri del curs: ORM per al CRUD, SQL a mà per als informes i les consultes crítiques. No és un terme mitjà covard: és que les dues coses són problemes diferents. "Desar una comanda i les seves línies" és exactament el que un ORM fa bé. "La facturació mensual amb acumulat, mitjana mòbil i variació" és exactament el que fa malament.

I aquest és l'exemple concret, la consulta de l'apartat 2: un JOIN a dues taules, un LEFT JOIN que no ha de perdre comandes, un GROUP BY, una expressió d'import amb descompte i un ORDER BY amb desempat. Cap ORM no l'escriu bé sense ajuda: o fa tres consultes, o porta objectes complets per comptar-ne les línies en memòria, o genera un GROUP BY amb totes les columnes del model. Escriu-la a mà, desa-la en un fitxer .sql del repositori (11-02) i executa-la amb el connector de l'ORM — tots permeten fer-ho. I quan ho facis, continua parametritzant: el raw()/text()/$queryRaw és exactament on torna la injecció SQL (11-03).

Els antipatrons que maten un web

4.1. N+1 — tanca 08-04


Símptoma	La pantalla triga segons; el registre mostra desenes o centenars de consultes per petició, totes rapidíssimes; no apareix al rànquing de consultes lentes
Causa	Es consulta la llista, i després una consulta per element per portar una dada relacionada. Gairebé sempre ho genera un ORM en accedir a una propietat dins d'un bucle
Arranjament	Un `JOIN`, o la càrrega anticipada de l'ORM

# ⚠️ INCORRECTA: 1 + 20 = 21 consultes
comandes = Comanda.objects.all()[:20]            # 1 consulta
for c in comandes:
    print(c.client.nom)                          # ⬅️ 1 consulta per volta, invisible al codi

# ✅ CORRECTA: 1 consulta
comandes = Comanda.objects.select_related("client").all()[:20]

21 consultes enfront d'1 per pintar la mateixa taula. I el que fa greu el N+1 és com escala, perquè el cost no és el temps de cada consulta sinó el cost fix que es paga a cadascuna: viatge de xarxa, anàlisi, planificació:

Elements	Latència 1 ms	Latència 20 ms (base en una altra regió)
20 amb N+1	~25 ms	~420 ms
500 amb N+1	~520 ms	~10 s
Qualsevol nombre amb `JOIN`	~2-4 ms	~25 ms

Com detectar-lo: compta les consultes per petició. Un comptador al middleware que registri "aquesta petició ha fet 61 consultes" troba un N+1 en cinc minuts; pg_stat_statements el delata com una consulta amb mean_exec_time mínim i un nombre de calls disbaratat, que és la raó d'ordenar per temps total. Les eines de cada entorn: django-debug-toolbar, Rails Bullet, Laravel Telescope, Hibernate statistics.

4.2. Portar totes les files i paginar en memòria


Símptoma	Consum de memòria disparat; la pantalla va bé en desenvolupament i es mor en producció
Causa	`SELECT * FROM comandes` i després `files[100:120]` al llenguatge. Amb 20 comandes funciona; amb 2 milions, no
Arranjament	`WHERE`, `ORDER BY` i `LIMIT` en SQL. I per a pàgines profundes, keyset en lloc d'`OFFSET` (02-06, 08-04)

-- ⚠️ Pàgina 1000 amb OFFSET: llegeix i descarta 20.000 files
SELECT id, data_comanda FROM comandes ORDER BY data_comanda DESC, id DESC LIMIT 20 OFFSET 20000;

-- ✅ Keyset: es posiciona a l'índex. Cost constant, sigui la pàgina que sigui
SELECT id, data_comanda FROM comandes
WHERE (data_comanda, id) < (:ultima_data, :ultim_id)
ORDER BY data_comanda DESC, id DESC LIMIT 20;

Sobre les 20 comandes del curs, la segona amb (:ultima_data, :ultim_id) = ('2026-01-13', 17) retorna les comandes 16, 15, 14, 13 i 12 amb LIMIT 5: exactament la pàgina següent a la de l'apartat 2, sense rellegir res.

4.3. Transacció oberta esperant alguna cosa lenta


Símptoma	Bloquejos, esperes, `idle in transaction` a `pg_stat_activity`, taules que no es netegen
Causa	`BEGIN` … crida a la passarel·la de pagament (2 s) … `COMMIT`. La transacció reté bloquejos mentre s'espera un tercer
Arranjament	La crida externa, fora de la transacció. Primer la crida, després una transacció curta que registri el resultat

És el de 09-01 portat al món real, i el dany va més enllà d'aquella petició: una transacció llarga impedeix que VACUUM netegi les versions antigues de fila a tota la base (09-02), així que una sola petició lenta degrada totes les altres. Vigila idle in transaction i posa idle_in_transaction_session_timeout.

4.4. Falta d'índexs en el que el web filtra


Símptoma	Tot bé fins que la taula creix; `Seq Scan` al pla (08-05)
Causa	El web filtra i ordena per columnes sense índex, i PostgreSQL no indexa les claus foranes automàticament (08-01)
Arranjament	Índexs sobre les FK (`linies_comanda(comanda_id)`, `comandes(client_id)`) i sobre el que ordena la pantalla (`comandes(data_comanda DESC, id DESC)`)

La manera sistemàtica de trobar-los: llista les pantalles, i per a cadascuna escriu el WHERE i l'ORDER BY que executa. Aquesta llista és la teva llista d'índexs candidats — i només aquesta, perquè cada índex que sobra frena totes les escriptures (08-02).

Patrons útils

Paginació per cursor per a scroll infinit i API. La resposta porta un next_cursor opac (la tupla de l'última fila, codificada) i el client el retorna. Cost constant i sense files repetides o perdudes quan algú insereix mentre l'usuari navega.
Filtres opcionals amb el patró d'11-01, i LIMIT defensiu a tota consulta exposada a l'usuari: encara que la interfície només permeti demanar-ne 50, l'API ha d'imposar un màxim (LEAST(:limit, 100)). Sense ell, algú demanarà ?limit=1000000 el dia que més trànsit tinguis.
Memòria cau per al que canvia poc i es llegeix molt: el catàleg, les categories, la portada. I amb l'avís de sempre: la invalidació és el problema difícil. Comença per caducitat per temps, que és senzilla i previsible, i deixa la invalidació per esdeveniments per quan la necessitis de debò.
Cues de treballs amb SELECT ... FOR UPDATE SKIP LOCKED (09-05). Diversos processos treballadors prenen tasques de la mateixa taula sense trepitjar-se ni esperar-se: cadascun se salta les files ja bloquejades per un altre. És el patró canònic per enviar correus, generar factures o processar imatges sense muntar un sistema de cues a part.

UPDATE treballs SET estat = 'en_curs', agafat_el = now()
WHERE  id = (SELECT id FROM treballs WHERE estat = 'pendent'
             ORDER BY creat_el FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING id, payload;

JSON directament des de PostgreSQL (10-06): jsonb_build_object + jsonb_agg retornen la comanda amb les seves línies imbricades en una fila i una consulta, en lloc de portar files planes i reassemblar-les al llenguatge. Quan compensa: respostes molt imbricades que l'aplicació només reenvia tal qual. Quan no: si l'aplicació ha de recórrer, validar o transformar l'objecte —llavors prefereix files tipades—, si el JSON es torna enorme, o si barrejar-ho amb l'ORM obliga a mantenir dues maneres de llegir el mateix.

Operació: desplegament, monitoratge i timeouts

Migracions compatibles cap enrere

Durant un desplegament conviuen, encara que siguin uns segons, el codi nou i el vell. Si la migració i el codi es despleguen alhora i la migració trenca l'esquema anterior, la versió antiga falla mentre duri el canvi. D'aquí l'expand/contract de 05-06, en tres desplegaments:

Fase	Què es fa	Compatible amb
1. Expand	Afegir la columna nova nul·lable o amb `DEFAULT`; escriure a totes dues	Codi vell i nou
2. Migrar i desplegar	Emplenar les dades per lots; el codi nou llegeix la columna nova	Codi nou
3. Contract	Quan ningú no fa servir la vella: `NOT NULL`, esborrar l'antiga	—

I dos detalls operatius que eviten una caiguda: CREATE INDEX CONCURRENTLY, perquè un CREATE INDEX normal bloqueja les escriptures de la taula mentre es construeix; i lock_timeout curt abans d'un ALTER TABLE, perquè la migració falli en tres segons en lloc d'encuar totes les consultes darrere d'un bloqueig que no aconsegueix (09-05).

Monitoratge i timeouts

SELECT LEFT(query, 60) AS consulta, calls, ROUND(total_exec_time::numeric, 1) AS ms_total,
       ROUND(mean_exec_time::numeric, 2) AS ms_mitjana
FROM   pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Ordena per total_exec_time, no per la mitjana: és l'única cosa que fa visible el N+1, la mitjana del qual és minúscula i el total del qual és enorme. I tres ajustos que haurien d'estar posats des del primer dia:

Ajust	Per a què	Valor de partida
`statement_timeout`	Cap consulta del web no ha de durar minuts	5-15 s al rol de l'aplicació
`idle_in_transaction_session_timeout`	Matar transaccions oblidades obertes	30-60 s
`log_min_duration_statement`	Registrar el que passi d'un llindar	200-1000 ms

Posa'ls per rol: ALTER ROLE web_prod SET statement_timeout = '10s'; deixa que els informes i les migracions, amb altres rols, tinguin els seus propis límits.

"El web va lent": llista de comprovació

En aquest ordre, perquè va del més probable i barat al més estrany i car:

És tot o una pantalla? Si és una, mira'n les consultes; si és tot, sospita del servidor, del pool o d'un bloqueig.
Quantes consultes fa aquella petició? Un comptador. Si en són desenes, és un N+1 i ja has acabat.
S'esgota el pool? Si les peticions esperen a obtenir connexió, el problema no és a la base: és el pool, o transaccions que no es tanquen. Mira idle in transaction.
pg_stat_statements per temps total. Les tres primeres consultes solen explicar el 80 % de la càrrega.
EXPLAIN ANALYZE de la sospitosa (08-05). Hi ha Seq Scan on hi hauria d'haver índex? Estimacions llunyanes de la realitat?
Hi ha bloquejos? pg_locks amb pg_stat_activity: una consulta en Lock no és lenta, està esperant (09-05).
Han canviat les estadístiques o el volum? Hi va haver càrrega massiva sense ANALYZE (08-04)?
Es porten files de més? Una consulta ràpida que retorna 200.000 files satura la xarxa i la memòria de l'aplicació.
I només llavors, el servidor: CPU, memòria, disc, VACUUM pendent, nombre de connexions.

La regla és la de 08-04 aplicada al web: mesura abans de tocar. I compta consultes, no només mil·lisegons, perquè el problema més comú de tots no apareix en cap rànquing de consultes lentes.

Errors habituals i consells

Obrir una connexió per petició. El cost de connexió supera de molt el de la consulta. Fes servir un pool.
Configurar el pool amb 200 connexions "per si de cas". La base no va més ràpida per rebre'n més alhora; el pool petit encua on es pot esperar. I recorda multiplicar pel nombre d'instàncies.
No tornar la connexió al pool. Un return enmig d'un try sense finally esgota el pool i congela l'aplicació sencera.
Deixar una transacció oberta durant una crida externa. Bloquejos, idle in transaction i un VACUUM que no pot treballar.
Confiar que l'ORM farà el correcte. Mira el SQL que genera: gairebé tots tenen un mode de registre. El que no mires, no ho saps. I fer servir raw() concatenant: aquí torna la injecció (11-03).
Paginar amb OFFSET en una API. Lent en pàgines profundes i amb files repetides o perdudes si algú insereix.
No posar LIMIT en una consulta exposada a l'usuari. Algú demanarà un milió de files.
Desplegar migració i codi alhora amb un canvi trencador. Expand/contract, i CREATE INDEX CONCURRENTLY.
Consell: registra el nombre de consultes i el temps de base de dades de cada petició. Amb aquestes dues mètriques, els problemes d'aquest apartat es veuen abans que els vegi un usuari.
Consell: posa application_name a la cadena de connexió. Quan la base pateixi, sabràs quin servei l'està matxucant en lloc d'endevinar-ho.
Consell: desa les consultes complexes en fitxers .sql del repositori, no incrustades entre línies de codi. Es revisen millor, es proven a psql i es poden formatar (11-02).

Exercicis

Exercici 1

La fitxa de client mostra les seves dades, les seves comandes i, per cada comanda, les seves línies amb el nom del producte i la seva categoria. El registre diu 23 consultes per càrrega a la fitxa de la Lucía.

D'on surten exactament les 23? (Pista: la Lucía té 3 comandes, de 3 línies cadascuna.)
Redueix la fitxa a dues consultes i escriu-les.
Per què dues i no una?

Exercici 2

La teva aplicació corre en 6 instàncies, cadascuna amb un pool de 25 connexions. Hi ha a més un procés de tasques en segon pla amb 10 i un servidor d'informes amb 5. max_connections està a 100.

Quin és el problema i quin error veuran els usuaris?
Dona tres solucions, de la més barata a la més cara.
Si tries PgBouncer en mode transaction, què has de revisar al codi?

Exercici 3

Una API retorna el detall d'una comanda amb el seu client i les seves línies. L'equip debat entre (a) tres consultes i reassemblar al codi, (b) una consulta amb JOIN i reassemblar, (c) una consulta que retorna el JSON ja muntat (10-06).

Escriu l'opció (c) per a la comanda 1 i digues què retorna.
Dona un argument a favor i un en contra de cada opció.
Quina triaries si l'API ha de retornar 50 comandes a la mateixa resposta?

Solucions

Solució 1

1. 1 per al client + 1 per a la seva llista de comandes + 3 (una per comanda, per portar-ne les línies) + 9 (una per línia, per al nom del producte) + 9 (una per línia, per a la categoria del producte) = 23. És un N+1 imbricat: cada nivell de la plantilla multiplica l'anterior, i per això el número creix tan de pressa. Amb un client de 20 comandes de 5 línies serien 1 + 1 + 20 + 100 + 100 = 222 consultes per a una sola pantalla.

2. Dues consultes: una per a la capçalera de les comandes i una altra per a totes les seves línies d'un cop, amb el producte ja unit.

-- (a) Client i les seves comandes amb totals, en una consulta
SELECT co.id, co.data_comanda, co.estat, COUNT(lc.id) AS linies,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total
FROM   comandes AS co LEFT JOIN linies_comanda AS lc ON lc.comanda_id = co.id
WHERE  co.client_id = %(client_id)s
GROUP  BY co.id, co.data_comanda, co.estat ORDER BY co.data_comanda DESC;

-- (b) TOTES les línies de TOTES aquelles comandes, d'un cop
SELECT lc.comanda_id, p.nom AS producte, lc.quantitat,
       ROUND(lc.quantitat * lc.preu_unitari * (1 - lc.descompte), 2) AS import
FROM   linies_comanda AS lc JOIN productes AS p ON p.id = lc.producte_id
WHERE  lc.comanda_id = ANY(%(ids)s)
ORDER  BY lc.comanda_id, lc.id;

Per a la Lucía (client 1), la primera retorna 3 files —les comandes 1, 5 i 15, amb 42,10 €, 32,10 € i 33,40 €, que sumen els seus 107,60 € canònics— i la segona, 9 files. Fixa't en = ANY(%(ids)s) amb un array: és la manera correcta de passar una llista d'identificadors sense construir el SQL concatenant (11-03).

3. Perquè capçalera i detall tenen cardinalitats diferents. Amb una sola consulta, el total de la comanda es repetiria a cadascuna de les seves línies i caldria desduplicar-lo al codi, o caldria agregar les línies a JSON. Dues consultes, cadascuna amb la granularitat del que retorna, són més clares i més eficients que una que multiplica files. La regla de la "càrrega anticipada" de tots els ORM és exactament aquesta: una consulta per nivell, no una per element.

Solució 2

1. 6 × 25 + 10 + 5 = 165 connexions possibles contra un max_connections de 100. Tan bon punt hi hagi càrrega, PostgreSQL rebutjarà les noves amb FATAL: sorry, too many clients already, i els usuaris veuran errors 500 intermitents — intermitents i per tant difícils de diagnosticar, perquè només apareixen als pics. Pitjor encara, pot impedir fins i tot connectar-se per administrar si no queden buits reservats (superuser_reserved_connections).

2. (a) Baixar la mida del pool a 10 per instància: 6 × 10 + 10 + 5 = 75, amb marge. És gratis, immediat i probablement no empitjorarà el rendiment, perquè 165 consultes simultànies no caben als nuclis de la màquina de tota manera. (b) Posar PgBouncer en mode transaction: centenars de connexions de client sobre 20 de reals. (c) Pujar max_connections i la memòria del servidor: és la més cara, exigeix reinici i només trasllada el problema, perquè cada connexió és un procés amb la seva memòria.

3. Amb transaction la connexió canvia entre transaccions, així que cal revisar: sentències preparades amb nom (molts connectors les fan servir per omissió: desactivar-les o fer servir la versió de PgBouncer que les suporta), SET de sessió (canviar a SET LOCAL dins de la transacció — inclòs el SET app.usuari de l'auditoria d'11-01 i el de RLS d'11-03), taules temporals, LISTEN/NOTIFY i advisory locks de sessió (09-05). Tot el que depengui de "continuar a la mateixa sessió" deixa de funcionar.

Solució 3

1. És la consulta de 10-06: jsonb_build_object amb l'id, la data i els ports, el client imbricat en un altre objecte i les línies en un jsonb_agg correlacionat. Retorna una fila i una columna amb la comanda 1 completa: Lucía Martínez Soler, Espanya, i les seves tres línies —oli 23,90 €, arròs 11,70 € i infusió 6,50 €— que sumen els 42,10 € de la comanda.

2. (a) Tres consultes: a favor, és la més simple i cada consulta és trivial de cachejar i indexar; en contra, són tres viatges de xarxa. (b) JOIN i reassemblar: a favor, un sol viatge i dades tipades que l'aplicació pot transformar; en contra, repeteix la capçalera a cada línia i obliga a agrupar al codi, que és feina manual i propensa a errors. (c) JSON des de PostgreSQL: a favor, un viatge i zero codi de reassemblatge; en contra, l'aplicació rep un text opac que no pot transformar sense analitzar-lo, es perd el tipatge i s'acobla la forma de la resposta de l'API al SQL — canviar un camp del contracte obliga a tocar la consulta.

3. Amb 50 comandes, (c), i per un motiu concret: és l'única que no creix en nombre de viatges ni obliga a agrupar 50 capçaleres amb les seves ~120 línies al codi. La (a) es convertiria en un N+1 si es fa per comanda —caldria reescriure-la com dues consultes amb = ANY(...), que és la solució de l'exercici 1—, i la (b) retornaria 120 files amb la capçalera repetida. La condició perquè (c) sigui bona idea continua sent la mateixa: que l'API reenviï el JSON tal qual. Si l'ha de tocar, torna la (a) en la seva versió de dues consultes.

Conclusió del mòdul

Així arriba el SQL a una aplicació real:

La connexió porta usuari de privilegi mínim, TLS (verify-full, no prefer) i application_name, i la clau mai al repositori. I va sempre per un pool, perquè obrir una connexió costa desenes de mil·lisegons i cadascuna és un procés al servidor. La mida és petita —10-20 per instància— i cal multiplicar-la pel nombre d'instàncies. PgBouncer en mode transaction multiplexa centenars de clients sobre poques connexions reals, a canvi de perdre tot el que viu a la sessió.
Des del codi: consultes parametritzades (11-03), RETURNING per a l'id (05-02) i el bloc try / commit / except rollback / finally putconn (09-03), amb la transacció tan curta com sigui possible i la connexió tornada sempre.
ORM enfront de SQL: l'ORM dona mapatge, migracions, seguretat per defecte i productivitat; treu control del SQL i previsibilitat del rendiment. El criteri: ORM per al CRUD, SQL a mà per als informes i les consultes crítiques — com el llistat de darreres comandes, que cap ORM no escriu bé.
Els antipatrons: el N+1, que converteix 1 consulta en 21 o en 501 i no apareix en cap rànquing de consultes lentes —tanca la promesa de 08-04—; portar-ho tot i paginar en memòria, que s'arregla amb LIMIT i amb keyset; la transacció oberta esperant una API externa, que reté bloquejos i frena el VACUUM de tota la base; i la falta d'índexs a les columnes per les quals filtra el web, començant per les claus foranes.
Els patrons: paginació per cursor, filtres opcionals, LIMIT defensiu a tota consulta exposada, memòria cau amb caducitat per temps, cues amb SKIP LOCKED i JSON directe des de PostgreSQL quan l'API només reenvia.
L'operació: migracions compatibles cap enrere en expand/contract, CREATE INDEX CONCURRENTLY, lock_timeout abans d'un ALTER, pg_stat_statements ordenat per temps total, i statement_timeout i idle_in_transaction_session_timeout per rol. Més la llista de nou punts per quan "el web va lent", que comença per comptar consultes.

I amb això es tanca el mòdul 11 i, amb ell, l'aprenentatge. En cinc lliçons has passat de conèixer el llenguatge a conèixer l'ofici: els casos d'ús que reapareixen a tots els projectes i quina eina resol cadascun; les bones pràctiques de nomenclatura, format, disseny, fiabilitat i procés, amb el seu catàleg d'antipatrons i el seu checklist; la seguretat, amb la injecció SQL i la seva única defensa real, el model de rols i permisos de PostgreSQL i el tracte de les dades personals; l'anàlisi de dades, on les funcions de finestra es van convertir en informes i on vas aprendre que definir i validar importa més que consultar; i el desenvolupament web, on el SQL s'executa de debò.

Queda una cosa, i és l'única que no s'aprèn llegint: fer-ho tu. Al mòdul 12, Projecte final, muntaràs un sistema complet de principi a fi: la descripció del projecte i el seu context de negoci, els requisits que ha de complir, la implementació pas a pas —model, DDL, dades, consultes, índexs, vistes, seguretat i rendiment—, les solucions comentades amb les decisions justificades una a una, i la presentació dels resultats. Tot el de dotze mòduls, junt i en un sol treball. És el moment de deixar de seguir un curs i començar a construir.

SQL en el desenvolupament web

Contingut

La connexió i el pool

Per què cal un pool

PgBouncer

Executar SQL des del codi

`RETURNING`: recuperar l'id sense una segona consulta

La transacció des de l'aplicació

ORM enfront de SQL a mà

Els antipatrons que maten un web

4.1. N+1 — tanca 08-04

4.2. Portar totes les files i paginar en memòria

4.3. Transacció oberta esperant alguna cosa lenta

4.4. Falta d'índexs en el que el web filtra

Patrons útils

Operació: desplegament, monitoratge i timeouts

Migracions compatibles cap enrere

Monitoratge i timeouts

"El web va lent": llista de comprovació

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió del mòdul

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

SQL en el desenvolupament web

Contingut

La connexió i el pool

Per què cal un pool

PgBouncer

Executar SQL des del codi

RETURNING: recuperar l'id sense una segona consulta

La transacció des de l'aplicació

ORM enfront de SQL a mà

Els antipatrons que maten un web

4.1. N+1 — tanca 08-04

4.2. Portar totes les files i paginar en memòria

4.3. Transacció oberta esperant alguna cosa lenta

4.4. Falta d'índexs en el que el web filtra

Patrons útils

Operació: desplegament, monitoratge i timeouts

Migracions compatibles cap enrere

Monitoratge i timeouts

"El web va lent": llista de comprovació

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió del mòdul

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

`RETURNING`: recuperar l'id sense una segona consulta