Rendiment de la base de dades

Quan una consulta va lenta, la reacció instintiva és "falta un índex". Moltes vegades és cert, però no sempre: el rendiment d'una base de dades depèn d'una cadena d'elements que va des del disc físic fins al disseny de les taules, passant per la memòria del servidor i l'arquitectura del sistema. Si una sola baula de la cadena és feble, la resta no la pot compensar.

Aquesta pàgina recorre aquesta cadena de baix a dalt. Primer, com es guarda realment la informació al disc i quin maquinari hi ha al darrere. Després, com la memòria (el buffer pool) evita anar al disc. Finalment, les estratègies lògiques (índexs, vistes materialitzades, particionament, rèpliques) i les decisions de disseny que, preses malament el primer dia, es paguen durant tota la vida de la base de dades.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#2563EB', 'primaryTextColor': '#FFFFFF', 'primaryBorderColor': '#1E40AF', 'lineColor': '#64748B', 'secondaryColor': '#16A34A', 'tertiaryColor': '#7C3AED', 'fontSize': '14px'}}}%%
flowchart TB
    A["Disseny de dades<br/>(tipus, claus, normalització)"] --> B["Estratègies lògiques<br/>(índexs, vistes materialitzades,<br/>particionament, rèpliques)"]
    B --> C["Memòria<br/>(buffer pool, caus del SO)"]
    C --> D["Emmagatzematge<br/>(pàgines, discos, RAID, SAN)"]
    style A fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style B fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style C fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style D fill:#1F2937,stroke:#64748B,color:#FFFFFF

Relació amb la resta del bloc

Aquesta pàgina és una visió de conjunt. Alguns temes només s'introdueixen aquí i es treballen a fons a les pàgines següents: els tipus d'índexs a Índexs avançats, el particionament a Particionament, la lectura de plans d'execució a Optimització de queries i la replicació a Replicació.

---

1. Com s'organitza la informació al disc

Per saber-ne més

Aquesta secció n'és un resum orientat al rendiment. Si voleu més informació sobre com els SGBD emmagatzemen la informació (pàgines, heap, motors d'emmagatzematge, tablespaces i filegroups, buffer pool), consulteu el mòdul M0377 — Administració de Sistemes Gestors de Bases de Dades d'ASIX, en especial la pàgina Arquitectura i fonaments dels SGBD (seccions «Nivell intern: l'emmagatzematge real», «Arquitectura física d'un SGBD» i «Motors d'emmagatzematge») i el bloc d'optimització, que comença a Fonaments del rendiment.

Sectors, blocs i pàgines

Una base de dades no llegeix "files" del disc: llegeix pàgines. Entre la fila que demana una consulta i el plat magnètic o la cel·la flash hi ha tres nivells d'unitat:

Nivell	Unitat	Mida habitual	Qui la gestiona
Maquinari	Sector (disc dur) o pàgina flash (SSD)	512 bytes (discos antics) o 4 KB (Advanced Format, estàndard actual)	El firmware del disc
Sistema operatiu	Bloc del sistema de fitxers	4 KB (ext4, XFS, NTFS per defecte)	El sistema de fitxers
Motor de BD	Pàgina o bloc de dades	8 KB o 16 KB	El SGBD

La pàgina és la unitat mínima d'entrada/sortida (E/S) del motor: per llegir una sola fila de 100 bytes, el motor ha de portar a memòria la pàgina sencera que la conté. Cada motor té la seva mida:

Motor	Unitat	Mida per defecte	Com consultar-la
PostgreSQL	page (bloc)	8 KB (fixada en compilar)	SHOW block_size;
MySQL / MariaDB (InnoDB)	page	16 KB (configurable en crear la instància)	SHOW VARIABLES LIKE 'innodb_page_size';
SQL Server	page, agrupades en extents de 8 pàgines (64 KB)	8 KB (fixa)	—
Oracle	data block	8 KB (DB_BLOCK_SIZE)	SHOW PARAMETER db_block_size

Per veure la mida de sector del disc i de bloc del sistema de fitxers en un servidor Linux:

# Mida de sector lògic i físic de cada disc
lsblk -o NAME,SIZE,LOG-SEC,PHY-SEC,ROTA
# ROTA = 1 → disc giratori (HDD); ROTA = 0 → SSD/NVMe

# Mida de bloc del sistema de fitxers on hi ha les dades
stat -f /var/lib/postgresql

Alineació de particions

Si un disc té sectors físics de 4 KB però la partició del sistema operatiu no comença en un múltiple de 4 KB (cosa habitual amb eines antigues que començaven al sector 63), cada pàgina de 8 KB de la base de dades trepitja tres sectors físics en lloc de dos, i cada escriptura obliga el disc a fer un cicle de lectura-modificació-escriptura. El resultat pot ser una pèrdua del 20-30 % del rendiment d'escriptura sense cap missatge d'error. Les eines actuals (parted, fdisk modern) alineen a 1 MiB per defecte, però convé comprovar-ho en servidors heretats.

Què hi ha dins d'una pàgina

Una pàgina de dades té una capçalera, un directori d'apuntadors a les files i les files pròpiament dites. A PostgreSQL, per exemple:

┌──────────────────────────────────────────────────────────┐
│ Capçalera de pàgina (24 bytes): LSN, checksum, espai lliure │
├──────────────────────────────────────────────────────────┤
│ Apuntadors a les files (4 bytes cadascun) →             │
│                                                          │
│                 ... espai lliure ...                     │
│                                                          │
│                ← Files (tuples), de baix cap a dalt       │
│   [capçalera fila 23 B | dades fila 3] [fila 2] [fila 1] │
└──────────────────────────────────────────────────────────┘

D'aquí se'n deriven tres conseqüències pràctiques:

1. Com més petites siguin les files, més files caben per pàgina i menys pàgines cal llegir per a la mateixa consulta. Una taula amb files de 200 bytes encabeix unes 36 files per pàgina de 8 KB; si les files en fan 2.000, només en caben 4. Per això els tipus de dades importen (vegeu la secció 10).
2. Les files que es consulten juntes haurien d'estar juntes. Si una consulta necessita 1.000 files i estan escampades en 1.000 pàgines diferents, cal llegir 1.000 pàgines; si estan agrupades (per exemple, en un índex clusteritzat), n'hi pot haver prou amb 30.
3. L'espai lliure dins la pàgina es pot reservar. El paràmetre FILLFACTOR (PostgreSQL, SQL Server) o PCTFREE (Oracle) deixa un percentatge de cada pàgina buit perquè les actualitzacions puguin escriure la nova versió de la fila a la mateixa pàgina, cosa que evita moure files i dividir pàgines (page splits).

-- PostgreSQL: deixar un 20 % de cada pàgina lliure per a actualitzacions
ALTER TABLE comandes SET (fillfactor = 80);

Accés seqüencial vs accés aleatori

La mateixa quantitat de dades es pot llegir de dues maneres:

