0

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.

1

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

  • but there will be limit even for bigint. – RTW Jun 6 at 16:53
  • yes, you can allow it to loop, just don't use the looping to means something – Jasen Jun 7 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.