Database Administrators Asked by ZiiMakc on November 14, 2021
I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs.
I can do it if i mark sequence as cycle and do requests like that:
INSERT INTO logs (initiator_id, target_id, action, message) VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE SET target_id=$2, initiator_id=$1, action=$3, message=$4;
What i don’t like, is that i need to repeat same inserts if there is conflict (line 2)
What i’am curios about, is it a good way to do, or there is other more practical ways for tables that you need clear lets say every 3 mounts?
Or maybe there is a way to generate unique id without using sequences?
For now i decided to make 2 columns primary key (date, initiator_id) as log is always 1 action at a time.
you could just use a monotonic sequence (always counting upwards) and delete records based on their age
you could do table partitioning by date and just drop the old partitions
Postgresql never replaces records, an update always creates a new record and marks the old one as unused. Autovacuum sooner or later will make the space used by the old one available to hold new records. If you want something like RRDB your should probably be using somethign like RRDB instead of postgresql.
Answered by Jasen on November 14, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP