ApsaraDB RDS:Marketing de précision en temps réel (Segmentation d'audience)

Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

• Une instance ApsaraDB RDS for PostgreSQL. Consultez la section Créer une instance ApsaraDB RDS for PostgreSQL.

• Une liste blanche d'adresses IP configurée pour l'instance RDS. Consultez la section Configurer une liste blanche d'adresses IP.

• Un compte de base de données. Consultez la section Créer un compte.

• Une base de données. Consultez la section Créer une base de données.

La solution 3 nécessite l'extension roaringbitmap et PostgreSQL 12 ou version ultérieure. Les exemples de cette rubrique utilisent PostgreSQL 12. Pour plus de détails sur l'extension, consultez la section Utiliser l'extension roaringbitmap .

Contexte

Les systèmes de marketing de précision doivent interroger les utilisateurs selon n'importe quelle combinaison de tags en temps réel. Il s'agit par exemple d'identifier tous les hommes de plus de 24 ans qui ont consulté des téléphones mobiles au cours des dernières 24 heures mais n'ont pas encore passé commande. Le défi consiste à effectuer cette opération rapidement sur des dizaines de millions d'utilisateurs disposant de dizaines de milliers de tags.

Difficultés courantes :

• Le nombre de tags atteint des centaines de milliers, ce qui repousse les limites des champs de base de données (généralement 1 000 champs par table).

• Le stockage des tags sous forme de tableaux nécessite la prise en charge des index GIN (Generalized Inverted Index), que toutes les bases de données ne proposent pas et qui consomment beaucoup d'espace de stockage.

• Les combinaisons de conditions de requête sont imprévisibles, de sorte que les index statiques ne peuvent pas couvrir tous les cas.

• Les données de profil utilisateur doivent rester quasi en temps réel : un utilisateur ayant passé commande la veille ne doit pas recevoir de publicités pour ce même produit le jour suivant.

Cette rubrique décrit trois solutions basées sur PostgreSQL qui répondent à ces difficultés avec différents compromis.

Choisir une solution

Environnement de test : instances RDS exécutant MySQL 8.0 et PostgreSQL 12, avec 8 cœurs CPU, 32 Go de mémoire et un disque ESSD (Enhanced SSD) de 1 500 Go. Jeu de données : 20 millions d'utilisateurs, 100 000 tags, 64 tags par utilisateur (1,28 milliard d'enregistrements de tags au total).

Utilisez la solution 3 pour les systèmes de production comptant des millions d'utilisateurs et nécessitant des requêtes en temps réel. Optez pour la solution 1 si vous devez prendre en charge MySQL. La solution 2 constitue un compromis lorsque le stockage est plus contraint que la vitesse des requêtes.

Solution 1 : Une ligne par utilisateur et par tag

Cette solution fonctionne avec PostgreSQL et MySQL.

Aperçu du schéma

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

• Index : index B-tree sur chaque champ de tag

• Recherche : WHERE tag = a AND tag = b (avec INTERSECT/UNION)

Inconvénients

• Utilisation élevée du stockage : un index par champ de tag sur 20 millions d'utilisateurs génère à lui seul 62 464 Mo de stockage d'index.

• L'ajout d'un nouveau tag nécessite l'insertion de lignes pour chaque utilisateur du groupe.

• Les performances des requêtes se dégradent avec l'augmentation du nombre de tags dans la condition de filtre.

Procédure

1. Créez une table de dictionnaire de tags, avec une ligne par tag :

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

2. Insérez 100 000 tags :

   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. Créez une table de profil utilisateur, avec une ligne par utilisateur et par tag :

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

4. Remplissez la table avec 20 millions d'utilisateurs ayant chacun 64 tags aléatoires (1,28 milliard de lignes au 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. Interrogez les utilisateurs correspondant aux tags 1 ET 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. Interrogez les utilisateurs correspondant au tag 1 OU 3 OU 10 OU 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

Solution 2 : Tableaux de tags avec index GIN

Cette solution nécessite PostgreSQL. MySQL ne prend pas en charge les tableaux ni les index GIN.

Aperçu du schéma

KEY:    user ID
VALUES: array of tag IDs

• Index : GIN sur le champ de tableau de tags

• Opérateurs de recherche : @> (AND/contient), && (OR/recouvrement), NOT @> (NOT)

Inconvénients

• Les index GIN consomment considérablement plus d'espace de stockage que les index B-tree pour les grands ensembles de données.

• L'ajout de tags à un utilisateur nécessite la mise à jour de sa ligne, ce qui peut être coûteux à grande échelle.

