ApsaraDB RDS:Pemasaran presisi Real-time (Segmentasi Audiens)

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

• Instans ApsaraDB RDS for PostgreSQL. Lihat Create an ApsaraDB RDS for PostgreSQL instance.

• Daftar putih alamat IP yang dikonfigurasi untuk instans RDS. Lihat Configure an IP address whitelist.

• Akun database. Lihat Create an account.

• Database. Lihat Create a database.

Solusi 3 memerlukan ekstensi roaringbitmap dan PostgreSQL 12 atau versi lebih baru. Contoh dalam topik ini menggunakan PostgreSQL 12. Untuk detail ekstensi, lihat Use the roaringbitmap extension.

Latar Belakang

Sistem pemasaran presisi perlu mengkueri pengguna berdasarkan kombinasi tag apa pun secara real-time—misalnya, mengidentifikasi semua pengguna laki-laki berusia di atas 24 tahun yang menjelajahi ponsel dalam 24 jam terakhir tetapi belum melakukan pemesanan. Tantangannya adalah melakukannya dengan cepat di antara puluhan juta pengguna dengan puluhan ribu tag.

Permasalahan umum:

• Jumlah tag meningkat hingga ratusan ribu, mendekati batas jumlah field database (biasanya 1.000 field per tabel).

• Menyimpan tag sebagai array memerlukan dukungan Generalized Inverted Index (GIN), yang tidak disediakan oleh semua database dan mengonsumsi ruang penyimpanan signifikan.

• Kombinasi kondisi kueri tidak dapat diprediksi, sehingga indeks statis tidak dapat mencakup semua kasus.

• Data profil pengguna harus tetap mendekati real-time—pengguna yang melakukan pemesanan tadi malam seharusnya tidak menerima iklan produk yang sama hari ini.

Topik ini menjelaskan tiga solusi berbasis PostgreSQL yang mengatasi permasalahan tersebut dengan pertukaran kompromi yang berbeda.

Pilih Solusi

Lingkungan pengujian: Instans RDS yang menjalankan MySQL 8.0 dan PostgreSQL 12, dengan 8 core CPU, memori 32 GB, dan Enhanced SSD (ESSD) 1.500 GB. Dataset: 20 juta pengguna, 100.000 tag, 64 tag per pengguna (total 1,28 miliar catatan tag).

Gunakan Solusi 3 untuk sistem produksi dengan jutaan pengguna dan persyaratan kueri real-time. Gunakan Solusi 1 jika Anda harus mendukung MySQL. Gunakan Solusi 2 sebagai jalan tengah ketika ruang penyimpanan lebih terbatas dibandingkan kecepatan kueri.

Solusi 1: Satu baris per pengguna per tag

Solusi ini bekerja dengan PostgreSQL dan MySQL.

Ikhtisar skema

KUNCI:    ID pengguna
NILAI:    satu baris per tag (tag 1, tag 2, ... tag N)

• Indeks: Indeks B-tree pada setiap field tag

• Pencarian: WHERE tag = a AND tag = b (dengan INTERSECT/UNION)

Kekurangan

• Penggunaan penyimpanan tinggi: satu indeks per field tag untuk 20 juta pengguna menghasilkan penyimpanan indeks sebesar 62.464 MB saja.

• Menambahkan tag baru memerlukan penyisipan baris untuk setiap pengguna dalam kelompok tersebut.

• Kinerja kueri menurun seiring bertambahnya jumlah tag dalam kondisi filter.

Prosedur

1. Buat tabel kamus tag, dengan satu baris per tag:

   CREATE TABLE t_tag_dict (
     tag      int PRIMARY KEY,   -- ID Tag
     info     text,              -- Deskripsi tag
     crt_time timestamp          -- Waktu pembuatan
   );

2. Masukkan 100.000 tag:

   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());
   -- ... tag tambahan ...
   
   INSERT INTO t_tag_dict
   SELECT generate_series(4, 100000), md5(random()::text), clock_timestamp();

3. Buat tabel profil pengguna, dengan satu baris per pengguna per tag:

   CREATE TABLE t_user_tag (
     uid      int8,       -- ID Pengguna
     tag      int,        -- ID Tag
     mod_time timestamp,  -- Terakhir dimodifikasi
     PRIMARY KEY (tag, uid)
   );

