Saltar al contenido principal
Esta es la Parte 3 de una guía sobre cómo migrar de PostgreSQL a ClickHouse. Mediante un ejemplo práctico, muestra cómo modelar datos en ClickHouse al migrar desde PostgreSQL.
Recomendamos a los usuarios que migran desde Postgres que lean la guía para modelar datos en ClickHouse. Esta guía utiliza el mismo conjunto de datos de Stack Overflow y explora varios enfoques con las funcionalidades de ClickHouse.

​
Claves primarias (de ordenación) en ClickHouse

Los usuarios que provienen de bases de datos OLTP suelen buscar el concepto equivalente en ClickHouse. Al ver que ClickHouse admite la sintaxis PRIMARY KEY, podrían sentirse tentados a definir el esquema de su tabla con las mismas claves que en su base de datos OLTP de origen. Esto no es apropiado.

​
¿En qué se diferencian las claves primarias de ClickHouse?

Para entender por qué no es adecuado usar su clave primaria de OLTP en ClickHouse, primero debe comprender los conceptos básicos de la indexación en ClickHouse. Usamos Postgres como ejemplo de comparación, pero estos conceptos generales también se aplican a otras bases de datos OLTP.
  • Las claves primarias de Postgres son, por definición, únicas por fila. El uso de estructuras B-tree permite buscar filas individuales de forma eficiente mediante esta clave. Aunque ClickHouse puede optimizarse para buscar el valor de una única fila, las cargas de trabajo analíticas suelen requerir leer unas pocas columnas, pero de muchas filas. Con más frecuencia, los filtros deberán identificar un subconjunto de filas sobre el que se realizará una agregación.
  • La eficiencia en memoria y disco es fundamental a la escala a la que suele utilizarse ClickHouse. Los datos se escriben en las tablas de ClickHouse en fragmentos conocidos como partes, y se aplican reglas para fusionar esas partes en segundo plano. En ClickHouse, cada parte tiene su propio índice primario. Cuando las partes se fusionan, los índices primarios de la parte resultante también se fusionan. A diferencia de Postgres, estos índices no se construyen para cada fila. En su lugar, el índice primario de una parte tiene una entrada de índice por cada grupo de filas; esta técnica se denomina indexación dispersa.
  • La indexación dispersa es posible porque ClickHouse almacena en disco las filas de una parte ordenadas por una clave especificada. En lugar de localizar directamente filas individuales (como hace un índice basado en B-tree), el índice primario disperso le permite identificar rápidamente (mediante una búsqueda binaria sobre las entradas del índice) grupos de filas que podrían coincidir con la consulta. Los grupos de filas potencialmente coincidentes identificados se envían después, en paralelo, al motor de ClickHouse para encontrar las coincidencias. Este diseño de índice permite que el índice primario sea pequeño (cabe por completo en la memoria principal) y, aun así, acelere significativamente los tiempos de ejecución de las consultas, especialmente en las consultas por rango típicas de los casos de uso de análisis de datos.
Para obtener más información, recomendamos esta guía detallada. La clave seleccionada en ClickHouse determinará no solo el índice, sino también el orden en que los datos se escriben en disco. Por ello, puede afectar drásticamente los niveles de compresión, lo que a su vez puede influir en el rendimiento de las consultas. Una clave de ordenación que haga que los valores de la mayoría de las columnas se escriban en un orden contiguo permitirá que el algoritmo de compresión seleccionado (y los codecs) comprima los datos de forma más eficaz.
Todas las columnas de una tabla se ordenarán según el valor de la clave de ordenación especificada, independientemente de si están incluidas o no en la propia clave. Por ejemplo, si CreationDate se usa como clave, el orden de los valores de todas las demás columnas corresponderá al orden de los valores de la columna CreationDate. Se pueden especificar varias claves de ordenación; en ese caso, el orden seguirá la misma semántica que una cláusula ORDER BY en una consulta SELECT.

​
Elección de una clave de ordenación

Para ver las consideraciones y los pasos para elegir una clave de ordenación, tomando la tabla Posts como ejemplo, consulte aquí. Al usar replicación en tiempo real con CDC, hay restricciones adicionales que deben tenerse en cuenta; consulte esta documentación para conocer técnicas sobre cómo personalizar las claves de ordenación con CDC.

​
Particiones

Si vienes de Postgres, te resultará familiar el concepto de particionamiento de tablas para mejorar el rendimiento y la capacidad de administración de bases de datos grandes, dividiendo las tablas en partes más pequeñas y manejables llamadas particiones. Este particionamiento puede lograrse usando un rango sobre una columna específica (p. ej., fechas), listas definidas o mediante un hash sobre una clave. Esto permite a los administradores organizar los datos según criterios concretos, como rangos de fechas o ubicaciones geográficas. El particionamiento ayuda a mejorar el rendimiento de las consultas al permitir un acceso más rápido a los datos mediante la poda de particiones y una indexación más eficiente. También facilita tareas de mantenimiento, como las copias de seguridad y la purga de datos, al permitir operaciones sobre particiones individuales en lugar de sobre la tabla completa. Además, el particionamiento puede mejorar significativamente la escalabilidad de las bases de datos PostgreSQL al distribuir la carga entre múltiples particiones. En ClickHouse, el particionamiento se especifica en una tabla cuando se define inicialmente mediante la cláusula PARTITION BY. Esta cláusula puede contener una expresión SQL sobre cualquier columna, cuyo resultado definirá a qué partición se envía una fila. Las partes de datos se asocian lógicamente con cada partición en disco y pueden consultarse de forma aislada. En el ejemplo siguiente, particionamos la tabla posts por año usando la expresión toYear(CreationDate). A medida que se insertan filas en ClickHouse, esta expresión se evaluará para cada fila y se enviará a la partición correspondiente si existe (si la fila es la primera de un año, se creará la partición). 
 CREATE TABLE posts
(
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
...
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)
Para obtener una descripción completa de las particiones, consulte “Particiones de tablas”.

​
Aplicaciones de las particiones

El particionamiento en ClickHouse tiene aplicaciones similares a las de Postgres, aunque con algunas diferencias sutiles. Más concretamente:
  • Gestión de datos - En ClickHouse, debes considerar el particionamiento principalmente como una funcionalidad de gestión de datos, no como una técnica de optimización de consultas. Al separar los datos de forma lógica según una clave, cada partición puede gestionarse de manera independiente; por ejemplo, puede eliminarse. Esto permite mover particiones y, por tanto, subconjuntos de datos, entre niveles de almacenamiento de forma eficiente en función del tiempo o hacer que los datos expiren/eliminarlos de forma eficiente del clúster. En el ejemplo siguiente, eliminamos publicaciones de 2008.
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'

┌─partition─┐
│ 2008      │
│ 2009      │
│ 2010      │
│ 2011      │
│ 2012      │
│ 2013      │
│ 2014      │
│ 2015      │
│ 2016      │
│ 2017      │
│ 2018      │
│ 2019      │
│ 2020      │
│ 2021      │
│ 2022      │
│ 2023      │
│ 2024      │
└───────────┘

17 rows in set. Elapsed: 0.002 sec.

ALTER TABLE posts
(DROP PARTITION '2008')

Ok.

