This tutorial shows how to analyze user dwell time statistics by product category in PolarDB for PostgreSQL with DynamoDB compatibility. A column-store index (IMCI) lets you push down analytical aggregations to the database engine and avoid pulling the entire dataset into your application for processing.
Example scenario
Assume that your business has a DynamoDB table named usertable that stores user dwell information on different product category pages. The business document (polardb_document) for each record has the following structure:
{
"userid": {"S": "user1"},
"event_time": {"S": "2025-01-01T10:00:00Z"},
"product_category": {"S": "Clothing"},
"duration": {"N": "10"}
}Field descriptions:
-
userid: The user ID, which serves as the DynamoDB partition key. -
event_time: The time when the visit started. -
product_category: The product category. -
duration: The dwell time in minutes.
Business requirement
Calculate the total dwell time for all users in each product category over the last year.
In a standard DynamoDB environment, you cannot perform server-side aggregation. You would typically use a filter expression to retrieve the data and then perform the aggregation in your application, an approach that can cause high latency and consume significant read capacity for large datasets.
PolarDB for PostgreSQL with DynamoDB compatibility lets you run high-performance analytical queries directly in the PostgreSQL engine, eliminating the need to transfer data to your application.
Step 1: Create a table and insert data
The following Python script uses the DynamoDB API to create the usertable table and insert five sample records. Replace the ENDPOINT_URL in the connection configuration with your cluster's DynamoDB-compatible endpoint URL. To obtain this URL, see the relevant documentation.
#!/usr/bin/env python3
"""
Create the usertable and insert test data.
"""
import boto3
from botocore.exceptions import ClientError
# DynamoDB connection configuration
ENDPOINT_URL = "<your-endpoint_url>"
REGION = "<your-region>"
ACCESS_KEY = "<your-access-key>"
SECRET_KEY = "<your-secret-key>"
TABLE_NAME = "usertable"
def create_dynamodb_client():
return boto3.client(
'dynamodb',
endpoint_url=ENDPOINT_URL,
region_name=REGION,
aws_access_key_id=ACCESS_KEY,
aws_secret_access_key=SECRET_KEY
)
def create_table(client):
try:
client.create_table(
TableName=TABLE_NAME,
KeySchema=[{'AttributeName': 'userid', 'KeyType': 'HASH'}],
AttributeDefinitions=[{'AttributeName': 'userid', 'AttributeType': 'S'}],
BillingMode='PAY_PER_REQUEST'
)
waiter = client.get_waiter('table_exists')
waiter.wait(TableName=TABLE_NAME, WaiterConfig={'Delay': 2, 'MaxAttempts': 60})
print(f"Table {TABLE_NAME} created successfully.")
except ClientError as e:
if e.response['Error']['Code'] == 'ResourceInUseException':
print(f"Table {TABLE_NAME} already exists. Skipping creation.")
else:
raise
def insert_test_data(client):
items = [
{'userid': 'user1', 'event_time': '2025-01-01T10:00:00Z', 'product_category': 'Clothing', 'duration': 10},
{'userid': 'user2', 'event_time': '2025-01-02T11:30:00Z', 'product_category': 'Electronics', 'duration': 5},
{'userid': 'user3', 'event_time': '2025-01-03T09:15:00Z', 'product_category': 'Clothing', 'duration': 20},
{'userid': 'user4', 'event_time': '2025-01-04T20:00:00Z', 'product_category': 'Pet Products', 'duration': 8},
{'userid': 'user5', 'event_time': '2025-01-05T08:45:00Z', 'product_category': 'Electronics', 'duration': 12},
]
for idx, d in enumerate(items, 1):
client.put_item(TableName=TABLE_NAME, Item={
'userid': {'S': d['userid']},
'event_time': {'S': d['event_time']},
'product_category': {'S': d['product_category']},
'duration': {'N': str(d['duration'])}
})
print(f"Inserted item {idx}: {d['userid']}, {d['product_category']}, {d['duration']} minutes")
def main():
client = create_dynamodb_client()
create_table(client)
insert_test_data(client)
resp = client.scan(TableName=TABLE_NAME)
print(f"Verification complete. Total items: {len(resp.get('Items', []))}")
if __name__ == "__main__":
main()After the data is inserted, connect to your PolarDB cluster using psql or another PostgreSQL client for the next steps.
Step 2: Create a column-store index in PolarDB
Enabling a column-store index requires specific versions and kernel parameters. For details, see the relevant documentation. For performance tuning guidance, see the tuning guide.
View the underlying table structure
All DynamoDB tables are stored in the polardb_internal_dynamodb database. The schema name corresponds to your DynamoDB account name. For example, if your account name is dynamodb, connect to the polardb_internal_dynamodb database and run the following SQL query to view the table data:
SELECT * FROM dynamodb.usertable LIMIT 5;Result:
polardb_internal_dynamodb=# select * from dynamodb.usertable limit 5;
userid | polardb_document
--------+------------------------------------------------------------------------------------------------------------------------------
user1 | {"userid": {"S": "user1"}, "duration": {"N": "10"}, "event_time": {"S": "2025-01-01T10:00:00Z"}, "product_category": {"S": "Clothing"}}
user2 | {"userid": {"S": "user2"}, "duration": {"N": "5"}, "event_time": {"S": "2025-01-02T11:30:00Z"}, "product_category": {"S": "Electronics"}}
user3 | {"userid": {"S": "user3"}, "duration": {"N": "20"}, "event_time": {"S": "2025-01-03T09:15:00Z"}, "product_category": {"S": "Clothing"}}
user4 | {"userid": {"S": "user4"}, "duration": {"N": "8"}, "event_time": {"S": "2025-01-04T20:00:00Z"}, "product_category": {"S": "Pet Products"}}
user5 | {"userid": {"S": "user5"}, "duration": {"N": "12"}, "event_time": {"S": "2025-01-05T08:45:00Z"}, "product_category": {"S": "Electronics"}}
(5 rows)The polardb_document column is a JSONB field that stores the complete business document for each DynamoDB record.
Create generated columns
To accelerate aggregation queries that filter by time and group by category, extract the required attributes from the polardb_document JSONB column by creating a stored generated column for each attribute:
ALTER TABLE dynamodb.usertable
ADD COLUMN col_event_time TEXT GENERATED ALWAYS AS
(polardb_document -> 'event_time' ->> 'S') STORED,
ADD COLUMN col_product_category TEXT GENERATED ALWAYS AS
(polardb_document -> 'product_category' ->> 'S') STORED,
ADD COLUMN col_duration BIGINT GENERATED ALWAYS AS
((polardb_document -> 'duration' ->> 'N')::bigint) STORED;After creating the columns, query the table again to view the new structure:
polardb_internal_dynamodb=# select * from dynamodb.usertable limit 5;
userid | polardb_document | col_event_time | col_product_category | col_duration
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----------------------+--------------
user1 | {"userid": {"S": "user1"}, "duration": {"N": "10"}, "event_time": {"S": "2025-01-01T10:00:00Z"}, "product_category": {"S": "Clothing"}} | 2025-01-01T10:00:00Z | Clothing | 10
user2 | {"userid": {"S": "user2"}, "duration": {"N": "5"}, "event_time": {"S": "2025-01-02T11:30:00Z"}, "product_category": {"S": "Electronics"}} | 2025-01-02T11:30:00Z | Electronics | 5
user3 | {"userid": {"S": "user3"}, "duration": {"N": "20"}, "event_time": {"S": "2025-01-03T09:15:00Z"}, "product_category": {"S": "Clothing"}} | 2025-01-03T09:15:00Z | Clothing | 20
user4 | {"userid": {"S": "user4"}, "duration": {"N": "8"}, "event_time": {"S": "2025-01-04T20:00:00Z"}, "product_category": {"S": "Pet Products"}} | 2025-01-04T20:00:00Z | Pet Products | 8
user5 | {"userid": {"S": "user5"}, "duration": {"N": "12"}, "event_time": {"S": "2025-01-05T08:45:00Z"}, "product_category": {"S": "Electronics"}} | 2025-01-05T08:45:00Z | Electronics | 12
(5 rows)When a record is written, the system automatically extracts and stores the generated column values from the JSONB data. You can use these columns directly in subsequent queries.
Create the column-store index
Create a column-store index (CSI) on the partition key and the new generated columns:
-- You can also use CREATE INDEX CONCURRENTLY to avoid a table lock.
CREATE INDEX csi_usertable
ON dynamodb.usertable
USING CSI (
userid,
col_event_time,
col_product_category,
col_duration
);Use the pg_indexes view to confirm the index was created:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'usertable' AND schemaname = 'dynamodb';Result:
indexname | indexdef
----------------+-----------------------------------------------------------------------------------------------
usertable_pkey | CREATE UNIQUE INDEX usertable_pkey ON dynamodb.usertable USING btree (userid)
csi_usertable | CREATE INDEX csi_usertable ON dynamodb.usertable USING csi (userid, col_event_time, col_product_category, col_duration)
(2 rows)Step 3: Aggregate data with the column-store index
Enable column-store index parameters
Before running an analytical query, enable the relevant column-store index parameters:
-- Allow queries to use the column-store index.
SET polar_csi.enable_query = on;
SHOW polar_csi.enable_query;
-- For testing and analysis, set the cost threshold to 0 to make it easier to trigger a CSI scan.
SET polar_csi.cost_threshold = 0;
SHOW polar_csi.cost_threshold;Run the aggregation query
Calculate the total dwell time in minutes for each product category over the last year. First, use EXPLAIN to view the execution plan and confirm that IMCI accelerates the query:
EXPLAIN
SELECT
col_product_category AS product_category,
SUM(col_duration) AS total_duration_minutes
FROM dynamodb.usertable
WHERE col_event_time::timestamptz >= now() - interval '1 year'
GROUP BY product_category
ORDER BY product_category; QUERY PLAN
──────────────────────────────────
CSI Executor:
┌─────────────────────────────────┐
│ PROJECTION │
│ ────────────────── │
│ __internal_decompress_strin │
│ g(#0) │
│ #1 │
│ │
│ ~0 Rows │
└─────────────┬───────────────────┘
│
┌─────────────────────────────────┐
│ ORDER_BY │
│ ────────────────── │
│ usertable │
│ .col_product_category ASC │
└─────────────┬───────────────────┘
│
┌─────────────────────────────────┐
│ PROJECTION │
│ ────────────────── │
│ __internal_compress_string_ │
│ uhugeint(#0) │
└─────────────┬───────────────────┘
│
...If the EXPLAIN output includes CSI Executor, it confirms that the column-store index accelerates the query. Remove EXPLAIN and run the query to get the actual aggregated results:
SELECT
col_product_category AS product_category,
SUM(col_duration) AS total_duration_minutes
FROM dynamodb.usertable
WHERE col_event_time::timestamptz >= now() - interval '1 year'
GROUP BY product_category
ORDER BY product_category;Based on the sample data in this tutorial, the expected result is as follows:
|
Product category |
Total duration (minutes) |
|
Clothing |
30 |
|
Electronics |
17 |
|
Pet Products |
8 |
Summary
Combining the DynamoDB compatibility of PolarDB for PostgreSQL with its column-store index feature lets you preserve the ease of use of the DynamoDB API while performing complex analytics and aggregations with PostgreSQL SQL. The IMCI feature significantly improves analytical query performance, making this architecture an excellent choice for workloads that require both OLTP and OLAP capabilities.