Uso de almacenes de datos híbridos y tablas HStore

Escenarios de almacenes de datos híbridos

A medida que evoluciona la era de los datos inteligentes, el ecosistema de datos empresariales presenta tres características significativas: expansión masiva de datos, tipos de datos diversificados (incluidos datos estructurados, semiestructurados y no estructurados) y escenarios cada vez más complejos. Para hacer frente a estos desafíos, surgen los almacenes de datos híbridos. Basados en capacidades de análisis y consulta de datos a gran escala, los almacenes de datos híbridos característica alta concurrencia, alto rendimiento, baja latencia y bajo costo en el procesamiento de transacciones. Las tablas HStore desempeñan un papel clave en la transformación digital de Internet, IoT y las industrias tradicionales. Los escenarios de aplicación típicos son los siguientes:

• Análisis inteligente del comportamiento del usuario: Recopila registros de navegación de páginas web en tiempo real para construir perfiles de usuario a lo largo del ciclo de vida y admite el análisis multidimensional de ruta del comportamiento. Gracias al OLAP de los almacenes de datos híbridos, puede calcular indicadores clave como la tasa de retención de usuarios y el embudo de conversión en segundos, lo que facilita la toma de decisiones de operación refinadas.
• Centro de control de riesgo en tiempo real: En los escenarios de transacciones de comercio electrónico y finanzas en Internet, el motor de cálculo de característica de riesgo se construye para procesar datos en tiempo real en milisegundos. Al asociar datos de comportamiento de usuarios de múltiples fuentes, identifica dinámicamente patrones anormales e intercepta transacciones fraudulentas en cuestión de cientos de milisegundos, lo que garantiza la seguridad del servicio.
• Industrial IoT and intelligent O&M: En industrias tradicionales como la energía eléctrica y la manufactura, los almacenes de datos híbridos pueden integrar datos masivos de sensores de dispositivos (incluidos flujos de datos de series temporales como vibración y temperatura) y datos semiestructurados como registros de mantenimiento de dispositivos para construir un modelo de mantenimiento predictivo. El análisis de tendencias en tiempo real se utiliza para monitorear dinámicamente el estado de salud de los dispositivos y predecir fallas, transformando el O&M pasivo tradicional en O&M preventivo inteligente.

Los almacenes de datos híbridos admiten dos métodos eficientes de importación de datos: método directo y método de búfer.

Mecanismo de almacenamiento para tablas de almacenamiento en columna ordinarias

En DWS, las tablas de almacenamiento en columna utilizan una unidad de compresión (CU) como la unidad de almacenamiento más pequeña. Por defecto, cada CU almacena 60,000 filas por columna y opera en modo de escritura de adición. Esto significa que las operaciones de UPDATE y DELETE no modifican directamente la CU original. Una vez creada una CU, sus datos no se pueden alterar, lo que genera una nueva CU completa cada vez que se insertan datos, independientemente de la cantidad.

Ventajas de las tablas HStore

Una tabla HStore utiliza una tabla delta adicional para equilibrar eficazmente el almacenamiento y las actualizaciones. Estos son los puntos clave:

Para mejorar el rendimiento, DWS 9.1.0 conserva la antigua tabla HStore para la compatibilidad futura. Las tablas optimizadas se conocen como tablas HStore Opt. Las tablas HStore se pueden reemplazar por tablas HStore Opt para un mejor rendimiento, excepto en escenarios que requieren un alto rendimiento sin actualizaciones de microlotes.

Sugerencias sobre el uso de tablas HStore

• Configuración de parámetros

  Establezca los parámetros de acuerdo con Tabla 4 para mejorar el rendimiento de las consultas y el almacenamiento de las tablas HStore:

• Sugerencias para importar datos a la base de datos (se recomiendan las tablas HStore Opt).
  1. Operaciones UPDATE:

     Utilice el modo UPSERT en lugar del modo UPDATE.

  2. Operaciones DELETE:
     • Asegúrese de que el plan de ejecución se escanee por índice.
     • El modo de lote JDBC es el más eficiente.
  3. Importación de datos por lotes:
     • Si la cantidad de datos que se importarán de una vez supera 1 millón de registros por DN y los datos son únicos, considere utilizar MERGE INTO.
     • Utilice UPSERT para escenarios comunes.
