KCIDB: Add timestamp metadata
Nikolai Kondrashov <[email protected]>
| Newsgroups | dev.linux.lists.kernelci |
|---|---|
| Message-ID | <[email protected]> |
Hello everyone involved with, or interested in KCIDB,
As we're working on transitioning away from BigQuery as our main database, and
lowering our growing costs, we have to implement data retention policies in
order to maintain performance of PostgreSQL as the replacement.
For that purpose I'd like to introduce a concept of "metadata" to the KCIDB
I/O schema. Specifically a timestamp field for each object. The change will
bump the latest schema to v4.3.
The "metadata" is any field with a name starting with an underscore ("_").
Such fields are always discarded on submission, and are not loaded into, or
fetched from the database, by default. The API and the tools will have the
option to load them, however. E.g. for transferring raw data between
databases.
The timestamp field will be called "_timestamp", and added to the schema of
each object. It will be impossible to submit it through normal means, and it
will be generated/updated by the database automatically on each submission.
The new field will allow us to implement a de-duplication deadline in
PostgreSQL, after which object updates won't be allowed, and the data could be
transferred into BigQuery for long-term storage and public access for
analysis. Allowing only de-duplicated data in BigQuery would let us partition
the dataset by timestamp, reducing query costs for public access.
The new field would also let us delete the data archived in BigQuery from
PostgreSQL, after a while, and thus help us maintain its performance for
notification generation and dashboards.
Note that the "_timestamp" field will be different from the existing
"start_time" fields. The former will be automatically-generated by the
database, and the latter are submitter-supplied and optional. However, when
the database schema is upgraded to support the "_timestamp" field, it will
receive the value of "start_time" where it exists and is specified, for
existing objects. Otherwise the existing objects will receive the time of
upgrade as its value.
Please respond with your comments here, or in the corresponding PR for
kcidb-io:
https://github.com/kernelci/kcidb-io/pull/76
Thank you.
Nick