drakkar-lig / drakkar-lig/walt-python-packages

Setup partitionning for logs database table

Open
#133 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement
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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.