• Sugerencias sobre consultas puntuales (se recomiendan tablas HStore Opt).
  1. Cree una partición de nivel 2 en columnas con valores distintos distribuidos uniformemente y criterios de filtro equivalentes frecuentes. Evite particiones de nivel 2 en columnas con valores distintos sesgados o con pocos valores distintos.
  2. Cuando se trate de columnas con criterios de filtro fijos (excluyendo particiones de nivel 2), utilice el índice CBTREE (hasta 5 columnas).
  3. Cuando se trate de columnas con criterios de filtro variables (excluyendo particiones de nivel 2), utilice el índice GIN (hasta 5 columnas).
  4. Para todas las columnas de cadenas que involucren filtrado equivalente, utilice el índice BITMAP durante la creación de la tabla. El número de columnas no está limitado, pero no se puede modificar más tarde.
  5. Especifique las columnas que se pueden filtrar por rango de tiempo como columnas de partición.
  6. Si las consultas puntuales devuelven más de 100,000 registros por DN, el escaneo de índices puede superar al escaneo sin índices. Utilice el parámetro GUC enable_seqscan para comparar el rendimiento.
• Precauciones relacionadas con índice
  1. Los índices consumen almacenamiento adicional.
  2. Los índices se crean para mejorar el rendimiento.
  3. Los índices se utilizan cuando se deben realizar operaciones de UPSERT.
  4. Los índices se utilizan para consultas puntuales con requisitos únicos o casi únicos.

• Precauciones relacionadas con MERGE
  1. Control de la velocidad de importación de datos:
     • La velocidad de importación de datos no puede superar la capacidad de procesamiento de MERGE.
     • El control de la simultaneidad de la importación de datos a la base de datos puede evitar que se expanda la tabla Delta.
  2. Reutilización de tablespace
     • La reutilización del tablespace Delta se ve afectada por oldestXmin.
     • Las transacciones de larga duración pueden causar retrasos y expansiones en la reutilización del tablespace.

Variantes de almacenes de datos híbridos

Elija la arquitectura de almacenamiento y cómputo acoplados cuando cree un clúster en la consola y asegúrese de que la relación entre vCPU y memoria sea de 1:4 al configurar las variantes de disco en la nube. Para obtener más información sobre las variantes de almacenes de datos híbridos y los escenarios de servicio correspondientes.

Parámetros GUC óptimos para almacenes de datos híbridos

Después de crear un almacén de datos híbrido, configure los parámetros GUC según se recomienda en Tabla 3.

1. Cambie el valor de enable_codegen a off para reducir la sobrecarga de memoria aplicada cuando se genera código de ejecución dinámica para consultas cortas.
   1. En la consola de DWS, seleccione Dedicated Clusters > Clusters.
   2. En la lista de clústeres, busque el clúster de destino y haga clic en el nombre del clúster para ir a la página de detalles del clúster.
   3. Haga clic en la pestaña Parameter Modifications, busque enable_codegen, cambie el valor a off y haga clic en Save.

2. Establezca NUMA en los DN en on y NUMA en los CN en off. La vinculación de procesos NUMA puede reducir la sobrecarga de los procesos de acceso entre NUMA.

   Comuníquese con el soporte técnico para cambiar el valor de enable_numa_bind.

3. Cambie el valor de abnormal_check_general_task a 3600 para reducir la sobrecarga de establecer conexiones repetidamente. El valor predeterminado es 60.
   

   El intervalo de eliminación predeterminado es de 60 segundos, lo que puede afectar significativamente el rendimiento del servicio a nivel de milisegundos. La recreación de un solo subproceso tiene un costo de aproximadamente 300 ms, por lo que se recomienda aumentar el intervalo para escenarios con sensibilidad de rendimiento a nivel de milisegundos. Si las conexiones se eliminan lentamente dentro del intervalo, puede ocasionar un alto uso de memoria.

   Comuníquese con el soporte técnico para cambiar el valor de abnormal_check_general_task.

Configuración óptima de parámetros para crear tablas HStore Opt en almacenes de datos híbridos

Al utilizar almacenes de datos híbridos, se recomienda utilizar tablas HStore Opt. Antes de utilizar dichas tablas, establezca los siguientes parámetros consultando Tabla 4.

