Suggestion: use TimeScaleDB

[justb4]

(with @tomvantilburg at OSGeo.org codesprint Bonn) : when going to millions or billions of Observations use of a time-series DB like InfluxDB or Prometheus may be beneficial. But PostgreSQL with PostGIS remains a strong option for geospatial data (e.g. InfluxDB has no official geospatial facilities).

To combine the best of both worlds (time-series and geospatial) TimeScale DB (with PosGIS) may be considered as a spatiotemporal DB solution for GOST.

[bertt]

some impact assement:

1] what database changes are needed? 2] what code changes are needed? 3] What are the performance benefits?

[bertt]

a concern: the SensorThings Observation entity has time range properties (ValidTime, PhenomenonTime). Normally the Postgres column type 'tsrange' is used for time ranges, but I read TimeScale DB does not use these? https://github.com/timescale/timescaledb/issues/277

[justb4]

I would expect performance benefits in queries and aggregations on time-fields. Many apps use nosql or InfluxDB databases, but these are usually weak in geospatial support. I would expect what I call "best of both worlds" (temporal and spatial) with TimescaleDB, like in inserts and queries.

The impact: hard to give absolute assessments, have no working experience with TimescaleDB. I would suggest to focus first on indexing (as in #3 and gost/server#152) as that is simple to implement and measure and to perform a PoC with TimescaleDB.

[bertt]

did some quick experiments with Timescale with a 10 million records NYC taxi table (https://docs.timescale.com/v0.11/tutorials/tutorial-hello-nyc).

Commands used: https://gist.github.com/bertt/2faf910e5417c48e9a3a57266fe53a8a

Some notes:

• Installation: Timescale is just an extension of PostgreSQL, just like PostGIS;
• Docker: There is a Docker available with PostGIS and Timescale (timescale/timescaledb-postgis);
• You can enable Timescale per table using command 'create_hypertable' with parameters tablename and columns. Temporary tables are created with names like 'hyper_1_1_chunk' containing parts of the table;
• To benefit from Timescale you need time-enabled tables with millions of records;
• All regular queries work on Timescale too (inluding spatial queries), but Timescale offers some extra functions like 'time_bucket';
• Observation: grouping by date queries are factor 2 or 3 faster with Timescale enabled, queries with 'time_bucket' (grouping by minutes) cannot be compared;
• Writing data (inserts) should be faster too (not tested yet);
• The 'time' column used in the create_hypertable function supports timestamp, date, or integer types, so you can use a parameter that is not explicitly time-based, as long as it can increment. So no time ranges support.

[bertt]

if we want to experiment with Timescale and GOST the following actions could be taken:

• database: split column observation.data (type jsonb) into columns like PhenomenonTime_start, PhenomenonTime_end, ValidTime_start, ValidTime_end, ResultTime, Result, ResultQuality and Parameters;
• database: enable Timescale on table observation (using columns PhenomenonTime_start, PhenomenonTime_end, ValidTime_start, ValidTime_end, ResultTime? Or a subset of these?);
• application: rewrite sql reading/writing to observation.data column;
• test with million of datastream/observation records, what are the pro/cons of using Timescale?

[bertt]

created experimental branch for this https://github.com/gost/gost-db/tree/timescale_db

changes:

• docker image based on timescale/timescaledb-postgis:latest-pg10 instead of mdillon/postgis

• schema creation sql script:

1] add 'create extension timescaledb'

2] changed table Observation, moved period phenomenonTime from data jsonb to columns phenomenonTime_start and phenomenonTime_end

3] Create hypertable using column phenomenonTime_ start from Observation:

SELECT create_hypertable('observation', 'phenomenontime_start',
    chunk_time_interval => interval '1 day');

todo: check if this works on rpi too

next step: get application working with these database changes