ApsaraDB RDS：Implementasikan pemasaran presisi waktu nyata dan pemilihan kelompok pengguna menggunakan instance ApsaraDB RDS for PostgreSQL

Solusi 1

Ikhtisar

• Skema:

  KEY: ID pengguna
  Tag 1: 
  Tag 2: 
  ...
  Tag N:

• Indeks: satu indeks untuk setiap bidang tag

• Metode pencarian:

  Kombinasi AND, OR, dan NOT
  where Tag a and Tag b and ...

• Kekurangan:

  • Jumlah tag dan bidang yang besar ada. Dalam hal ini, banyak sumber daya penyimpanan yang dikonsumsi.

  • Jumlah bidang dalam database mungkin melebihi batas atas. Dalam banyak kasus, sebuah database dapat berisi hingga 1.000 bidang. Untuk menyelesaikan masalah ini, Anda dapat menggunakan skema banyak-ke-banyak untuk mempertahankan satu entri untuk setiap tag.

  • Kombinasi kondisi pemilihan beragam. Oleh karena itu, tidak ada indeks tetap yang tersedia untuk memfasilitasi pemilihan pengguna. Dalam hal ini, jika Anda membuat indeks untuk setiap bidang, konsumsi sumber daya penyimpanan meningkat secara dramatis.

  • Sejumlah besar data harus diperbarui saat tag grup baru ditambahkan.

  • Kinerja kueri buruk.

Prosedur

1. Buat tabel kelompok pengguna, dengan setiap entri mewakili kelompok pengguna. Contoh kode:

   create table t_tag_dict (
   tag int primary key,   -- ID tag yang mengidentifikasi kelompok pengguna.
   info text,  -- Deskripsi kelompok pengguna.
   crt_time timestamp  -- Waktu.
   ); 

2. Buat 100.000 tag kelompok pengguna. Contoh kode:

   insert into t_tag_dict values (1, 'Pria', now());
   insert into t_tag_dict values (2, 'Wanita', now());
   insert into t_tag_dict values (3, 'Lebih tua dari 24 tahun', now());
   -- ...
   
   insert into t_tag_dict 
   select generate_series(4,100000), md5(random()::text), clock_timestamp();

3. Buat tabel profil pengguna, dengan setiap entri pengguna mewakili tag pengguna. Contoh kode:

   create table t_user_tag (
   uid int8,   -- ID pengguna.
   tag int,            -- Tag pengguna. Sebuah tag mengidentifikasi kelompok pengguna.
   mod_time timestamp,     -- Waktu.
   primary key (tag,uid)
   ); 

4. Tetapkan 64 tag acak untuk masing-masing 10 juta pengguna pria dan 10 juta pengguna wanita. Total 1,28 miliar entri dihasilkan. Contoh kode:

   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;
   
   -- Anda juga dapat menggunakan metode berikut untuk mengimpor tag:
   create sequence seq;
   
   vi test.sql
   insert into t_user_tag
   select uid, gen_rand_tag(100000,63) as tag, clock_timestamp() 
   from nextval('seq'::regclass) as uid 
   on conflict(tag,uid) do nothing;
   
   pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 400000

5. Kueri pengguna yang cocok dengan tag 1 dan 3. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   select count(*) from 
   (
   select uid from t_user_tag where tag=1 
   intersect
   select uid from t_user_tag where tag=3
   ) t;
   -- Time: 1494.789 ms (00:01.495)
   
   -- Dapatkan ID kelompok pengguna.
   select uid from t_user_tag where tag=1 
   intersect
   select uid from t_user_tag where tag=3;
   -- Time: 3246.184 ms (00:03.246)

6. Kueri pengguna yang cocok dengan tag 1, 3, 10, atau 200. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   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: 3577.714 ms (00:03.578)
   
   -- Dapatkan ID kelompok 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;
   -- Time: 5682.458 ms (00:05.682)

Solusi 2

Ikhtisar

• Skema:

  KEY: ID pengguna
  VALUES: array tag

• Indeks:

  Bidang array tag: GIN

• Metode pencarian:

  AND, OR, dan NOT
  where VALUES @> array[Tags] -- AND
  where VALUES && array[Tags] -- OR
  where not VALUES @> array [Tags] -- NOT

• Kekurangan:

  • Jika Anda menggunakan array sebagai pengganti beberapa bidang untuk menyimpan tag, database harus mendukung GIN. Namun, tidak semua database mendukung GIN.

  • Jika Anda menggunakan array sebagai pengganti beberapa bidang untuk menyimpan tag dan menggunakan GIN, konsumsi sumber daya penyimpanan meningkat secara dramatis.

  • Sejumlah besar data harus diperbarui saat tag grup baru ditambahkan.

Prosedur

1. Buat tabel kelompok pengguna, dengan setiap entri mewakili kelompok pengguna. Contoh kode:

   create table t_tag_dict (
   tag int primary key,   -- ID tag yang mengidentifikasi kelompok pengguna.
   info text,  -- Deskripsi kelompok pengguna.
   crt_time timestamp  -- Waktu.
   ); 

