drakkar-lig / drakkar-lig/walt-python-packages
Setup partitionning for logs database table
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 7
- Forks
- 4
- PR merge metrics
- No merged PRs in 30d
Description
Because of the size of table logs, possibly large (e.g., 36GB on our server), walt device forget can be long when deleting log lines of the device.
We could use postgresql table partitionning, with one partition per device mac, so that removing the log lines of a device would just mean removing the relevant partition.
Table partitionning in postgresql implies manually creating the partitions, but we could maybe add a trigger for that, or maybe just create the partition at the log insert call in the python code (there is probably one single call which inserts logs).
Most log queries specify the set of "log senders" (one exception is the web api entrypoint maybe), so this would probably not decrease the performance of those other queries.
An interesting video : https://www.youtube.com/watch?v=HVAImbzm9pc
Postgresql doc : https://www.postgresql.org/docs/current/ddl-partitioning.html
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the PostgreSQL partitioning documentation and locate the logs table plus the Python log-insert path referenced in the issue. Check how walt device forget deletes log lines and how the web API queries logs. Done means an agreed partition-creation approach is implemented and device log removal no longer requires deleting rows individually.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100