4. Isi 20 juta pengguna dengan masing-masing 64 tag acak (total 1,28 miliar baris):

   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. Kueri pengguna yang cocok dengan tag 1 DAN 3:

   -- Hitung jumlah pengguna yang cocok
   SELECT count(*) FROM (
     SELECT uid FROM t_user_tag WHERE tag = 1
     INTERSECT
     SELECT uid FROM t_user_tag WHERE tag = 3
   ) t;
   -- Waktu: 1.494 ms
   
   -- Dapatkan ID pengguna
   SELECT uid FROM t_user_tag WHERE tag = 1
   INTERSECT
   SELECT uid FROM t_user_tag WHERE tag = 3;
   -- Waktu: 3.246 ms

6. Kueri pengguna yang cocok dengan tag 1 ATAU 3 ATAU 10 ATAU 200:

   -- Hitung jumlah pengguna yang cocok
   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;
   -- Waktu: 3.578 ms
   
   -- Dapatkan ID pengguna
   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;
   -- Waktu: 5.682 ms

Solusi 2: Array tag dengan indeks GIN

Solusi ini memerlukan PostgreSQL. MySQL tidak mendukung array atau indeks GIN.

Ikhtisar skema

KUNCI:    ID pengguna
NILAI:    array ID tag

• Indeks: GIN pada field array tag

• Operator pencarian: @> (AND/memuat), && (OR/tumpang tindih), NOT @> (NOT)

Kekurangan

• Indeks GIN mengonsumsi ruang penyimpanan jauh lebih besar daripada indeks B-tree untuk dataset besar.

• Menambahkan tag ke pengguna memerlukan pembaruan baris mereka, yang bisa mahal pada skala besar.

• Penulisan GIN menggunakan mekanisme fastupdate yang ditunda: pembaruan terakumulasi dalam daftar tertunda dan disiram secara batch. Ketika daftar tertunda mencapai gin_pending_list_limit (default 4 MB), proses penyiraman terjadi selama operasi penulisan, menyebabkan lonjakan latensi periodik. Untuk beban kerja pembaruan profil pengguna real-time, pantau perilaku ini dan pertimbangkan untuk menyesuaikan gin_pending_list_limit atau menonaktifkan fastupdate jika konsistensi latensi penulisan sangat penting.

Prosedur

1. Buat tabel kamus tag (sama seperti Solusi 1):

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

2. Masukkan 100.000 tag:

   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. Buat tabel profil pengguna dengan satu baris per pengguna dan array tag:

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

4. Buat fungsi pembantu untuk menghasilkan array tag acak:

   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. Beri tag 20 juta pengguna dengan masing-masing 64 tag acak (10 juta laki-laki, 10 juta perempuan):

   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. Buat indeks GIN pada array tag:

   CREATE INDEX idx_t_user_tags_1 ON t_user_tags USING gin (tags);
   -- Waktu pembuatan indeks: ~20 menit

7. Kueri pengguna yang cocok dengan tag 1 DAN 3:

   -- Hitung jumlah pengguna
   SELECT count(uid) FROM t_user_tags WHERE tags @> ARRAY[1, 3];
   
   -- Dapatkan ID pengguna
   SELECT uid FROM t_user_tags WHERE tags @> ARRAY[1, 3];

8. Kueri pengguna yang cocok dengan tag 1 ATAU 3 ATAU 10 ATAU 200:

   -- Hitung jumlah pengguna
   SELECT count(uid) FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];
   
   -- Dapatkan ID pengguna
   SELECT uid FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];

Solusi 3: Agregasi bitmap Roaring

Solusi ini memerlukan PostgreSQL dengan ekstensi roaringbitmap. MySQL tidak mendukung ekstensi ini.

Cara kerja

Model bitmap roaring membalik skema: alih-alih menyimpan tag apa saja yang dimiliki pengguna, ia menyimpan—untuk setiap tag—bitmap terkompresi dari semua pengguna yang memiliki tag tersebut. Bayangkan roaringbitmap sebagai string bit yang sangat panjang di mana setiap posisi bit merepresentasikan ID pengguna; jika bit *n* diatur, maka pengguna *n* memiliki tag tersebut.

