Particionamiento en ClickHouse: Estrategias y Operaciones

Particionamiento en ClickHouse: Cómo gestionar datos a nivel de carpeta

1. Por qué particionar — Aislamiento de datos por períodos de tiempo

Imagina que almacenas todas las apuestas de un casino online de los últimos tres años. Son miles de millones de filas. El dueño del negocio dice de repente: "Solo necesitamos datos de los últimos 6 meses; borra todo lo anterior".

En una base de datos normal, escribirías DELETE FROM bets WHERE created_at < '2025-01-01'. En ClickHouse, esa consulta tardaría... mucho tiempo. Porque DELETE en ClickHouse no es una eliminación instantánea, sino una reescritura asíncrona de las partes de datos sin las filas marcadas.

Pero hay una forma de hacer la eliminación instantánea — el particionamiento. Si los datos se dividen en particiones (bloques lógicos, cada uno almacenado físicamente en una carpeta separada en el disco), entonces DROP PARTITION elimina una carpeta entera en milisegundos. No es necesario escanear filas, ni reescribir — solo rm -rf a nivel del sistema de archivos.

Analogía de la vida real: Imagina que tienes archivos en papel de varios años. Están guardados en cajas, cada caja para un mes. Si necesitas eliminar los datos de enero de 2024, simplemente llevas la caja etiquetada como "Enero 2024" al contenedor de basura. No necesitas revisar cada hoja. Las particiones en ClickHouse son esas cajas.

¿Para qué más sirve el particionamiento?

• Copias de seguridad — puedes congelar (FREEZE) solo la partición requerida.
• Movimiento de datos — datos calientes (consultados con frecuencia) en SSD rápidos, datos fríos (raros) en HDD lentos o almacenamiento en la nube (S3).
• Aceleración de SELECT — si la cláusula WHERE incluye la columna de particionamiento, ClickHouse sabe inmediatamente qué carpetas leer y cuáles ignorar.

2. PARTITION BY — Sintaxis y ejemplos

El particionamiento se define al crear una tabla usando PARTITION BY. El patrón más común es dividir por fecha.

-- Crear una tabla de apuestas con particiones mensuales
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)   -- Partición = año + mes, ej. 202512
ORDER BY (user_id, created_at);

¿Qué está pasando aquí?

• toYYYYMM(created_at) — una función de ClickHouse que convierte una fecha-hora 2025-12-15 14:30:00 en el número 202512 (año 2025, mes 12). Todas las filas con el mismo 202512 van a una partición.
• El nombre de la partición es solo una cadena. ClickHouse creará una carpeta en el disco con un nombre como 202512_1_1_0 (los detalles no son importantes, pero internamente está vinculado a 202512).

Otras opciones de particionamiento basadas en tiempo:

-- Por día (¡cuidado! puede crear demasiadas particiones)
PARTITION BY toYYYYMMDD(created_at)   -- 20251215

-- Por hora (muy granular, casi nunca necesario)
PARTITION BY toStartOfHour(created_at)   -- 2025-12-15 14:00:00

-- Por semana
PARTITION BY toWeek(created_at)   -- Número de semana en el año

-- Por año
PARTITION BY toYear(created_at)   -- 2025

¿Qué pasa si no especificas PARTITION BY? ClickHouse crea una única partición llamada all. Todos los datos residen en una carpeta. Solo puedes eliminar mediante DELETE (lento) o TRUNCATE (todo de una vez). Para la mayoría de las tablas de series temporales, esto es una mala idea.

3. Estrategia de selección de particiones — El punto óptimo

¿Por qué las particiones no deben ser demasiado pequeñas?

A ClickHouse no le gustan demasiadas particiones (se recomienda no más de 1000–2000). Porque:

• Cada partición es una carpeta separada con metadatos.
• Al insertar datos, ClickHouse puede crear partes en diferentes particiones.
• Las fusiones en segundo plano trabajan dentro de una partición, no entre ellas.
• La tabla system.parts (metadatos sobre las partes) será enorme.

¿Qué pasa si particionas por hora con una carga de 1 millón de filas por hora? En un año, obtienes 365 * 24 = 8760 particiones. Eso ya es malo. En cada inserción, ClickHouse abrirá descriptores de archivo para miles de carpetas. SELECT con filtro de fecha será rápido, pero las operaciones del sistema (copias de seguridad, eliminación, listado de particiones) se ralentizarán.

¿Por qué las particiones no deben ser demasiado grandes?

Si una partición es un año, entonces DROP PARTITION elimina todo ese año al instante. Es conveniente, pero:

• Incluso un SELECT de un solo día tendrá que escanear toda la partición anual.
• No puedes eliminar rápidamente un solo mes — tendrías que eliminar el año o usar DELETE (lento).
• Mover datos "calientes" y "fríos" (ej., el último mes en SSD, el resto en HDD) es imposible a nivel mensual.

Regla general

Carga	Tamaño de partición	Ejemplo
Miles de millones de filas por mes	Día (toYYYYMMDD)	Datos de sensores IoT, logs de CDN
Cientos de millones por mes	Mes (toYYYYMM)	Apuestas de casino, transacciones
Decenas de millones por mes	Mes o trimestre	Analítica de ventas
Miles de filas por mes	Año	Datos de referencia que cambian lentamente

Criterio principal: Después de particionar, deberías tener entre 100 y 1000 particiones por servidor. Si tienes 10,000 particiones, te has pasado.

4. Visualización de particiones — system.parts

Para ver qué particiones existen en una tabla y cuántos datos contienen, usa la tabla del sistema system.parts.

-- Ver todas las particiones de la tabla bets
SELECT 
    partition,                          -- Nombre de la partición (ej., 202512)
    name,                               -- Nombre específico de la parte
    rows,                               -- Número de filas en esta parte
    bytes_on_disk,                      -- Tamaño en disco en bytes
    modification_time,                  -- Última hora de modificación
    active                              -- Si la parte está activa (1) o eliminada (0)
FROM system.parts
WHERE table = 'bets' AND active = 1    -- solo partes activas
ORDER BY partition DESC;

Desglose:

• partition — lo que especificaste en PARTITION BY. Útil para agrupar.
• name — nombre interno de la parte de datos, contiene el número de partición y la versión.
• active = 1 — la parte se usa para lectura. Si es 0, la parte está marcada para eliminación pero aún existe físicamente.
• bytes_on_disk — muestra cuánto espacio consume la partición.

¿Por qué puede haber varias partes en una partición? Porque insertas datos en lotes. ClickHouse no los fusiona instantáneamente. Dentro de una misma partición (202512), puede haber 5–10 partes pequeñas que eventualmente se fusionarán en una grande.

Consulta agregada — suma por partición:

SELECT 
    partition,
    sum(rows) AS total_rows,
    sum(bytes_on_disk) AS total_bytes,
    formatReadableSize(sum(bytes_on_disk)) AS human_size
FROM system.parts
WHERE table = 'bets' AND active = 1
GROUP BY partition
ORDER BY partition DESC;

5. Operaciones con particiones — DROP, DETACH, ATTACH, FREEZE

Esta es la razón principal por la que el particionamiento es tan popular. Puedes hacer cosas con particiones que no son posibles a nivel de fila.

DROP PARTITION — Eliminación instantánea

-- Eliminar todos los datos de diciembre de 2025
ALTER TABLE bets DROP PARTITION '202512';

-- Eliminar datos anteriores a 90 días (dinámicamente)
-- Primero calcular el nombre de la partición para hace 90 días
ALTER TABLE bets DROP PARTITION toYYYYMM(today() - interval 90 day);

Después de este comando, la carpeta de la partición desaparece del disco. La operación toma milisegundos, independientemente de cuántas filas hubiera dentro — un millón o mil millones.

¿Por qué es seguro? Porque las particiones están aisladas. Eliminar una no afecta a las demás.

