3 ms·

I set up a time-series Postgres 9.6 database last year with traditional table partitioning (based on entity id, not time). I looked at TimescaleDB but it was ab

I set up a time-series Postgres 9.6 database last year with traditional table partitioning (based on entity id, not time). I looked at TimescaleDB but it was about a month old at the time so I wasn't comfortable recommending it. Also I got the impression that it was little more than a wrapper around traditional partitioning, just to make it easier to use. Looking at their Github now it seems to be more than that though. I will have to give it a closer look! But also you might want to clarify your literature about what's going on. Is it something like cstore_fdw with its own files (but not built on FDW)? I have a Postgres extension of my own for fast timeseries inserts that gives you back the data as an array (https://github.com/pjungwir/floatfile https://github.com/pjungwir/floatfile), but it is much less developed and comes with a lot of caveats. My own approach is very simple: just make a file of 8-byte floats and keep appending to it. Is timescaledb something like that (but better)? One of the drawbacks with my approach is that you have to re-implement a lot of things (like histograms). Even though you say you support "all of SQL" it looks like you are similarly re-implementing things that would be slow otherwise (histograms). Also, I see a lot about insert performance, but I keep getting projects where inserts aren't challenging but getting fast analytic queries is. I'd love to see some examples/numbers there. I really like the idea of keeping timeseries data quasi-connected to your main RDBMS, so I'm thrilled to see successful projects in that space. It can definitely be challenging. I wonder if something like influx_fdw is possible and would make sense? (I've never used InfluxDB.)

mfreed 9y ago
Thanks for the comments, and it sounds like you want a deeper dive as to some of the design decisions around TimescaleDB. What might be useful to start is two of our technical blog posts around how we design internal partitioning [1], as well as how this compares against declarative partitioning in PG10 [2]. We aren't rewriting the lowest storage layers like cstore_fdw, so we can continue to use Postgres' heavily-tested storage layers....as well as work with its existing streaming replication and recovery mechanisms. We also aren't packing individual writes into files (like it sounds you are doing with floatfile), arrays (like in tgres), or other TOASTED data structures, although we might be compatible. We certainly do support the range of types (arrays, JSON/B, etc.) in our data rows. Fully agree that projects differ on their needs of inserts vs. queries time, but in addition to much higher insert rates, our general data model and aggressive constraint exclusion across chunks provides meaningful time-based query improvements. We also have all sorts of planner optimizations, e.g., we taught the planner that querying for the data by time rounded-to-some-time-bucket preserves the sort on time --- which is useful for all kinds of ORDER BY/GROUP BY queries. We've published various benchmarks around this [3]. Finally, there are a lot of changes needed to "just make things work", which also includes support for constraints, foreign keys, upserts, triggers, etc., which enable capabilities like data retention and continuous aggregation policies. [1] https://blog.timescale.com/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c https://blog.timescale.com/time-series-data-why-and-how-to-u... [2] https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5#88f3 https://blog.timescale.com/time-series-data-postgresql-10-vs... [3] https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e
- bsg75 9y ago
  To confirm, Timescale does not currently support upserts? INSERT ON CONFLICT ... ?
  - RobAtticus 9y ago
    We do support UPSERTs: http://docs.timescale.com/v0.8/using-timescaledb/writing-data#upsert http://docs.timescale.com/v0.8/using-timescaledb/writing-dat... There is one limitation mentioned there ("TimescaleDB does not yet support using ON CONFLICT ON CONSTRAINT with a named key") that doesn't work, and a tip that mentions requirements for the UNIQUE constraint, but these are not usually dealbreakers.

mfreed 9y ago

Thanks for the comments, and it sounds like you want a deeper dive as to some of the design decisions around TimescaleDB. What might be useful to start is two of our technical blog posts around how we design internal partitioning [1], as well as how this compares against declarative partitioning in PG10 [2]. We aren't rewriting the lowest storage layers like cstore_fdw, so we can continue to use Postgres' heavily-tested storage layers....as well as work with its existing streaming replication and recovery mechanisms. We also aren't packing individual writes into files (like it sounds you are doing with floatfile), arrays (like in tgres), or other TOASTED data structures, although we might be compatible. We certainly do support the range of types (arrays, JSON/B, etc.) in our data rows. Fully agree that projects differ on their needs of inserts vs. queries time, but in addition to much higher insert rates, our general data model and aggressive constraint exclusion across chunks provides meaningful time-based query improvements. We also have all sorts of planner optimizations, e.g., we taught the planner that querying for the data by time rounded-to-some-time-bucket preserves the sort on time --- which is useful for all kinds of ORDER BY/GROUP BY queries. We've published various benchmarks around this [3]. Finally, there are a lot of changes needed to "just make things work", which also includes support for constraints, foreign keys, upserts, triggers, etc., which enable capabilities like data retention and continuous aggregation policies. [1] https://blog.timescale.com/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c https://blog.timescale.com/time-series-data-why-and-how-to-u... [2] https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5#88f3 https://blog.timescale.com/time-series-data-postgresql-10-vs... [3] https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e

bsg75 9y ago
To confirm, Timescale does not currently support upserts? INSERT ON CONFLICT ... ?
- RobAtticus 9y ago
  We do support UPSERTs: http://docs.timescale.com/v0.8/using-timescaledb/writing-data#upsert http://docs.timescale.com/v0.8/using-timescaledb/writing-dat... There is one limitation mentioned there ("TimescaleDB does not yet support using ON CONFLICT ON CONSTRAINT with a named key") that doesn't work, and a tip that mentions requirements for the UNIQUE constraint, but these are not usually dealbreakers.