On-demand Schemaless Slicing in PostgreSQL with TimescaleDB

Background

TimescaleDB is a time-series database plug-in for PostgreSQL, and its automatic sharding function is very popular.

http://www.timescale.com/

In fact, PostgreSQL plpgsql can also implement a similar function, provided that schemaless mode is used.

There are several examples of schemaless design ideas and applications:

PostgreSQL Schemaless Design and Stress Testing in China Railway Corporation’s Ordering System

PostgreSQL Schemaless Implementation (Similar to Mongodb Collection)

PostgreSQL Time-series Best Practices — Design a Stock Exchange System Database — Alibaba Cloud RDS for PostgreSQL Best Practices

Next, with respect to the automatic sharding function, let’s take a look at the example of schemaless implementation.

1. First, you should monitor the amount of writes. You can count data writes by using the track_counts parameter (the track_counts parameter is enabled by default).

PostgreSQL pg_stat_reset Eliminates the Hidden Danger of track_counts

2. When data is written to a certain amount, the next table is automatically written.

Example of Schemaless and Automatic Sharding

1. Design:

When writing, data is written through UDF, the write speed of each data stream is monitored in real time, and data sharding is dynamically performed.

2. Test table:

3. Rules for sharding:

When the number of records exceeds 100,000, the partition is automatically switched.

4. UDF definitions:

5. Stress testing

Automatic sharding succeeded:

Summary

The TimescaleDB plug-in also provides many other functions and is more borderless in usage. We still recommend using TimescaleDB plug-in when it becomes more sophisticated.

For Alibaba Cloud RDS PG, you can also write the RDS PG in real time by using the method mentioned in this article, and write the OSS external table in batches according to the set threshold at the same time (DBLINK asynchronous interface can be used for writing OSS external tables).

Alibaba Cloud RDS PostgreSQL OSS External Table — (DBLINK Asynchronous Call Encapsulation) Parallel Write Acceleration Case

Reference:https://www.alibabacloud.com/blog/on-demand-schemaless-slicing-in-postgresql-with-timescaledb_594811?spm=a2c41.12889666.0.0

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