Data structure and naming conventions

We follow the standards from prometheus if possible : https://prometheus.io/docs/practices/naming/

Variables should have a suffix describing the unit. And a prefix describing the origin. Example data structure: https://docs.timescale.com/latest/tutorials/tutorial-hello-timescale

As variables can store data in an aggregated way we have this indication:

_d: Daily _h: Hourly

Metrics vs Variables

Metrics: Time Based integer values (hypertables) Variables: Additional metainformation, can also store other formats

Performance Metrics vs Page Variables

Performance Metrics are all of type integer and already aggregated (e.g. by day). They measure the performance of a specific url, e.g. how many times a user visited the page.

Page Variables define a specific state of the page for a given time. It can be integer (e.g. number of headings), holding text (e.g. the Metadescription) or boolean (e.g. if breadcrumbs are present).

Storage

The ACONA Data Warehouse is using TimeScaleDB: https://www.timescale.com/

We create one table by metric/variable (narrow table mode): https://docs.timescale.com/latest/introduction/data-model

Example:

CREATE TABLE "metric_d_bounces"(
    url TEXT, 
    date DATE NOT NULL,
    value INTEGER
    );
SELECT create_hypertable('metric_d_bounces', 'date', create_default_indexes=>FALSE);
CREATE INDEX ON metric_d_bounces (url, date DESC);