Help Center/ Relational Database Service_RDS for PostgreSQL/ Best Practices/ Real-Time Precision Marketing (Audience Segmentation)

Updated on 2026-09-23 GMT+08:00

Real-Time Precision Marketing (Audience Segmentation)

Context

In consumer-oriented (ToC) industries such as e-commerce, finance, online education, and gaming, precision marketing is a core capability for improving conversion rates and reducing marketing costs. The essence of precision marketing is to push the right content to the right people at the right time. The right people are the target audience selected through audience segmentation.

The business requirements for audience segmentation can be abstracted as follows: Based on the tags carried by users, perform intersection, union, and difference operations to select the target audience that meets the conditions from hundreds of millions of users in real time. Typical scenarios include:

Audience segmentation based on combined tags: For example, select users who have made purchases in the last 30 days, are aged between 25 and 35, and have not purchased a new product as the target audience for new product recommendation.
Real-time update: User tags change in real time based on user behavior (for example, a user is labeled as a "high-value customer" immediately after completing an order). The segmentation result needs to reflect the latest tag status in seconds.
Hundreds of millions of users and tens of millions of tags: Top Internet platforms can have hundreds of millions of users and thousands to tens of thousands of tag dimensions. Traditional database solutions cannot balance write throughput and query performance.

Huawei Cloud RDS for PostgreSQL provides multiple solutions to implement real-time audience segmentation for precision marketing. This practice uses precision marketing in e-commerce as a typical scenario to demonstrate how to use RDS for PostgreSQL to perform real-time audience segmentation based on tags for hundreds of millions of users. By modeling and querying the same batch of user tag data using three different solutions, we compare the differences in write, query, and storage performance of the solutions and provide selection suggestions.

Prerequisites

RDS for PostgreSQL instances have been created and are running properly.
RDS for PostgreSQL instance version requirements: Solution 1 and Solution 2 require PostgreSQL 10 or later, and Solution 3 requires PostgreSQL 11 or later.
Database accounts have been created and granted the read and write permissions on the corresponding schema.
Solution 3 requires the bitmap calculation extension roaringbitmap to be installed on the RDS console (PostgreSQL 11 or later).

Solution Overview

RDS for PostgreSQL provides the following three solutions for audience segmentation:

Solution 1 (array + GIN index): User tags are stored in the user table as integer arrays (int[]), and the Generalized Inverted Index (GIN) is used to accelerate array containment and intersection queries. This solution is the simplest and is suitable for scenarios with a moderate number of tags (no more than hundreds of tags per user) and millions of users.
Solution 2 (inverted index): The user-tag relationship is split into an inverted table (each row contains a tag-UID pair), and the B-tree index and GROUP BY are used to implement intersection, union, and difference operations. This solution is suitable for scenarios with a large number of tag dimensions and tens of millions of users.
Solution 3 (roaringbitmap): The bitmap data structure is used to store the user set corresponding to each tag, and bitmap bitwise operations (AND/OR/ANDNOT) are used to implement millisecond-level segmentation. This solution is suitable for ultra-large-scale scenarios with hundreds of millions of users and tens of millions of tags.

In this solution, all tags of each user are stored in the user table as an integer array (int[]). PostgreSQL's GIN index is used to accelerate array containment queries, enabling tag-based intersection, union, and difference operations for audience segmentation. This solution does not require additional extensions and can be implemented using native SQL. It is suitable for quick verification and small- to medium-scale scenarios.

The table structure design is as follows:

Creating a user tag table

CREATE TABLE t_user (
    uid   int PRIMARY KEY,       -- User ID
    uname text,                   -- Username (for demonstration)
    tag   int[] NOT NULL          -- Tag array, with each element being a tag ID
);

Writing user data (example)

INSERT INTO t_user VALUES
(1, 'user_001', ARRAY[1,2,3,10,20]),
(2, 'user_002', ARRAY[2,3,5,10]),
(3, 'user_003', ARRAY[1,3,10,30]),
(4, 'user_004', ARRAY[2,4,10,20]),
(5, 'user_005', ARRAY[1,5,30]);

Creating a GIN index to accelerate array queries