1. Configuración de parámetros GUC.
   1. En la consola de DWS, seleccione Dedicated Clusters > Clusters.
   2. En la lista de clústeres, busque el clúster de destino y haga clic en el nombre del clúster para ir a la página de detalles del clúster.
   3. Modificación de parámetros obligatorios: Haga clic en la pestaña Parameter Modifications, busque autovacuum_max_workers y autovacuum_max_workers_hstore, configúrelos con los valores recomendados consultando Tabla 4 y haga clic en Save.
      

      Se recomienda utilizar los valores predeterminados de autovacuum y enable_col_index_vacuum y no es necesario configurarlos por separado.

   4. Modifique los parámetros opcionales: Busque autovacuum_naptime y cost_model_version. Establezca los valores recomendados en Tabla 4 y haga clic en Save.
      

      Para otros parámetros opcionales, comuníquese con el soporte técnico.

2. Utilice el editor SQL para conectarse al clúster de DWS y crear una tabla HStore Opt. A continuación se muestra un ejemplo de creación de dicha tabla:

   Seleccione una clave de distribución y una clave de partición adecuadas en función de las características de los datos. Para obtener más información, consulte Propuesta de diseño de desarrollo de DWS.

   1 2 3 4 5 6 7 8 9 10 11 12 13

   CREATE table public.hstore_opt_table_demo( t_code character varying(20), t_gisid character varying(800), t_datatime timestamp(6) without time zone, t_gmid character varying(64) ) WITH (orientation=column, enable_hstore_opt=on) --This configuration is used by default when a table is created. DISTRIBUTE BY hash (t_gmid) --Distribution key, which can be a primary key or an associated column. PARTITION BY range (t_datatime) -- Partition key ( partition p2024_1 start('2024-01-01') end ('2024-06-01') every (interval '1 month'), partition p2024_7 start('2024-06-01') end ('2024-12-31') every (interval '1 month') );

3. Ajuste las particiones cuando se procesen datos anormales.

   1	ALTER TABLE public.hstore_opt_table_demo ADD PARTITION pmax VALUES LESS THAN (maxvalue);

4. Para obtener más información sobre otras sugerencias de uso y comparación de rendimiento de las tablas HStore Opt, consulte Comparación de rendimiento entre las tablas HStore y las tablas de almacenamiento de filas y columnas ordinarias.

Comparación de rendimiento entre las tablas HStore y las tablas de almacenamiento de filas y columnas ordinarias

1. Actualización concurrente
   Una vez que se inserta un lote de datos en una tabla de almacenamiento de columnas ordinaria, se inician dos sesiones. En la sesión 1, se elimina un fragmento de datos y la transacción no se confirma.

   1 2 3 4 5 6 7 8 9 10 11

   CREATE TABLE col(a int , b int)with(orientation=column); CREATE TABLE INSERT INTO col select * from data; INSERT 0 100 BEGIN; BEGIN DELETE col where a = 1; DELETE 1

   Cuando la sesión 2 intenta eliminar más datos, se hace evidente que la sesión 2 solo puede proceder después de que se haya confirmado la sesión 1. Este escenario imita el problema de bloqueo de CU en el almacenamiento de columnas.

   1 2 3

   BEGIN; BEGIN DELETE col where a = 2;

   Repita las operaciones anteriores utilizando una tabla HStore. La sesión 2 se puede ejecutar correctamente sin ninguna espera de bloqueo.

   1 2 3 4

   BEGIN; BEGIN DELETE hs where a = 2; DELETE 1

2. Eficiencia de compresión
   Cree una tabla de datos con 3 millones de registros de datos.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   CREATE TABLE data( a int, b bigint, c varchar(10), d varchar(10)); CREATE TABLE INSERT INTO data values(generate_series(1,100),1,'asdfasdf','gergqer'); INSERT 0 100 INSERT INTO data select * from data; INSERT 0 100 INSERT INTO data select * from data; INSERT 0 200 ---Insert data cyclically until the data volume reaches 3 million. INSERT INTO data select * from data; INSERT 0 1638400 SELECT COUNT(*) FROM data; count --------- 3276800 (1 row)

   Importe datos a una tabla de almacenamiento de filas por lotes y verifique si el tamaño es de 223 MB.

   1 2 3 4 5 6 7 8 9

   CREATE TABLE row (like data including all); CREATE TABLE INSERT INTO row SELECT * FROM data; INSERT 0 3276800 SELECT pg_size_pretty(pg_relation_size('row')); pg_size_pretty ---------------- 223 MB (1 row)

   Importe datos a una tabla HStore Opt por lotes y verifique si el tamaño es de 3.5 MB.

   1 2 3 4 5 6 7 8 9 10

   CREATE TABLE hs(a int, b bigint, c varchar(10),d varchar(10))with(orientation= column, enable_hstore_opt=on); CREATE TABLE INSERT INTO hs SELECT * FROM data; INSERT 0 3276800 SELECT pg_size_pretty(pg_relation_size('hs')); pg_size_pretty ---------------- 3568 KB (1 row)

   Las tablas HStore tienen un buen efecto de compresión debido a su estructura de tabla simple y a los datos duplicados. Por lo general, se comprimen de tres a cinco veces más que las tablas de almacenamiento de filas.

