Telemetry: prune the amount of data stored

[humitos]

When we implemented telemetry database to save BuildData objects we didn't implement the prune of it. After some days/weeks, we experimented with growth of 3Gb in data.

We should prune the BuildData objects with some useful logic. We talked about pruning based on:

1. only creation datetime (e.g. store last 6 months)
2. spam score (e.g. only save BuildData if spam score is less than 150)
3. fixed number of objects based on project/version (e.g. save 100 objects per project/version)

Each of them has its own downsides and we can talk/discuss a good implementation. It would be good if we can keep it simple and store only the data we need for the answers we are looking for.

Worth to note that we created this issue because a PagerDuty alarm was triggered due to the lack of extra free space in the database.

[ericholscher]

Yea, I think the big question I have is "what valuable queries are we running against this data?" I think the answer currently is "we aren't really using this data yet", but I think knowing how we think it will be useful is important in making sure we can only keep the data we care about. Alternatively, we could archive it somewhere that isn't stored in postgres (eg. a monthly pg_dump file or csv in S3) so that we can query it if we need to, but we aren't paying to store it in a queryable form.

We do this with our ads data, but we almost never go back and query it, so I'm not sure how useful it is to have archived old data, we can probably just delete it. The ads data we do this because it's billing data, but BuildData is not as important.

[humitos]

@ericholscher

Yea, I think the big question I have is "what valuable queries are we running against this data?" I think the answer currently is "we aren't really using this data yet", but I think knowing how we think it will be useful is important in making sure we can only keep the data we care about

These are two real cases where I used it and it was useful:

• Do not install unsupported Pillow for Python 3.8+ #9118 (comment)
• Removing JavaScript Dependencies sphinx-doc/sphinx#10070 (comment)

Alternatively, we could archive it somewhere that isn't stored in postgres (eg. a monthly pg_dump file or csv in S3) so that we can query it if we need to, but we aren't paying to store it in a queryable form.

I don't think it is worth the effort because I don't think we will come back to pretty old data. The data we want to query to make decisions shouldn't be too old.

[humitos]

As a first step, to avoid it growing too much, I'd save X months of data. Then, we probably want to save more data only for "active projects" --which are the ones that we care about the most.

As a reference, ~90 days of data is ~8Gb:

docs=> SELECT pg_size_pretty(pg_database_size('telemetry')); 
 pg_size_pretty 
----------------
 8014 MB
(1 row)

Quick math:

• 6 months of data is 30Gb
• 12 months of data is 60Gb (about the same size of docs db)

[ericholscher]

Sounds like we should probably just keep the last 90 days for now at a minimum?

[humitos]

ha ha! I don't how my math works... "90 days is 8Gb and 6 months is 30Gb" 🤔

Sounds like we should probably just keep the last 90 days for now at a minimum?

Yes. I'd start with 180 days --which should be ~16Gb and I think it's acceptable. We can keep tuning it later as we start using this data more.