ApsaraDB RDS:Marketing de precisión en tiempo real (segmentación de audiencias)

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

• Una instancia de ApsaraDB RDS for PostgreSQL. Consulte Create an ApsaraDB RDS for PostgreSQL instance.

• Una lista de permitidos de direcciones IP configurada para la instancia de RDS. Consulte Configure an IP address whitelist.

• Una cuenta de base de datos. Consulte Create an account.

• Una base de datos. Consulte Create a database.

La solución 3 requiere la extensión roaringbitmap y PostgreSQL 12 o posterior. Los ejemplos de este tema utilizan PostgreSQL 12. Para obtener más información sobre las extensiones, consulte Use the roaringbitmap extension .

Contexto

Los sistemas de marketing de precisión necesitan consultar usuarios según cualquier combinación de etiquetas en tiempo real. Por ejemplo, deben identificar a todos los usuarios mayores de 24 años que buscaron celulares en las últimas 24 horas pero aún no han realizado un pedido. El desafío consiste en ejecutar estas consultas con rapidez sobre decenas de millones de usuarios y decenas de miles de etiquetas.

Puntos problemáticos habituales:

• La cantidad de etiquetas crece hasta alcanzar cientos de miles, lo que supera los límites de campos de la base de datos (generalmente 1,000 campos por tabla).

• Almacenar etiquetas como arreglos requiere compatibilidad con índices GIN (Generalized Inverted Index), una función que no todas las bases de datos ofrecen y que consume mucho almacenamiento.

• Las combinaciones de condiciones de consulta son impredecibles, por lo que los índices estáticos no cubren todos los casos.

• Los datos del perfil de usuario deben mantenerse casi en tiempo real; un usuario que realizó un pedido anoche no debería recibir hoy anuncios del mismo producto.

Este tema describe tres soluciones basadas en PostgreSQL que abordan estos problemas con diferentes compromisos técnicos.

Selección de una solución

Entorno de prueba: instancias de RDS con MySQL 8.0 y PostgreSQL 12, con 8 núcleos de CPU, 32 GB de memoria y un disco SSD mejorado (ESSD) de 1,500 GB. Conjunto de datos: 20 millones de usuarios, 100,000 etiquetas y 64 etiquetas por usuario (1,280 millones de registros de etiquetas en total).

Utilice la solución 3 para sistemas de producción con millones de usuarios y requisitos de consulta en tiempo real. Use la solución 1 si necesita compatibilidad con MySQL. La solución 2 es una opción intermedia adecuada cuando el almacenamiento es más limitado que la velocidad de consulta.

Solución 1: una fila por usuario por etiqueta

Esta solución funciona tanto con PostgreSQL como con MySQL.

Descripción general del esquema

KEY:    user ID
VALUES: one row per tag (tag 1, tag 2, ... tag N)

• Índice: índice B-tree en cada campo de etiqueta.

• Búsqueda: WHERE tag = a AND tag = b (con INTERSECT/UNION).

Desventajas

• Alto consumo de almacenamiento: un índice por campo de etiqueta para 20 millones de usuarios genera 62,464 MB solo en almacenamiento de índices.

• Agregar una nueva etiqueta implica insertar filas para cada usuario del grupo.

• El rendimiento de las consultas se degrada a medida que aumenta el número de etiquetas en la condición de filtro.

Procedimiento

1. Cree una tabla de diccionario de etiquetas con una fila por etiqueta:

   CREATE TABLE t_tag_dict (
     tag      int PRIMARY KEY,   -- Tag ID
     info     text,              -- Tag description
     crt_time timestamp          -- Creation time
   );

2. Inserte 100,000 etiquetas:

   INSERT INTO t_tag_dict VALUES (1, 'Male', now());
   INSERT INTO t_tag_dict VALUES (2, 'Female', now());
   INSERT INTO t_tag_dict VALUES (3, 'Older than 24 years old', now());
   -- ... additional tags ...
   
   INSERT INTO t_tag_dict
   SELECT generate_series(4, 100000), md5(random()::text), clock_timestamp();

