PostgreSQL Feed Systems Similar to Weibo — Design and Performance Metrics

By Digoal

Background

Frequently used features in systems similar to Weibo

User A, D, and E follow user B.  

User B pushes messages.

User A, D, and E receive messages.

User A, D, and E consume messages. This involves the consumption sorting algorithms.

This article describes scenarios that are related message pushing and message consumption.

Take a 2048-letter-long message for example.

Design

To ensure efficient pushing and consumption, when designing, we need to consider partitioning. Partitioning is also helpful for transparent database sharding later.

For example, we can perform hash partitioning by UID.

1. Create a Hash Partition Table

Create a message push table

create table tbl_feed(  
uid int8, -- User ID
from_uid int8, -- ID of the followed user
ts timestamp, -- Time when the followed user sends this message
content text, -- The message content that the followed user sends
status int -- The message reading status of the current user: 0 indicates that message is in the initial status and 1 indicates that the message has been consumed.
);

Create a partial index because only records that are not consumed are a concern during consumption.

create index idx_tbl_feed_1 on tbl_feed(uid,ts) where status=0;

Create 1024 partitions

do language plpgsql 
$$

declare
begin
for i in 0.. 1023 loop
execute format('create table tbl_feed_%s (like tbl_feed including all , constraint ck_tbl_feed_%s check(abs(mod(uid,1024))=%s)) inherits(tbl_feed)', i, i, i);
end loop;
end;
$$
;

2. Write Data by Using UDFs

Currently the writing and querying efficiency of RDS PostgreSQL 10 partition tables is not very good. To ensure relatively ideal writing efficiency, it is recommended to use UDFs and dynamically join SQL.

create or replace function ins_feed(int8, int8, timestamp, text, int) returns void as 
$$

declare
i int := abs(mod($1,1024)); -- Dynamically join table names
begin
execute format('insert into tbl_feed_%s(uid,from_uid,ts,content,status) values(%s,%s,%L,%L,%s)', i, $1,$2,$3,$4,$5);
end;
$$
language plpgsql strict;

Writing Performance

Consider 2 billion users. Enter a user randomly and push a message that is made up of 2048 English letters.

In PostgreSQL 10, a single instance can write 195,000 rows/s. The performance bottleneck occurs mainly due to writing LOCK of the WAL log.

\set uid random(1,2000000000)  
select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5('a'),64),0);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 23464891
latency average = 0.286 ms
latency stddev = 0.486 ms
tps = 195379.681306 (including connections establishing)
tps = 195404.169885 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set uid random(1,2000000000)
0.285 select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5('a'),64),0);

Consume UDFs

Currently the writing and querying efficiency of RDS PostgreSQL 10 partition tables is not very good. To ensure relatively ideal writing efficiency, it is recommended to use UDFs and dynamically join SQL.

create or replace function get_feed(int8, int, text) returns setof tbl_feed as 
$$

declare
i int := abs(mod($1,1024)); -- Dynamically join table names
begin
return query execute format('with tmp as
(
update tbl_feed_%s set status=1 where ctid = any (array(
select ctid from tbl_feed_%s where status=0 and uid=%s order by ts limit %s -- Consume N entries each time either in chronological order or in reverse time sequence. Indexes are used in both the consumption order cases
))
returning *
)
select * from tmp order by %s', -- The sorting algorithms can be written as UDF or passed in by using parameters. TS is used for sorting in this example
i, i, $1, $2, $3
);
end;
$$
language plpgsql strict;

Consumption example

postgres=# select * from get_feed(642960384,10,'from_uid');  
-[ RECORD 1 ]------------------------------------------------------------------------------------------------
uid | 642960384
from_uid | 642960385
ts | 2018-03-05 19:41:40.574568
content | 0cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc17
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e
2697726610cc175b9c0f1b6a831c399e269772661
status | 1

Consumption Performance

In order to make the actual consumption obvious and observable, at least 20 entries are consumed each time. Here we generate a batch of intensive data first and then test the performance.

\set uid random(1,4096)  
select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5('a'),64),0);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120

Enter a user randomly and consume 20 rows each time. Perform consumption 27000 times per second.

# \set uid random(1,2000000000)  
Use \set uid random(1,4096) during the test
select * from get_feed(:uid,20,'ts');

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 45
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 45 s
number of transactions actually processed: 1195840
latency average = 2.106 ms
latency stddev = 2.707 ms
tps = 26560.345111 (including connections establishing)
tps = 26572.467067 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set uid random(1,4096)
2.105 select * from get_feed(:uid,20,'ts');

Summary

Writing and querying performance for partition tables will be considerably improved in after PostgreSQL 11. In the future, partition tables can be directly used to avoid using UDFs and dynamic SQL to access partitions.

The built-in UDFs of PostgreSQL (such plpgsql, plpython, pljava, and plv8) support the expansion of any sorting algorithms.

Performance metrics for a single instance

Push | Consume

195,000 rows/s | 540,000 rows/s , 27,000 times/s (An average of 20 rows are consumed each time)

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store