CREATE INDEX idx_user_tag ON t_user USING GIN (tag);

Audience segmentation query examples:
- Intersection segmentation (users who have tags 1, 3, and 10, that is, the AND relationship)
```
SELECT uid, uname FROM t_user WHERE tag @> ARRAY[1,3,10];
```
- Union segmentation (users who have any of tags 1, 2, and 3, that is, the OR relationship)
```
SELECT uid, uname FROM t_user WHERE tag && ARRAY[1,2,3];
```
- Difference segmentation (users who have tags 1 and 3 but do not have tag 5)
```
SELECT uid, uname FROM t_user
WHERE tag @> ARRAY[1,3]
  AND NOT tag @> ARRAY[5];
```
- Real-time tag update (adding and deleting tags)
  Add tag 99 to user 1.
```
UPDATE t_user SET tag = tag || 99 WHERE uid = 1;
```
  Delete tag 20 from user 1.
```
UPDATE t_user SET tag = array_remove(tag, 20) WHERE uid = 1;
```
The @> operator indicates containment, meaning that the left array contains all elements of the right array. The && operator indicates intersection, meaning that the arrays on both sides have common elements. The GIN index automatically accelerates these two operators. Solution 1 performs well when the number of tags for a single user does not exceed hundreds and the number of users is in the millions. However, when the number of users reaches tens of millions and multiple tags need to be combined for audience segmentation, the cost of array scans and GIN index bitmap scans increases significantly.

This solution is inspired by the inverted index concept used in search engines. It splits the user-tag relationship into an independent inverted table, with each row storing a (tag, uid) pair. In this way, all users corresponding to each tag can be quickly obtained through primary key index range scanning, and then the intersection, union, and difference operations of multiple tags can be implemented using GROUP BY and HAVING.

The table structure design is as follows:

Inverted table: one row per (tag, user)

CREATE TABLE t_user_tag (
    tag int NOT NULL,
    uid int NOT NULL,
    PRIMARY KEY (tag, uid)
);

Writing inverted data (example)

INSERT INTO t_user_tag VALUES
(1,1),(2,1),(3,1),(10,1),(20,1),
(2,2),(3,2),(5,2),(10,2),
(1,3),(3,3),(10,3),(30,3),
(2,4),(4,4),(10,4),(20,4),
(1,5),(5,5),(30,5);

The primary key (tag, uid) has a built-in B-tree composite index, so no additional index needs to be created.

Audience segmentation query examples:
- Intersection segmentation (users who have tags 1, 3, and 10)
```
SELECT uid
FROM t_user_tag
WHERE tag IN (1,3,10)
GROUP BY uid
HAVING count(*) = 3;   -- Number of tags
```
- Union segmentation (users who have any of tags 1, 3, and 10)
```
SELECT uid
FROM t_user_tag
WHERE tag IN (1,3,10)
GROUP BY uid;
```
- Difference segmentation (users who have tags 1 and 3 but do not have tag 5)
```
SELECT uid
FROM t_user_tag
WHERE tag IN (1,3)
GROUP BY uid
HAVING count(*) = 2
EXCEPT
SELECT uid FROM t_user_tag WHERE tag = 5;
```
- Real-time tag update
  Add tag 10 to user 6.
```
INSERT INTO t_user_tag VALUES (10,6) ON CONFLICT DO NOTHING;
```
  Delete tag 20 from user 1.
```
DELETE FROM t_user_tag WHERE uid=1 AND tag=20;
```
In Solution 2, the tag relationship is split into row-level storage. Both writes and deletions are lightweight single-row operations, and the update cost is extremely low. This solution is suitable for scenarios with frequent tag changes. However, when there are hundreds of millions of users and tens of millions of tags, a single popular tag may correspond to tens of millions of rows. GROUP BY aggregation of multiple tags still needs to scan a large amount of data, and the query latency increases linearly with the data volume.

This solution uses PostgreSQL's roaringbitmap extension to store the user set corresponding to each tag as a compressed bitmap (Roaring Bitmap). Roaring Bitmap divides the 32-bit integer space into chunks and adaptively selects dense arrays or bitmap compression for each chunk based on data density, balancing storage efficiency and computing speed. During audience segmentation, bitmap AND (intersection), OR (union), and ANDNOT (difference) operations are used to filter hundreds of millions of users in milliseconds.