DETACH PARTITION — Desactivación temporal

-- Desvincular la partición (los datos permanecen en disco pero no están disponibles para SELECT)
ALTER TABLE bets DETACH PARTITION '202512';

La partición se mueve a la subcarpeta detached. No participa en consultas, pero puedes recuperarla.

¿Cuándo se necesita?

• Sospechas de corrupción de datos y quieres ocultarla temporalmente.
• Quieres copiar la partición a otro servidor (luego ATTACH en el nuevo servidor).

ATTACH PARTITION — Restaurar una partición desvinculada

-- Recuperar la partición (si está en la carpeta detached)
ALTER TABLE bets ATTACH PARTITION '202512';

FREEZE PARTITION — Copia de seguridad instantánea

-- Crear una copia de seguridad de la partición de diciembre de 2025
ALTER TABLE bets FREEZE PARTITION '202512';

ClickHouse crea enlaces duros a los archivos de la partición en la carpeta shadow/. Esto no es copiar datos (rápido), solo crear punteros adicionales a los mismos bloques en disco. Luego puedes copiar shadow a otro servidor.

¿Por qué es mejor que una copia de seguridad normal? Porque no necesitas detener las escrituras y no desperdicia espacio en duplicación de datos.

MOVE PARTITION — Para almacenamiento por niveles

Más detalles en la sección 6.

6. MOVE PARTITION — Almacenamiento por niveles (SSD → HDD → S3)

En ClickHouse, puedes configurar múltiples niveles de almacenamiento con diferente costo y velocidad. Por ejemplo:

• Datos calientes (último mes) — en SSD NVMe rápidos
• Datos templados (2–6 meses) — en HDD normales
• Datos fríos (más de 6 meses) — en S3 en la nube (barato pero lento)

Las particiones te permiten mover datos entre estos niveles con un solo comando.

Configuración en config.xml (simplificado):

<storage_configuration>
    <disks>
        <ssd>
            <path>/mnt/ssd/clickhouse/</path>
        </ssd>
        <hdd>
            <path>/mnt/hdd/clickhouse/</path>
        </hdd>
    </disks>
    <policies>
        <hot_to_cold>
            <volumes>
                <hot>
                    <disk>ssd</disk>
                </hot>
                <cold>
                    <disk>hdd</disk>
                </cold>
            </volumes>
        </hot_to_cold>
    </policies>
</storage_configuration>

Mover una partición de SSD a HDD:

-- Datos de enero de 2025 (ya no necesitan ser rápidos) mover a HDD
ALTER TABLE bets MOVE PARTITION '202501' TO VOLUME 'cold';

¿Qué sucede? ClickHouse mueve físicamente la carpeta de la partición de SSD a HDD. SELECT sigue funcionando pero será un poco más lento (debido al HDD). DROP PARTITION sigue siendo rápido.

Analogía: Mueves carpetas viejas de un archivador rápido junto a ti a un archivador de archivo distante. El acceso sigue siendo posible, pero lleva más tiempo llegar allí.

7. Particionamiento por múltiples columnas

A veces necesitas dividir los datos no solo por tiempo sino también por otra dimensión. Por ejemplo, tienes una plataforma multimarca (varios casinos en una misma base de datos). Quieres eliminar datos antiguos por separado para cada marca y posiblemente almacenarlos en discos diferentes.

-- Tabla de apuestas para 5 marcas, particiones por mes Y marca
CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,          -- 1 = casino_A, 2 = casino_B, ...
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
PARTITION BY (toYYYYMM(created_at), brand_id)   -- ¡Dos columnas!
ORDER BY (brand_id, user_id, created_at);

Cómo funciona:

• Cada combinación única de (año+mes, brand_id) se convierte en una partición separada.
• Para diciembre de 2025 y marca 1 — partición ('202512', 1).
• Para diciembre de 2025 y marca 2 — partición ('202512', 2).

Por qué es útil:

• DROP PARTITION puede eliminar datos de una marca específica en un mes específico.
• MOVE PARTITION puede mover datos antiguos de la marca A a HDD, mientras mantiene la marca B (premium) en SSD.

Cómo eliminar una partición para una marca específica:

-- Eliminar datos de casino_A para diciembre de 2025
ALTER TABLE bets_multi_brand DROP PARTITION ('202512', 1);

Pero hay un matiz: El nombre de la partición ahora es una tupla. Cuando se ve en system.parts, aparecerá como ('202512', '1'). Eso es normal.

8. Cómo afecta el particionamiento al rendimiento

Impacto en INSERT

Al insertar datos, ClickHouse evalúa la expresión PARTITION BY y dirige la fila a la partición correspondiente. Si tienes 1000 particiones, es rápido. Si tienes 100,000, cada inserción debe determinar la carpeta correcta, ralentizando las escrituras.

¿Qué pasa si un solo INSERT contiene filas de diferentes particiones? ClickHouse las distribuye en diferentes carpetas — eso es normal, pero un poco más lento que si todas las filas fueran de una partición.

Consejo: Si insertas datos en lotes (ej., una vez por minuto), intenta mantener las filas de un lote dentro de un rango de tiempo corto. Así caerán en una o dos particiones, haciendo la inserción más rápida.

Impacto en SELECT

El particionamiento acelera SELECT solo si la cláusula WHERE incluye una condición sobre la columna de particionamiento. ClickHouse usa las particiones para poda aproximada: comprueba qué particiones son necesarias y solo lee sus carpetas.

-- RÁPIDO: la partición descarta todos los datos fuera de diciembre de 2025
SELECT count() FROM bets 
WHERE created_at BETWEEN '2025-12-01' AND '2025-12-31';

-- LENTO: la partición no se usa, escanea todas las particiones
SELECT count() FROM bets 
WHERE amount > 1000;   -- sin filtro en created_at

¿Por qué no particionar siempre de forma muy fina? Porque:

• Leer de 1000 particiones pequeñas (ej., por día durante 3 años) puede ser más lento que de 36 grandes (por mes) si la consulta cubre un rango amplio. ClickHouse abre un descriptor de archivo por partición.
• Los índices (clave primaria) suelen ser más efectivos que un particionamiento muy fino.

Regla general: Optimiza primero ORDER BY (índice), luego el particionamiento. Las particiones son para la gestión de datos (DROP, MOVE), no solo para acelerar SELECT.

9. Error común: Particionamiento demasiado granular

Este es el punto de dolor más frecuente para los principiantes en ClickHouse.

Ejemplo de enfoque incorrecto:

-- MALO: particiones diarias para una tabla con 10 millones de filas por día
CREATE TABLE logs_bad
(
    created_at DateTime,
    message String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(created_at)   -- ¡Cuidado!
ORDER BY created_at;

En 3 años, obtienes 365 * 3 = 1095 particiones. Eso aún es tolerable (pero al límite). En 10 años — 3650 particiones, que es malo. El sistema comenzará a ralentizarse.

Peor aún — por hora con una carga de 1 millón de registros por hora: En un año, 8760 particiones. En cada inserción, ClickHouse buscará o creará una carpeta para la hora actual. SELECT * FROM system.parts tardará segundos. Las fusiones en segundo plano se atascarán.

Señales de que tienes demasiadas particiones:

1. La consulta SELECT * FROM system.parts WHERE table = 'mytable' tarda más de 1 segundo.
2. Los logs de ClickHouse muestran advertencias: Too many parts (300+).
3. ALTER TABLE ... DROP PARTITION no funciona al instante sino que tarda varios segundos.

¿Qué hacer si ya has particionado demasiado fino? Puedes fusionar particiones mediante ALTER TABLE ... MODIFY PARTITION BY ..., pero es complejo. Es más fácil crear una nueva tabla con el particionamiento adecuado, migrar los datos y renombrar.

10. Script para eliminación automática de particiones antiguas

En la práctica, necesitas conservar datos solo, por ejemplo, los últimos 90 días. Recordarte cada mes eliminar particiones antiguas no es una opción. Necesitas automatización.

Opción 1: Consulta periódica mediante cron o tarea

-- Eliminar todas las particiones anteriores a 90 días
-- Ejecutar una vez al día a las 3:00 AM
ALTER TABLE bets DROP PARTITION WHERE partition < toYYYYMM(today() - interval 90 day);

Cómo funciona:

• toYYYYMM(today() - interval 90 day) — calcula el nombre de la partición para la fecha de hace 90 días. Por ejemplo, si hoy es 2026-06-11, entonces hace 90 días es 2026-03-13, toYYYYMM da 202603.
• partition < 202603 — elimina todas las particiones con nombres 202602, 202601, 202512, etc.

Importante: Esta consulta funciona solo si los nombres de partición son números (año+mes). Para nombres de cadena (ej., '2025-12'), necesitas una comparación diferente.

Opción 2: Script más seguro usando listas almacenadas

-- Encontrar lista de particiones anteriores a N días
SELECT partition
FROM system.parts
WHERE table = 'bets' 
  AND active = 1
  AND toDate(parseDateTimeBestEffort(partition)) < today() - interval 90 day
GROUP BY partition;

-- Para cada partición encontrada, ejecutar DROP (desde un script externo, no directamente en SQL)

¿Por qué no hacer DROP PARTITION WHERE partition < ... con cálculo dinámico? Porque si la tabla tiene particiones con nombres no estándar (ej., 'all' o creadas manualmente), la condición podría comportarse inesperadamente. Mejor primero ver la lista.

Ejemplo completo de script en Bash / Python:

#!/bin/bash
# Eliminar particiones anteriores a 90 días para la tabla bets

CLICKHOUSE_CLIENT="clickhouse-client --host localhost"
TABLE="bets"
DAYS_TO_KEEP=90

# Calcular la fecha límite
BORDER_DATE=$(date -d "today - $DAYS_TO_KEEP days" +%Y%m)

# Obtener lista de particiones anteriores a la fecha límite
PARTITIONS=$($CLICKHOUSE_CLIENT --query="
    SELECT DISTINCT partition 
    FROM system.parts 
    WHERE table = '$TABLE' 
      AND active = 1 
      AND partition < '$BORDER_DATE'
      AND partition != 'all'
    FORMAT TabSeparated
")

# Eliminar cada partición
for PARTITION in $PARTITIONS; do
    echo "Eliminando partición $PARTITION de $TABLE"
    $CLICKHOUSE_CLIENT --query="ALTER TABLE $TABLE DROP PARTITION '$PARTITION'"
done

Automatización mediante cron:

# Ejecutar todos los días a las 3:00
0 3 * * * /usr/local/bin/cleanup_clickhouse_partitions.sh >> /var/log/cleanup.log 2>&1

Qué sigue

Ahora entiendes cómo el particionamiento convierte la eliminación de datos de una operación dolorosa en una acción instantánea. Próximos temas:

• Estrategias avanzadas de TTL (tiempo de vida) — eliminación o movimiento automático de filas sin tu intervención.
• Configuración de políticas de almacenamiento — cómo automatizar MOVE de particiones de SSD a S3 según un horario.
• Optimización de SELECT con particiones — cómo escribir consultas para que ClickHouse descarte el 99% de las particiones.

Conclusión: El particionamiento en ClickHouse es una herramienta para gestionar el ciclo de vida de los datos, no solo para acelerar consultas. Elige el tamaño de partición de modo que tengas entre 100 y 1000 particiones por servidor. No te excedas en granularidad, no te pases en agregación. Y siempre revisa system.parts — te dirá la verdad.

---

← Anterior: CollapsingMergeTree: Cómo Actualizar Agregados Sin UPDATE en ClickHouse
→ Siguiente: ORDER BY y PRIMARY KEY en ClickHouse: Cómo Configurar el Índice Correctamente

— Editorial Team