• Les écritures GIN utilisent un mécanisme différé fastupdate : les mises à jour s'accumulent dans une liste en attente et sont vidées par lots. Lorsque la liste en attente atteint gin_pending_list_limit (4 Mo par défaut), un vidage se produit lors d'une opération d'écriture, provoquant des pics de latence périodiques. Pour les charges de travail de mise à jour de profils utilisateurs en temps réel, surveillez ce comportement et envisagez d'ajuster gin_pending_list_limit ou de désactiver fastupdate si la cohérence de la latence d'écriture est critique.

Procédure

1. Créez une table de dictionnaire de tags (identique à la solution 1) :

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

2. Insérez 100 000 tags :

   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. Créez une table de profil utilisateur avec une ligne par utilisateur et un tableau de tags :

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

4. Créez une fonction d'aide pour générer des tableaux de tags aléatoires :

   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. Attribuez 64 tags aléatoires à 20 millions d'utilisateurs (10 millions d'hommes, 10 millions de femmes) :

   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. Créez un index GIN sur le tableau de tags :

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

7. Interrogez les utilisateurs correspondant aux tags 1 ET 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. Interrogez les utilisateurs correspondant au tag 1 OU 3 OU 10 OU 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];

Solution 3 : Agrégation de bitmaps Roaring

Cette solution nécessite PostgreSQL avec l'extension roaringbitmap . MySQL ne prend pas en charge cette extension.

Fonctionnement

Le modèle de bitmap roaring inverse le schéma : au lieu de stocker les tags associés à un utilisateur, il stocke — pour chaque tag — un bitmap compressé de tous les utilisateurs portant ce tag. Considérez un roaringbitmap comme une très longue chaîne de bits où chaque position de bit représente un ID utilisateur ; si le bit n est défini, l'utilisateur n possède le tag.

L'algorithme Roaring Bitmap divise les entiers 32 bits en blocs de 2^16 en fonction des 16 bits de poids fort et stocke les 16 bits de poids faible dans un conteneur. Deux types de conteneurs sont utilisés automatiquement : un conteneur de type tableau pour les données clairsemées (moins de 4 096 entiers) et un conteneur de type bitmap pour les données denses. Cette structure adaptative explique pourquoi l'index passe de 62 464 Mo dans la solution 1 à seulement 2 Mo ici, et pourquoi les requêtes AND/OR s'exécutent en moins de 2 millisecondes.

Aperçu du schéma

KEY:    tag ID
VALUES: compressed bitmap of user IDs

• Index : B-tree sur l'ID du tag

• Opérations : rb_and_agg / rb_and_cardinality_agg (AND), rb_or_agg / rb_or_cardinality_agg (OR), rb_build_agg (création du bitmap)

Avantages

• Stockage minimal : un index B-tree avec une entrée par tag (au maximum quelques centaines de milliers d'entrées).

• L'ajout d'un tag à un groupe d'utilisateurs nécessite uniquement l'insertion ou la mise à jour d'une ligne de bitmap, sans mise à jour massive des lignes.

• Les requêtes AND ou OR s'exécutent en moins de 2 millisecondes sur 20 millions d'utilisateurs.

Limitations

• Les IDs utilisateur doivent être des entiers. Si votre système utilise des IDs non entiers, créez une table de mappage.

• Le type standard roaringbitmap prend en charge les entiers 32 bits (jusqu'à environ 4 milliards d'IDs utilisateur). Si vos IDs utilisateur dépassent 4 milliards, utilisez l'approche uid_offset décrite ci-dessous.

Procédure

Pour plus d'informations sur l'extension roaringbitmap , consultez la section pg_roaringbitmap . Pour gérer le dépassement d'UID, consultez la section Dépannage pour le dépassement d'UID .

1. Installez l'extension roaringbitmap :

   CREATE EXTENSION roaringbitmap;

2. Créez une table de bitmap qui mappe chaque tag à l'ensemble des utilisateurs qui le possèdent :

   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. Remplissez la table de bitmap à partir de la table t_user_tags créée dans la solution 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. Interrogez les utilisateurs correspondant aux tags 1 ET 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. Interrogez les utilisateurs correspondant au tag 1 OU 3 OU 10 OU 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;

Résumé

ApsaraDB RDS for PostgreSQL 12 et versions ultérieures prend en charge l'extension roaringbitmap , qui permet une génération, une compression et une agrégation efficaces de bitmaps avec des opérations AND, OR, NOT et XOR. Pour les systèmes de marketing de précision comptant des centaines de millions d'utilisateurs et des dizaines de millions de tags, la solution 3 offre des temps de requête inférieurs à 2 millisecondes et un stockage d'index inférieur à 2 Mo, répondant ainsi aux exigences de vitesse et d'échelle que les modèles de schéma plus simples ne peuvent satisfaire.

Étapes suivantes

• Utiliser l'extension roaringbitmap

• Créer une instance ApsaraDB RDS for PostgreSQL