3. Cree una tabla de perfiles de usuario con una fila por usuario por etiqueta:

   CREATE TABLE t_user_tag (
     uid      int8,       -- User ID
     tag      int,        -- Tag ID
     mod_time timestamp,  -- Last modified
     PRIMARY KEY (tag, uid)
   );

4. Pueble la tabla con 20 millones de usuarios y 64 etiquetas aleatorias cada uno (1,280 millones de filas en total):

   CREATE OR REPLACE FUNCTION gen_rand_tag(int, int) RETURNS SETOF int AS
   $$
     SELECT CASE WHEN random() > 0.5 THEN 1::int ELSE 2::int END AS tag
     UNION ALL
     SELECT ceil(random() * $1)::int AS tag FROM generate_series(1, $2);
   $$ LANGUAGE sql STRICT VOLATILE;
   
   INSERT INTO t_user_tag
   SELECT uid, gen_rand_tag(100000, 63) AS tag, clock_timestamp()
   FROM generate_series(1, 20000000) AS uid
   ON CONFLICT (uid, tag) DO NOTHING;

5. Consulte los usuarios que coinciden con las etiquetas 1 Y 3:

   -- Count matching users
   SELECT count(*) FROM (
     SELECT uid FROM t_user_tag WHERE tag = 1
     INTERSECT
     SELECT uid FROM t_user_tag WHERE tag = 3
   ) t;
   -- Time: 1,494 ms
   
   -- Get user IDs
   SELECT uid FROM t_user_tag WHERE tag = 1
   INTERSECT
   SELECT uid FROM t_user_tag WHERE tag = 3;
   -- Time: 3,246 ms

6. Consulte los usuarios que coinciden con la etiqueta 1 O 3 O 10 O 200:

   -- Count matching users
   SELECT count(*) FROM (
     SELECT uid FROM t_user_tag WHERE tag = 1
     UNION SELECT uid FROM t_user_tag WHERE tag = 3
     UNION SELECT uid FROM t_user_tag WHERE tag = 10
     UNION SELECT uid FROM t_user_tag WHERE tag = 200
   ) t;
   -- Time: 3,578 ms
   
   -- Get user IDs
   SELECT uid FROM t_user_tag WHERE tag = 1
   UNION SELECT uid FROM t_user_tag WHERE tag = 3
   UNION SELECT uid FROM t_user_tag WHERE tag = 10
   UNION SELECT uid FROM t_user_tag WHERE tag = 200;
   -- Time: 5,682 ms

Solución 2: arreglos de etiquetas con índices GIN

Esta solución requiere PostgreSQL. MySQL no admite arreglos ni índices GIN.

Descripción general del esquema

KEY:    user ID
VALUES: array of tag IDs

• Índice: GIN en el campo de arreglo de etiquetas.

• Operadores de búsqueda: @> (AND/contiene), && (OR/superposición), NOT @> (NOT).

Desventajas

• Los índices GIN consumen considerablemente más almacenamiento que los índices B-tree en conjuntos de datos grandes.

• Agregar etiquetas a un usuario requiere actualizar su fila, una operación costosa a gran escala.

• Las escrituras GIN utilizan un mecanismo diferido fastupdate: las actualizaciones se acumulan en una lista pendiente y se vacían en lotes. Cuando la lista pendiente alcanza gin_pending_list_limit (valor predeterminado de 4 MB), se produce un vaciado durante una operación de escritura, lo que causa picos periódicos de latencia. En cargas de trabajo de actualización de perfiles de usuario en tiempo real, supervise este comportamiento y considere ajustar gin_pending_list_limit o deshabilitar fastupdate si la consistencia en la latencia de escritura es crítica.

Procedimiento

