
Building columnar compression in a row-oriented database - craigkerstiens
https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/
======
georgewfraser
Reading this post is frustrating. What they are describing is where column
store databases were 20 years ago. Perhaps at some point the folks at
TimescaleDB will read Daniel Abadi’s 2008 paper, which describes the key
elements of how all modern column stores work:

[http://db.csail.mit.edu/pubs/abadi-column-
stores.pdf](http://db.csail.mit.edu/pubs/abadi-column-stores.pdf)

The key takeaway is that columnar compression only accounts for a small
minority of the speed up that you get for scan-oriented workloads; the real
big win comes when you implement a block-oriented query processor and
pipelined execution. Of course you can’t do this by building inside the
Postgres codebase, which is why every good column store is built more or less
from scratch.

Anyone considering a “time series database” should first set up a modern
commercial column store, partition their tables on the time column, and time
their workload. For any scan-oriented workload, it will _crush_ a row store
like Timescale.

~~~
grumpydba
> Anyone considering a “time series database” should first set up a modern
> commercial column store, partition their tables on the time column, and time
> their workload. For any scan-oriented workload, it will crush a row store
> like Timescale.

Or you can set up a clickhouse instance. It's a seriously promising and
underrated product.

~~~
atombender
Clickhouse is good, but it's definitely made for a very limited purpose; it's
not a general purpose SQL database. Which is fine, but the attraction with
something like TimescaleDB is that your time series data can coexist with
normal data.

~~~
PeterZaitsev
Many Time-Series applications do not need super complicated SQL. This is why
there are many timeseries focused databases even without SQL support.

There is also PostgreSQL Foreign Data Wrapper for ClickHouse which allows you
to run all SQL PostgreSQL support and often with great performance

~~~
buremba
If you use Postgresql's query engine on the Clickhouse data, you lose all the
benefits of the columnar query engine of Clickhouse so that's not correct.

~~~
grumpydba
No you don't lose them. Fdw supports push down of where clauses, only selects
the required columns. You can also create views in clickhouse to make sur the
joins are processed there.

~~~
buremba
You're right but if the syntax that you're using is not supported in
Clickhouse, aggregate and predicate pushdowns won't work and this FDW
([https://github.com/adjust/clickhouse_fdw](https://github.com/adjust/clickhouse_fdw))
needs to map all the Postgresql functions / produces to Clickhouse in order to
take advantage of push-down so the only use-case here is that you may want to
join the data in Clickhouse with the data in Postgresql (or other FDW
sources).

------
GordonS
Of anyone from TimescaleDB is here: I run a (very) small business using
TimescaleDB in production, but don't need any enterprise features and so
haven't purchased a commercial license. But I love it!

Do you have a Patreon, PayPal account, or any other means to receive money as
a donation, gift or token of appreciation?

~~~
mfreed
No, but we always love user stories! Shoot me email at mike (at) timescale.com

------
dgudkov
For anyone interested in columnar compression and columnar databases I highly
recommend reading Vertica's Technical Overview White Paper [1]. I don't know
how Vertica is doing now, but it's a beautifully designed columnar database
with many interesting concepts which influenced a lot the product I'm
currently working on.

[1]
[https://pdfs.semanticscholar.org/0cb4/d21ca5198819a85adae8ea...](https://pdfs.semanticscholar.org/0cb4/d21ca5198819a85adae8ea18fae83a0e9939.pdf)

------
cevian
Hey Timescale engineer here. Happy to answer any questions.

~~~
mamcx
I'm building a relational lang (that could feel like a in memory db) and
explored the idea of using a columnar backed structures.

How well could be apply the same ideas for in-memory processing?

If I understand correctly, you have something alike:

\- Store each column on a array of N=1000 \- Store the group of columns in
pages, with metadata of ranges of keys to locate rows in the adequate page

~~~
ants_a
This is commonly done in graphics and physics processing engines. In that
domain it's the array-of-structures vs. structure-of-arrays design choice.

------
fabian2k
I really like Postgres as an allround database, and putting monitoring data
into Postgres is an attractive idea to me. One thing I'm wondering is how well
you can mix a regular Postgres database with timeseries data using
TimescaleDB? If I have a regular application using Postgres, can I just add
timeseries in there with TimescaleDB in the same database? Or is that either
not possible, or simply a bad idea?

~~~
RobAtticus
You definitely can mix the two, and we actually find it a very useful thing so
you can have your metadata available to query alongside the time series data.
TimescaleDB will only make a table a hypertable if you tell it too, otherwise
regular tables should work as you'd expect. And since we appear as a table in
the contexts that it matters (JOINs, querying, inserting, etc), you shouldn't
have any issues mixing the two.

------
valyala
This is great news! We at VictoriaMetrics [1] are happy that TimescaleDB
eventually addressed high disk usage issue and implemented type-aware column-
based compression. This should improve its' position and performance in TSBS
benchmark [2].

One question: why do you use Gorilla compression for floating-point values? It
works well for integer values, but is pretty useless for floating-point values
[3].

[1]
[https://github.com/VictoriaMetrics/VictoriaMetrics](https://github.com/VictoriaMetrics/VictoriaMetrics)

[2] [https://medium.com/@valyala/measuring-vertical-
scalability-f...](https://medium.com/@valyala/measuring-vertical-scalability-
for-time-series-databases-in-google-cloud-92550d78d8ae)

[3] [https://medium.com/faun/victoriametrics-achieving-better-
com...](https://medium.com/faun/victoriametrics-achieving-better-compression-
for-time-series-data-than-gorilla-317bc1f95932)

~~~
mfreed
Gorilla is one of the few simple, efficent, _lossless_ algorithms that
compress binary data.

Tricks such as renormalizing floats in base-10 (which I believe
VictoriaMetrics implements) while great when they work, potentially truncates
lower-order digits.

Lossiness is not a decision we're willing to make for our users unilaterally.
And, as Gorilla is one the default compression algorithms we employ, it needs
to be one that is correct in all cases.

Incidentally, we experimented with the binary equivalent of the
renormalization algorithm, and it performed on-par with Gorilla in our tests.

------
1996
Too bad pipelinedb died. It was a nice complement to timescaledb -- with small
oddities that sometimes took a production database down if you had no
experience of advanced vacuum / repack features.

Any plan to bring back something like continuous views in timescaledb? (or to
integrate pipelinedb work)

~~~
mfreed
Continuous aggregates were first introduced in v1.3. Not identical to
PipelineDB features, but address some of the very same use cases. We have some
cool new features for continuous aggs planned too.

Tutorial: [https://docs.timescale.com/latest/using-
timescaledb/continuo...](https://docs.timescale.com/latest/using-
timescaledb/continuous-aggregates)

Example with Grafana dashboarding: [https://blog.timescale.com/blog/how-to-
quickly-build-dashboa...](https://blog.timescale.com/blog/how-to-quickly-
build-dashboards-with-time-series-data/)

Technical details of correctly handling backfilled data:
[https://blog.timescale.com/blog/continuous-aggregates-
faster...](https://blog.timescale.com/blog/continuous-aggregates-faster-
queries-with-automatically-maintained-materialized-views/)

------
powturbo
\- Timestamps are very easy to compress down to 0.01%

\- Floats are hard to compress without quantization or lossy compression. The
Gorilla algorithm is not helping so much.

You can do your own benchmarks with your data with TurboPFor :
[https://github.com/powturbo/TurboPFor](https://github.com/powturbo/TurboPFor)

or download icapp including more powerfull algorithms.

~~~
m0zg
Nonrandom time series floats are often significantly helped by delta-
compression before applying the actual block compression algorithm. I used to
do this quite successfully in late 90s when sending FOREX quotes to clients
over analog modem connections.

~~~
powturbo
Yeah, for benchmarking it is important to use real data, because the
compression ratio can significantly differ. You'll have better compression
with lossy compression and better precision with user controlled and bounded
relative error. see for ex. this benchmark for scientific data:
[https://github.com/powturbo/TurboTranspose](https://github.com/powturbo/TurboTranspose)

------
jnordwick
Instead of trying to hack column like features and performance onto a row
oriented structure, why not just develop a column storage for hypertables? All
this work seems like a dead end performance-wise.

~~~
cevian
The article covers this in more depth, but the short answer is: The hybrid
approach has benefits when your workload has both shallow-and-wide (fetch all
data for user X) and deep-and-narrow queries (calculate the average number of
logins for all users). Row stores excel on shallow queries, column stores
excel on deep queries. For time-series data you want row-oriented structures
when the data just comes in and convert it to a column store after it ages.

------
vkaku
+10 for moar compression!