3. Rendimiento de consulta por lotes
   Se tarda aproximadamente cuatro segundos en consultar la cuarta columna de la tabla de almacenamiento de filas.

   1 2 3 4 5 6 7

   EXPLAIN ANALYZE SELECT d FROM data; EXPLAIN ANALYZE QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs ----+------------------------------+----------------------+---------+---------+--------------+----------+---------+---------+---------- 1 | -> Streaming (type: GATHER) | 4337.881 | 3276800 | 3276800 | 32KB | | | 8 | 61891.00 2 | -> Seq Scan on data | [1571.995, 1571.995] | 3276800 | 3276800 | [32KB, 32KB] | 1MB | | 8 | 61266.00

   Se tarda aproximadamente 300 milisegundos en consultar la cuarta columna de la tabla HStore Opt.

   1 2 3 4 5 6 7 8

   EXPLAIN ANALYZE SELECT d from hs; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs ----+----------------------------------------+--------------------+---------+---------+----------------+----------+---------+---------+---------- 1 | -> Row Adapter | 335.280 | 3276800 | 3276800 | 24KB | | | 8 | 15561.80 2 | -> Vector Streaming (type: GATHER) | 111.492 | 3276800 | 3276800 | 96KB | | | 8 | 15561.80 3 | -> CStore Scan on hs | [111.116, 111.116] | 3276800 | 3276800 | [254KB, 254KB] | 1MB | | 8 | 14936.80

   Las tablas almacenadas y las tablas HStore superan a las tablas de almacenamiento de filas en términos de consultas por lotes.

Cambio de tablas ordinarias de almacenamiento de filas y columnas a tablas HStore

Cuando se utiliza una tabla delta de almacenamiento de columnas, las operaciones MERGE pueden no ocurrir de inmediato debido a la ausencia de un mecanismo de fusión programado para el ancho de banda del disco y la tabla delta de almacenamiento de columnas. Esto puede provocar la expansión de la tabla delta y una disminución del rendimiento de las consultas y la eficiencia de las actualizaciones concurrentes.

En comparación con las tablas delta de almacenamiento de columnas, las tablas HStore ofrecen importación de datos concurrente, consultas de alto rendimiento y un mecanismo de fusión asincrónica. Las tablas HStore pueden reemplazar a las tablas delta de almacenamiento de columnas.

Aquí hay una guía para cambiar las tablas ordinarias de almacenamiento de columnas y filas a tablas HStore:

1. Verifique la lista de tablas delta y reemplace nspname por el espacio de nombres de destino.

   1	select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%';

   Después de ejecutar los pasos 2 y 3, ejecute las sentencias de creación de tablas generadas en el paso 2 y las sentencias de importación de datos generadas en el paso 3 en secuencia.

2. Genere la sentencia de creación de tablas que contenga el parámetro enable_hstore_opt.

   1 2

   select 'create table if not exists '|| tbname ||'_opt (like '|| tbname ||' INCLUDING all EXCLUDING reloptions) with(orientation=column,enable_hstore_opt=on);' from( select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%');

3. Genere la sentencia para la importación de datos. Reemplace el nombre de la tabla y elimine la sentencia de la tabla anterior.

   1 2 3 4 5 6 7 8

   select 'start transaction; lock table '|| tbname ||' in EXCLUSIVE mode; insert into '|| tbname ||'_opt select * from '|| tbname ||'; alter table '|| tbname ||' rename to '|| tbname ||'_bk; alter table '|| tbname ||'_opt rename to '|| tbname ||'; commit; drop table '|| tbname ||'_bk;' from(select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%');