Working with time-series data and indexes

[georgolden]

Is your feature request related to a problem? Please describe.

We have schema of a kind Journal. It supposes to handle time-series data (logs). This is a useful feature to have out-of-box.

Solution

Kind Journal needs to be handled with creation of an index for time-series data.
There must be queries example of it.
For easier scalability, it makes sens to implement TimeScale Db extension support.

To-Do

• clone this repo, run a container or standalone Postgres instance, setup database

git clone git@github.com:metarhia/metasql.git
cd metasql
npm ci
cd db

# optional
sh docker.sh

sh setup.sh
npm t

• create a test Journal schemas
  Example of schema:

// YourSchema.js
({
  Journal: { timescale: true },

  data: 'json',
});

This will contain now fields:

{
  kind: 'journal',
  name: 'YourSchema',
  fields: {
    data: { type: 'json', required: true },
    // this field automatically adds now
    yourSchemaId: { type: 'string', required: false, note: 'Auto-generated primary key },
    // this field must be added only for kind journal
    datetime: { type: 'datetime', required: false, default: 'now', note: 'Timestamp with time zone' },
  },
}

• write tests and implement index for time-series data on native Postgres
• after previous done you can design a scenario, where a developer can use TimeScaleDb extension and implement it's support

This is very early concept and needs to be improved. @tshemsedinov @nechaido please contribute to this issue