• Seqüencial: pàgines consecutives, una darrere l'altra (un Seq Scan d'una taula sencera). El disc pot llegir blocs grans de cop i el sistema operatiu fa lectura anticipada (read-ahead).
• Aleatòria: pàgines disperses (un Index Scan que salta de fila en fila per tota la taula). Cada pàgina és una operació d'E/S independent.

En un disc dur mecànic, cada accés aleatori obliga a moure el capçal i esperar que el plat giri fins al sector correcte (~8-10 ms). Per això en un HDD llegir seqüencialment pot ser 50-100 vegades més ràpid que llegir aleatòriament el mateix volum. En un SSD la diferència es redueix molt, però no desapareix. Els optimitzadors ho tenen en compte: a PostgreSQL, els paràmetres seq_page_cost (1,0 per defecte) i random_page_cost (4,0 per defecte) expressen aquest cost relatiu. En servidors amb SSD és habitual baixar random_page_cost a 1,1-1,5 perquè l'optimitzador triï índexs més sovint.

---

2. Discos i throughput

Tipus de disc

Tipus	Latència d'accés aleatori	IOPS aleatòries (4 KB)	Throughput seqüencial	Cost per TB
HDD 7.200 rpm	8-10 ms	100-200	150-250 MB/s	Molt baix
HDD 15.000 rpm (empresarial)	3-5 ms	200-300	200-300 MB/s	Baix
SSD SATA	~0,1 ms	50.000-100.000	~550 MB/s (límit del bus SATA)	Mitjà
SSD NVMe (PCIe 4.0/5.0)	0,02-0,1 ms	500.000-1.500.000	3.000-14.000 MB/s	Mitjà-alt

Les xifres són ordres de magnitud orientatius: varien segons el model i la càrrega. Cal distingir dues mètriques que sovint es confonen:

• IOPS (Input/Output Operations Per Second): quantes operacions petites i independents pot fer el disc per segon. És la mètrica clau per a càrregues OLTP (moltes transaccions curtes que llegeixen i escriuen poques files: una botiga en línia, un banc).
• Throughput (MB/s): quants megabytes per segon pot transferir en lectures llargues i seqüencials. És la mètrica clau per a càrregues analítiques/OLAP (informes que escanegen milions de files).

Separar el registre de transaccions de les dades

El registre de transaccions (WAL a PostgreSQL, redo log a InnoDB i Oracle, transaction log a SQL Server) s'escriu seqüencialment i cada COMMIT n'ha d'esperar l'escriptura. Les dades, en canvi, s'escriuen de manera aleatòria i en diferit. Si comparteixen disc, els dos patrons es trepitgen. Posar el registre en un disc (o volum) propi i ràpid és una de les millores més antigues i efectives en sistemes amb moltes escriptures.

Més discos petits o menys discos grans?

Aquesta és una pregunta clàssica de dimensionament. Suposem que necessitem 4 TB útils amb redundància, amb discos durs de ~150 IOPS cadascun:

Opció	Configuració	Capacitat útil	IOPS lectura (aprox.)	IOPS escriptura (aprox.)
A	2 discos de 4 TB en RAID 1 (mirall)	4 TB	2 × 150 = 300	150
B	8 discos d'1 TB en RAID 10 (mirall + striping)	4 TB	8 × 150 = 1.200	4 × 150 = 600

Amb la mateixa capacitat, l'opció B ofereix quatre vegades més IOPS i també més throughput seqüencial, perquè les dades es reparteixen (striping) entre quatre parells de discos que treballen en paral·lel. El rendiment d'un conjunt de discos depèn del nombre de capçals (o controladores) que treballen alhora, no de la capacitat total.

Els inconvenients de l'opció B són el cost (més discos, més ports de controladora, més consum i més espai al rack) i la probabilitat que falli algun disc, que és més alta com més discos hi ha. Però té un avantatge amagat: si falla un disc d'1 TB, la reconstrucció (rebuild) triga una fracció del que trigaria amb un disc de 4 TB, i durant aquest temps el sistema funciona degradat i vulnerable.

Els nivells RAID més habituals per a bases de dades:

RAID	Funcionament	Lectura	Escriptura	Ús típic en BD
RAID 0	Striping sense redundància	Excel·lent	Excel·lent	Mai per a dades de producció (un disc que falla ho perd tot)
RAID 1	Mirall de 2 discos	Bona	Com un disc	Registre de transaccions, sistema operatiu
RAID 5 / 6	Striping amb paritat (1 o 2 discos)	Bona	Dolenta: cada escriptura implica llegir i recalcular la paritat	Magatzems de dades amb poques escriptures, arxius
RAID 10	Mirall + striping	Excel·lent	Molt bona	Opció recomanada per a dades OLTP

I amb SSD?

Amb SSD i NVMe el raonament canvia: un sol NVMe ja ofereix més IOPS que un armari sencer de discos durs, i sovint el coll d'ampolla passa a ser la CPU o la xarxa. Tot i així, repartir les dades entre diversos dispositius continua multiplicant el throughput, i la redundància continua sent imprescindible.

---

3. Emmagatzematge en xarxa: SAN

En servidors petits, els discos són dins del mateix servidor (DAS, Direct Attached Storage). En centres de dades, l'emmagatzematge sol estar centralitzat i els servidors hi accedeixen per xarxa. Hi ha dos models:

• NAS (Network Attached Storage): el dispositiu comparteix fitxers per xarxa (NFS, SMB). El servidor veu una carpeta remota. No es recomana per a les dades d'una base de dades (latència, semàntica de bloqueig de fitxers), tot i que alguns motors ho suporten amb configuracions concretes.
• SAN (Storage Area Network): una xarxa dedicada que ofereix blocs. El servidor veu un disc (una LUN, Logical Unit Number) com si fos local, hi crea el seu sistema de fitxers i el motor de BD no nota cap diferència.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#2563EB', 'primaryTextColor': '#FFFFFF', 'primaryBorderColor': '#1E40AF', 'lineColor': '#64748B', 'fontSize': '14px'}}}%%
flowchart LR
    S1["Servidor BD 1"] -- "HBA 1" --> F1["Switch FC A"]
    S1 -- "HBA 2" --> F2["Switch FC B"]
    S2["Servidor BD 2"] --> F1
    S2 --> F2
    F1 --> C1["Controladora 1<br/>(cau RAM + bateria)"]
    F2 --> C2["Controladora 2<br/>(cau RAM + bateria)"]
    C1 --> D["Cabina de discos<br/>LUN 1 · LUN 2 · LUN 3"]
    C2 --> D
    style S1 fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style S2 fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style F1 fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style F2 fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style C1 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style C2 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style D fill:#1F2937,stroke:#64748B,color:#FFFFFF

Elements clau d'una SAN:

• Protocol de transport: Fibre Channel (FC, 16-64 Gbit/s, xarxa pròpia i dedicada), iSCSI (SCSI sobre TCP/IP, fa servir xarxa Ethernet estàndard, més barat) o NVMe over Fabrics (NVMe-oF, la més moderna i de menor latència).
• Multipath: cada servidor té dues targetes (HBA) connectades a dos switches i dues controladores diferents. Si un camí falla, l'altre continua funcionant; en condicions normals, els dos camins reparteixen la càrrega.
• Cau de la controladora: les cabines tenen gigabytes de RAM protegida per bateria. Les escriptures es confirmen quan arriben a aquesta RAM, no al disc, i per això les escriptures a una SAN poden ser més ràpides que a un disc local.
• Funcionalitats avançades: instantànies (snapshots), replicació entre centres de dades, thin provisioning i tiering automàtic (moure les dades calentes a SSD i les fredes a HDD).

Avantatges: l'emmagatzematge es comparteix entre servidors (imprescindible per a clústers de failover com SQL Server FCI o Oracle RAC), es pot ampliar sense aturar res i la gestió és centralitzada.

Riscos per al rendiment: la latència afegida per la xarxa, i sobretot que la cabina és compartida. Si una altra aplicació llança una còpia de seguretat massiva a la mateixa cabina, la base de dades se'n ressent sense que res hagi canviat al seu servidor. Per això és important monitorar la latència d'E/S des del mateix motor (per exemple, sys.dm_io_virtual_file_stats a SQL Server o pg_stat_io a PostgreSQL 16+).

L'equivalent al núvol

Els volums de bloc del núvol (Amazon EBS, Azure Managed Disks, Google Persistent Disk) són, conceptualment, una SAN gestionada pel proveïdor. Per això, en triar un volum, es pot contractar un nombre concret d'IOPS i de throughput independentment de la capacitat (per exemple, els volums gp3 i io2 d'Amazon EBS). Ho veureu al Bloc 4.

---

4. La memòria: el buffer pool

Memòria, CPU i disc: quin recurs importa més?

Un servidor de bases de dades depèn de tres recursos: memòria, CPU i disc. Si només es pot millorar un d'ells, en la gran majoria dels casos el més important és la quantitat de memòria RAM. El motiu és que la memòria és el recurs que determina quant es fa servir el disc, que és, amb molta diferència, la part més lenta del sistema:

• Memòria: si el conjunt de dades que es consulta habitualment (el working set: els índexs i les files "calentes") hi cap sencer, gairebé no cal llegir mai del disc i les consultes van a velocitat de RAM. Si no hi cap, cada consulta acaba esperant el disc i la resta de millores es noten poc.
• Disc: és important, però la memòria amaga la seva lentitud: un disc lent amb molta RAM sovint rendeix més que un disc ràpid amb poca RAM. On el disc no es pot amagar és en les escriptures del registre de transaccions (cada COMMIT hi ha d'arribar) i en les consultes que llegeixen més dades de les que caben a la memòria.
• CPU: en una base de dades transaccional (OLTP), la CPU passa la major part del temps esperant que arribin les pàgines del disc. Només es converteix en el coll d'ampolla quan les dades ja són en memòria i hi ha moltes consultes concurrents, o en càrregues analítiques amb molts càlculs, ordenacions i agregacions.

Regla pràctica

Abans de comprar CPU o discos més ràpids, comproveu la taxa d'encerts del buffer pool (vegeu més avall). Si és baixa, afegir memòria i configurar-la bé és gairebé sempre la millora amb més impacte per euro invertit. Si ja és superior al 99 %, més memòria no aportarà res i caldrà buscar el problema en una altra banda (consultes mal escrites, índexs que falten, CPU saturada).

Llegir de RAM vs llegir de disc

Tot el que hem vist fins ara té un objectiu: fer el disc tan ràpid com es pugui. Però la manera més efectiva de fer ràpida una lectura de disc és no fer-la. Observeu la diferència de temps d'accés entre els nivells de la jerarquia de memòria:

Mitjà	Latència típica	Si 1 accés a RAM durés 1 segon...
Cau L1 de la CPU	~1 ns	0,01 s
Memòria RAM	~100 ns	1 segon
SSD NVMe	~20-100 µs	3-17 minuts
SSD SATA	~100-150 µs	~20 minuts
Cabina SAN (per xarxa)	~0,3-2 ms	1-5 hores
HDD (accés aleatori)	~10 ms	~28 hores

Llegir una pàgina de la RAM és entre mil i cent mil vegades més ràpid que llegir-la del disc. Per això tots els motors reserven una zona de memòria on guarden les pàgines llegides recentment: el buffer pool (o buffer cache).

Com funciona el buffer pool

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#2563EB', 'primaryTextColor': '#FFFFFF', 'primaryBorderColor': '#1E40AF', 'lineColor': '#64748B', 'fontSize': '14px'}}}%%
flowchart TB
    Q["La consulta necessita la pàgina 4812"] --> H{"És al buffer pool?"}
    H -- "Sí (cache hit)" --> R["Es llegeix de la RAM<br/>~100 ns"]
    H -- "No (cache miss)" --> L["Es llegeix del disc<br/>µs o ms"]
    L --> E{"Hi ha espai lliure?"}
    E -- "Sí" --> P["Es desa al buffer pool"]
    E -- "No" --> V["Es desallotja la pàgina menys usada<br/>(si està bruta, primer s'escriu al disc)"]
    V --> P
    P --> R
    style Q fill:#1F2937,stroke:#64748B,color:#FFFFFF
    style R fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style L fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style P fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style V fill:#2563EB,stroke:#1E40AF,color:#FFFFFF

1. Lectura: quan una consulta necessita una pàgina, el motor la busca primer al buffer pool. Si hi és (hit), la llegeix de la RAM. Si no (miss), la porta del disc i la hi desa.
2. Desallotjament: quan el buffer pool és ple, cal fer lloc. Els motors fan servir variants de l'algorisme LRU (Least Recently Used): PostgreSQL fa servir clock sweep, i InnoDB una LRU dividida en una zona "jove" i una de "vella" perquè un escaneig puntual d'una taula gegant no expulsi les pàgines que es fan servir constantment.
3. Escriptura: quan una transacció modifica una fila, la pàgina es modifica a la RAM (queda bruta, dirty) i el canvi s'anota al registre de transaccions, que sí que s'escriu al disc en fer COMMIT. Les pàgines brutes s'escriuen al disc més tard, en bloc, durant els checkpoints o per processos en segon pla. Això converteix moltes escriptures aleatòries petites en poques escriptures agrupades.

La mètrica clau és la taxa d'encerts (buffer cache hit ratio): el percentatge de lectures servides des de la RAM. En un sistema OLTP ben dimensionat hauria de ser superior al 99 %. Per sota del 90 %, el servidor passa bona part del temps esperant el disc.

Paràmetres bàsics de configuració

PostgreSQLMySQL / MariaDBSQL ServerOracle

# postgresql.conf — servidor dedicat amb 64 GB de RAM
shared_buffers = 16GB           # buffer pool propi: ~25 % de la RAM
effective_cache_size = 48GB     # NO reserva memòria: indica a l'optimitzador
                                # quanta cau hi ha en total (pròpia + del SO)
work_mem = 64MB                 # memòria per a cada ordenació/hash d'una consulta
maintenance_work_mem = 2GB      # per a CREATE INDEX, VACUUM...
random_page_cost = 1.1          # disc SSD: l'accés aleatori és gairebé com el seqüencial

PostgreSQL es recolza també en la cau de pàgines del sistema operatiu, i per això shared_buffers no s'acostuma a posar per sobre del 25-40 % de la RAM. Compte amb work_mem: s'aplica per operació, no per connexió. Una consulta amb 4 ordenacions i 100 connexions simultànies pot arribar a consumir 4 × 100 × 64 MB = 25 GB.

-- Taxa d'encerts de la base de dades actual
SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

# my.cnf — servidor dedicat amb 64 GB de RAM
[mysqld]
innodb_buffer_pool_size = 48G        # 50-75 % de la RAM en un servidor dedicat
innodb_buffer_pool_instances = 8     # divideix el pool per reduir contenció
innodb_log_file_size = 2G            # redo log més gran = menys checkpoints forçats
innodb_flush_method = O_DIRECT       # evita la doble cau (InnoDB + SO)

InnoDB no es recolza en la cau del sistema operatiu (amb O_DIRECT la salta), i per això el seu buffer pool pot i ha de ser molt més gran que el de PostgreSQL. A MySQL 8.4+, innodb_log_file_size ha estat substituït per innodb_redo_log_capacity.

-- Lectures lògiques (peticions) vs lectures físiques (han anat al disc)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- hit ratio = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

-- SQL Server gestiona la memòria dinàmicament i n'agafa tanta com pot.
-- Cal limitar-la per deixar memòria al sistema operatiu (servidor de 64 GB):
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 57344;   -- 56 GB
RECONFIGURE;

-- Page Life Expectancy: segons que una pàgina roman al buffer pool.
-- Un valor que baixa en picat indica pressió de memòria.
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
  AND counter_name IN ('Page life expectancy', 'Buffer cache hit ratio');

-- Oracle reparteix la memòria entre la SGA (compartida, inclou el buffer cache)
-- i la PGA (per sessió, per a ordenacions i hash joins)
ALTER SYSTEM SET sga_target = 40G SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 12G SCOPE = SPFILE;
-- Amb sga_target, Oracle ajusta automàticament DB_CACHE_SIZE dins de la SGA

-- Taxa d'encerts del buffer cache
SELECT name, physical_reads, db_block_gets + consistent_gets AS logical_reads,
       round(100 * (1 - physical_reads / nullif(db_block_gets + consistent_gets, 0)), 2) AS hit_ratio
FROM v$buffer_pool_statistics;

Experiment: la mateixa consulta, en fred i en calent

A PostgreSQL, l'opció BUFFERS d'EXPLAIN mostra quantes pàgines s'han trobat al buffer pool (shared hit) i quantes s'han hagut de llegir (read):

-- Primera execució (dades encara no carregades en memòria)
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM vendes WHERE data_venda >= '2025-01-01';
--   Buffers: shared hit=12 read=54021
--   Execution Time: 4812.337 ms

-- Segona execució (ara les pàgines ja són al buffer pool)
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM vendes WHERE data_venda >= '2025-01-01';
--   Buffers: shared hit=54033
--   Execution Time: 612.904 ms

La consulta és idèntica i el pla també, però la segona vegada va vuit vegades més ràpida perquè no ha tocat el disc. Tingueu-ho present quan mesureu el rendiment: la primera execució d'una prova no és representativa, i cal repetir-la diverses vegades o indicar sempre si es mesura en fred o en calent.

---

5. Índexs

Quan les dades no caben senceres a la memòria, o fins i tot quan hi caben, cal reduir el nombre de pàgines que llegeix cada consulta. L'eina principal per fer-ho és l'índex: una estructura auxiliar, ordenada i mantinguda pel motor, que permet anar directament a les pàgines que contenen les files buscades sense llegir la taula sencera.

Una bona analogia és l'índex alfabètic d'un llibre: per trobar on es parla de "replicació" no cal llegir les 500 pàgines, sinó consultar l'índex, que diu "pàgines 212 i 348".

Idees clau abans d'entrar en detall:

• Un índex té cost. Ocupa espai en disc i al buffer pool, i cada INSERT, UPDATE o DELETE l'ha d'actualitzar. Una taula amb deu índexs fa onze escriptures per cada fila inserida.
• Un índex només serveix si la consulta és selectiva. Si la consulta retorna el 40 % de la taula, és més ràpid llegir-la seqüencialment que saltar de l'índex a la taula milions de vegades, i l'optimitzador ho sap.
• Hi ha diversos tipus d'índexs, cadascun per a un tipus de consulta: B-Tree (el de propòsit general), Hash (igualtat exacta), GIN (arrays, JSON i text complet), BRIN (taules enormes ordenades físicament, com els logs) i GiST (dades geomètriques i rangs), a més dels índexs clusteritzats, compostos, parcials, funcionals i de cobertura.

Els dos tipus més comuns: arbres B+ i hash

De tots aquests tipus, la gran majoria d'índexs que trobareu en qualsevol base de dades són de dues famílies: els arbres B+ i els índexs hash.

Arbre B+ (B+ Tree)

Tot i que els motors l'anomenen "B-Tree" (CREATE INDEX ... USING btree), l'estructura que fan servir PostgreSQL, InnoDB, SQL Server i Oracle és en realitat un arbre B+, que es caracteritza per dues coses:

• Els nodes interns només contenen claus que fan de "senyals de direcció" per baixar per l'arbre.
• Totes les entrades reals (clau + apuntador a la fila) són a les fulles, que estan encadenades en ordre. Un cop trobada la primera fila d'un rang, el motor només ha de recórrer les fulles cap a la dreta, sense tornar a pujar per l'arbre.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#2563EB', 'primaryTextColor': '#FFFFFF', 'primaryBorderColor': '#1E40AF', 'lineColor': '#64748B', 'fontSize': '14px'}}}%%
flowchart TB
    R["Arrel: 40 · 80"] --> I1["10 · 25"]
    R --> I2["55 · 70"]
    R --> I3["90"]
    I1 --> F1["Fulla: 3 · 7 · 10"]
    I1 --> F2["Fulla: 12 · 18 · 25"]
    I1 --> F3["Fulla: 31 · 36 · 40"]
    I2 --> F4["Fulla: 44 · 51 · 55"]
    I2 --> F5["Fulla: 62 · 70"]
    I2 --> F6["Fulla: 73 · 80"]
    I3 --> F7["Fulla: 84 · 90"]
    I3 --> F8["Fulla: 93 · 99"]
    F1 -.-> F2 -.-> F3 -.-> F4 -.-> F5 -.-> F6 -.-> F7 -.-> F8
    style R fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style I1 fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style I2 fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style I3 fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style F1 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F2 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F3 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F4 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F5 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F6 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F7 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style F8 fill:#16A34A,stroke:#15803D,color:#FFFFFF

Per buscar el valor 62: a l'arrel, 62 és entre 40 i 80 → es baixa pel segon fill; al node intern, 62 és entre 55 i 70 → es baixa cap a la fulla 62 · 70. Per a una consulta de rang (BETWEEN 51 AND 75), es baixa fins a la fulla que conté el 51 i es continua per la cadena de fulles fins que se supera el 75.

La clau del seu rendiment és que cada node és una pàgina del disc (secció 1). En una pàgina de 8 KB hi caben uns 400 valors d'una clau BIGINT, i per tant cada nivell multiplica per 400 el nombre de files adreçables:

Nivells de l'arbre	Files que pot indexar (aprox.)	Pàgines a llegir per trobar una fila
2	400 × 400 = 160.000	2
3	400³ = 64 milions	3
4	400⁴ = 25.600 milions	4

Trobar una fila entre 25.000 milions només requereix llegir 4 pàgines. A més, l'arrel i els nivells interns són poques pàgines i es consulten constantment, de manera que sempre són al buffer pool: a la pràctica, sovint només cal llegir del disc la fulla (una altra raó per la qual la memòria és tan important, vegeu la secció 4).

Un arbre B+ serveix per a igualtats (=), comparacions (<, >, BETWEEN), ordenacions (ORDER BY, MIN, MAX) i prefixos de text (LIKE 'abc%'). Per això és l'índex per defecte a tots els motors.

-- Taula d'exemple
CREATE TABLE clients (
    id          BIGINT PRIMARY KEY,     -- la clau primària crea automàticament un arbre B+
    dni         CHAR(9)      NOT NULL,
    cognom      VARCHAR(80)  NOT NULL,
    data_alta   DATE         NOT NULL
);

-- Índex B+ sobre la data d'alta (sintaxi vàlida als quatre motors)
CREATE INDEX idx_clients_data_alta ON clients (data_alta);

-- Consultes que el poden aprofitar:
SELECT * FROM clients WHERE data_alta = DATE '2026-09-01';                            -- igualtat
SELECT * FROM clients WHERE data_alta BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'; -- rang
SELECT * FROM clients ORDER BY data_alta DESC;                                        -- ordenació (sense Sort)
SELECT MAX(data_alta) FROM clients;                                                   -- n'hi ha prou amb l'extrem de l'arbre

Índex hash

Un índex hash aplica una funció de hash al valor de la clau i n'obté el número d'una cubeta (bucket), on hi ha l'apuntador a la fila. Per buscar un valor, es calcula el hash i es va directament a la cubeta, sense recórrer cap arbre: el cost és O(1), constant independentment de la mida de la taula.

hash('12345678Z') = 7   →   cubeta 7: [ '12345678Z' → fila (pàgina 812, posició 4) ]
hash('87654321X') = 2   →   cubeta 2: [ '87654321X' → fila (pàgina 35, posició 11) ]
hash('11111111H') = 7   →   cubeta 7: [ ..., '11111111H' → fila (pàgina 2090, posició 1) ]   ← col·lisió

El preu d'aquesta rapidesa és que la funció de hash destrueix l'ordre: valors consecutius acaben en cubetes qualssevol. Per això un índex hash només serveix per a igualtats exactes (=, o IN amb pocs valors). No es pot fer servir per a rangs, ordenacions, MIN/MAX ni LIKE 'prefix%'.

-- PostgreSQL: índex hash explícit
CREATE INDEX idx_clients_dni_hash ON clients USING hash (dni);

SELECT * FROM clients WHERE dni = '12345678Z';         -- ✔ fa servir l'índex hash
SELECT * FROM clients WHERE dni IN ('12345678Z', '87654321X');  -- ✔ (una cerca per valor)
SELECT * FROM clients WHERE dni > '50000000A';         -- ✘ no el pot fer servir (rang)
SELECT * FROM clients ORDER BY dni;                    -- ✘ no el pot fer servir (ordre)

El suport als índexs hash varia molt entre motors:

Motor	Arbre B+	Índex hash
PostgreSQL	Per defecte (btree)	USING hash, fiable des de la versió 10
MySQL / MariaDB	Per defecte a InnoDB	Només a les taules ENGINE=MEMORY. InnoDB, però, crea automàticament un adaptive hash index en memòria sobre les pàgines B+ més consultades
SQL Server	Per defecte (índexs rowstore)	Només a les taules optimitzades per a memòria (In-Memory OLTP)
Oracle	Per defecte	No com a índex, sinó com a hash cluster (la taula sencera s'organitza per hash)

B+ o hash?

En la pràctica, l'arbre B+ és gairebé sempre la tria correcta: fa pràcticament el mateix que un hash per a les igualtats (3-4 pàgines, amb els nivells superiors ja en memòria) i, a més, serveix per a rangs i ordenacions. L'índex hash només té sentit en columnes que exclusivament es consulten per igualtat i amb valors llargs (com un hash SHA-256 o una URL), on l'índex hash és més petit perquè només guarda el hash de 4 bytes i no el valor sencer.

Tots aquests tipus, i la resta (GIN, BRIN, GiST, parcials, funcionals...), es treballen amb detall i amb exemples per als quatre motors a la pàgina Índexs avançats.

---

6. Vistes materialitzades

Una vista normal (CREATE VIEW) és només una consulta desada: cada vegada que s'hi fa un SELECT, el motor torna a executar la consulta sencera. Si la vista agrega 300 milions de vendes per mes i per botiga, cada consulta a la vista torna a processar els 300 milions de files.

Una vista materialitzada desa el resultat de la consulta en disc, com una taula. Consultar-la és llegir unes quantes centenars de files ja calculades en comptes de recalcular-les. El preu és que el resultat envelleix: cal refrescar-lo quan les dades d'origen canvien.

Són ideals per a informes, quadres de comandament i agregats que es consulten molt i que toleren dades amb uns minuts o hores de retard.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE MATERIALIZED VIEW mv_vendes_mensuals AS
SELECT date_trunc('month', data_venda) AS mes,
       botiga_id,
       count(*)          AS num_vendes,
       sum(import_total) AS facturacio
FROM vendes
GROUP BY 1, 2;

-- Cal un índex únic per poder refrescar sense bloquejar les lectures
CREATE UNIQUE INDEX ON mv_vendes_mensuals (mes, botiga_id);

-- Refresc (per exemple, cada nit des d'un cron o un DAG d'Airflow)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_vendes_mensuals;

PostgreSQL sempre recalcula la vista sencera en refrescar-la; no té refresc incremental natiu.

-- MySQL no té vistes materialitzades: se simulen amb una taula resum
CREATE TABLE mv_vendes_mensuals (
    mes        DATE          NOT NULL,
    botiga_id  INT           NOT NULL,
    num_vendes INT           NOT NULL,
    facturacio DECIMAL(14,2) NOT NULL,
    PRIMARY KEY (mes, botiga_id)
);

-- Refresc idempotent programat amb l'Event Scheduler
CREATE EVENT ev_refresc_vendes_mensuals
ON SCHEDULE EVERY 1 DAY STARTS '2026-01-01 03:00:00'
DO
  REPLACE INTO mv_vendes_mensuals
  SELECT DATE_FORMAT(data_venda, '%Y-%m-01'), botiga_id, COUNT(*), SUM(import_total)
  FROM vendes
  GROUP BY 1, 2;

-- SQL Server fa servir "vistes indexades": el resultat es manté
-- actualitzat AUTOMÀTICAMENT a cada INSERT/UPDATE/DELETE (no cal refrescar)
CREATE VIEW dbo.vw_vendes_per_botiga
WITH SCHEMABINDING
AS
SELECT botiga_id,
       COUNT_BIG(*)                AS num_vendes,
       SUM(ISNULL(import_total,0)) AS facturacio
FROM dbo.vendes
GROUP BY botiga_id;
GO
CREATE UNIQUE CLUSTERED INDEX ix_vw_vendes_per_botiga
    ON dbo.vw_vendes_per_botiga (botiga_id);

El manteniment automàtic té un cost: cada escriptura a vendes també actualitza la vista. Són adequades per a taules amb moltes més lectures que escriptures.

-- El registre de canvis (materialized view log) permet el refresc incremental (FAST)
CREATE MATERIALIZED VIEW LOG ON vendes
    WITH ROWID, SEQUENCE (data_venda, botiga_id, import_total) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_vendes_mensuals
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TRUNC(data_venda, 'MM') AS mes, botiga_id,
       COUNT(*) AS num_vendes, COUNT(import_total) AS cnt_import,
       SUM(import_total) AS facturacio
FROM vendes
GROUP BY TRUNC(data_venda, 'MM'), botiga_id;

-- Refresc incremental: només aplica els canvis registrats al log
EXEC DBMS_MVIEW.REFRESH('MV_VENDES_MENSUALS', 'F');

Amb ENABLE QUERY REWRITE, l'optimitzador d'Oracle pot fer servir la vista materialitzada automàticament quan algú executa la consulta original sobre vendes, sense que l'aplicació n'hagi de conèixer l'existència.

Documentació: PostgreSQL · SQL Server · Oracle.

---

7. Particionament i distribució de les particions en discos diferents

El particionament divideix una taula gran en trossos (particions) segons el valor d'una columna, habitualment una data. Les consultes que filtren per aquesta columna només llegeixen les particions afectades (partition pruning), i les dades antigues es poden eliminar esborrant una partició sencera en comptes de fer un DELETE massiu. Els mètodes (rang, llista, hash) i el pruning es treballen a fons a la pàgina Particionament.

Aquí ens interessa un avantatge que connecta amb les seccions 1-3: cada partició pot viure en un disc diferent. Això permet aplicar una estratègia d'emmagatzematge per nivells (storage tiering):

• Les particions calentes (el mes actual, que rep totes les escriptures i la majoria de consultes) en discos NVMe ràpids i cars.
• Les particions tèbies (l'últim any) en SSD estàndard.
• Les particions fredes (històric de fa anys, que gairebé no es consulta) en HDD grans i barats, o fins i tot en mode només lectura (vegeu la secció 8).

Els motors associen les particions a ubicacions físiques mitjançant tablespaces (PostgreSQL, Oracle), filegroups (SQL Server) o directoris de dades (MySQL):

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Un tablespace és un directori en un disc concret
CREATE TABLESPACE ts_rapid LOCATION '/mnt/nvme/pgdata';
CREATE TABLESPACE ts_arxiu LOCATION '/mnt/hdd/pgdata';

-- La partició nova es crea al disc ràpid
CREATE TABLE vendes_2026_09 PARTITION OF vendes
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
    TABLESPACE ts_rapid;

-- Quan envelleix, es mou al disc d'arxiu
ALTER TABLE vendes_2024_01 SET TABLESPACE ts_arxiu;

SET TABLESPACE reescriu la partició sencera i la bloqueja (ACCESS EXCLUSIVE) mentre dura: convé fer-ho en hores de poca activitat. Els índexs de la partició es mouen a part (ALTER INDEX ... SET TABLESPACE).

-- InnoDB (amb innodb_file_per_table) permet ubicar cada partició en un directori
CREATE TABLE vendes (
    id         BIGINT NOT NULL,
    data_venda DATE   NOT NULL,
    import_total DECIMAL(12,2),
    PRIMARY KEY (id, data_venda)
)
PARTITION BY RANGE (YEAR(data_venda)) (
    PARTITION p2024 VALUES LESS THAN (2025) DATA DIRECTORY = '/mnt/hdd/mysql',
    PARTITION p2025 VALUES LESS THAN (2026) DATA DIRECTORY = '/mnt/ssd/mysql',
    PARTITION p2026 VALUES LESS THAN (2027) DATA DIRECTORY = '/mnt/nvme/mysql'
);

Els directoris han d'estar declarats a innodb_directories. Per moure una partició existent cal reorganitzar-la (ALTER TABLE ... REORGANIZE PARTITION), cosa que en reescriu les dades.

-- Cada filegroup agrupa fitxers de dades situats en discos concrets
ALTER DATABASE Botiga ADD FILEGROUP fg_arxiu;
ALTER DATABASE Botiga ADD FILE (NAME = arxiu1, FILENAME = 'H:\Data\arxiu1.ndf')
    TO FILEGROUP fg_arxiu;

-- L'esquema de partició indica a quin filegroup va cada partició
CREATE PARTITION FUNCTION pf_any (date)
    AS RANGE RIGHT FOR VALUES ('2025-01-01', '2026-01-01');
CREATE PARTITION SCHEME ps_any
    AS PARTITION pf_any TO (fg_arxiu, fg_ssd, fg_nvme);

-- Moure una partició a un altre tablespace, en línia (12c+) i sense invalidar índexs
ALTER TABLE vendes MOVE PARTITION p_2024 TABLESPACE ts_arxiu
    ONLINE UPDATE INDEXES;

-- Opcionalment, comprimir-la en el mateix moviment
ALTER TABLE vendes MOVE PARTITION p_2023 TABLESPACE ts_arxiu
    COMPRESS ONLINE UPDATE INDEXES;

Encara que no es faci tiering, repartir particions en discos diferents també reparteix la càrrega d'E/S: una consulta que llegeix tres particions situades en tres discos pot aprofitar els tres alhora.

---

8. Bases de dades en mode només lectura

Una part important del treball d'un motor relacional és garantir la consistència davant de les escriptures concurrents: bloquejos (locks), versions de files per al control de concurrència (MVCC), identificadors de transacció, escriptures al registre de transaccions, checkpoints... Si el motor sap que una base de dades, un tablespace o una transacció no escriurà mai, pot estalviar-se una part d'aquesta feina.

Els casos d'ús típics són les dades històriques tancades (exercicis comptables ja auditats, anys anteriors d'un magatzem de dades), les bases de dades d'informes carregades cada nit i les rèpliques de consulta.

El guany concret depèn del motor:

Motor	Com s'activa	Què s'estalvia
SQL Server	ALTER DATABASE Historic SET READ_ONLY;	El motor no gestiona bloquejos de fila ni de pàgina (ningú no pot modificar res), ni versions de files. És el cas on el guany és més clar en consultes molt concurrents.
Oracle	ALTER TABLESPACE ts_2020 READ ONLY; o ALTER DATABASE OPEN READ ONLY;	Els fitxers d'un tablespace de només lectura no participen en els checkpoints i només cal fer-ne una còpia de seguretat: les còpies diàries són molt més petites i ràpides.
MySQL / InnoDB	START TRANSACTION READ ONLY; o SET GLOBAL super_read_only = ON;	Les transaccions declarades de només lectura no reben identificador de transacció ni estructures internes d'escriptura, cosa que redueix la contenció amb molta concurrència.
PostgreSQL	ALTER DATABASE historic SET default_transaction_read_only = on;	Aquí és sobretot una protecció contra modificacions accidentals; el guany directe de rendiment és petit. El benefici real a PostgreSQL arriba amb les rèpliques de lectura (secció següent) i amb VACUUM FREEZE després de la càrrega, que evita escriptures posteriors per marcar files com a visibles.

Combinar-ho amb el particionament

Una estratègia molt habitual és tenir la taula particionada per any i passar a només lectura el tablespace o filegroup de cada any tancat. Les dades del curs actual continuen sent modificables, les històriques deixen de consumir temps de manteniment i de còpia de seguretat, i es poden comprimir.

---

9. Distribuir la càrrega: un node d'escriptura i rèpliques de lectura

En la majoria d'aplicacions, les lectures superen àmpliament les escriptures: en una botiga en línia, per cada comanda (escriptura) hi ha centenars de consultes de catàleg (lectures). Quan un únic servidor no pot més, una solució molt efectiva és separar-les:

• Un node primari (primary) rep totes les operacions d'escriptura (INSERT, UPDATE, DELETE) i també les lectures que necessiten les dades més recents.
• Una o més rèpliques de només lectura (read replicas) reben una còpia contínua dels canvis del primari i atenen les consultes: el catàleg, els informes, els quadres de comandament, les extraccions per a processos ETL.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#2563EB', 'primaryTextColor': '#FFFFFF', 'primaryBorderColor': '#1E40AF', 'lineColor': '#64748B', 'fontSize': '14px'}}}%%
flowchart LR
    APP["Aplicació"] --> PX["Proxy / encaminador<br/>(ProxySQL, Pgpool-II,<br/>HAProxy, cadena de connexió)"]
    PX -- "Escriptures (CRUD)" --> P[("Primari<br/>lectura/escriptura")]
    PX -- "Lectures" --> R1[("Rèplica 1<br/>només lectura")]
    PX -- "Lectures" --> R2[("Rèplica 2<br/>només lectura")]
    PX -- "Informes / ETL" --> R3[("Rèplica 3<br/>només lectura")]
    P -. "Replicació (WAL / binlog)" .-> R1
    P -.-> R2
    P -.-> R3
    style APP fill:#1F2937,stroke:#64748B,color:#FFFFFF
    style PX fill:#7C3AED,stroke:#5B21B6,color:#FFFFFF
    style P fill:#2563EB,stroke:#1E40AF,color:#FFFFFF
    style R1 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style R2 fill:#16A34A,stroke:#15803D,color:#FFFFFF
    style R3 fill:#16A34A,stroke:#15803D,color:#FFFFFF

Avantatges:

• Escalabilitat de lectura: afegir una rèplica afegeix capacitat de consulta sense tocar el primari.
• Aïllament de càrregues: un informe pesat que triga 20 minuts s'executa en una rèplica dedicada i no alenteix les transaccions dels clients.
• Alta disponibilitat: si el primari cau, una rèplica pot passar a ser el nou primari (failover).

Allò que cal tenir en compte:

• Retard de replicació (replication lag): les rèpliques van uns mil·lisegons (o segons, amb molta càrrega) per darrere del primari. Si un usuari desa el seu perfil i tot seguit el consulta en una rèplica, pot veure la versió antiga. Les lectures que han de veure l'última escriptura (read-your-writes) s'han d'enviar al primari.
• Les escriptures no escalen: totes continuen passant pel primari. Si el problema són les escriptures, cal particionar, optimitzar o repartir les dades entre diversos primaris (sharding), cosa molt més complexa.
• Algú ha de decidir a quin node va cada consulta: l'aplicació (amb dues cadenes de connexió), un proxy (ProxySQL per a MySQL, Pgpool-II per a PostgreSQL) o el mateix motor (SQL Server Always On encamina a una rèplica secundària les connexions que indiquen ApplicationIntent=ReadOnly; Oracle Active Data Guard permet obrir la base de dades de reserva en només lectura mentre continua aplicant canvis).

La configuració concreta de la replicació en els quatre motors es treballa a la pàgina Replicació.

---

10. Organització de les dades: claus i tipus

L'última baula de la cadena, i la més barata de fer bé, és el disseny. Les decisions que es prenen quan es crea una taula determinen quantes files caben per pàgina, quant ocupen els índexs i quant costa cada comparació. I canviar-les quan la taula ja té 500 milions de files és car i arriscat.

Claus numèriques vs claus alfanumèriques

La clau primària apareix a molts llocs: a l'índex de la clau primària, a totes les claus foranes de les taules que hi fan referència, als índexs d'aquestes claus foranes i, a InnoDB i SQL Server (on la taula s'organitza per la clau primària), dins de cada índex secundari. La seva mida es multiplica.

Tipus de clau	Mida	Exemple
INT	4 bytes	42
BIGINT	8 bytes	9876543210
UUID (tipus natiu)	16 bytes	0191f3a2-...
VARCHAR(36) amb un UUID en text	37-40 bytes	'550e8400-e29b-41d4-a716-446655440000'
VARCHAR(100) amb un correu electrònic	20-50 bytes	'nom.cognom@exemple.cat'

Per a 300 milions de files, passar d'una clau BIGINT a una VARCHAR(36) afegeix uns 9 GB a l'índex de clau primària, i la mateixa quantitat a cada clau forana i a cada índex secundari que la contingui. Tot això ocupa disc i, sobretot, buffer pool: menys dades útils caben a la RAM.

A més, comparar dos enters és una sola instrucció de la CPU, mentre que comparar dues cadenes implica recórrer-les caràcter a caràcter i aplicar les regles d'ordenació de l'idioma (collation), que poden tenir en compte accents i majúscules. En un JOIN de milions de files, la diferència es nota.

Recomanacions:

• Feu servir una clau subrogada numèrica (BIGINT generada per una seqüència o IDENTITY) com a clau primària, i guardeu les claus naturals (DNI, correu, codi de producte) com a columnes normals amb una restricció UNIQUE. A més, les claus naturals canvien (un client canvia de correu), i canviar una clau primària obliga a actualitzar totes les claus foranes.
• Si necessiteu UUID (per exemple, perquè els identificadors es generen en sistemes diferents i s'han de poder fusionar), feu servir el tipus natiu (uuid a PostgreSQL, UNIQUEIDENTIFIER a SQL Server, BINARY(16) a MySQL, RAW(16) a Oracle), mai un VARCHAR.

El problema de les claus aleatòries

Hi ha un segon efecte, menys evident, que afecta els índexs B-Tree i, especialment, les taules organitzades per clau primària (InnoDB, índexs clusteritzats de SQL Server):

• Amb una clau seqüencial (1, 2, 3...), cada fila nova va al final de l'índex. Només la darrera pàgina rep insercions i sempre és al buffer pool. Les pàgines s'omplen completament.
• Amb una clau aleatòria (un UUID v4), cada fila nova va a una posició aleatòria de l'índex. Cada inserció pot necessitar portar al buffer pool una pàgina diferent (una lectura de disc) i, si és plena, dividir-la en dues de mig buides (page split). L'índex acaba fragmentat, ocupant fins al doble d'espai, i el buffer pool s'omple de pàgines que només es fan servir un cop.

La solució, si calen UUID, és fer servir UUID ordenats en el temps: UUIDv7 (definit al RFC 9562), que comença amb una marca de temps i, per tant, s'insereix gairebé en ordre. PostgreSQL 18 inclou la funció uuidv7(), i SQL Server ofereix NEWSEQUENTIALID() amb un objectiu semblant.

Altres bones pràctiques de disseny

• El tipus més petit que sigui correcte. Un SMALLINT (2 bytes) per a un codi de 0 a 1.000 en lloc d'un BIGINT (8 bytes). Però sense passar-se: canviar de INT a BIGINT quan una taula arriba als 2.147 milions de files és una operació molt costosa. Per a les claus primàries de taules que poden créixer molt, BIGINT des del principi.
• Cada dada en el seu tipus. Les dates com a DATE o TIMESTAMP (no com a VARCHAR amb '25/09/2026': ocupen més, no es poden comparar ni ordenar correctament i no es poden indexar per rang). Els imports com a NUMERIC/DECIMAL (no FLOAT, que té errors d'arrodoniment). Els booleans com a BOOLEAN o BIT.
• Ordre de les columnes (PostgreSQL). PostgreSQL alinea cada columna a la seva mida, i per això l'ordre de declaració afecta la mida de la fila. Posar primer les columnes de mida fixa gran (bigint, timestamp) i després les petites (int, smallint, boolean) i les de mida variable (text) evita bytes de farciment.
• Columnes grans fora de la fila principal. Les descripcions llargues, el JSON i els binaris fan que capiguen poques files per pàgina. Els motors les treuen automàticament de la fila quan superen una mida (TOAST a PostgreSQL, pàgines de desbordament a InnoDB i SQL Server), però si la columna es consulta poc, és millor posar-la en una taula separada amb una relació 1:1.
• Normalitzar, i desnormalitzar amb criteri. Un model normalitzat evita redundàncies i fa les escriptures eficients. En sistemes analítics, desnormalitzar (repetir el nom de la botiga a la taula de vendes, per exemple) estalvia JOIN a canvi d'espai i de complexitat en les actualitzacions. És la idea del model dimensional que veureu a Data Warehouse.
• Mantenir les estadístiques al dia. L'optimitzador decideix el pla d'execució a partir d'estadístiques sobre les dades (nombre de files, distribució de valors). Unes estadístiques obsoletes porten a plans dolents encara que tot el que hem vist estigui ben fet (ANALYZE a PostgreSQL i MySQL, UPDATE STATISTICS a SQL Server, DBMS_STATS a Oracle). Ho veureu a Optimització de queries.

---

Resum: on actuar segons el símptoma

Símptoma	Primera sospita	Mesures possibles
Totes les consultes són lentes, fins i tot les senzilles	Memòria insuficient o disc lent	Augmentar el buffer pool, comprovar la taxa d'encerts, passar a SSD/NVMe, revisar la latència de la SAN
Les escriptures són lentes, però les lectures van bé	Disc del registre de transaccions, massa índexs, claus aleatòries	Registre en un disc propi, RAID 10 en lloc de RAID 5, eliminar índexs innecessaris, claus seqüencials
Una consulta concreta és lenta	Falta un índex o el pla d'execució és dolent	Analitzar-ne el pla (EXPLAIN), crear l'índex adequat, actualitzar estadístiques
Els informes agregats tarden minuts	Es recalculen sobre milions de files cada vegada	Vistes materialitzades, particionament, rèplica dedicada a informes
La taula creix sense aturador i el manteniment es fa etern	Una única taula monolítica	Particionament, particions antigues a discos barats i en només lectura
El servidor està saturat de consultes de lectura	Un únic node ho fa tot	Rèpliques de lectura amb encaminament de les consultes

---

AC5074/02/06 — Miniactivitat

Una botiga en línia té la seva base de dades PostgreSQL en un servidor amb aquesta configuració:

• 64 GB de RAM; shared_buffers = 128MB i la resta de paràmetres amb els valors per defecte.
• Dos discos durs de 8 TB en RAID 1, on hi ha el sistema operatiu, les dades i el WAL.
• La taula comandes té 300 milions de files, amb clau primària id VARCHAR(36) que conté un UUID v4 generat per l'aplicació, i la columna data_comanda VARCHAR(10) amb el format 'DD/MM/AAAA'. Hi ha 6 taules més amb una clau forana cap a comandes.id.
• Un informe de facturació mensual per categoria es calcula des de zero cada vegada que algú obre el quadre de comandament (unes 400 vegades al dia).

• El 90 % de les operacions són lectures i les comandes de fa més de dos anys no es modifiquen mai.

• Identifiqueu com a mínim sis problemes de rendiment, classificats segons la capa on es troben (emmagatzematge, memòria, estratègies lògiques, disseny de dades).

• Per a cada problema, proposeu una millora concreta (amb els valors de paràmetres o les sentències SQL quan calgui) i expliqueu-ne el motiu.
• Ordeneu les millores de més a menys prioritat, tenint en compte l'impacte esperat i el cost o risc d'aplicar-les en un sistema que ja és en producció.