2. Buat 100.000 tag kelompok pengguna. Contoh kode:

   insert into t_tag_dict values (1, 'Pria', now());
   insert into t_tag_dict values (2, 'Wanita', now());
   insert into t_tag_dict values (3, 'Lebih tua dari 24 tahun', now());
   -- ...
   
   insert into t_tag_dict 
   select generate_series(4,100000), md5(random()::text), clock_timestamp();

3. Buat tabel profil pengguna, dengan entri tunggal setiap pengguna mewakili array tag pengguna. Contoh kode:

   create table t_user_tags (
   uid int8 primary key,   -- ID pengguna.
   tags int[],            -- Array tag pengguna. Sebuah tag mengidentifikasi kelompok pengguna.
   mod_time timestamp     -- Waktu.
   ); 

4. Buat fungsi untuk menghasilkan array tag acak. Contoh kode:

   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. Pilih 8 tag acak dari 100.000 tag. Contoh kode:

   select gen_rand_tags(100000, 8);
   
                      gen_rand_tags                   
   ---------------------------------------------------
    {43494,46038,74102,25308,99129,40893,33653,29690}
   (1 row)

6. Tag 20 juta pengguna. Setiap pengguna memiliki 64 tag acak. Setengah dari pengguna adalah pria dan setengah lainnya adalah wanita. Contoh kode:

   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();

7. Buat GIN untuk bidang tag grup. Contoh kode:

   create index idx_t_user_tags_1 on t_user_tags using gin (tags);

8. Kueri pengguna yang cocok dengan tag 1 dan 3. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   select count(uid) from t_user_tags where tags @> array[1,3];
   
   -- Dapatkan ID kelompok pengguna.
   select uid from t_user_tags where tags @> array[1,3];

9. Kueri pengguna yang cocok dengan tag 1, 3, 10, atau 200. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   select count(uid) from t_user_tags where tags && array[1,3,10,200];
   
   -- Dapatkan ID kelompok pengguna.
   select uid from t_user_tags where tags && array[1,3,10,200];

Solusi 3

Solusi 3 menggunakan ekstensi roaringbitmap untuk mengimplementasikan kueri cepat. Untuk informasi lebih lanjut, lihat Gunakan Ekstensi Roaringbitmap.

Ikhtisar

• Skema:

  KEY: ID tag
  VALUES: bitmap pengguna

• Indeks:

  Bidang ID tag: Indeks Pohon-B

• Metode pencarian:

  Agregat bitmap: AND, OR, dan NOT
  and_agg(bitmaps) where KEY in (Tags) -- AND
  or_agg(bitmaps) where KEY in (Tags) -- OR
  except(bitmap1,bitmap2) -- NOT 

• Keuntungan:

  • Tabel mengonsumsi sejumlah kecil sumber daya penyimpanan.

  • Indeks mengonsumsi sejumlah kecil sumber daya penyimpanan. Hanya satu indeks Pohon-B yang diperlukan untuk menyimpan entri indeks. Jumlah entri indeks sama dengan jumlah tag. Dalam banyak kasus, jumlah tag kurang dari 1 juta.

  • Jika Anda ingin menambahkan tag ke kelompok pengguna, Anda hanya perlu menambahkan entri bitmap grup. Dalam hal ini, Anda tidak perlu memperbarui sejumlah besar data.

  • Kinerja kueri sangat baik.

• Kekurangan:

  • Panjang maksimum bitmap adalah 1 GB. Jika jumlah pengguna melebihi panjang maksimum, Anda harus menggunakan offset.

    offset0_bitmap, offset1gb_bitmap, ... 

  • ID pengguna harus terdiri dari digit, dan angka berturut-turut direkomendasikan. Jika tidak ada ID pengguna numerik, Anda harus membuat tabel pemetaan.

Prosedur

1. Instal ekstensi roaringbitmap. Contoh kode:

   create extension roaringbitmap;

2. Buat tabel bitmap yang berisi tag pengguna. Contoh kode:

   create table t_tag_users (
     tagid int primary key,   -- ID tag atau ID kelompok pengguna. 
     uid_offset int,          -- Konversikan ID pengguna dari INT8 ke INT4.    
     userbits roaringbitmap,     -- Bitmap ID pengguna.  
     mod_time timestamp       -- Waktu. 
   );

3. Masukkan data untuk menghasilkan tabel bitmap yang berisi tag ID pengguna. Contoh kode:

   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, 
     mod(uid, (2^31)::int8) as uid 
   from t_user_tags 
   ) t 
   group by tagid, uid_offset; 

4. Kueri pengguna yang cocok dengan tag 1 dan 3. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   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;
   
   -- Dapatkan ID kelompok pengguna.
   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, 3, 10, atau 200. Contoh kode:

   -- Kueri jumlah kelompok pengguna.
   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;
   
   -- Dapatkan ID kelompok pengguna.
   select uid_offset,rb_or_agg(userbits) as ub 
   from t_tag_users 
   where tagid in (1,3,10,200) 
   group by uid_offset;