Algoritma Roaring Bitmap membagi bilangan bulat 32-bit menjadi chunk 2^16 berdasarkan 16 bit tinggi, dan menyimpan 16 bit rendah dalam sebuah container. Dua jenis container digunakan secara otomatis: container array untuk data sparse (kurang dari 4.096 bilangan bulat) dan container bitmap untuk data padat. Struktur adaptif inilah yang membuat ukuran indeks menyusut dari 62.464 MB pada Solusi 1 menjadi hanya 2 MB di sini, serta membuat kueri AND/OR selesai dalam waktu kurang dari 2 milidetik.

Ikhtisar skema

KUNCI:    ID tag
NILAI:    bitmap terkompresi ID pengguna

• Indeks: B-tree pada ID tag

• Operasi: rb_and_agg / rb_and_cardinality_agg (AND), rb_or_agg / rb_or_cardinality_agg (OR), rb_build_agg (bangun bitmap)

Keunggulan

• Penyimpanan minimal: satu indeks B-tree dengan satu entri per tag (maksimal beberapa ratus ribu entri).

• Menambahkan tag ke kelompok pengguna hanya memerlukan penyisipan atau pembaruan satu baris bitmap—tanpa pembaruan massal baris.

• Kueri AND atau OR berjalan dalam waktu kurang dari 2 milidetik untuk 20 juta pengguna.

Batasan

• ID pengguna harus berupa bilangan bulat. Jika sistem Anda menggunakan ID non-bilangan bulat, buat tabel pemetaan.

• Tipe roaringbitmap standar mendukung bilangan bulat 32-bit (hingga ~4 miliar ID pengguna). Jika ID pengguna Anda melebihi 4 miliar, gunakan pendekatan uid_offset yang dijelaskan di bawah.

Prosedur

Untuk informasi lebih lanjut tentang ekstensi roaringbitmap, lihat pg_roaringbitmap. Untuk menangani overflow UID, lihat Troubleshooting for UID overflow.

1. Instal ekstensi roaringbitmap:

   CREATE EXTENSION roaringbitmap;

2. Buat tabel bitmap yang memetakan setiap tag ke kumpulan pengguna yang memilikinya:

   CREATE TABLE t_tag_users (
     tagid      int PRIMARY KEY,   -- ID Tag
     uid_offset int,               -- Bucket offset (mengonversi ID pengguna INT8 ke rentang INT4)
     userbits   roaringbitmap,     -- Bitmap terkompresi ID pengguna
     mod_time   timestamp
   );

3. Isi tabel bitmap dari tabel t_user_tags yang dibuat pada Solusi 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,   -- Bit tinggi sebagai bucket offset
       mod(uid, (2^31)::int8) AS uid          -- Bit rendah sebagai posisi bitmap
     FROM t_user_tags
   ) t
   GROUP BY tagid, uid_offset;

4. Kueri pengguna yang cocok dengan tag 1 DAN 3:

   -- Hitung jumlah pengguna yang cocok
   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;
   -- Waktu: 1,5 ms
   
   -- Dapatkan hasil bitmap (ID pengguna direkonstruksi dari 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. Kueri pengguna yang cocok dengan tag 1 ATAU 3 ATAU 10 ATAU 200:

   -- Hitung jumlah pengguna yang cocok
   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;
   -- Waktu: 1,7 ms
   
   -- Dapatkan hasil bitmap
   SELECT uid_offset, rb_or_agg(userbits) AS ub
   FROM t_tag_users
   WHERE tagid IN (1, 3, 10, 200)
   GROUP BY uid_offset;

Ringkasan

ApsaraDB RDS for PostgreSQL 12 dan versi lebih baru mendukung ekstensi roaringbitmap, yang memungkinkan pembuatan, kompresi, dan agregasi bitmap secara efisien dengan operasi AND, OR, NOT, dan XOR. Untuk sistem pemasaran presisi dengan ratusan juta pengguna dan puluhan juta tag, Solusi 3 memberikan waktu kueri di bawah 2 milidetik dan penyimpanan indeks di bawah 2 MB—memenuhi persyaratan kecepatan dan skala yang tidak dapat dipenuhi oleh model skema sederhana.

Langkah Berikutnya

• Use the roaringbitmap extension

• Create an ApsaraDB RDS for PostgreSQL instance