Hey! We're looking to evaluate TimescaleDB for a logistics IoT scenario. Some of the data that enters our system comes from connected devices where recorded_at and inserted_at columns are basically the same. Some data however is sourced from dataloggers that may record for months before the data arrives at our system.

With TimescaleDB, would I use the recorded_at or inserted_at column for the hypertable?

Does this change if data for an individual sensor can sometimes arrive out of order? If the sensor malfunctions and the data contains timestamps in the far past or the far future does this cause issues with TimescaleDB?

What we've done in postgres so far is have the tables with data generally structured around the recorded_at column because most analysis wants to look at the data "in order" . to generate reports, graphs, etc. Each data row also contains a "payload_id" relating it to a "payloads" table which helps group data by when it actually hit the system. Data processing has generally been built around the payloads and then query any additional data in recorded_at order on the main data tables if we need to look back or forward in time.

For choosing the column, you'll usually want to think about what your queries will be using. It sounds like `recorded_at` is probably more likely to be useful since that's when the data "occurred," but again it depends on your expected query load.

Out of order data should be handled fine by TimescaleDB -- if you do have data that is far in the future or in the past, you may get stray chunks to hold those, but it's not going to create all the intermediate chunks or anything that might be undesirable. You can later correct those fields by deleting and reinserting the record with a corrected timestamp.

Sounds like he's describing a bitemporal database (although not in the canonical form usually associated with them).

I've looked into timescaledb for this, and it doesn't support them.

I agree. Bitemporal databases can natively handle late-arriving data in these kinds of upstream timestamp integration scenarios.

However, the intersection of bitemporal indexes and columnar time-series queries seems important and yet I haven't seen anything that looks like it might offer both, possibly asides from kdb+ and SAP HANA.

Disclosure: I work on https://github.com/juxt/crux (which is optimised for bitemporal graph joins and doesn't currently employ columnar indexes)

I generally like the direction y'all are going.

Question: how does the new compression compare to TokuDB, and is it tuneable for performance/size tradeoff?

https://www.percona.com/doc/percona-server/5.7/tokudb/using_...

Haven't done any comparisons against TokuDB, so can't give a deep answer there. [Edit: removed incorrect bit about tuning algorithm]. We do not offer tuning yet, but this is the initial implementation, so we'll definitely be looking at what knobs we can offer in the future.

Very cool! The effect on page layout sounds like it would be pretty similar to Oracle's hybrid columnar compression[1], but they claim average compression ratio is more like 10:1. Any idea what would make so much of a difference?

[1] https://www.oracle.com/technetwork/database/exadata/ehcc-twp...

One guess from a super quick scan can be that we use type-specific compression algorithms. So if your table has one column of timestamps, another of floats, another int, another string, the database employs different compression algorithms (typically best-in-class) based on the column type.

Quick scan of the Oracle paper couldn't find specifics, other than something like this:

"Warehouse Compression provides two levels of compression: LOW and HIGH. Warehouse Compression HIGH typically provides a 10x reduction in storage, while Warehouse Compression LOW typically provides a 6x reduction"

That would at least suggest that they aren't doing anything type-specific like we are.

It may also be that we're operating in a slightly more delayed fashion (partially based on chunk boundaries), so we can organize across a lot larger range. For example, if you choose to segment by a device_id, it might scan 1M rows to assemble blocks/segments of device_ids, with each device_id having 1000 records to compress in a "mini column".

This also leads to significant query performance settings if you common filter by device_id, for example. Which are super common in time-series workloads for IT monitoring / devops / IOT / etc.

Congratulations!

* What is a plan for PG12 PLUGGABLE STORAGE[1]? this is for PG12?

* Can you compare with Zedstore[2]?

[1] https://www.postgresql.org/docs/devel/tableam.html

[2] https://github.com/greenplum-db/postgres/tree/zedstore

Hi @pello, article author here.

One of the interesting things of our technique is that it doesn't require low-level changes to Postgres, and actually then works with any version of PG that TimescaleDB supports (currently PG10, 11...PG12 coming soon).

That said, we're excited by the work Postgres has been doing with pluggable storage, particularly how PG13 will further open up possibilities such as Zedstore, and look to see how we can then marry some of these ideas. In terms of feature-by-feature comparison, haven't yet dug enough into the details.

Aside, one interesting aspects of our approach is discussed in the article: Having a "hybrid" row/column, rather than purely columnar, can actually be beneficial for many time-series workloads that constantly query very recent data (e.g., for dashboarding) as well as to improve ingest rates (although some column stores do build a temporary in-memory row-based cache before batch writing a column).

for dashboarding use something like pipelinedb (RIP)

PipelineDB was useful primarily if you primarily wanted to maintain a _continuous_ materialization of some aggregate (e.g., an approximation of the distinct items in the database), not necessarily an aggregate per time period.

If you are interested in primarily showing the recent data -- which you see in many monitoring examples in IT/devops or IoT -- you often want the raw data or an aggregate per time period.

As an aside, TimescaleDB introduced continuous aggregations in v1.3. Here's a nice example of using it with Grafana for dashboarding: https://blog.timescale.com/blog/how-to-quickly-build-dashboa...

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

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.

Are there any plans to collaborate with the PostgreSQL project? Especially on Andres' work on speeding up the executor? My apologies if you already are and I have just not seen your names on the mailing list.