Particionament de taules
El particionament de taules permet dividir una taula gran en segments més petits (particions) que es gestionen independentment, millorant el rendiment de les consultes gràcies al partition pruning automàtic.
Per què partir taules?
Quan una taula supera els centenars de milions de files, apareixen tres problemes que el particionament resol directament:
Rendiment de consultes (partition pruning): Si les consultes filtren quasi sempre per un rang de dates o un valor de llista, el motor pot descartar completament les particions que no contenen dades rellevants i llegir només les que calen. Una consulta que sense particionament hauria d'escanejar 10 anys de logs ara escanejarà únicament el mes consultat.
Manteniment eficient: Eliminar un mes de dades antigues sense particionament requereix un DELETE que processa fila per fila, genera molta activitat al registre de transaccions i deixa espai mort pendent de recollir. Amb particionament, eliminar la partició sencera (per exemple amb DROP TABLE a PostgreSQL/MySQL/Oracle, o ALTER TABLE ... SWITCH a SQL Server) allibera l'espai pràcticament a l'instant, sense escanejar fila per fila.
Paral·lelisme: Els motors poden escanejar múltiples particions en paral·lel quan la configuració del servidor ho permet, accelerant consultes que necessiten llegir rangs amplis de dades. A PostgreSQL aquest comportament es controla amb el paràmetre max_parallel_workers_per_gather.
Els tres mètodes de particionament que es treballen en aquest apartat (RANGE, LIST i HASH) estan disponibles, amb variacions de sintaxi i suport, a PostgreSQL, MySQL/MariaDB, SQL Server i Oracle.
Particionament per rang (RANGE)
És el més habitual per a sèries temporals, logs i qualsevol dada amb una dimensió ordenada natural.
-- Taula pare: defineix l'esquema i el mètode de particionament
CREATE TABLE logs (
id bigserial,
ts timestamp NOT NULL,
nivell text NOT NULL,
servei text,
missatge text
) PARTITION BY RANGE (ts);
-- Particions mensuals per a 2026
CREATE TABLE logs_2026_01 PARTITION OF logs
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE logs_2026_02 PARTITION OF logs
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE logs_2026_03 PARTITION OF logs
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- La partició DEFAULT recull qualsevol fila que no encaixi en cap altra
CREATE TABLE logs_default PARTITION OF logs DEFAULT;
-- A MySQL/MariaDB el particionament és nadiu dins el propi CREATE TABLE:
-- no hi ha taules "filla" separades, és tot una sola sentència.
CREATE TABLE logs (
id BIGINT AUTO_INCREMENT,
ts DATETIME NOT NULL,
nivell VARCHAR(20),
missatge TEXT,
PRIMARY KEY (id, ts)
)
PARTITION BY RANGE (TO_DAYS(ts)) (
PARTITION p_2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
PARTITION p_2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
PARTITION p_2026_03 VALUES LESS THAN (TO_DAYS('2026-04-01')),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
Clau primària i columna de particionament a MySQL
A MySQL, igual que a PostgreSQL, la clau primària (o qualsevol clau única) ha d'incloure totes les columnes usades a PARTITION BY. Per això PRIMARY KEY (id, ts) inclou ts encara que id ja seria suficient per identificar la fila.
-- SQL Server requereix dos passos previs: una partition function
-- (defineix els punts de tall) i un partition scheme (assigna cada
-- partició a un filegroup). Després la taula es crea "ON" l'esquema.
CREATE PARTITION FUNCTION pf_logs_mensual (DATETIME)
AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01', '2026-04-01');
CREATE PARTITION SCHEME ps_logs_mensual
AS PARTITION pf_logs_mensual ALL TO ([PRIMARY]);
CREATE TABLE logs (
id BIGINT IDENTITY(1,1),
ts DATETIME NOT NULL,
nivell NVARCHAR(20),
missatge NVARCHAR(MAX)
) ON ps_logs_mensual (ts);
-- Sintaxi nativa similar a MySQL conceptualment, dins el CREATE TABLE.
CREATE TABLE logs (
id NUMBER GENERATED ALWAYS AS IDENTITY,
ts DATE NOT NULL,
nivell VARCHAR2(20),
missatge VARCHAR2(4000)
)
PARTITION BY RANGE (ts) (
PARTITION p_2026_01 VALUES LESS THAN (DATE '2026-02-01'),
PARTITION p_2026_02 VALUES LESS THAN (DATE '2026-03-01'),
PARTITION p_2026_03 VALUES LESS THAN (DATE '2026-04-01'),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
Oracle també ofereix l'interval partitioning, que crea automàticament noves particions a mesura que arriben dades fora del rang definit, sense necessitat de DDL manual:
CREATE TABLE logs (
id NUMBER GENERATED ALWAYS AS IDENTITY,
ts DATE NOT NULL,
nivell VARCHAR2(20),
missatge VARCHAR2(4000)
)
PARTITION BY RANGE (ts)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION p_inicial VALUES LESS THAN (DATE '2026-02-01')
);
Això és una millora respecte a la necessitat de crear particions futures manualment (vegeu més avall la secció Creació automàtica de particions).
Llicenciament a Oracle
El particionament a Oracle requereix la Partitioning option, llicenciada a part dins Oracle Enterprise Edition. No està inclosa a Oracle Standard Edition.
El rang és semiobert: [inici, fi)
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01') inclou el dia 1 de gener però exclou el dia 1 de febrer. Assegureu-vos que les particions siguin contigues i no se solapin. Aquesta mateixa semàntica semioberta s'aplica a VALUES LESS THAN a MySQL i Oracle, i a RANGE RIGHT a SQL Server.
Als quatre motors, els índexs, les restriccions i les insercions sobre una taula particionada es gestionen de manera automàtica: cada INSERT es redirigeix tot sol a la partició que correspon segons el valor de la columna de particionament, i les consultes amb filtre sobre aquesta columna es beneficien del partition pruning (vegeu la secció Partition pruning en acció més avall per a la sintaxi de verificació de cada motor).
Particionament per llista (LIST)
Ideal quan les consultes filtren per un conjunt finit de valors discrets com ara el país, la categoria o l'estat.
CREATE TABLE comandes (
id bigserial,
data date NOT NULL,
client_id int NOT NULL,
pais text NOT NULL,
total numeric(12, 2)
) PARTITION BY LIST (pais);
-- Una partició per als grans mercats
CREATE TABLE comandes_es PARTITION OF comandes
FOR VALUES IN ('ES');
CREATE TABLE comandes_fr PARTITION OF comandes
FOR VALUES IN ('FR');
CREATE TABLE comandes_de PARTITION OF comandes
FOR VALUES IN ('DE');
-- Partició per a tots els altres països
CREATE TABLE comandes_altres PARTITION OF comandes
FOR VALUES IN ('IT', 'PT', 'NL', 'BE', 'AT', 'PL');
-- Partició DEFAULT per a qualsevol país no llistat
CREATE TABLE comandes_default PARTITION OF comandes DEFAULT;
CREATE TABLE comandes (
id BIGINT AUTO_INCREMENT,
data DATE NOT NULL,
client_id INT NOT NULL,
pais VARCHAR(2) NOT NULL,
total DECIMAL(12,2),
PRIMARY KEY (id, pais)
)
PARTITION BY LIST COLUMNS (pais) (
PARTITION p_es VALUES IN ('ES'),
PARTITION p_fr VALUES IN ('FR'),
PARTITION p_de VALUES IN ('DE'),
PARTITION p_altres VALUES IN ('IT', 'PT', 'NL', 'BE', 'AT', 'PL')
);
LIST vs LIST COLUMNS a MySQL
El PARTITION BY LIST "clàssic" (sense COLUMNS) exigeix una expressió numèrica per a cada valor. PARTITION BY LIST COLUMNS (disponible des de MySQL/MariaDB 5.5+) permet valors de text directament, com a l'exemple anterior, i és l'opció recomanada per a columnes com pais.
SQL Server no té un mètode de partició LIST natiu: la partition function només admet RANGE. Per emular una partició per valors discrets cal crear una "range" amb un punt de tall just abans de cada valor ordenat, cosa molt menys natural que PARTITION BY LIST:
-- Emulació de LIST mitjançant RANGE: cal ordenar els valors discrets
-- alfabèticament i definir un punt de tall abans de cada un.
CREATE PARTITION FUNCTION pf_comandes_pais (VARCHAR(2))
AS RANGE RIGHT FOR VALUES ('DE', 'ES', 'FR');
CREATE PARTITION SCHEME ps_comandes_pais
AS PARTITION pf_comandes_pais ALL TO ([PRIMARY]);
CREATE TABLE comandes (
id BIGINT IDENTITY(1,1),
data DATE NOT NULL,
client_id INT NOT NULL,
pais VARCHAR(2) NOT NULL,
total DECIMAL(12,2)
) ON ps_comandes_pais (pais);
Aquesta emulació té limitacions reals: agrupar diversos països en una mateixa partició (com comandes_altres a PostgreSQL) no és directe amb RANGE, i cal vigilar l'ordre alfabètic dels punts de tall. No es tracta d'una limitació de detall sinó d'una mancança funcional real respecte als altres motors.
CREATE TABLE comandes (
id NUMBER GENERATED ALWAYS AS IDENTITY,
data DATE NOT NULL,
client_id NUMBER NOT NULL,
pais VARCHAR2(2) NOT NULL,
total NUMBER(12,2)
)
PARTITION BY LIST (pais) (
PARTITION p_es VALUES ('ES'),
PARTITION p_fr VALUES ('FR'),
PARTITION p_de VALUES ('DE'),
PARTITION p_altres VALUES ('IT', 'PT', 'NL', 'BE', 'AT', 'PL'),
PARTITION p_default VALUES (DEFAULT)
);
Als quatre motors, una consulta que filtri per igualtat sobre la columna de particionament (per exemple WHERE pais = 'ES') accedeix únicament a la partició corresponent gràcies al partition pruning, sense necessitat d'escanejar la resta.
Particionament per hash (HASH)
Quan no hi ha cap columna amb valors discrets ni ordenació natural, el hash distribueix les files uniformement entre un nombre fix de particions. S'usa per a balancejar la càrrega d'escriptura o per a repartir taules molt grans sense un criteri temporal o categòric evident.
CREATE TABLE sessions (
id uuid NOT NULL,
usuari_id int NOT NULL,
dades jsonb,
creat_en timestamp DEFAULT now()
) PARTITION BY HASH (usuari_id);
-- 4 particions amb distribució uniforme
CREATE TABLE sessions_0 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
SQL Server no té particionament HASH natiu. S'emula amb una columna calculada que aplica mòdul sobre la columna de partició, combinada amb una partition function RANGE sobre aquesta columna calculada:
CREATE PARTITION FUNCTION pf_hash_4 (INT) AS RANGE LEFT FOR VALUES (0, 1, 2);
CREATE PARTITION SCHEME ps_hash_4 AS PARTITION pf_hash_4 ALL TO ([PRIMARY]);
CREATE TABLE sessions (
id UNIQUEIDENTIFIER NOT NULL,
usuari_id INT NOT NULL,
dades NVARCHAR(MAX),
creat_en DATETIME DEFAULT GETDATE(),
usuari_mod4 AS (usuari_id % 4) PERSISTED
) ON ps_hash_4 (usuari_mod4);
Aquesta no és una funcionalitat HASH nativa sinó una emulació manual: la distribució depèn que el mòdul de usuari_id es comporti de manera prou uniforme, i cal mantenir la columna calculada sincronitzada amb el nombre de particions.
Hash i partition pruning
El partition pruning amb HASH només funciona quan el filtre és una igualtat exacta sobre la columna de particionament (WHERE usuari_id = 42). Consultes amb rangs o sense filtre llegiran totes les particions. Aquest comportament és comú a PostgreSQL, MySQL/MariaDB i Oracle; a SQL Server depèn de si el predicat es pot traduir a la columna calculada del mòdul.
Partition pruning en acció
El partition pruning (o partition elimination a SQL Server) és el mecanisme pel qual el planificador de consultes elimina del pla d'execució les particions que no poden contenir files rellevants per a la consulta, llegint només les que calen. Cada motor té el seu propi terme i mecanisme de verificació per a aquest comportament:
El terme és partition pruning. Es pot veure clarament amb EXPLAIN:
-- Amb partition pruning actiu (per defecte)
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*) FROM logs
WHERE ts BETWEEN '2026-03-01' AND '2026-03-31 23:59:59';
Sortida rellevant (simplificada):
Aggregate (cost=... rows=1 ...)
-> Seq Scan on logs_2026_03
Filter: ((ts >= '2026-03-01') AND (ts <= '2026-03-31 23:59:59'))
Partition pruning: logs_2026_01, logs_2026_02, logs_default (skipped)
PostgreSQL ha eliminat del pla totes les particions excepte logs_2026_03. Per desactivar-lo temporalment i comparar:
El terme també és partition pruning. Es verifica amb EXPLAIN PARTITIONS SELECT ...;, que mostra la columna partitions amb les particions realment accedides:
EXPLAIN PARTITIONS
SELECT COUNT(*) FROM logs
WHERE ts BETWEEN '2026-03-01' AND '2026-03-31 23:59:59';
A partir de MySQL 8.0, l'EXPLAIN normal (sense PARTITIONS) ja inclou aquesta informació de partició al pla.
El terme aquí és partition elimination. Es pot verificar consultant sys.dm_db_partition_stats, activant l'opció "Actual Partition Count" a l'execution plan gràfic d'SSMS, o amb SET STATISTICS IO ON per veure quantes particions s'han llegit realment:
El terme també és partition pruning. Es verifica amb EXPLAIN PLAN i mirant les columnes Pstart/Pstop de DBMS_XPLAN.DISPLAY, que indiquen el rang de particions accedides:
Sub-particionament
El sub-particionament (partir cada partició en sub-particions) és útil per a taules amb molts anys d'historial on, per exemple, cal partir per any i després per mes dins de cada any. El suport varia molt entre motors:
PostgreSQL suporta particions de particions de manera nativa: simplement s'afegeix PARTITION BY també a la definició de la partició filla.
-- Nivell 1: particionament per any
CREATE TABLE metriques (
ts timestamp NOT NULL,
sensor text NOT NULL,
valor numeric
) PARTITION BY RANGE (ts);
CREATE TABLE metriques_2025 PARTITION OF metriques
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
PARTITION BY RANGE (ts); -- <-- sub-particionament
CREATE TABLE metriques_2026 PARTITION OF metriques
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
PARTITION BY RANGE (ts);
-- Nivell 2: particionament per mes dins de 2026
CREATE TABLE metriques_2026_01 PARTITION OF metriques_2026
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE metriques_2026_02 PARTITION OF metriques_2026
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
El partition pruning actua a tots dos nivells: primer descarta l'any i després el mes.
MySQL/MariaDB no suporta sub-particionament: només es pot definir un nivell de PARTITION BY. L'exemple de metriques (any + mes) no és reproduïble directament; caldria simular-ho combinant la granularitat en una sola columna, per exemple particionant directament per mes en lloc de per any+mes.
SQL Server tampoc té sub-particionament natiu: una partition function només defineix una dimensió de partició per taula. Igual que a MySQL, cal combinar la granularitat en una sola columna de partició (per exemple, partir directament per mes) en lloc de modelar dos nivells independents.
Oracle ho anomena composite partitioning i el suporta nativament amb qualsevol combinació (PARTITION BY RANGE (...) SUBPARTITION BY HASH (...), RANGE-LIST, etc.):
CREATE TABLE metriques (
ts DATE NOT NULL,
sensor VARCHAR2(50) NOT NULL,
valor NUMBER
)
PARTITION BY RANGE (ts)
SUBPARTITION BY RANGE (ts)
SUBPARTITION TEMPLATE (
SUBPARTITION sp_q1 VALUES LESS THAN (DATE '2026-04-01'),
SUBPARTITION sp_q2 VALUES LESS THAN (DATE '2026-07-01'),
SUBPARTITION sp_q3 VALUES LESS THAN (DATE '2026-10-01'),
SUBPARTITION sp_q4 VALUES LESS THAN (DATE '2027-01-01')
)
(
PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01'),
PARTITION p_2026 VALUES LESS THAN (DATE '2027-01-01')
);
Índexs sobre taules particionades
El comportament dels índexs sobre taules particionades varia significativament entre motors:
Quan es crea un índex sobre la taula pare, PostgreSQL el propaga automàticament a totes les particions existents i futures com a índexs locals independents:
-- Índex global: es crea a la taula pare i es replica a totes les particions
CREATE INDEX idx_logs_servei ON logs (servei);
-- Verificació: cada partició té el seu propi índex físic
SELECT indexname, tablename
FROM pg_indexes
WHERE tablename LIKE 'logs_%'
ORDER BY tablename;
Es pot crear un índex únicament sobre una partició concreta si es necessita una estratègia diferent per a un rang específic:
Cada partició té el seu propi índex físic local de manera automàtica. No existeix el concepte d'índex "global" abans de MySQL 8.0, i fins i tot a partir de 8.0 els índexs globals tenen suport molt limitat:
Un índex creat sobre l'esquema de partició ("aligned index") es propaga automàticament a totes les particions:
Claus primàries i particionament
La clau primària d'una taula particionada ha d'incloure la columna de particionament. Per exemple, PRIMARY KEY (id, ts) en lloc de PRIMARY KEY (id). Això és una limitació actual de PostgreSQL per garantir la unicitat localment a cada partició, i s'aplica també a MySQL/MariaDB (vegeu la nota a la secció RANGE). Oracle i SQL Server són més flexibles en aquest punt, però en general es recomana incloure sempre la columna de particionament a la clau primària o única per evitar sorpreses.
Creació automàtica de particions
En entorns de producció, les particions futures s'han de crear abans que arribin les dades. L'enfocament varia molt segons el motor:
Hi ha dues estratègies habituals. Opció A: cronjob diari/setmanal que executa una funció SQL:
-- Funció per crear la partició del mes vinent si no existeix
CREATE OR REPLACE FUNCTION crea_particio_logs_seguent()
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
inici date := date_trunc('month', now() + interval '1 month');
fi date := inici + interval '1 month';
nom text := 'logs_' || to_char(inici, 'YYYY_MM');
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class WHERE relname = nom
) THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF logs FOR VALUES FROM (%L) TO (%L)',
nom, inici, fi
);
RAISE NOTICE 'Partició creada: %', nom;
END IF;
END;
$$;
-- Programar-la amb pg_cron (extensió)
-- SELECT cron.schedule('0 6 25 * *', 'SELECT crea_particio_logs_seguent()');
Opció B: extensió pg_partman, que automatitza completament la gestió del cicle de vida de les particions (creació, retenció, arxivat).
MySQL/MariaDB no té cap mecanisme natiu d'automatització de creació de particions. Cal recórrer a un script extern (per exemple, en bash o Python) programat amb cron del sistema operatiu, o a un EVENT del planificador d'esdeveniments de MySQL que executi periòdicament un ALTER TABLE ... ADD PARTITION:
SQL Server tampoc té automatització nativa. Cal un job de SQL Server Agent programat (típicament mensual) que executi un script T-SQL amb ALTER PARTITION FUNCTION ... SPLIT RANGE (...) per afegir el nou punt de tall a la partition function existent, seguit d'ALTER PARTITION SCHEME si cal assignar un nou filegroup.
Oracle és l'únic dels quatre amb automatització totalment nativa mitjançant l'interval partitioning (vegeu la secció Particionament per rang (RANGE) més amunt): un cop definit INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) a la creació de la taula, Oracle crea les particions noves automàticament a mesura que arriben dades fora del rang definit, sense necessitat de cap job ni script addicional.
Quan NO particionar
El particionament no és gratuït i pot empitjorar el rendiment en alguns escenaris:
- Taules petites (menys de 10-20 milions de files): un escaneig complet de la taula (Seq Scan a PostgreSQL, Table Scan als altres motors) ben indexada és més ràpid que la gestió de múltiples particions.
- Consultes cross-partition freqüents: si la majoria de consultes no filtren per la columna de particionament, el motor llegirà totes les particions i el pla serà més complex sense guany.
- JOINs entre la taula particionada i moltes altres: el planificador ha de considerar totes les combinacions de particions, augmentant el temps de planificació.
- Moltes particions petites (centenars): l'overhead de planificació creix linearment. Amb 1000 particions, fins i tot una consulta senzilla pot tardar dècimes de segon només en la fase de planificació.
Aquestes mateixes consideracions apliquen igualment a MySQL/MariaDB, SQL Server i Oracle: taules petites, consultes cross-partition freqüents i un nombre excessiu de particions petites degraden el rendiment independentment del motor. A Oracle cal afegir-hi un matís addicional: el particionament té un cost de llicència propi (la Partitioning option), de manera que la decisió de particionar no és només tècnica sinó també econòmica.
AC5074/02/02 — Miniactivitat
Una entitat bancària té una taula transaccions amb les columnes (id bigserial, data_operacio date, compte_origen text, compte_desti text, import numeric, tipus text, estat text). La taula acumula aproximadament 50 milions de transaccions per any i s'ha de mantenir un historial de 5 anys en línia.
Les consultes principals són: cerca per data_operacio (rang mensual o anual), cerca per compte_origen dins d'un mes concret, i informes mensuals agregats per tipus.
Dissenyeu l'esquema de particionament complet: mètode, columna, granularitat, política de retenció i índexs necessaris. Justifiqueu cada decisió i lliureu el DDL complet.