VSD - Create partitioned table for counts
For the Value Stream Dashboard overview widget, we want to keep track of various counts in the database.
Proposed schema: value_stream_dashboard_counts
column | type | description |
---|---|---|
id | bigint | primary key |
namespace_id | bigint, foreign key | reference to the namespace record |
count | bigint | aggregated count |
metric | smallint | which metric is it |
recorded_at | timestamp | measurement time |
The table should be partitioned by recorded_at
(time range) where the partitioning strategy uses the monthly strategy (see WebHookLog
as an example). This gives us the opportunity to drop old data if needed. For now, the retain_for
option shouldn't be specified (keep all partitions).
Model setup: Analytics::ValueStreamDashboard::Count
Validations:
-
namespace_id
: required. -
count
: required, cannot be less than 0. -
metric
: required, enum. -
recorded_at
: required.
metric
mapping:
{
projects: 1,
issues: 2,
groups: 3,
merge requests: 4,
pipelines: 5,
members: 6
}
Database indexes: only add FK index on namespace_id
, the rest will be added when we implement the DB queries.