Postgres looped table for logs

user_10188661 0
Viewed 45

Is it ok to make looped table for logs that has no need to live long using cycle sequence?

Only inconvenience I can see is that for log inserts you always need to add on conflic and maybe >second cycle insert will be slower because it's always be a conflict.

Sandbox.

create table foo (
    id int primary key,
    data text
);

CREATE SEQUENCE id_seq MINVALUE 1 maxvalue 3 cycle;
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('id_seq');


insert into foo (data) values ('1') on conflict (id) do update set data='1';
insert into foo (data) values ('2') on conflict (id) do update set data='2';
insert into foo (data) values ('3') on conflict (id) do update set data='3';
insert into foo (data) values ('4') on conflict (id) do update set data='4';
select* from foo order by id;

Or maybe there is easier way to do it? Like cleaning job that will run every year by postgres itself?

Requirements:

  • many logs inserts
  • reasonable overall table size
  • logs don't need to live long, year is enough
postgresql
2 Answers

It works, but I would suggest it is NOT a good design. The problem is your log reuses the id when it cycles. The the question then becomes "how many log entries will there be in a given time frame?" And what is the downside if you guess too small? I would add a date to the table and periodically run a delete for anything older than your time frame and let your sequence have full integer range. If however, you want to persist with a cyclic sequence you do not necessary need to change your statement. Instead of an insert statement, wrap it in a procedure.

create or replace procedure log_foo(p_data text) 
  language sql 
as $$
   insert into foo (data) values (p_data) 
       on conflict (id)
       do update set data=excluded.data 
$$;

or if your Postgres version does not have procedures or you want the ability to get the id:

create or replace function foo_log(p_data text) 
  returns integer
  language sql 
as $$
   insert into foo (data) values (p_data) 
       on conflict (id)
       do update set data=excluded.data 
       returning id;
$$;

See full example here. Note the procedure and function as they are themselves no matter how you define the sequence. Also the insert itself need only change for the actual data value in 1 place, not 2.

user_76238560

This seems overly complicated and might not perform very well.

I would recommend using a partitioned table for logging. I would partition it by log time.

Then you can get rid of old entries by dropping a partition, which is very fast.

user_64643080
Related