0 rows in set. Elapsed: 0.103 sec.
  • Optimización de consultas - Aunque el particionamiento puede ayudar a mejorar el rendimiento de las consultas, esto depende en gran medida de los patrones de acceso. Si las consultas se limitan a unas pocas particiones (idealmente una sola), el rendimiento puede mejorar. Normalmente, esto solo resulta útil si la clave de particionamiento no está en la clave primaria y se filtra por ella. Sin embargo, las consultas que necesitan abarcar muchas particiones pueden tener peor rendimiento que si no se usara particionamiento (ya que este puede dar lugar a más partes). La ventaja de dirigirse a una sola partición será aún menor, o incluso inexistente, si la clave de particionamiento ya aparece entre las primeras columnas de la clave primaria. El particionamiento también puede usarse para optimizar las consultas GROUP BY si los valores de cada partición son únicos. Sin embargo, en general, debes asegurarte de que la clave primaria esté optimizada y considerar el particionamiento como una técnica de optimización de consultas solo en casos excepcionales, cuando los patrones de acceso se centran en un subconjunto específico y predecible del tiempo; por ejemplo, particionar por día cuando la mayoría de las consultas se realizan sobre el último día.

​
Recomendaciones para las particiones

Debería considerar el particionamiento como una técnica de gestión de datos. Resulta ideal cuando es necesario eliminar datos del clúster al trabajar con series temporales; por ejemplo, la partición más antigua puede simplemente eliminarse. Importante: Asegúrese de que la expresión de la clave de partición no dé como resultado un conjunto de alta cardinalidad; es decir, debe evitar crear más de 100 particiones. Por ejemplo, no particione sus datos por columnas de alta cardinalidad, como identificadores o nombres de clientes. En su lugar, haga que el identificador o el nombre del cliente sea la primera columna de la expresión ORDER BY.
Internamente, ClickHouse crea partes para los datos insertados. A medida que se insertan más datos, aumenta el número de partes. Para evitar un número excesivo de partes, que degradará el rendimiento de las consultas (al haber más archivos que leer), las partes se fusionan en un proceso asíncrono en segundo plano. Si el número de partes supera un límite preconfigurado, ClickHouse lanzará una excepción durante la inserción con un error de “demasiadas partes”. Esto no debería ocurrir en condiciones normales de funcionamiento y solo sucede si ClickHouse está mal configurado o se usa de forma incorrecta; por ejemplo, con muchas inserciones pequeñas.
Como las partes se crean de forma aislada para cada partición, aumentar el número de particiones hace que también aumente el número de partes; es decir, es un múltiplo del número de particiones. Por lo tanto, las claves de partición de alta cardinalidad pueden provocar este error y deben evitarse.

​
Vistas materializadas vs proyecciones

Postgres permite crear varios índices en una misma tabla, lo que permite optimizar distintos patrones de acceso. Esta flexibilidad permite a administradores y desarrolladores adaptar el rendimiento de la base de datos a consultas concretas y necesidades operativas. El concepto de proyecciones de ClickHouse, aunque no es del todo análogo, permite especificar varias cláusulas ORDER BY para una tabla. En la documentación de modelado de datos de ClickHouse, exploramos cómo pueden usarse las vistas materializadas en ClickHouse para precomputar agregaciones, transformar filas y optimizar consultas para distintos patrones de acceso. Para este último caso, mostramos un ejemplo en el que la vista materializada envía filas a una tabla de destino con un orden de clasificación distinto al de la tabla original que recibe las inserciones. Por ejemplo, considere la siguiente consulta: 
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.
Esta consulta requiere escanear las 90 millones de filas (aunque sea rápidamente), ya que UserId no es la clave de ordenación. Anteriormente, resolvimos esto usando una vista materializada que actuaba como tabla de búsqueda para PostId. El mismo problema puede resolverse con una proyección. El siguiente comando añade una proyección para ORDER BY user_id. 
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)

ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
Ten en cuenta que primero tenemos que crear la proyección y luego materializarla. Este último comando hace que los datos se almacenen dos veces en disco, en dos órdenes diferentes. La proyección también puede definirse al crear los datos, como se muestra a continuación, y se mantendrá automáticamente a medida que se inserten los datos. 
CREATE TABLE comments
(
        `Id` UInt32,
        `PostId` UInt32,
        `Score` UInt16,
        `Text` String,
        `CreationDate` DateTime64(3, 'UTC'),
        `UserId` Int32,
        `UserDisplayName` LowCardinality(String),
        PROJECTION comments_user_id
        (
        SELECT *
        ORDER BY UserId
        )
)
ENGINE = MergeTree
ORDER BY PostId
Si la proyección se crea mediante un ALTER, su creación es asíncrona cuando se ejecuta el comando MATERIALIZE PROJECTION. Puede comprobar el progreso de esta operación con la siguiente consulta y esperar a que is_done=1. 
SELECT
        parts_to_do,
        is_done,
        latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')

   ┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │           1 │       0 │                    │
   └─────────────┴─────────┴────────────────────┘

