ClickBench
TimescaleDB
ClickBench | TimescaleDB | |
---|---|---|
71 | 82 | |
571 | 16,472 | |
3.2% | 0.8% | |
9.0 | 9.8 | |
2 days ago | 7 days ago | |
HTML | C | |
GNU General Public License v3.0 or later | GNU General Public License v3.0 or later |
Stars - the number of stars that a project has on GitHub. Growth - month over month growth in stars.
Activity is a relative number indicating how actively a project is being developed. Recent commits have higher weight than older ones.
For example, an activity of 9.0 indicates that a project is amongst the top 10% of the most actively developed projects that we are tracking.
ClickBench
-
Umbra: A Disk-Based System with In-Memory Performance [pdf]
Benchmarks: https://benchmark.clickhouse.com
So definitely compared against PostgreSQL, MariaDB it is significantly faster.
On par with lower-end Snowflake.
-
Loading a trillion rows of weather data into TimescaleDB
TimescaleDB primarily serves operational use cases: Developers building products on top of live data, where you are regularly streaming in fresh data, and you often know what many queries look like a priori, because those are powering your live APIs, dashboards, and product experience.
That's different from a data warehouse or many traditional "OLAP" use cases, where you might dump a big dataset statically, and then people will occasionally do ad-hoc queries against it. This is the big weather dataset file sitting on your desktop that you occasionally query while on holidays.
So it's less about "can you store weather data", but what does that use case look like? How are the queries shaped? Are you saving a single dataset for ad-hoc queries across the entire dataset, or continuously streaming in new data, and aging out or de-prioritizing old data?
In most of the products we serve, customers are often interested in recent data in a very granular format ("shallow and wide"), or longer historical queries along a well defined axis ("deep and narrow").
For example, this is where the benefits of TimescaleDB's segmented columnar compression emerges. It optimizes for those queries which are very common in your application, e.g., an IoT application that groups by or selected by deviceID, crypto/fintech analysis based on the ticker symbol, product analytics based on tenantID, etc.
If you look at Clickbench, what most of the queries say are: Scan ALL the data in your database, and GROUP BY one of the 100 columns in the web analytics logs.
- https://github.com/ClickHouse/ClickBench/blob/main/clickhous...
There are almost no time-predicates in the benchmark that Clickhouse created, but perhaps that is not surprising given it was designed for ad-hoc weblog analytics at Yandex.
So yes, Timescale serves many products today that use weather data, but has made different choices than Clickhouse (or things like DuckDB, pg_analytics, etc) to serve those more operational use cases.
-
Variant in Apache Doris 2.1.0: a new data type 8 times faster than JSON for semi-structured data analysis
We tested with 43 Clickbench SQL queries. Queries on the Variant columns are about 10% slower than those on pre-defined static columns, and 8 times faster than those on JSON columns. (For I/O reasons, most cold runs on JSONB data failed with OOM.)
-
Fair Benchmarking Considered Difficult (2018) [pdf]
I have a project dedicated to this topic: https://github.com/ClickHouse/ClickBench
It is important to explain the limitations of a benchmark, provide a methodology, and make it reproducible. It also has to be simple enough, otherwise it will not be realistic to include a large number of participants.
I'm also collecting all database benchmarks I could find: https://github.com/ClickHouse/ClickHouse/issues/22398
- ClickBench – A Benchmark for Analytical DBMS
- FLaNK Stack 05 Feb 2024
-
Why Postgres RDS didn't work for us
Indeed, ClickHouse results were run on an older instance type of the same family and size (c5.4xlarge for ClickHouse and c6a.4xlarge for Timescale), so if anything ClickHouse results are at a slight disadvantage.
This is an open source benchmark - we'd love contributions from Timescale enthusiasts if we missed something: https://github.com/ClickHouse/ClickBench/
-
Show HN: Stanchion – Column-oriented tables in SQLite
Interesting project! Thank you for open sourcing and sharing. Agree that local and embedded analytics are an increasing trend, I see it too.
A couple of questions:
* I’m curious what the difficulties were in the implementation. I suspect it is quite a challenge to implement this support in the current SQLite architecture, and would curious to know which parts were tricky and any design trade-off you were faced with.
* Aside from ease-of-use (install extension, no need for a separate analytical database system), I wonder if there are additional benefits users can anticipate resulting from a single system architecture vs running an embedded OLAP store like DuckDB or clickhouse-local / chdb side-by-side with SQLite? Do you anticipate performance or resource efficiency gains, for instance?
* I am also curious, what the main difficulty with bringing in a separate analytical database is, assuming it natively integrates with SQLite. I may be biased, but I doubt anything can approach the performance of native column-oriented systems, so I'm curious what the tipping point might be for using this extension vs using an embedded OLAP store in practice.
Btw, would love for you or someone in the community to benchmark Stanchion in ClickBench and submit results! (https://github.com/ClickHouse/ClickBench/)
Disclaimer: I work on ClickHouse.
- ClickBench: A Benchmark for Analytical Databases
- DuckDB performance improvements with the latest release
TimescaleDB
- TimescaleDB: An open-source time-series SQL database
-
Google Cloud Spanner is now half the cost of Amazon DynamoDB
Don't forget PostgreSQL extensions. For something like a chat log, TimescaleDB (https://www.timescale.com/) can be surprisingly efficient. It will handle partitioning for you, with additional features like data reordering, compression, and retention policies.
-
How to setup Postgres master-master cluster.
Offboard it to Postgres specialists like https://www.timescale.com/
-
How to Choose the Right MQTT Data Storage for Your Next Project
TimescaleDB{:target="_blank"}: an extension of PostgreSQL that adds time-series capabilities to the relational database model. It provides scalability and performance optimizations for handling large volumes of time-stamped data while maintaining the flexibility of a relational database.
-
Why does the presence of a large write-only table in a PostgreSQL database cause severe performance degradation?
Have some experience with https://www.timescale.com in this context
-
Opinions and Suggestions for PostgreSQL Extension under Development
What about getting in touch with commercial organisations that have products/services based on PostgreSQL? For example Timescale, EDB, and Citus Data, or really any hosting provider that offers a managed PostgreSQL service.
-
I have to do about a million inserts on a table every day that is also under very frequent reads. How should I do that?
There is Timescale.
-
Ask HN: It's 2023, how do you choose between MySQL and Postgres?
Friends don't let their friends choose Mysql :)
A super long time ago (decades) when I was using Oracle regularly I had to make a decision on which way to go. Although Mysql then had the mindshare I thought that Postgres was more similar to Oracle, more standards compliant, and more of a real enterprise type of DB. The rumor was also that Postgres was heavier than MySQL. Too many horror stories of lost data (MyIsam), bad transactions (MyIsam lacks transaction integrity), and the number of Mysql gotchas being a really long list influenced me.
In time I actually found out that I had underestimated one of the most important attributes of Postgres that was a huge strength over Mysql: the power of community. Because Postgres has a really superb community that can be found on Libera Chat and elsewhere, and they are very willing to help out, I think Postgres has a huge advantage over Mysql. RhodiumToad [Andrew Gierth] https://github.com/RhodiumToad & davidfetter [David Fetter] https://www.linkedin.com/in/davidfetter are incredibly helpful folks.
I don't know that Postgres' licensing made a huge difference or not but my perception is that there are a ton of 3rd party products based on Postgres but customized to specific DB needs because of the more liberalness of the PG license which is MIT/BSD derived https://www.postgresql.org/about/licence/
Some of the PG based 3rd party DBs:
Enterprise DB https://www.enterprisedb.com/ - general purpose PG with some variants
Greenplum https://greenplum.org/ - Data warehousing
Crunchydata https://www.crunchydata.com/products/hardened-postgres - high security Postgres for regulated environments
Citus https://www.citusdata.com - Distributed DB & Columnar
Timescale https://www.timescale.com/
Why Choose PG today?
If you want better ACID: Postgres
If you want more compliant SQL: Postgres
If you want more customizability to a variety of use-cases: Postgres using a variant
If you want the flexibility of using NOSQL at times: Postgres
If you want more product knowledge reusability for other backend products: Postgres
-
Help with timeseries data
TimescaleDB is Postgres with extensions to automatically partition tables for fast processing of time series data.
- Postgres for time-series data
What are some alternatives?
starrocks - StarRocks, a Linux Foundation project, is a next-generation sub-second MPP OLAP database for full analytics scenarios, including multi-dimensional analytics, real-time analytics, and ad-hoc queries. InfoWorld’s 2023 BOSSIE Award for best open source software.
ClickHouse - ClickHouse® is a free analytics DBMS for big data
duckdb - DuckDB is an in-process SQL OLAP Database Management System
promscale - [DEPRECATED] Promscale is a unified metric and trace observability backend for Prometheus, Jaeger and OpenTelemetry built on PostgreSQL and TimescaleDB.
TDengine - TDengine is an open source, high-performance, cloud native time-series database optimized for Internet of Things (IoT), Connected Cars, Industrial IoT and DevOps.
hosts - 🔒 Consolidating and extending hosts files from several well-curated sources. Optionally pick extensions for porn, social media, and other categories.
GORM - The fantastic ORM library for Golang, aims to be developer friendly
TablePlus - TablePlus macOS issue tracker
temporal_tables - Temporal Tables PostgreSQL Extension
clickhouse-bulk - Collects many small inserts to ClickHouse and send in big inserts
pgbouncer - lightweight connection pooler for PostgreSQL