Postgres looped table (start sequence from 1 and rewrite old rows)

Question

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.

Jasen · Answer

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.

Postgres looped table (start sequence from 1 and rewrite old rows)

One Answer

Add your own answers!

Ask a Question