Cuando una tabla como orders o logs crece hasta cientos de millones de filas, los indices se hinchan, VACUUM tarda horas y "borra todo lo anterior a un ano" se convierte en un DELETE que tumba produccion. El particionado por rango lo resuelve: una tabla logica se divide fisicamente en trozos por un rango de valores, casi siempre una fecha.
PARTITION BY RANGE: la tabla padre y sus particiones
El particionado declarativo llego en PostgreSQL 10 y hoy es la herramienta estandar. Declaras una tabla padre con una clave de particion y luego enganchas particiones hijas, cada una con su propio rango FROM ... TO.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY,
user_id bigint NOT NULL,
amount numeric(12,2) NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
Detalles que importan:
FROM es inclusivo, TO es exclusivo. Los rangos no pueden solaparse.- La clave de particion debe formar parte de cada
UNIQUE y PRIMARY KEY. Por eso aqui la clave primaria es (id, created_at) y no solo id.
- Los indices creados en el padre se crean automaticamente en cada particion.
Partition pruning: el planificador lee solo lo necesario
La gran ganancia de velocidad es el pruning: cuando el WHERE restringe la clave de particion, el planificador descarta las particiones que no pueden coincidir con el rango.
EXPLAIN
SELECT sum(amount)
FROM orders
WHERE created_at >= '2025-03-01'
AND created_at < '2025-04-01';
El plan solo mencionara orders_2025; la particion de 2024 ni siquiera se abre. El pruning ocurre tanto al planificar como al ejecutar (para parametros y JOIN). Pero si la consulta nunca menciona created_at (por ejemplo, filtra solo por user_id), Postgres tiene que escanear todas las particiones. Esa es la razon clasica de "el particionado no sirvio de nada".
Eliminar datos viejos en milisegundos
La retencion es la funcionalidad estrella. Borrar un ano de filas con DELETE son horas de trabajo y un WAL inflado. Con particiones es una operacion de metadatos:
DROP TABLE orders_2024;
ALTER TABLE orders DETACH PARTITION orders_2024;
DROP TABLE orders_2024;
DETACH convierte la particion en una tabla independiente normal: puedes archivarla, moverla a almacenamiento barato o simplemente conservarla. La operacion inversa, ATTACH PARTITION, conecta una tabla ya lista como nueva particion; usala para backfills o para incorporar datos historicos.
ALTER TABLE orders ATTACH PARTITION orders_2026
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
La particion DEFAULT: una trampa de "el resto"
Si una fila no cae en ningun rango, el INSERT falla con error. La red de seguridad es una particion DEFAULT que absorbe todo lo demas:
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
Gotcha: la particion DEFAULT es comoda pero peligrosa. Si ya contiene filas de 2026, entonces hacer ATTACH de una nueva particion orders_2026 obliga a un escaneo completo de orders_default para probar que no hay solapamiento, y bajo un bloqueo. En series temporales conviene precrear las particiones futuras (con pg_partman o un cron) y mantener DEFAULT vacia como alarma de rangos faltantes.
MySQL y ClickHouse: en que se diferencian
- MySQL: tambien existe
PARTITION BY RANGE (...), pero la clave debe ser un entero o ir envuelta en una funcion como TO_DAYS(created_at); no hay DETACH, una particion vieja se quita con ALTER TABLE ... DROP PARTITION.
- ClickHouse: las particiones se definen con
PARTITION BY toYYYYMM(created_at) sobre el motor MergeTree; existen sobre todo para DROP PARTITION y manipular partes enteras, no para acelerar filtros, de eso se encarga la clave de orden ORDER BY.
En resumen: disena la clave de particion segun como filtras y como borras los datos. Para logs y pedidos casi siempre es una fecha, cortada por mes o ano, con las particiones futuras creadas con antelacion.
Cuando una tabla como
ordersologscrece hasta cientos de millones de filas, los indices se hinchan,VACUUMtarda horas y "borra todo lo anterior a un ano" se convierte en unDELETEque tumba produccion. El particionado por rango lo resuelve: una tabla logica se divide fisicamente en trozos por un rango de valores, casi siempre una fecha.PARTITION BY RANGE: la tabla padre y sus particiones
El particionado declarativo llego en PostgreSQL 10 y hoy es la herramienta estandar. Declaras una tabla padre con una clave de particion y luego enganchas particiones hijas, cada una con su propio rango
FROM ... TO.CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY, user_id bigint NOT NULL, amount numeric(12,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');Detalles que importan:
FROMes inclusivo,TOes exclusivo. Los rangos no pueden solaparse.UNIQUEyPRIMARY KEY. Por eso aqui la clave primaria es(id, created_at)y no soloid.Partition pruning: el planificador lee solo lo necesario
La gran ganancia de velocidad es el pruning: cuando el
WHERErestringe la clave de particion, el planificador descarta las particiones que no pueden coincidir con el rango.EXPLAIN SELECT sum(amount) FROM orders WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';El plan solo mencionara
orders_2025; la particion de 2024 ni siquiera se abre. El pruning ocurre tanto al planificar como al ejecutar (para parametros yJOIN). Pero si la consulta nunca mencionacreated_at(por ejemplo, filtra solo poruser_id), Postgres tiene que escanear todas las particiones. Esa es la razon clasica de "el particionado no sirvio de nada".Eliminar datos viejos en milisegundos
La retencion es la funcionalidad estrella. Borrar un ano de filas con
DELETEson horas de trabajo y un WAL inflado. Con particiones es una operacion de metadatos:-- Instantaneo: elimina una particion entera con sus datos e indices DROP TABLE orders_2024; -- Mas limpio: primero desengancha para poder archivar ALTER TABLE orders DETACH PARTITION orders_2024; -- ... COPY orders_2024 TO '/archive/orders_2024.csv' ... DROP TABLE orders_2024;DETACHconvierte la particion en una tabla independiente normal: puedes archivarla, moverla a almacenamiento barato o simplemente conservarla. La operacion inversa,ATTACH PARTITION, conecta una tabla ya lista como nueva particion; usala para backfills o para incorporar datos historicos.ALTER TABLE orders ATTACH PARTITION orders_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');La particion DEFAULT: una trampa de "el resto"
Si una fila no cae en ningun rango, el
INSERTfalla con error. La red de seguridad es una particionDEFAULTque absorbe todo lo demas:CREATE TABLE orders_default PARTITION OF orders DEFAULT;MySQL y ClickHouse: en que se diferencian
PARTITION BY RANGE (...), pero la clave debe ser un entero o ir envuelta en una funcion comoTO_DAYS(created_at); no hayDETACH, una particion vieja se quita conALTER TABLE ... DROP PARTITION.PARTITION BY toYYYYMM(created_at)sobre el motorMergeTree; existen sobre todo paraDROP PARTITIONy manipular partes enteras, no para acelerar filtros, de eso se encarga la clave de ordenORDER BY.En resumen: disena la clave de particion segun como filtras y como borras los datos. Para logs y pedidos casi siempre es una fecha, cortada por mes o ano, con las particiones futuras creadas con antelacion.