The roaringbitmap extension supports the following core features:

Value range: 4 billion (int4, 0 to 2^31 – 1), covering the ID space of most users.
Compressed storage: Compared to regular bitmaps, this extension greatly saves space. A bitmap of 100 million users occupies only dozens of megabytes.
Bitmap operations: It supports set operations such as AND, OR, XOR, and ANDNOT, as well as aggregate functions.
Conversion functions: rb_build constructs bitmaps, rb_to_array outputs arrays, and rb_cardinality calculates cardinality.

To use this solution, you need to install the roaringbitmap extension on the RDS console (version 11 or later is required).

The table structure design is as follows:

Tag bitmap table: one row per (tag, offset_chunk), storing the user bitmap within that chunk

CREATE TABLE t_tag_users (
    tag        int NOT NULL,            -- Tag ID
    uid_offset int NOT NULL,            -- User ID chunk offset (2^31 per chunk)
    userbits   roaringbitmap NOT NULL,  -- Bitmap of users with this tag in the chunk
    PRIMARY KEY (tag, uid_offset)
);

When the user ID exceeds the int4 range (about 4.2 billion), it is stored in chunks based on uid_offset. In most scenarios, uid_offset is set to 0.

Writing bitmap data (example)

-- Tag 1: owned by users 1, 3, and 5
INSERT INTO t_tag_users VALUES (1, 0, rb_build(ARRAY[1,3,5]));
-- Tag 3: owned by users 1, 2, and 3
INSERT INTO t_tag_users VALUES (3, 0, rb_build(ARRAY[1,2,3]));
-- Tag 10: owned by users 1, 2, 3, and 4
INSERT INTO t_tag_users VALUES (10, 0, rb_build(ARRAY[1,2,3,4]));

Audience segmentation query examples

Intersection segmentation (users who have tags 1, 3, and 10)

SELECT uid_offset, rb_and_agg(userbits) AS ub
FROM t_tag_users
WHERE tag IN (1,3,10)
GROUP BY uid_offset;
 
-- Convert the bitmap into a user ID array and view the result.
SELECT uid_offset, rb_to_array(rb_and_agg(userbits)) AS uids
FROM t_tag_users
WHERE tag IN (1,3,10)
GROUP BY uid_offset;

Union segmentation (users who have any of tags 1, 3, and 10)

SELECT uid_offset, rb_or_agg(userbits) AS ub
FROM t_tag_users
WHERE tag IN (1,3,10)
GROUP BY uid_offset;

Difference segmentation (users who have tags 1 and 3 but do not have tag 10)

WITH tag_1_3 AS (
    SELECT uid_offset, rb_and_agg(userbits) AS ub
    FROM t_tag_users
    WHERE tag IN (1,3)
    GROUP BY uid_offset
),
tag_10 AS (
    SELECT uid_offset, rb_or_agg(userbits) AS ub
    FROM t_tag_users
    WHERE tag = 10
    GROUP BY uid_offset
)
SELECT a.uid_offset, rb_andnot(a.ub, b.ub) AS result
FROM tag_1_3 a JOIN tag_10 b USING (uid_offset);

Collecting statistics on the number of selected users (User IDs are not displayed, and only the number of users is returned.)

SELECT uid_offset, rb_cardinality(rb_or_agg(userbits)) AS user_cnt
FROM t_tag_users
WHERE tag IN (1,3,10)
GROUP BY uid_offset;

Real-time tag update (adding and deleting tags of a single user. Set the userbits parameter to the actual value.)

-- Add tag 10 to user 6 (bitmap addition).
UPDATE t_tag_users
SET userbits = rb_add(userbits, rb_build(ARRAY[6]))
WHERE tag = 10 AND uid_offset = 0;
 
-- Delete tag 10 from user 1.
UPDATE t_tag_users
SET userbits = rb_remove(userbits, rb_build(ARRAY[1]))
WHERE tag = 10 AND uid_offset = 0;

