Skip to content

Setup partitionning for logs database table #133

@eduble

Description

@eduble

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

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions