https://pradeepchhetri.xyz/clickhousevstimescaledb/ skip to content W3C SVG [logo] Main navigation Menu * Home * Blog * Tags * Today I Learned * About ClickHouse vs TimescaleDB Publish date: Oct 27, 2021 Last updated: Nov 2, 2021 Tags: * benchmark * clickhouse * timescaledb Table of contents 1. Credits 2. Introduction 3. Benchmark Methodology 4. Hardware 5. Provisioning 6. Table Creation 7. Data Loading 8. Data Storage 9. Data Query 10. Enabling TimescaleDB Compression 11. Digging Deeper Into TimescaleDB 12. Conclusion Credits Special thanks to Ilya for extending this blog by adding remaining ClickHouse equivalent queries (Window functions, Joins and Geo queries) from the TimescaleDB docs. Your feedback and help is much appreciated. Introduction Recently, TimescaleDB published a blog comparing ClickHouse & TimescaleDB using timescale/tsbs, a timeseries benchmarking framework. I have some experience with PostgreSQL and ClickHouse but never got the chance to play with TimescaleDB. Some of the claims about TimescaleDB made in their post are very bold, that made me even more curious. I thought it'd be a great opportunity to try it out and see if those claims are really true. Benchmark Methodology To make sure that I am doing apple to apple comparison and results don't get biased due to my lack of knowledge of one particular system, I decided to select a dataset which both TimescaleDB and ClickHouse has documented in their website: NYC Taxi Dataset. * TimescaleDB: IoT Analysis: New York City Taxicabs * ClickHouse: Example Datasets: New York Taxi Data From TimescaleDB page, it does analysis on a subset of NYC taxi dataset (~11M rows: 1 month), I decided to download from them and run the queries mentioned there. Let's see how ClickHouse and TimescaleDB perform. To make sure that I am capturing the correct result, I ran the same query (all mentioned in this blog) multiple times and picked the one which ran fastest. Hardware Following is the spec of the physical machine selected for the benchmarks. $ rg processor /proc/cpuinfo | wc -l 32 $ free -h total used free shared buff/cache available Mem: 125Gi 2.9Gi 99Gi 5.9Gi 23Gi 114Gi Swap: 0B 0B 0B Provisioning To make it easily replicable, I decided to go with docker containers for running them. TimescaleDB $ docker run -d -e POSTGRES_PASSWORD=timescaledb timescale/timescaledb:2.4.0-pg13 As mentioned in their blog, I used timescaledb-tune utility to tune the configuration. I made sure that updated config is picked by triggering a config reload. # timescaledb-tune Using postgresql.conf at this path: /var/lib/postgresql/data/postgresql.conf Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup202110290606 success: shared_preload_libraries is set correctly Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 125.79 GB of available memory and 32 CPUs for PostgreSQL 13 Memory settings recommendations success: memory settings are already tuned Parallelism settings recommendations success: parallelism settings are already tuned WAL settings recommendations success: WAL settings are already tuned Miscellaneous settings recommendations success: miscellaneous settings are already tuned Saving changes to: /var/lib/postgresql/data/postgresql.conf # psql -U postgres psql (13.3) Type "help" for help. postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit (1 row) ClickHouse $ docker run -d --ulimit nofile=262144:262144 yandex/clickhouse-server:21.8 SELECT version() +-version()--+ | 21.8.10.19 | +------------+ Table Creation TimescaleDB To make sure that timescaledb extension is installed: postgres=# \dx List of installed extensions Name | Version | Schema | Description -------------+---------+------------+------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language timescaledb | 2.4.0 | public | Enables scalable inserts and complex queries for time-series data (2 rows) Table creation postgres=# CREATE TABLE "rides"( vendor_id TEXT, pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL, dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL, passenger_count NUMERIC, trip_distance NUMERIC, pickup_longitude NUMERIC, pickup_latitude NUMERIC, rate_code INTEGER, dropoff_longitude NUMERIC, dropoff_latitude NUMERIC, payment_type INTEGER, fare_amount NUMERIC, extra NUMERIC, mta_tax NUMERIC, tip_amount NUMERIC, tolls_amount NUMERIC, improvement_surcharge NUMERIC, total_amount NUMERIC ); CREATE TABLE postgres=# SELECT create_hypertable( 'rides', 'pickup_datetime', 'payment_type', 2, create_default_indexes=>FALSE ); create_hypertable -------------------- (1,public,rides,t) (1 row) postgres=# CREATE TABLE IF NOT EXISTS "payment_types"( payment_type INTEGER, description TEXT ); CREATE TABLE postgres=# INSERT INTO payment_types(payment_type, description) VALUES (1, 'credit card'), (2, 'cash'), (3, 'no charge'), (4, 'dispute'), (5, 'unknown'), (6, 'voided trip'); INSERT 0 6 postgres=# CREATE TABLE IF NOT EXISTS "rates"( rate_code INTEGER, description TEXT ); CREATE TABLE postgres=# INSERT INTO rates(rate_code, description) VALUES (1, 'standard rate'), (2, 'JFK'), (3, 'Newark'), (4, 'Nassau or Westchester'), (5, 'negotiated fare'), (6, 'group ride'); INSERT 0 6 Indices creation postgres=# CREATE INDEX ON rides (vendor_id, pickup_datetime desc); CREATE INDEX postgres=# CREATE INDEX ON rides (pickup_datetime desc, vendor_id); CREATE INDEX postgres=# CREATE INDEX ON rides (rate_code, pickup_datetime DESC); CREATE INDEX postgres=# CREATE INDEX ON rides (passenger_count, pickup_datetime desc); CREATE INDEX ClickHouse Table creation CREATE TABLE rides ( `vendor_id` String, `pickup_datetime` DateTime, `dropoff_datetime` Nullable(DateTime), `passenger_count` Nullable(UInt8), `trip_distance` Nullable(Float64), `pickup_longitude` Nullable(Float64), `pickup_latitude` Nullable(Float64), `rate_code` Nullable(UInt8), `dropoff_longitude` Nullable(Float64), `dropoff_latitude` Nullable(Float64), `payment_type` Nullable(String), `fare_amount` Nullable(Float32), `extra` Nullable(Float32), `mta_tax` Nullable(Float32), `tip_amount` Nullable(Float32), `tolls_amount` Nullable(Float32), `improvement_surcharge` Nullable(Float32), `total_amount` Nullable(Float32) ) ENGINE = MergeTree PARTITION BY toYYYYMM(pickup_datetime) ORDER BY pickup_datetime SETTINGS index_granularity = 8192; CREATE TABLE payment_types ( `payment_type` UInt8, `description` String ) ENGINE = MergeTree ORDER BY payment_type; INSERT INTO payment_types(payment_type, description) VALUES (1, 'credit card'), (2, 'cash'), (3, 'no charge'), (4, 'dispute'), (5, 'unknown'), (6, 'voided trip'); CREATE TABLE rates ( `rate_code` UInt8, `description` String ) ENGINE = MergeTree ORDER BY rate_code; INSERT INTO rates(rate_code, description) VALUES (1, 'standard rate'), (2, 'JFK'), (3, 'Newark'), (4, 'Nassau or Westchester'), (5, 'negotiated fare'), (6, 'group ride'); Data Loading Let's try loading the dataset. We had to turn timing on for TimescaleDB. ClickHouse shows the query duration by default. postgres=# \timing on Timing is on. TimescaleDB postgres=# \COPY rides FROM nyc_data_rides.csv CSV; COPY 10906860 Time: 220695.563 ms (03:40.696) Since postgres COPY command is single threaded, this was expected. We decided to try the timescaledb-parallel-copy increasing workers and batch size from default. # timescaledb-parallel-copy --db-name postgres --table rides --file nyc_data_rides.csv --workers 10 --batch-size 10000 --reporting-period 10s at 10s, row rate 187998.43/sec (period), row rate 187998.43/sec (overall), 1.880000E+06 total rows at 20s, row rate 201998.60/sec (period), row rate 194998.51/sec (overall), 3.900000E+06 total rows at 30s, row rate 192001.22/sec (period), row rate 193999.42/sec (overall), 5.820000E+06 total rows at 40s, row rate 181001.56/sec (period), row rate 190749.99/sec (overall), 7.630000E+06 total rows at 50s, row rate 194998.37/sec (period), row rate 191599.67/sec (overall), 9.580000E+06 total rows COPY 10906860 ClickHouse # cat nyc_data_rides.csv | clickhouse-client --time --query="INSERT INTO rides FORMAT CSV" 6.702 Database Mechanism Duration (s) TimescaleDB COPY 220 TimescaleDB timescaledb-parallel-copy 50 ClickHouse INSERT 7 Data Storage TimescaleDB # du -sh /var/lib/postgresql/data 4.2G /var/lib/postgresql/data ClickHouse # du -sh /var/lib/clickhouse 453M /var/lib/clickhouse Data Query Counting all the rides that happened in 1 month. TimescaleDB postgres=# select count(*) from rides; count ---------- 10906860 (1 row) Time: 434.663 ms ClickHouse SELECT count(*) FROM rides Query id: f2ec17cc-bf45-42a9-8181-e77ba5097416 +--count()-+ | 10906860 | +----------+ 1 rows in set. Elapsed: 0.003 sec. Database Query Duration (ms) TimescaleDB 435 ClickHouse 3 Counting the number of rides on daily basis. TimescaleDB postgres=# SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) FROM rides GROUP BY day ORDER BY day; day | count ---------------------+-------- 2016-01-01 00:00:00 | 345037 2016-01-02 00:00:00 | 312831 2016-01-03 00:00:00 | 302878 2016-01-04 00:00:00 | 316171 2016-01-05 00:00:00 | 343251 2016-01-06 00:00:00 | 348516 2016-01-07 00:00:00 | 364894 2016-01-08 00:00:00 | 392070 2016-01-09 00:00:00 | 405825 2016-01-10 00:00:00 | 351788 2016-01-11 00:00:00 | 342651 2016-01-12 00:00:00 | 367390 2016-01-13 00:00:00 | 395090 2016-01-14 00:00:00 | 396473 2016-01-15 00:00:00 | 401289 2016-01-16 00:00:00 | 411899 2016-01-17 00:00:00 | 379156 2016-01-18 00:00:00 | 341481 2016-01-19 00:00:00 | 385187 2016-01-20 00:00:00 | 382105 2016-01-21 00:00:00 | 399654 2016-01-22 00:00:00 | 420162 2016-01-23 00:00:00 | 78133 2016-01-24 00:00:00 | 159766 2016-01-25 00:00:00 | 282087 2016-01-26 00:00:00 | 327655 2016-01-27 00:00:00 | 359180 2016-01-28 00:00:00 | 383326 2016-01-29 00:00:00 | 414039 2016-01-30 00:00:00 | 435369 2016-01-31 00:00:00 | 361505 2017-11-17 00:00:00 | 2 (32 rows) Time: 1054.827 ms (00:01.055) ClickHouse SELECT toDate(pickup_datetime) AS day, COUNT(*) FROM rides GROUP BY day ORDER BY day ASC Query id: 97033310-0219-47d9-850c-405cae2e66a2 +--------day-+-count()-+ | 2016-01-01 | 345037 | | 2016-01-02 | 312831 | | 2016-01-03 | 302878 | | 2016-01-04 | 316171 | | 2016-01-05 | 343251 | | 2016-01-06 | 348516 | | 2016-01-07 | 364894 | | 2016-01-08 | 392070 | | 2016-01-09 | 405825 | | 2016-01-10 | 351788 | | 2016-01-11 | 342651 | | 2016-01-12 | 367390 | | 2016-01-13 | 395090 | | 2016-01-14 | 396473 | | 2016-01-15 | 401289 | | 2016-01-16 | 411899 | | 2016-01-17 | 379156 | | 2016-01-18 | 341481 | | 2016-01-19 | 385187 | | 2016-01-20 | 382105 | | 2016-01-21 | 399654 | | 2016-01-22 | 420162 | | 2016-01-23 | 78133 | | 2016-01-24 | 159766 | | 2016-01-25 | 282087 | | 2016-01-26 | 327655 | | 2016-01-27 | 359180 | | 2016-01-28 | 383326 | | 2016-01-29 | 414039 | | 2016-01-30 | 435369 | | 2016-01-31 | 361505 | | 2017-11-17 | 2 | +------------+---------+ 32 rows in set. Elapsed: 0.021 sec. Processed 10.91 million rows, 43.63 MB (516.78 million rows/s., 2.07 GB/s.) Database Query Duration (ms) TimescaleDB 1055 ClickHouse 21 Calculating the average fare amount for passengers. TimescaleDB postgres=# SELECT date_trunc('day', pickup_datetime) AS day, avg(fare_amount) FROM rides WHERE passenger_count = 1 GROUP BY day ORDER BY day; day | avg ---------------------+--------------------- 2016-01-01 00:00:00 | 12.5464748850129787 2016-01-02 00:00:00 | 12.1129878886746750 2016-01-03 00:00:00 | 12.8262352076841150 2016-01-04 00:00:00 | 11.9116533573721472 2016-01-05 00:00:00 | 11.7534235580737452 2016-01-06 00:00:00 | 11.7824805635293235 2016-01-07 00:00:00 | 11.9498961299166930 2016-01-08 00:00:00 | 11.8738271415577235 2016-01-09 00:00:00 | 11.4878104510668158 2016-01-10 00:00:00 | 12.0888246569772508 2016-01-11 00:00:00 | 12.1343557409576630 2016-01-12 00:00:00 | 11.8598489409162272 2016-01-13 00:00:00 | 11.8031804213844504 2016-01-14 00:00:00 | 12.3123409782144971 2016-01-15 00:00:00 | 12.5613139967255876 2016-01-16 00:00:00 | 11.8351508976680107 2016-01-17 00:00:00 | 12.0295602006362590 2016-01-18 00:00:00 | 11.8623529387202913 2016-01-19 00:00:00 | 12.3472510415266765 2016-01-20 00:00:00 | 12.4609413534048953 2016-01-21 00:00:00 | 12.6083024358000252 2016-01-22 00:00:00 | 12.0812572192038226 2016-01-23 00:00:00 | 11.8564034256627468 2016-01-24 00:00:00 | 12.1723178660326188 2016-01-25 00:00:00 | 14.6872562077357373 2016-01-26 00:00:00 | 14.2577452509214630 2016-01-27 00:00:00 | 13.2432283566172145 2016-01-28 00:00:00 | 12.9854551087781026 2016-01-29 00:00:00 | 12.5911266525638913 2016-01-30 00:00:00 | 12.2759556743269683 2016-01-31 00:00:00 | 12.5867816634207905 2017-11-17 00:00:00 | 9.7500000000000000 (32 rows) Time: 1499.333 ms (00:01.499) ClickHouse SELECT toDate(pickup_datetime) AS day, avg(fare_amount) FROM rides WHERE passenger_count = 1 GROUP BY day ORDER BY day ASC Query id: 92abcc32-f516-42d9-99dd-3b0faa7103fb +--------day-+---avg(fare_amount)-+ | 2016-01-01 | 12.546474885017933 | | 2016-01-02 | 12.112987888606051 | | 2016-01-03 | 12.826235207666542 | | 2016-01-04 | 11.911653357328007 | | 2016-01-05 | 11.753423558134099 | | 2016-01-06 | 11.78248056342069 | | 2016-01-07 | 11.949896129834796 | | 2016-01-08 | 11.873827141488679 | | 2016-01-09 | 11.487810451064721 | | 2016-01-10 | 12.088824656929884 | | 2016-01-11 | 12.134355740954636 | | 2016-01-12 | 11.859848940902094 | | 2016-01-13 | 11.803180421366301 | | 2016-01-14 | 12.312340978159439 | | 2016-01-15 | 12.561313996603548 | | 2016-01-16 | 11.835150897615112 | | 2016-01-17 | 12.02956020059271 | | 2016-01-18 | 11.862352938597022 | | 2016-01-19 | 12.347251041579934 | | 2016-01-20 | 12.460941353486442 | | 2016-01-21 | 12.608302435857224 | | 2016-01-22 | 12.081257219198683 | | 2016-01-23 | 11.856403425394397 | | 2016-01-24 | 12.17231786602557 | | 2016-01-25 | 14.687256207078526 | | 2016-01-26 | 14.257745250869808 | | 2016-01-27 | 13.243228356662698 | | 2016-01-28 | 12.985455108728543 | | 2016-01-29 | 12.591126652543593 | | 2016-01-30 | 12.275955679633002 | | 2016-01-31 | 12.586781663432406 | | 2017-11-17 | 9.75 | +------------+--------------------+ 32 rows in set. Elapsed: 0.054 sec. Processed 10.91 million rows, 119.98 MB (202.67 million rows/s., 2.23 GB/s.) Database Query Duration (ms) TimescaleDB 1500 ClickHouse 54 Calculating number of rides that took place for each rate type TimescaleDB postgres=# SELECT rate_code, COUNT(vendor_id) AS num_trips FROM rides WHERE pickup_datetime < '2016-02-01' GROUP BY rate_code ORDER BY rate_code; rate_code | num_trips -----------+----------- 1 | 10626315 2 | 225019 3 | 16822 4 | 4696 5 | 33688 6 | 102 99 | 216 (7 rows) Time: 1014.495 ms (00:01.014) ClickHouse SELECT rate_code, COUNT(vendor_id) AS num_trips FROM rides WHERE pickup_datetime < '2016-02-01' GROUP BY rate_code ORDER BY rate_code ASC Query id: 288415a8-316b-42c8-8c65-ec85d476bb1e +-rate_code-+-num_trips-+ | 1 | 10626315 | | 2 | 225019 | | 3 | 16822 | | 4 | 4696 | | 5 | 33688 | | 6 | 102 | | 99 | 216 | +-----------+-----------+ 7 rows in set. Elapsed: 0.053 sec. Processed 10.91 million rows, 174.51 MB (205.99 million rows/s., 3.30 GB/s.) Database Query Duration (ms) TimescaleDB 1014 ClickHouse 53 Analysis of rides to JFK and EWR. TimescaleDB postgres=# SELECT rates.description, COUNT(vendor_id) AS num_trips, AVG(dropoff_datetime - pickup_datetime) AS avg_trip_duration, AVG(total_amount) AS avg_total, AVG(tip_amount) AS avg_tip, MIN(trip_distance) AS min_distance, AVG (trip_distance) AS avg_distance, MAX(trip_distance) AS max_distance, AVG(passenger_count) AS avg_passengers FROM rides JOIN rates ON rides.rate_code = rates.rate_code WHERE rides.rate_code IN (2,3) AND pickup_datetime < '2016-02-01' GROUP BY rates.description ORDER BY rates.description; description | num_trips | avg_trip_duration | avg_total | avg_tip | min_distance | avg_distance | max_distance | avg_passengers -------------+-----------+-------------------+---------------------+--------------------+--------------+---------------------+--------------+-------------------- JFK | 225019 | 00:45:46.822517 | 64.3278115181384683 | 7.3334228220728027 | 0.00 | 17.2602816651038357 | 221.00 | 1.7333869584346211 Newark | 16822 | 00:35:16.157472 | 86.4633688027582927 | 9.5461657353465700 | 0.00 | 16.2706122934252764 | 177.23 | 1.7435501129473309 (2 rows) Time: 5132.768 ms (00:05.133) ClickHouse SELECT rates.description, COUNT(vendor_id) AS num_trips, AVG(dropoff_datetime - pickup_datetime) AS avg_trip_duration, AVG(total_amount) AS avg_total, AVG(tip_amount) AS avg_tip, MIN(trip_distance) AS min_distance, AVG(trip_distance) AS avg_distance, MAX(trip_distance) AS max_distance, AVG(passenger_count) AS avg_passengers FROM rides INNER JOIN rates ON rides.rate_code = rates.rate_code WHERE (rides.rate_code IN (2, 3)) AND (pickup_datetime < '2016-02-01') GROUP BY rates.description ORDER BY rates.description ASC Query id: 303edbf1-73f8-4426-86b2-66fbecc57f4c +-description-+-num_trips-+--avg_trip_duration-+---------avg_total-+-----------avg_tip-+-min_distance-+-------avg_distance-+-max_distance-+----avg_passengers-+ | JFK | 225019 | 2746.8225172096577 | 64.32781102360629 | 7.333422817827029 | 0 | 17.26028166510384 | 221 | 1.733386958434621 | | Newark | 16822 | 2116.157472357627 | 86.46336993224668 | 9.546165732798725 | 0 | 16.270612293425287 | 177.23 | 1.743550112947331 | +-------------+-----------+--------------------+-------------------+-------------------+--------------+--------------------+--------------+-------------------+ 2 rows in set. Elapsed: 0.116 sec. Processed 10.91 million rows, 458.09 MB (94.31 million rows/s., 3.96 GB/s.) Database Query Duration (ms) TimescaleDB 5132 ClickHouse 116 Calculate the number of rides happened every 5 minutes for the first day of 2016. TimescaleDB postgres=# SELECT EXTRACT(hour from pickup_datetime) as hours, trunc(EXTRACT(minute from pickup_datetime) / 5)*5 AS five_mins, COUNT(*) FROM rides WHERE pickup_datetime < '2016-01-02 00:00' GROUP BY hours, five_mins; hours | five_mins | count -------+-----------+------- 0 | 0 | 703 0 | 5 | 1482 0 | 10 | 1959 0 | 15 | 2200 0 | 20 | 2285 0 | 25 | 2291 0 | 30 | 2349 0 | 35 | 2328 0 | 40 | 2440 0 | 45 | 2372 0 | 50 | 2388 0 | 55 | 2473 1 | 0 | 2395 1 | 5 | 2510 1 | 10 | 2412 1 | 15 | 2482 1 | 20 | 2428 1 | 25 | 2433 1 | 30 | 2337 1 | 35 | 2366 1 | 40 | 2325 1 | 45 | 2257 1 | 50 | 2316 1 | 55 | 2250 2 | 0 | 2303 2 | 5 | 2259 2 | 10 | 2253 2 | 15 | 2176 2 | 20 | 2138 2 | 25 | 2131 2 | 30 | 2135 2 | 35 | 2047 2 | 40 | 2086 2 | 45 | 2049 2 | 50 | 1938 2 | 55 | 1975 3 | 0 | 1966 3 | 5 | 1927 3 | 10 | 1896 3 | 15 | 1840 3 | 20 | 1917 3 | 25 | 1816 3 | 30 | 1784 3 | 35 | 1607 3 | 40 | 1652 3 | 45 | 1619 3 | 50 | 1655 3 | 55 | 1598 4 | 0 | 1684 4 | 5 | 1608 4 | 10 | 1533 4 | 15 | 1468 4 | 20 | 1430 4 | 25 | 1303 4 | 30 | 1230 4 | 35 | 1171 4 | 40 | 1092 4 | 45 | 1023 4 | 50 | 998 4 | 55 | 879 5 | 0 | 837 5 | 5 | 858 5 | 10 | 733 5 | 15 | 776 5 | 20 | 662 5 | 25 | 703 5 | 30 | 629 5 | 35 | 624 5 | 40 | 611 5 | 45 | 560 5 | 50 | 555 5 | 55 | 554 6 | 0 | 551 6 | 5 | 519 6 | 10 | 516 6 | 15 | 501 6 | 20 | 500 6 | 25 | 510 6 | 30 | 497 6 | 35 | 524 6 | 40 | 508 6 | 45 | 463 6 | 50 | 479 6 | 55 | 483 7 | 0 | 476 7 | 5 | 456 7 | 10 | 447 7 | 15 | 459 7 | 20 | 451 7 | 25 | 450 7 | 30 | 440 7 | 35 | 418 7 | 40 | 417 7 | 45 | 426 7 | 50 | 413 7 | 55 | 401 8 | 0 | 396 8 | 5 | 405 8 | 10 | 409 8 | 15 | 447 8 | 20 | 408 8 | 25 | 404 8 | 30 | 396 8 | 35 | 421 8 | 40 | 441 8 | 45 | 374 8 | 50 | 427 8 | 55 | 412 9 | 0 | 410 9 | 5 | 454 9 | 10 | 487 9 | 15 | 510 9 | 20 | 488 9 | 25 | 456 9 | 30 | 456 9 | 35 | 539 9 | 40 | 542 9 | 45 | 564 9 | 50 | 568 9 | 55 | 581 10 | 0 | 596 10 | 5 | 592 10 | 10 | 669 10 | 15 | 656 10 | 20 | 703 10 | 25 | 667 10 | 30 | 706 10 | 35 | 662 10 | 40 | 739 10 | 45 | 788 10 | 50 | 814 10 | 55 | 843 11 | 0 | 779 11 | 5 | 861 11 | 10 | 798 11 | 15 | 881 11 | 20 | 893 11 | 25 | 977 11 | 30 | 1006 11 | 35 | 1025 11 | 40 | 1114 11 | 45 | 1120 11 | 50 | 1165 11 | 55 | 1133 12 | 0 | 1132 12 | 5 | 1156 12 | 10 | 1157 12 | 15 | 1166 12 | 20 | 1203 12 | 25 | 1122 12 | 30 | 1119 12 | 35 | 1181 12 | 40 | 1194 12 | 45 | 1228 12 | 50 | 1174 12 | 55 | 1195 13 | 0 | 1234 13 | 5 | 1204 13 | 10 | 1276 13 | 15 | 1285 13 | 20 | 1272 13 | 25 | 1280 13 | 30 | 1282 13 | 35 | 1252 13 | 40 | 1420 13 | 45 | 1370 13 | 50 | 1430 13 | 55 | 1389 14 | 0 | 1423 14 | 5 | 1346 14 | 10 | 1293 14 | 15 | 1328 14 | 20 | 1355 14 | 25 | 1333 14 | 30 | 1367 14 | 35 | 1475 14 | 40 | 1397 14 | 45 | 1439 14 | 50 | 1444 14 | 55 | 1352 15 | 0 | 1315 15 | 5 | 1327 15 | 10 | 1330 15 | 15 | 1380 15 | 20 | 1351 15 | 25 | 1322 15 | 30 | 1411 15 | 35 | 1346 15 | 40 | 1355 15 | 45 | 1353 15 | 50 | 1386 15 | 55 | 1263 16 | 0 | 1308 16 | 5 | 1278 16 | 10 | 1185 16 | 15 | 1256 16 | 20 | 1223 16 | 25 | 1223 16 | 30 | 1204 16 | 35 | 1269 16 | 40 | 1184 16 | 45 | 1208 16 | 50 | 1182 16 | 55 | 1266 17 | 0 | 1228 17 | 5 | 1323 17 | 10 | 1285 17 | 15 | 1242 17 | 20 | 1303 17 | 25 | 1272 17 | 30 | 1335 17 | 35 | 1297 17 | 40 | 1328 17 | 45 | 1444 17 | 50 | 1423 17 | 55 | 1367 18 | 0 | 1365 18 | 5 | 1377 18 | 10 | 1327 18 | 15 | 1352 18 | 20 | 1370 18 | 25 | 1316 18 | 30 | 1448 18 | 35 | 1341 18 | 40 | 1475 18 | 45 | 1508 18 | 50 | 1543 18 | 55 | 1358 19 | 0 | 1308 19 | 5 | 1368 19 | 10 | 1426 19 | 15 | 1372 19 | 20 | 1365 19 | 25 | 1315 19 | 30 | 1321 19 | 35 | 1260 19 | 40 | 1280 19 | 45 | 1246 19 | 50 | 1216 19 | 55 | 1192 20 | 0 | 1154 20 | 5 | 1091 20 | 10 | 1100 20 | 15 | 1118 20 | 20 | 1105 20 | 25 | 1060 20 | 30 | 1135 20 | 35 | 1126 20 | 40 | 1098 20 | 45 | 1136 20 | 50 | 1039 20 | 55 | 1087 21 | 0 | 1042 21 | 5 | 1025 21 | 10 | 1113 21 | 15 | 1127 21 | 20 | 1102 21 | 25 | 1068 21 | 30 | 1104 21 | 35 | 1083 21 | 40 | 1101 21 | 45 | 1127 21 | 50 | 1086 21 | 55 | 1095 22 | 0 | 1103 22 | 5 | 1199 22 | 10 | 1126 22 | 15 | 1130 22 | 20 | 1110 22 | 25 | 1098 22 | 30 | 1197 22 | 35 | 1189 22 | 40 | 1167 22 | 45 | 1247 22 | 50 | 1146 22 | 55 | 1030 23 | 0 | 1139 23 | 5 | 1082 23 | 10 | 1065 23 | 15 | 986 23 | 20 | 1032 23 | 25 | 988 23 | 30 | 1032 23 | 35 | 1100 23 | 40 | 1142 23 | 45 | 1145 23 | 50 | 1149 23 | 55 | 1063 (288 rows) Time: 170.031 ms ClickHouse SELECT toHour(pickup_datetime) AS hours, trunc(toMinute(pickup_datetime) / 5) * 5 AS five_mins, COUNT(*) FROM rides WHERE pickup_datetime < '2016-01-02 00:00:00' GROUP BY hours, five_mins ORDER BY hours ASC, five_mins ASC Query id: f9606d18-3852-41a3-9b21-7b58dc23c9da +-hours-+-five_mins-+-count()-+ | 0 | 0 | 703 | | 0 | 5 | 1482 | | 0 | 10 | 1959 | | 0 | 15 | 2200 | | 0 | 20 | 2285 | | 0 | 25 | 2291 | | 0 | 30 | 2349 | | 0 | 35 | 2328 | | 0 | 40 | 2440 | | 0 | 45 | 2372 | | 0 | 50 | 2388 | | 0 | 55 | 2473 | | 1 | 0 | 2395 | | 1 | 5 | 2510 | | 1 | 10 | 2412 | | 1 | 15 | 2482 | | 1 | 20 | 2428 | | 1 | 25 | 2433 | | 1 | 30 | 2337 | | 1 | 35 | 2366 | | 1 | 40 | 2325 | | 1 | 45 | 2257 | | 1 | 50 | 2316 | | 1 | 55 | 2250 | | 2 | 0 | 2303 | | 2 | 5 | 2259 | | 2 | 10 | 2253 | | 2 | 15 | 2176 | | 2 | 20 | 2138 | | 2 | 25 | 2131 | | 2 | 30 | 2135 | | 2 | 35 | 2047 | | 2 | 40 | 2086 | | 2 | 45 | 2049 | | 2 | 50 | 1938 | | 2 | 55 | 1975 | | 3 | 0 | 1966 | | 3 | 5 | 1927 | | 3 | 10 | 1896 | | 3 | 15 | 1840 | | 3 | 20 | 1917 | | 3 | 25 | 1816 | | 3 | 30 | 1784 | | 3 | 35 | 1607 | | 3 | 40 | 1652 | | 3 | 45 | 1619 | | 3 | 50 | 1655 | | 3 | 55 | 1598 | | 4 | 0 | 1684 | | 4 | 5 | 1608 | | 4 | 10 | 1533 | | 4 | 15 | 1468 | | 4 | 20 | 1430 | | 4 | 25 | 1303 | | 4 | 30 | 1230 | | 4 | 35 | 1171 | | 4 | 40 | 1092 | | 4 | 45 | 1023 | | 4 | 50 | 998 | | 4 | 55 | 879 | | 5 | 0 | 837 | | 5 | 5 | 858 | | 5 | 10 | 733 | | 5 | 15 | 776 | | 5 | 20 | 662 | | 5 | 25 | 703 | | 5 | 30 | 629 | | 5 | 35 | 624 | | 5 | 40 | 611 | | 5 | 45 | 560 | | 5 | 50 | 555 | | 5 | 55 | 554 | | 6 | 0 | 551 | | 6 | 5 | 519 | | 6 | 10 | 516 | | 6 | 15 | 501 | | 6 | 20 | 500 | | 6 | 25 | 510 | | 6 | 30 | 497 | | 6 | 35 | 524 | | 6 | 40 | 508 | | 6 | 45 | 463 | | 6 | 50 | 479 | | 6 | 55 | 483 | | 7 | 0 | 476 | | 7 | 5 | 456 | | 7 | 10 | 447 | | 7 | 15 | 459 | | 7 | 20 | 451 | | 7 | 25 | 450 | | 7 | 30 | 440 | | 7 | 35 | 418 | | 7 | 40 | 417 | | 7 | 45 | 426 | | 7 | 50 | 413 | | 7 | 55 | 401 | | 8 | 0 | 396 | | 8 | 5 | 405 | | 8 | 10 | 409 | | 8 | 15 | 447 | | 8 | 20 | 408 | | 8 | 25 | 404 | | 8 | 30 | 396 | | 8 | 35 | 421 | | 8 | 40 | 441 | | 8 | 45 | 374 | | 8 | 50 | 427 | | 8 | 55 | 412 | | 9 | 0 | 410 | | 9 | 5 | 454 | | 9 | 10 | 487 | | 9 | 15 | 510 | | 9 | 20 | 488 | | 9 | 25 | 456 | | 9 | 30 | 456 | | 9 | 35 | 539 | | 9 | 40 | 542 | | 9 | 45 | 564 | | 9 | 50 | 568 | | 9 | 55 | 581 | | 10 | 0 | 596 | | 10 | 5 | 592 | | 10 | 10 | 669 | | 10 | 15 | 656 | | 10 | 20 | 703 | | 10 | 25 | 667 | | 10 | 30 | 706 | | 10 | 35 | 662 | | 10 | 40 | 739 | | 10 | 45 | 788 | | 10 | 50 | 814 | | 10 | 55 | 843 | | 11 | 0 | 779 | | 11 | 5 | 861 | | 11 | 10 | 798 | | 11 | 15 | 881 | | 11 | 20 | 893 | | 11 | 25 | 977 | | 11 | 30 | 1006 | | 11 | 35 | 1025 | | 11 | 40 | 1114 | | 11 | 45 | 1120 | | 11 | 50 | 1165 | | 11 | 55 | 1133 | | 12 | 0 | 1132 | | 12 | 5 | 1156 | | 12 | 10 | 1157 | | 12 | 15 | 1166 | | 12 | 20 | 1203 | | 12 | 25 | 1122 | | 12 | 30 | 1119 | | 12 | 35 | 1181 | | 12 | 40 | 1194 | | 12 | 45 | 1228 | | 12 | 50 | 1174 | | 12 | 55 | 1195 | | 13 | 0 | 1234 | | 13 | 5 | 1204 | | 13 | 10 | 1276 | | 13 | 15 | 1285 | | 13 | 20 | 1272 | | 13 | 25 | 1280 | | 13 | 30 | 1282 | | 13 | 35 | 1252 | | 13 | 40 | 1420 | | 13 | 45 | 1370 | | 13 | 50 | 1430 | | 13 | 55 | 1389 | | 14 | 0 | 1423 | | 14 | 5 | 1346 | | 14 | 10 | 1293 | | 14 | 15 | 1328 | | 14 | 20 | 1355 | | 14 | 25 | 1333 | | 14 | 30 | 1367 | | 14 | 35 | 1475 | | 14 | 40 | 1397 | | 14 | 45 | 1439 | | 14 | 50 | 1444 | | 14 | 55 | 1352 | | 15 | 0 | 1315 | | 15 | 5 | 1327 | | 15 | 10 | 1330 | | 15 | 15 | 1380 | | 15 | 20 | 1351 | | 15 | 25 | 1322 | | 15 | 30 | 1411 | | 15 | 35 | 1346 | | 15 | 40 | 1355 | | 15 | 45 | 1353 | | 15 | 50 | 1386 | | 15 | 55 | 1263 | | 16 | 0 | 1308 | | 16 | 5 | 1278 | | 16 | 10 | 1185 | | 16 | 15 | 1256 | | 16 | 20 | 1223 | | 16 | 25 | 1223 | | 16 | 30 | 1204 | | 16 | 35 | 1269 | | 16 | 40 | 1184 | | 16 | 45 | 1208 | | 16 | 50 | 1182 | | 16 | 55 | 1266 | | 17 | 0 | 1228 | | 17 | 5 | 1323 | | 17 | 10 | 1285 | | 17 | 15 | 1242 | | 17 | 20 | 1303 | | 17 | 25 | 1272 | | 17 | 30 | 1335 | | 17 | 35 | 1297 | | 17 | 40 | 1328 | | 17 | 45 | 1444 | | 17 | 50 | 1423 | | 17 | 55 | 1367 | | 18 | 0 | 1365 | | 18 | 5 | 1377 | | 18 | 10 | 1327 | | 18 | 15 | 1352 | | 18 | 20 | 1370 | | 18 | 25 | 1316 | | 18 | 30 | 1448 | | 18 | 35 | 1341 | | 18 | 40 | 1475 | | 18 | 45 | 1508 | | 18 | 50 | 1543 | | 18 | 55 | 1358 | | 19 | 0 | 1308 | | 19 | 5 | 1368 | | 19 | 10 | 1426 | | 19 | 15 | 1372 | | 19 | 20 | 1365 | | 19 | 25 | 1315 | | 19 | 30 | 1321 | | 19 | 35 | 1260 | | 19 | 40 | 1280 | | 19 | 45 | 1246 | | 19 | 50 | 1216 | | 19 | 55 | 1192 | | 20 | 0 | 1154 | | 20 | 5 | 1091 | | 20 | 10 | 1100 | | 20 | 15 | 1118 | | 20 | 20 | 1105 | | 20 | 25 | 1060 | | 20 | 30 | 1135 | | 20 | 35 | 1126 | | 20 | 40 | 1098 | | 20 | 45 | 1136 | | 20 | 50 | 1039 | | 20 | 55 | 1087 | | 21 | 0 | 1042 | | 21 | 5 | 1025 | | 21 | 10 | 1113 | | 21 | 15 | 1127 | | 21 | 20 | 1102 | | 21 | 25 | 1068 | | 21 | 30 | 1104 | | 21 | 35 | 1083 | | 21 | 40 | 1101 | | 21 | 45 | 1127 | | 21 | 50 | 1086 | | 21 | 55 | 1095 | | 22 | 0 | 1103 | | 22 | 5 | 1199 | | 22 | 10 | 1126 | | 22 | 15 | 1130 | | 22 | 20 | 1110 | | 22 | 25 | 1098 | | 22 | 30 | 1197 | | 22 | 35 | 1189 | | 22 | 40 | 1167 | | 22 | 45 | 1247 | | 22 | 50 | 1146 | | 22 | 55 | 1030 | | 23 | 0 | 1139 | | 23 | 5 | 1082 | | 23 | 10 | 1065 | | 23 | 15 | 986 | | 23 | 20 | 1032 | | 23 | 25 | 988 | | 23 | 30 | 1032 | | 23 | 35 | 1100 | | 23 | 40 | 1142 | | 23 | 45 | 1145 | | 23 | 50 | 1149 | | 23 | 55 | 1063 | +-------+-----------+---------+ 288 rows in set. Elapsed: 0.015 sec. Processed 352.26 thousand rows, 1.41 MB (23.05 million rows/s., 92.19 MB/s.) Database Query Duration (ms) TimescaleDB 170 ClickHouse 15 Calculating number of rides on New Year's morning originated from within 400m of Times Square, in 30 minute buckets. TimescaleDB postgres=# SELECT time_bucket('30 minutes', pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq FROM rides WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400 AND pickup_datetime < '2016-01-01 14:00' GROUP BY thirty_min ORDER BY thirty_min; thirty_min | near_times_sq ---------------------+--------------- 2016-01-01 00:00:00 | 74 2016-01-01 00:30:00 | 102 2016-01-01 01:00:00 | 120 2016-01-01 01:30:00 | 98 2016-01-01 02:00:00 | 112 2016-01-01 02:30:00 | 109 2016-01-01 03:00:00 | 163 2016-01-01 03:30:00 | 181 2016-01-01 04:00:00 | 214 2016-01-01 04:30:00 | 185 2016-01-01 05:00:00 | 158 2016-01-01 05:30:00 | 113 2016-01-01 06:00:00 | 102 2016-01-01 06:30:00 | 91 2016-01-01 07:00:00 | 88 2016-01-01 07:30:00 | 58 2016-01-01 08:00:00 | 72 2016-01-01 08:30:00 | 94 2016-01-01 09:00:00 | 115 2016-01-01 09:30:00 | 118 2016-01-01 10:00:00 | 135 2016-01-01 10:30:00 | 160 2016-01-01 11:00:00 | 212 2016-01-01 11:30:00 | 229 2016-01-01 12:00:00 | 244 2016-01-01 12:30:00 | 230 2016-01-01 13:00:00 | 235 2016-01-01 13:30:00 | 238 (28 rows) Time: 167.335 ms ClickHouse SELECT toStartOfInterval(pickup_datetime, toIntervalMinute(30)) AS thirty_min, COUNT(*) AS near_times_sq FROM rides WHERE (greatCircleDistance(-73.9851, 40.7589, pickup_longitude, pickup_latitude) < 400) AND (pickup_datetime < '2016-01-01 14:00:00') GROUP BY thirty_min ORDER BY thirty_min ASC Query id: 047dfcbf-8a49-47b1-a2b6-2a44600bed30 +----------thirty_min-+-near_times_sq-+ | 2016-01-01 00:00:00 | 73 | | 2016-01-01 00:30:00 | 101 | | 2016-01-01 01:00:00 | 120 | | 2016-01-01 01:30:00 | 98 | | 2016-01-01 02:00:00 | 111 | | 2016-01-01 02:30:00 | 109 | | 2016-01-01 03:00:00 | 163 | | 2016-01-01 03:30:00 | 181 | | 2016-01-01 04:00:00 | 215 | | 2016-01-01 04:30:00 | 186 | | 2016-01-01 05:00:00 | 158 | | 2016-01-01 05:30:00 | 114 | | 2016-01-01 06:00:00 | 101 | | 2016-01-01 06:30:00 | 92 | | 2016-01-01 07:00:00 | 88 | | 2016-01-01 07:30:00 | 60 | | 2016-01-01 08:00:00 | 72 | | 2016-01-01 08:30:00 | 95 | | 2016-01-01 09:00:00 | 114 | | 2016-01-01 09:30:00 | 118 | | 2016-01-01 10:00:00 | 136 | | 2016-01-01 10:30:00 | 160 | | 2016-01-01 11:00:00 | 212 | | 2016-01-01 11:30:00 | 230 | | 2016-01-01 12:00:00 | 245 | | 2016-01-01 12:30:00 | 228 | | 2016-01-01 13:00:00 | 237 | | 2016-01-01 13:30:00 | 239 | +---------------------+---------------+ 28 rows in set. Elapsed: 0.016 sec. Processed 196.61 thousand rows, 4.33 MB (12.11 million rows/s., 266.38 MB/s.) Database Query Duration (ms) TimescaleDB 167 ClickHouse 16 Enabling TimescaleDB Compression After running the above benchmarks, I felt that I am missing something important about TimescaleDB and started going through their docs again until I realised that TimescaleDB doesn't enable compression by default, which is quite strange. I ran the benchmark queries by enabling compression. postgres=# ALTER TABLE rides SET ( timescaledb.compress, timescaledb.compress_segmentby = 'vendor_id' ); ALTER TABLE postgres=# SELECT add_compression_policy('rides', INTERVAL '0 days'); add_compression_policy ------------------------ 1000 (1 row) After enabling the compression, verified that it triggered the compression from logs. 2021-10-31 02:16:48.675 UTC [252] LOG: completed compressing chunk _timescaledb_internal._hyper_1_1_chunk 2021-10-31 02:16:57.931 UTC [255] LOG: completed compressing chunk _timescaledb_internal._hyper_1_2_chunk 2021-10-31 02:17:04.964 UTC [258] LOG: completed compressing chunk _timescaledb_internal._hyper_1_5_chunk 2021-10-31 02:17:18.686 UTC [260] LOG: completed compressing chunk _timescaledb_internal._hyper_1_6_chunk 2021-10-31 02:17:25.894 UTC [264] LOG: completed compressing chunk _timescaledb_internal._hyper_1_7_chunk 2021-10-31 02:17:39.172 UTC [266] LOG: completed compressing chunk _timescaledb_internal._hyper_1_8_chunk 2021-10-31 02:17:49.474 UTC [270] LOG: completed compressing chunk _timescaledb_internal._hyper_1_9_chunk 2021-10-31 02:17:55.110 UTC [273] LOG: completed compressing chunk _timescaledb_internal._hyper_1_10_chunk 2021-10-31 02:18:03.536 UTC [275] LOG: completed compressing chunk _timescaledb_internal._hyper_1_3_chunk 2021-10-31 02:18:08.258 UTC [278] LOG: completed compressing chunk _timescaledb_internal._hyper_1_4_chunk 2021-10-31 02:18:08.403 UTC [280] LOG: completed compressing chunk _timescaledb_internal._hyper_1_11_chunk 2021-10-31 02:18:08.510 UTC [281] LOG: completed compressing chunk _timescaledb_internal._hyper_1_12_chunk This definitely reduced the disk usage by TimescaleDB. # du -sh /var/lib/postgresql/data/ 1.6G /var/lib/postgresql/data/ Let's run the above queries again. Counting all the rides that happened in 1 month. TimescaleDB (with compression) postgres=# select count(*) from rides; count ---------- 10906860 (1 row) Time: 282.369 ms Counting the number of rides on daily basis. TimescaleDB (with compression) postgres=# SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) FROM rides GROUP BY day ORDER BY day; day | count ---------------------+-------- 2016-01-01 00:00:00 | 345037 2016-01-02 00:00:00 | 312831 2016-01-03 00:00:00 | 302878 2016-01-04 00:00:00 | 316171 2016-01-05 00:00:00 | 343251 2016-01-06 00:00:00 | 348516 2016-01-07 00:00:00 | 364894 2016-01-08 00:00:00 | 392070 2016-01-09 00:00:00 | 405825 2016-01-10 00:00:00 | 351788 2016-01-11 00:00:00 | 342651 2016-01-12 00:00:00 | 367390 2016-01-13 00:00:00 | 395090 2016-01-14 00:00:00 | 396473 2016-01-15 00:00:00 | 401289 2016-01-16 00:00:00 | 411899 2016-01-17 00:00:00 | 379156 2016-01-18 00:00:00 | 341481 2016-01-19 00:00:00 | 385187 2016-01-20 00:00:00 | 382105 2016-01-21 00:00:00 | 399654 2016-01-22 00:00:00 | 420162 2016-01-23 00:00:00 | 78133 2016-01-24 00:00:00 | 159766 2016-01-25 00:00:00 | 282087 2016-01-26 00:00:00 | 327655 2016-01-27 00:00:00 | 359180 2016-01-28 00:00:00 | 383326 2016-01-29 00:00:00 | 414039 2016-01-30 00:00:00 | 435369 2016-01-31 00:00:00 | 361505 2017-11-17 00:00:00 | 2 (32 rows) Time: 1033.075 ms (00:01.033) Calculating the average fare amount for passengers. TimescaleDB (with compression) postgres=# SELECT date_trunc('day', pickup_datetime) AS day, avg(fare_amount) FROM rides WHERE passenger_count = 1 GROUP BY day ORDER BY day; day | avg ---------------------+--------------------- 2016-01-01 00:00:00 | 12.5464748850129787 2016-01-02 00:00:00 | 12.1129878886746750 2016-01-03 00:00:00 | 12.8262352076841150 2016-01-04 00:00:00 | 11.9116533573721472 2016-01-05 00:00:00 | 11.7534235580737452 2016-01-06 00:00:00 | 11.7824805635293235 2016-01-07 00:00:00 | 11.9498961299166930 2016-01-08 00:00:00 | 11.8738271415577235 2016-01-09 00:00:00 | 11.4878104510668158 2016-01-10 00:00:00 | 12.0888246569772508 2016-01-11 00:00:00 | 12.1343557409576630 2016-01-12 00:00:00 | 11.8598489409162272 2016-01-13 00:00:00 | 11.8031804213844504 2016-01-14 00:00:00 | 12.3123409782144971 2016-01-15 00:00:00 | 12.5613139967255876 2016-01-16 00:00:00 | 11.8351508976680107 2016-01-17 00:00:00 | 12.0295602006362590 2016-01-18 00:00:00 | 11.8623529387202913 2016-01-19 00:00:00 | 12.3472510415266765 2016-01-20 00:00:00 | 12.4609413534048953 2016-01-21 00:00:00 | 12.6083024358000252 2016-01-22 00:00:00 | 12.0812572192038226 2016-01-23 00:00:00 | 11.8564034256627468 2016-01-24 00:00:00 | 12.1723178660326188 2016-01-25 00:00:00 | 14.6872562077357373 2016-01-26 00:00:00 | 14.2577452509214630 2016-01-27 00:00:00 | 13.2432283566172145 2016-01-28 00:00:00 | 12.9854551087781026 2016-01-29 00:00:00 | 12.5911266525638913 2016-01-30 00:00:00 | 12.2759556743269683 2016-01-31 00:00:00 | 12.5867816634207905 2017-11-17 00:00:00 | 9.7500000000000000 (32 rows) Time: 1599.145 ms (00:01.599) Calculating number of rides that took place for each rate type TimescaleDB (with compression) postgres=# SELECT rate_code, COUNT(vendor_id) AS num_trips FROM rides WHERE pickup_datetime < '2016-02-01' GROUP BY rate_code ORDER BY rate_code; rate_code | num_trips -----------+----------- 1 | 10626315 2 | 225019 3 | 16822 4 | 4696 5 | 33688 6 | 102 99 | 216 (7 rows) Time: 974.998 ms Overall, although some TimescaleDB queries became faster by enabling compression but many others became bit slower probably due to decompression overhead. This may be the reason why TimescaleDB disable compression by default. Digging Deeper Into TimescaleDB I wanted to understand how the queries work in TimescaleDB. So I decided to try out EXPLAIN (ANALYZE, FORMAT JSON, VERBOSE, BUFFERS) . alt text alt text It stores the data in chunks (12 in our case) and runs the query over those chunks parallelly and aggregate the result. postgres=# SELECT show_chunks('rides'); show_chunks ----------------------------------------- _timescaledb_internal._hyper_1_1_chunk _timescaledb_internal._hyper_1_2_chunk ... ... _timescaledb_internal._hyper_1_11_chunk _timescaledb_internal._hyper_1_12_chunk (12 rows) I was curious if changing this chunk size/count will impact the query performance. Reduce chunk count Let's first try to reduce the number of chunks. postgres=# SELECT set_chunk_time_interval('rides', INTERVAL '1 year'); set_chunk_time_interval ------------------------- (1 row) postgres=# SELECT show_chunks('rides'); show_chunks ------------------------------------------- _timescaledb_internal._hyper_1_1565_chunk _timescaledb_internal._hyper_1_1566_chunk _timescaledb_internal._hyper_1_1567_chunk _timescaledb_internal._hyper_1_1568_chunk (4 rows) Let's run the query to calculate the number of rides on daily basis. TimescaleDB postgres=# SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) FROM rides GROUP BY day ORDER BY day; day | count ---------------------+-------- 2016-01-01 00:00:00 | 345037 2016-01-02 00:00:00 | 312831 2016-01-03 00:00:00 | 302878 2016-01-04 00:00:00 | 316171 2016-01-05 00:00:00 | 343251 2016-01-06 00:00:00 | 348516 2016-01-07 00:00:00 | 364894 2016-01-08 00:00:00 | 392070 2016-01-09 00:00:00 | 405825 2016-01-10 00:00:00 | 351788 2016-01-11 00:00:00 | 342651 2016-01-12 00:00:00 | 367390 2016-01-13 00:00:00 | 395090 2016-01-14 00:00:00 | 396473 2016-01-15 00:00:00 | 401289 2016-01-16 00:00:00 | 411899 2016-01-17 00:00:00 | 379156 2016-01-18 00:00:00 | 341481 2016-01-19 00:00:00 | 385187 2016-01-20 00:00:00 | 382105 2016-01-21 00:00:00 | 399654 2016-01-22 00:00:00 | 420162 2016-01-23 00:00:00 | 78133 2016-01-24 00:00:00 | 159766 2016-01-25 00:00:00 | 282087 2016-01-26 00:00:00 | 327655 2016-01-27 00:00:00 | 359180 2016-01-28 00:00:00 | 383326 2016-01-29 00:00:00 | 414039 2016-01-30 00:00:00 | 435369 2016-01-31 00:00:00 | 361505 2017-11-17 00:00:00 | 2 (32 rows) Time: 1184.163 ms (00:01.184) Query performance reduced by this change. Increase chunk count Let's now try to increase the number of chunks. postgres=# SELECT set_chunk_time_interval('rides', INTERVAL '1 day'); set_chunk_time_interval ------------------------- (1 row) postgres=# SELECT show_chunks('rides'); show_chunks ------------------------------------------- _timescaledb_internal._hyper_1_1501_chunk _timescaledb_internal._hyper_1_1502_chunk ... ... _timescaledb_internal._hyper_1_1563_chunk _timescaledb_internal._hyper_1_1564_chunk (64 rows) This created 64 chunks. Let's run the query to calculate the number of rides on daily basis. TimescaleDB postgres=# SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) FROM rides GROUP BY day ORDER BY day; day | count ---------------------+-------- 2016-01-01 00:00:00 | 345037 2016-01-02 00:00:00 | 312831 2016-01-03 00:00:00 | 302878 2016-01-04 00:00:00 | 316171 2016-01-05 00:00:00 | 343251 2016-01-06 00:00:00 | 348516 2016-01-07 00:00:00 | 364894 2016-01-08 00:00:00 | 392070 2016-01-09 00:00:00 | 405825 2016-01-10 00:00:00 | 351788 2016-01-11 00:00:00 | 342651 2016-01-12 00:00:00 | 367390 2016-01-13 00:00:00 | 395090 2016-01-14 00:00:00 | 396473 2016-01-15 00:00:00 | 401289 2016-01-16 00:00:00 | 411899 2016-01-17 00:00:00 | 379156 2016-01-18 00:00:00 | 341481 2016-01-19 00:00:00 | 385187 2016-01-20 00:00:00 | 382105 2016-01-21 00:00:00 | 399654 2016-01-22 00:00:00 | 420162 2016-01-23 00:00:00 | 78133 2016-01-24 00:00:00 | 159766 2016-01-25 00:00:00 | 282087 2016-01-26 00:00:00 | 327655 2016-01-27 00:00:00 | 359180 2016-01-28 00:00:00 | 383326 2016-01-29 00:00:00 | 414039 2016-01-30 00:00:00 | 435369 2016-01-31 00:00:00 | 361505 2017-11-17 00:00:00 | 2 (32 rows) Time: 691.523 ms Query performance improved by this change. Database Chunk Count Query Duration (ms) TimescaleDB 4 1184 TimescaleDB 12 1055 TimescaleDB 64 692 Conclusion * We found ClickHouse did better while evaluting on storage, read, write performance. * ClickHouse defaults are pretty good and doesn't require any server side tuning, while tuning the table schema definitely helps. TimescaleDB requires tuning to make it perform faster. Last but not the least, I may be completely wrong in my evaluation. Feel free to point it out. We all are doing our best to write good software. Show comments Enable JavaScript to view Disqus comments. dark theme: [ ]