1. Cree una tabla de diccionario de etiquetas (igual que en la solución 1):

   CREATE TABLE t_tag_dict (
     tag      int PRIMARY KEY,
     info     text,
     crt_time timestamp
   );

2. Inserte 100,000 etiquetas:

   INSERT INTO t_tag_dict VALUES (1, 'Male', now());
   INSERT INTO t_tag_dict VALUES (2, 'Female', now());
   INSERT INTO t_tag_dict VALUES (3, 'Older than 24 years old', now());
   
   INSERT INTO t_tag_dict
   SELECT generate_series(4, 100000), md5(random()::text), clock_timestamp();

3. Cree una tabla de perfiles de usuario con una fila por usuario y un arreglo de etiquetas:

   CREATE TABLE t_user_tags (
     uid      int8 PRIMARY KEY,  -- User ID
     tags     int[],             -- Array of tag IDs
     mod_time timestamp
   );

4. Cree una función auxiliar para generar arreglos de etiquetas aleatorios:

   CREATE OR REPLACE FUNCTION gen_rand_tags(int, int) RETURNS int[] AS $$
     SELECT array_agg(ceil(random() * $1)::int) FROM generate_series(1, $2);
   $$ LANGUAGE sql STRICT;

5. Asigne etiquetas a 20 millones de usuarios con 64 etiquetas aleatorias cada uno (10 millones masculinos, 10 millones femeninos):

   INSERT INTO t_user_tags
   SELECT generate_series(1, 10000000),
     array_append(gen_rand_tags(100000, 63), 1), now();
   
   INSERT INTO t_user_tags
   SELECT generate_series(10000001, 20000000),
     array_append(gen_rand_tags(100000, 63), 2), now();

6. Cree un índice GIN en el arreglo de etiquetas:

   CREATE INDEX idx_t_user_tags_1 ON t_user_tags USING gin (tags);
   -- Index creation time: ~20 minutes

7. Consulte los usuarios que coinciden con las etiquetas 1 Y 3:

   -- Count matching users
   SELECT count(uid) FROM t_user_tags WHERE tags @> ARRAY[1, 3];
   
   -- Get user IDs
   SELECT uid FROM t_user_tags WHERE tags @> ARRAY[1, 3];

8. Consulte los usuarios que coinciden con la etiqueta 1 O 3 O 10 O 200:

   -- Count matching users
   SELECT count(uid) FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];
   
   -- Get user IDs
   SELECT uid FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];

Solución 3: agregación con mapas de bits Roaring

Esta solución requiere PostgreSQL con la extensión roaringbitmap . MySQL no admite esta extensión.

Funcionamiento

El modelo de mapa de bits Roaring invierte el esquema: en lugar de almacenar qué etiquetas tiene un usuario, almacena para cada etiqueta un mapa de bits comprimido de todos los usuarios que la poseen. Un roaringbitmap puede entenderse como una cadena de bits muy grande donde cada posición representa un ID de usuario; si el bit n está activado, el usuario n tiene la etiqueta.

El algoritmo Roaring Bitmap divide los enteros de 32 bits en fragmentos de 2^16 según los 16 bits superiores y almacena los 16 bits inferiores en un contenedor. Se utilizan automáticamente dos tipos de contenedor: un contenedor de arreglo para datos dispersos (menos de 4,096 enteros) y un contenedor de mapa de bits para datos densos. Esta estructura adaptativa permite que el índice se reduzca de 62,464 MB en la solución 1 a solo 2 MB aquí, y que las consultas AND/OR se completen en menos de 2 milisegundos.

Descripción general del esquema

KEY:    tag ID
VALUES: compressed bitmap of user IDs

• Índice: B-tree en el ID de etiqueta.

• Operaciones: rb_and_agg / rb_and_cardinality_agg (AND), rb_or_agg / rb_or_cardinality_agg (OR), rb_build_agg (construir mapa de bits).

Ventajas

• Almacenamiento mínimo: un solo índice B-tree con una entrada por etiqueta (como máximo unos pocos cientos de miles de entradas).