Solution 3 uses bitmap compression and bitwise operations to reduce the set operation time for hundreds of millions of users to milliseconds. It is the most efficient solution among the three and is most suitable for ultra-large-scale scenarios.

Solution Comparison

The three solutions differ in terms of implementation complexity, data update cost, query performance, and applicable scale. The comparison is as follows:

**Table 1** Solution comparison
Dimension	Solution 1 (Array + GIN)	Solution 2 (Inverted Index)	Solution 3 (roaringbitmap)
Implementation complexity	Low, native int[] and GIN indexes	Medium, inverted table maintenance required	Medium, extension installation and chunk design required
Data update	Array addition/deletion, single-row update	Single-row INSERT/DELETE, extremely lightweight	Bitmap rb_add/rb_remove, single-row update
Query performance (millions of users)	Milliseconds to hundreds of milliseconds	Milliseconds to hundreds of milliseconds	Milliseconds
Query performance (hundreds of millions of users)	More than seconds, obvious performance deterioration	More than seconds, high aggregation cost	Milliseconds, stable performance
Storage cost	Medium (array + GIN index)	High (one row per tag-UID)	Low (bitmap compression)
User scale	Millions	Tens of millions	Hundreds of millions
Tag scale	Hundreds to thousands	Thousands to tens of thousands	Tens of thousands to hundreds of thousands
Extension dependency	None	None	roaringbitmap required (PostgreSQL 11 or later)
Scenario	Small- and medium-scale, quick verification	Frequent tag changes, medium scale	Ultra-large scale, high real-time requirements

Suggestions on solution selection:

If there are millions of users and thousands of tag dimensions, preferentially use Solution 1. This solution is the simplest to implement, requires no additional extensions, and incurs the lowest development and O&M costs.
If there are tens of millions of users, tags change frequently, and flexible multi-tag combinations are required, use Solution 2. This solution features lightweight inverted table updates and flexible queries.
If there are hundreds of millions of users and millisecond-level segmentation delay is required, use Solution 3. roaringbitmap offers significant performance advantages in ultra-large-scale scenarios.

Suggestions on Best Practices

Extension version: The roaringbitmap extension in Solution 3 requires that the RDS for PostgreSQL instance version be 11 or later. Check the major version of your instance on the RDS console. If the instance version is earlier than 11, upgrade the instance before installing the extension.
User ID planning: Solution 3 requires that user IDs be of the int4 type (0 to 2^31 – 1, approximately 4.2 billion). If user IDs exceed this range, they must be stored across chunks via uid_offset. Otherwise, the bitmap cannot represent them. It is recommended that user IDs be planned as consecutive integers during user ID generation to avoid bitmap space waste caused by sparse IDs.
Index maintenance: In Solution 1, GIN indexes can bloat when tags are frequently updated. It is recommended that you periodically use REINDEX to check the index health and rebuild indexes during off-peak hours. RDS for PostgreSQL supports online index rebuilding (CREATE INDEX CONCURRENTLY) to avoid table locking.
Batch import: During the initial data import, if you use Solution 1, you should create GIN indexes after the data import is complete to avoid write performance degradation caused by building indexes during the write process. If you use Solution 3, you should construct bitmaps in batches by tag and then perform a one-time INSERT operation to avoid the high cost of updating bitmaps row by row.
Connection pool and parameters: Audience segmentation queries involving hundreds of millions of records require bitmap aggregation, which consumes a large amount of memory. You are advised to appropriately increase the value of work_mem (for example, from 256 MB to 1 GB) based on the instance specifications and use PgBouncer to control the number of concurrent connections to prevent memory overflow under high concurrency.
Monitoring: Observe the CPU, memory, and IOPS usage through the monitoring metrics on the RDS console. Use pg_stat_statements to identify slow SQL statements and analyze and optimize the time-consuming queries involving combined tags.
Tag governance: It is recommended that you create a tag metadata table (tag dictionary) to record the meaning, data source, update frequency, and validity period of each tag. Periodically delete invalid tags to prevent audience segmentation performance deterioration and business understanding deviation caused by the accumulation of invalid tags.

FAQ