1 fila en el conjunto. Elapsed: 0.003 sec.
Si repetimos la consulta anterior, podemos ver que el rendimiento ha mejorado significativamente a cambio de almacenamiento adicional. 
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.
Con un comando EXPLAIN, también confirmamos que se utilizó la proyección para ejecutar esta consulta: 
EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

    ┌─explain─────────────────────────────────────────────┐
 1. │ Expression ((Projection + Before ORDER BY))         │
 2. │   Aggregating                                       │
 3. │   Filter                                            │
 4. │           ReadFromMergeTree (comments_user_id)      │
 5. │           Indexes:                                  │
 6. │           PrimaryKey                                │
 7. │           Keys:                                     │
 8. │           UserId                                    │
 9. │           Condition: (UserId in [8592047, 8592047]) │
10. │           Parts: 2/2                                │
11. │           Granules: 2/11360                         │
    └─────────────────────────────────────────────────────┘

11 rows in set. Elapsed: 0.004 sec.

​
Cuándo usar proyecciones

Las proyecciones son una funcionalidad atractiva para los usuarios nuevos, ya que se mantienen automáticamente a medida que se insertan los datos. Además, las consultas pueden enviarse simplemente a una sola tabla, donde las proyecciones se aprovechan, cuando es posible, para acelerar el tiempo de respuesta. Esto contrasta con las vistas materializadas, donde el usuario tiene que seleccionar la tabla de destino optimizada adecuada o reescribir su consulta, según los filtros. Esto exige más a las aplicaciones del usuario y aumenta la complejidad del lado del cliente. A pesar de estas ventajas, las proyecciones tienen algunas limitaciones inherentes que debe conocer y, por lo tanto, conviene implementarlas con moderación. Recomendamos usar proyecciones cuando:
  • Se requiere una reordenación completa de los datos. Aunque la expresión de la proyección puede, en teoría, usar un GROUP BY, las vistas materializadas son más eficaces para mantener agregados. También es más probable que el optimizador de consultas aproveche las proyecciones que usan una reordenación simple, es decir, SELECT * ORDER BY x. Puede seleccionar un subconjunto de columnas en esta expresión para reducir la huella de almacenamiento.
  • Los usuarios aceptan el aumento asociado de la huella de almacenamiento y la sobrecarga de escribir los datos dos veces. Pruebe el impacto en la velocidad de inserción y evalúe la sobrecarga de almacenamiento.
Desde la versión 25.5, ClickHouse admite la columna virtual _part_offset en las proyecciones. Esto ofrece una forma de almacenar proyecciones más eficiente en cuanto a espacio.Para más detalles, consulte “Projections”

​
Desnormalización

Dado que Postgres es una base de datos relacional, su modelo de datos está muy normalizado y a menudo implica cientos de tablas. En ClickHouse, la desnormalización puede resultar beneficiosa en algunos casos para optimizar el rendimiento de los JOIN. Puedes consultar esta guía, que muestra las ventajas de desnormalizar el conjunto de datos de Stack Overflow en ClickHouse. Con esto concluye nuestra guía básica si estás migrando de Postgres a ClickHouse. Te recomendamos leer la guía para modelar datos en ClickHouse para conocer mejor las funcionalidades avanzadas de ClickHouse.
Última modificación el 10 de junio de 2026