Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL Feed Systems Similar to Weibo - Design and Performance Metrics

PostgreSQL Feed Systems Similar to Weibo - Design and Performance Metrics

This article describes scenarios that are related message pushing and message consumption by using a 2048-letter-long message as an example.

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)

PostgreSQL Database ApsaraDB RDS Partition Table
0 0 0
Share on

Read previous post:

PostgreSQL Family Tree Application Practices - Graph Relation Storage and Search

Read next post:

PostgreSQL Graph Search Practices - 10 Billion-Scale Graph with Millisecond Response

digoal

282 posts | 25 followers

You may also like

Comments

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All