Question 1: How do I install the roaringbitmap extension?
On the RDS for PostgreSQL console, search for roaringbitmap on the Plugins page and install it. The major version of the DB instance must be 11 or later. After the installation, you can run the SELECT * FROM pg_extension WHERE extname='roaringbitmap'; statement to check the extension status.
Question 2: Can Solution 3 be used if user IDs are not consecutive integers (for example, UUIDs)?
roaringbitmap requires that user IDs be int4 integers. If your business uses UUIDs or strings as user IDs, you need to create a user ID mapping table (user_mapping) to map business IDs to consecutive integers (UIDs). During audience segmentation, UIDs are used for bitmap operations, and the results are joined with the mapping table to restore them to the original business IDs. The UIDs in the mapping table can be generated using an auto-increment sequence (SERIAL) or a solution like Snowflake.
Question 3: Why does the GIN index in Solution 1 slow down when there are hundreds of millions of data records?
GIN indexes are essentially inverted indexes, with each tag corresponding to a posting list (a list of TIDs matching that tag). When the number of users reaches hundreds of millions and a single tag matches tens of millions of records, GIN bitmap scanning needs to read a large number of posting lists and TIDs. Additionally, the TOAST storage of the array incurs extra I/O overhead. When multiple tags are combined, the cost of bitmap AND/OR operations increases linearly. Therefore, the performance of Solution 1 deteriorates significantly in scenarios with hundreds of millions of records.
Question 4: Is bitmap update in Solution 3 suitable for high-frequency real-time writes?
The rb_add and rb_remove functions of roaringbitmap are single-row UPDATE operations, which are suitable for medium-frequency tag updates (for example, seconds to minutes). If the tag update frequency is extremely high (for example, tens of thousands of times per second), it is recommended to introduce a message queue combined with batch-merged writes to avoid row lock contention and WAL pressure caused by frequent single-row UPDATE operations. You can also use Solution 2 (inverted table) to handle real-time writes and periodically merge them into a bitmap for audience segmentation queries.
Question 5: Can the three solutions be used in combination?
Yes. Typical hybrid architecture: Solution 2 (inverted table) handles real-time tag writes to ensure lightweight updates. The inverted table is periodically (e.g., every minute) merged into a bitmap in Solution 3 for online audience segmentation queries. This architecture balances real-time writes and millisecond-level queries and is recommended for real-time marketing systems with hundreds of millions of users.
Question 6: How can the audience segmentation results be integrated with the marketing delivery system?
The segmentation results can be integrated with the downstream marketing system in the following ways: First, write the results to a result table, which is then polled and consumed by the marketing system. Second, use the logical publication/subscription feature of RDS for PostgreSQL to push result changes to the downstream system in real time. Third, use Data Replication Service (DRS) to synchronize the results to a data warehouse for further analysis and user profiling.
Question 7: What are the differences between rb_and_agg and rb_or_agg in Solution 3?
rb_and_agg is an aggregate function that performs bitwise AND (intersection) on multiple rows of bitmaps and returns the set of users who appear in all bitmaps. It is used to implement AND audience segmentation. rb_or_agg performs bitwise OR (union) on multiple rows of bitmaps and returns the set of users who appear in any bitmap. It is used to implement OR audience segmentation. Both functions need to be used with GROUP BY on uid_offset.
Question 8: How do I evaluate whether the query performance of tag-based audience segmentation meets the requirements?
You can use EXPLAIN ANALYZE to view the execution plan and actual execution time of the SQL statement for audience segmentation. Pay attention to whether bitmap aggregation uses index scanning, whether full table scanning occurs, and whether work_mem is sufficient to avoid disk sorting. It is recommended that you perform a stress test based on the actual data volume. The target is to complete a single audience segmentation within 100 milliseconds (in scenarios with hundreds of millions of users). The performance monitoring on the RDS console and the pg_stat_statements extension can help locate slow SQL statements.

Previous topic: Finding the Most Resource-Consuming SQL Statements (Top SQL)

Next topic: User Data Isolation Solution Based on Row-Level Security Policies

Real-Time Precision Marketing (Audience Segmentation)

Context

Prerequisites

Solution Overview

Solution Comparison

Suggestions on Best Practices

FAQ

What is your overall rating for this page?