Social Friend Relationship System Practice in PostgreSQL — Accelerating Queries of Positive and Negative Relationships

Background

A relationship system is very common in various design applications. For example, query who your friends are and to whom you are a friend.

Another example is who I liked and who liked me.

Then how can we speed up the query of positive and negative relationships?

The efficiency difference between multi-row query and single-row query in databases is very significant because multi-row query needs to run more code. Of course, multi-row query has its advantages, for example, multi-row query performs better than single-row query when returning a small number of records.

To speed up returning all relations of an ID, we can compress multiple rows into one single row.

1. Generate Positive and Negative Relationships in a Real-Time and Streaming Manner

Apply rules to automatically convert detail data into multiple rows of data.

The advantage of this method is that the data is real-time.

The disadvantage is that the large row of the merged table needs to be updated every time, producing large amounts of trash in a multi-version implementation. In addition, it is very likely to experience performance bottlenecks on popular users. (For example, users who are followed by many people have a very large relation network.)

Implementation example:

(1) Create an original relationship table.

(2) Create positive and negative relationship tables and use arrays to represent one-to-many relationships.

(3) Create an intarray plug-in to remove duplicates from arrays.

(4) Create rules that automatically merge relationships into the positive and negative relationship tables when data is written into the original relationship table.

(5) Create a function for stress testing, that is, to write relationships among users.

(6) Perform stress testing

(7) View examples of positive and negative relationships.

(8) Check if positive and negative relationships are consistent with the relationships in the original relationship table. To do this, simply compare the hash values. The relationships are completely consistent in this example.

2. Batch Generate Positive and Negative Relationships in an Asynchronous and Streaming Manner

The defects of the first method may easily cause increased I/O and increased RT of writing relationships in the case of very popular users.

To avoid these problems, we can use the “batch merge” method, which is similar to the method used in the cellphone user profile system of a cellphone manufacturer: Paste, delete and modify tags, generate data as a log, perform async batch consume with atomicity on feed data and then merge data into the tag table.

Query Performance Comparison between Positive and Negative Relationships

Compare query performance among the original table, the negative relationship table, and the negative relationship table.

(1) Add a single-field index to the original table

(2) The query performance of the positive relationships in the original table

(3) The query performance of the negative relationships in the original table

(4) The query performance of the positive relationship table

(5) The query performance of the negative relationship table

Comparison testTPS for positive relationshipsTPS for negative relationshipsOriginal method616,00023,000Array query in the positive and negative tables713,000214,000

The more relationship records there are, the more significantly the performance is improved by using arrays to store positive and negative relationships.

Summary

A relationship system is very common in various design applications. For example, query who your friends are and to whom you are a friend.

Directly querying original relationships requires the batch extraction of all related relationships. This may cause relatively high RT and low TPS.

Real-time stream computing and batch traffic computing allow relationships to be divided into positive and negative relationships. Each user is a record and arrays are used to store one-to-many relationships. This method brings significant performance improvement.

Original Source

https://www.alibabacloud.com/blog/social-friend-relationship-system-practice-in-postgresql---accelerating-queries-of-positive-and-negative-relationships_595043?spm=a2c41.13112387.0.0

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