TransWikia.com

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

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.

One 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.

Answered by Jasen on November 14, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP