CouchDB data removal

[AroshanFernando]

We have CouchDB setup to store sensor data and view reports via dashboard. Currently one DB is over 2TB in size and we need to get rid of old data. We couldn’t find a way to delete data and free up the space. We though of filter replicate to a new DB and delete the old. We have a requirement of keeping past 6 months’ worth of data for viewing at a later day.

Option 1:
Create a new DB. Add a filtered replication from existing DB to get only last 6 months’ worth of data. Once the replication is completed start using the new DB and delete existing one.

Option 2:
Create a new DB and use that as the primary for data ingestion. Add a replication from new DB to existing larger DB and use that DB to view past months reports. After 6 months period, make a the new DB as primary for reports and delete larger DB.

What would be the best option considering your experience with similar approaches. Will there be better approach than this? What are the pros and cons. TIA.

[skeyby]

Just to understand better your situation, did you already delete the old datas and just need to get rid of the tombstones, or all the datas are still alive in the DB?

[AroshanFernando]

All the data are still alive in the DB. Was planning to drop old data with a filter replication.

[nickva]

Would you be able to query data across multiple databases or do you have to have only a single primary database for the clients to talk to?

If you can query multiple dbs, you could create a new db every month, then your query would have to find the range of dbs (this assumes time is part of every query), query them and aggregate the results in the application layer. And then to get rid of old data, you could simply delete dbs that are older than 6 months. To handle the switchover, if some clients might be updating the old dbs for a bit, you could start a filtered replication from previous dbs to ensure you bring any of those updates to the new db if they belong there. Depending exactly how dbs get created and where the timestamps get inserted you may not need this last part.

If you need to have only a single db holding at least 6 months of data, and querying multiple dbs is not going to work, then it seems Option 1 is pretty simple:

2021-01 ... 2021-06,2021-07 ... 2021-12
                    2021-07 ... 2021-12,2022-01 ... 2022-06
                                        2022-01 ... 2022-06,2022-07 ... 2022-12

After the 7th month and before the 12th (say on the 11th) , create a new db (second row) then start a filtered replication with >= 7th month into it (1st to 2nd row). You'd still use the 1st (top row) as the primary until the 1st month in 2022 then switch to the second db.

Some time before the 6th month in 2022 (say on the 5th), create the 1st -> 12th (third row) and start a filtered replication >= 1st (2nd row db 3rd row db). Keep using that 2nd db as primary until you hit 7th month, then switch to the 3rd, etc.

For filtered replications, you could do a continuous first, and after you switch db, and that is no long the primary, do another top-off one-shot replication, or maybe monitor that you don't have any more pending changes left. But a one-shot replication is best then I think. Once that completes you can delete the old db.

[AroshanFernando]

Current setup allows (without any changes) to communicate with single instance at a time. The way you described the option 1 seems solid and top-off option is something I didn't consider. Thanks!