testing environment
Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz
32G memory
SSD harddisk.
postgresql.conf
max_connections = 256 shared_buffers = 3GB # min 128kB work_mem = 16MB # min 64kB maintenance_work_mem = 256MB # min 1MB wal_buffers = 4MB checkpoint_timeout = 10min # range 30s-1d max_wal_size = 2GB checkpoint_completion_target = 0.9 --------------------- Author: Kun_Tsai Source: CSDN Original: https://blog.csdn.net/jacicson 1987/article/details/82988556?Utm_source=copy Copyright Statement: This article is the original article of the blogger. Please attach a link to the blog article for reprinting.
CASE 1 Single TIME Index
timescaledb does not set the index (default time index)
PG Sets time Index
ts1=# \d+ ckts1
Table "public.ckts1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
time | timestamp with time zone | | not null | | plain | |
id | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
col4 | integer | | | | plain | |
col5 | integer | | | | plain | |
col6 | integer | | | | plain | |
col7 | integer | | | | plain | |
col8 | integer | | | | plain | |
col9 | integer | | | | plain | |
col10 | integer | | | | plain | |
col11 | integer | | | | plain | |
col12 | character varying(30) | | | | extended | |
col13 | character varying(30) | | | | extended | |
col14 | character varying(30) | | | | extended | |
col15 | character varying(30) | | | | extended | |
col16 | character varying(30) | | | | extended | |
col17 | character varying(30) | | | | extended | |
col18 | character varying(30) | | | | extended | |
col19 | character varying(30) | | | | extended | |
col20 | character varying(30) | | | | extended | |
Indexes:
"ckts1_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON ckts1 FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
postgres=# \d+ cts1
Table "public.cts1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
time | timestamp with time zone | | not null | | plain | |
id | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
col4 | integer | | | | plain | |
col5 | integer | | | | plain | |
col6 | integer | | | | plain | |
col7 | integer | | | | plain | |
col8 | integer | | | | plain | |
col9 | integer | | | | plain | |
col10 | integer | | | | plain | |
col11 | integer | | | | plain | |
col12 | character varying(30) | | | | extended | |
col13 | character varying(30) | | | | extended | |
col14 | character varying(30) | | | | extended | |
col15 | character varying(30) | | | | extended | |
col16 | character varying(30) | | | | extended | |
col17 | character varying(30) | | | | extended | |
col18 | character varying(30) | | | | extended | |
col19 | character varying(30) | | | | extended | |
col20 | character varying(30) | | | | extended | |
Indexes:
"cts1_time_index" btree ("time")
Result
Single line write
10W rows per thread. About 180 bytes per line. One thread writes about 18 MB.
It seems that the performance of PG is slightly stronger than that of PG by 5%~10%.
| Thread Average Rate (Row/s) | Single thread | 2 thread | 4 thread | 8 thread | 16 thread | 32 thread |
| timescaledb | 1075 | 1008 | 1026 | 1045 | 923 | 654 |
| PG | 1092 | 1020 | 1070 | 1120 | 982 | 737 |
| Total Rate (Row/s) | Single thread | 2 thread | 4 thread | 8 thread | 16 thread | 32 thread |
| timescaledb | 1075 | 2011 | 4000 | 8166 | 14358 | 20337 |
| PG | 1092 | 2037 | 4037 | 8734 | 15087 | 22445 |
WAL file size
They are basically the same, about twice the amount of data.
| Thread count | Bytes | PG_WAL_SIZE | TS_WAL_SIZE |
| 1 | 18697562 | 37428272 | 37464824 |
| 2 | 37379223 | 74547144 | 75026672 |
| 4 | 74785548 | 149803536 | 149525488 |
| 8 | 149548561 | 298455240 | 298400040 |
| 16 | 299134911 | 596227840 | 596302792 |
| 32 | 598385115 | 1191610952 | 1194038480 |
BATCH writes
Set the number of single-threaded rows to 10 million and BATCHSIZE to 1000
PG is superior to TIMESCALEDB.
| 1000W rows per thread | batchsize=1000 | |
| Thread Average Rate (Row/s) | Single thread | 8 thread |
| timescaledb | 26732 | 11916 |
| PG | 39113 | 14972 |
| Total Rate (Row/s) | Single thread | 8 thread |
| timescaledb | 26732 | 95140 |
| PG | 39113 | 119528 |
Increase id to add an index to index CASE 2
ts1=# \d+ ckts1
Table "public.ckts1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
time | timestamp with time zone | | not null | | plain | |
id | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
col4 | integer | | | | plain | |
col5 | integer | | | | plain | |
col6 | integer | | | | plain | |
col7 | integer | | | | plain | |
col8 | integer | | | | plain | |
col9 | integer | | | | plain | |
col10 | integer | | | | plain | |
col11 | integer | | | | plain | |
col12 | character varying(30) | | | | extended | |
col13 | character varying(30) | | | | extended | |
col14 | character varying(30) | | | | extended | |
col15 | character varying(30) | | | | extended | |
col16 | character varying(30) | | | | extended | |
col17 | character varying(30) | | | | extended | |
col18 | character varying(30) | | | | extended | |
col19 | character varying(30) | | | | extended | |
col20 | character varying(30) | | | | extended | |
Indexes:
"ckts1_id_idx" btree (id)
"ckts1_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON ckts1 FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
ts1=#
postgres=# \d+ cts1
Table "public.cts1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
time | timestamp with time zone | | not null | | plain | |
id | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
col4 | integer | | | | plain | |
col5 | integer | | | | plain | |
col6 | integer | | | | plain | |
col7 | integer | | | | plain | |
col8 | integer | | | | plain | |
col9 | integer | | | | plain | |
col10 | integer | | | | plain | |
col11 | integer | | | | plain | |
col12 | character varying(30) | | | | extended | |
col13 | character varying(30) | | | | extended | |
col14 | character varying(30) | | | | extended | |
col15 | character varying(30) | | | | extended | |
col16 | character varying(30) | | | | extended | |
col17 | character varying(30) | | | | extended | |
col18 | character varying(30) | | | | extended | |
col19 | character varying(30) | | | | extended | |
col20 | character varying(30) | | | | extended | |
Indexes:
"cst1_id_index" btree (id)
"cts1_time_index" btree ("time")
Single line write
100,000 rows of data, single write
The performance of PG looks a little bit worse than that without adding indexes.
| Total Rate (Row/s) | Single thread |
| timescaledb | 1051 |
| PG | 1019 |
BATCH writes
10 million rows per thread, batchsize set to 1000, 8 threads to process, 13.9G data
After adding an index, the performance of PG and TSDB is lower than before, and PG writing is still faster than TSDB.
| 1000W rows per thread | batchsize=1000 | |
| Thread Average Rate (Row/s) | Single thread | 8 thread |
| timescaledb | 26142 | 10591 |
| PG | 36861 | 13037 |
| Total Rate (Row/s) | Single thread | 8 thread |
| timescaledb | 26142 | 84588 |
| PG | 36861 | 103806 |
So far, the test has not seen the write performance advantages of timescaledb, especially after introducing more than one index, the performance has declined. Considering whether the data design or the amount of data is insufficient, it can not reflect the advantages of timescaledb.