• Agregar una etiqueta a un grupo de usuarios solo requiere insertar o actualizar una fila de mapa de bits, sin actualizaciones masivas de filas.

• Las consultas AND u OR se ejecutan en menos de 2 milisegundos con 20 millones de usuarios.

Límites

• Los ID de usuario deben ser enteros. Si su sistema utiliza ID no enteros, cree una tabla de asignación.

• El tipo estándar roaringbitmap admite enteros de 32 bits (hasta aproximadamente 4,000 millones de ID de usuario). Si sus ID de usuario superan los 4,000 millones, utilice el enfoque uid_offset que se describe a continuación.

Procedimiento

Para obtener más información sobre la extensión roaringbitmap , consulte pg_roaringbitmap . Para gestionar el desbordamiento de UID, consulte Resolución de problemas de desbordamiento de UID .

1. Instale la extensión roaringbitmap:

   CREATE EXTENSION roaringbitmap;

2. Cree una tabla de mapas de bits que asocie cada etiqueta con el conjunto de usuarios que la tienen:

   CREATE TABLE t_tag_users (
     tagid      int PRIMARY KEY,   -- Tag ID
     uid_offset int,               -- Offset bucket (converts INT8 user IDs to INT4 range)
     userbits   roaringbitmap,     -- Compressed bitmap of user IDs
     mod_time   timestamp
   );

3. Pueble la tabla de mapas de bits a partir de la tabla t_user_tags creada en la solución 2:

   INSERT INTO t_tag_users
   SELECT tagid, uid_offset, rb_build_agg(uid::int) AS userbits
   FROM (
     SELECT
       unnest(tags) AS tagid,
       (uid / (2^31)::int8) AS uid_offset,   -- High bits as offset bucket
       mod(uid, (2^31)::int8) AS uid          -- Low bits as bitmap position
     FROM t_user_tags
   ) t
   GROUP BY tagid, uid_offset;

4. Consulte los usuarios que coinciden con las etiquetas 1 Y 3:

   -- Count matching users
   SELECT sum(ub) FROM (
     SELECT uid_offset, rb_and_cardinality_agg(userbits) AS ub
     FROM t_tag_users
     WHERE tagid IN (1, 3)
     GROUP BY uid_offset
   ) t;
   -- Time: 1.5 ms
   
   -- Get the bitmap result (user IDs reconstructed from offset + bitmap)
   SELECT uid_offset, rb_and_agg(userbits) AS ub
   FROM t_tag_users
   WHERE tagid IN (1, 3)
   GROUP BY uid_offset;

5. Consulte los usuarios que coinciden con la etiqueta 1 O 3 O 10 O 200:

   -- Count matching users
   SELECT sum(ub) FROM (
     SELECT uid_offset, rb_or_cardinality_agg(userbits) AS ub
     FROM t_tag_users
     WHERE tagid IN (1, 3, 10, 200)
     GROUP BY uid_offset
   ) t;
   -- Time: 1.7 ms
   
   -- Get the bitmap result
   SELECT uid_offset, rb_or_agg(userbits) AS ub
   FROM t_tag_users
   WHERE tagid IN (1, 3, 10, 200)
   GROUP BY uid_offset;

Resumen

ApsaraDB RDS for PostgreSQL 12 y versiones posteriores admiten la extensión roaringbitmap, que permite generar, comprimir y agregar mapas de bits de forma eficiente mediante operaciones AND, OR, NOT y XOR. Para sistemas de marketing de precisión con cientos de millones de usuarios y decenas de millones de etiquetas, la solución 3 ofrece tiempos de consulta inferiores a 2 milisegundos y un almacenamiento de índices menor a 2 MB, lo que satisface los requisitos de velocidad y escala que los modelos de esquema más simples no pueden cumplir.

Pasos siguientes

• Use the roaringbitmap extension

• Create an ApsaraDB RDS for PostgreSQL instance