CODEWITHSUNDEEP
postgresqlpostgresql-9.3citus
Raj Patil
Raj Patil

Reputation: 11

Delete in Postgres citus columnar storage alternatives

I am planning to use citus to store system logs for upto n no of days after which they should be deleted.Citus columnar store looked like the perfect database for this until I read this where its mentioned no deletes can be performed on columnar. So my question is there an alternate way of achieving delete in the columnar store?

Upvotes: 1

Views: 891

Answers (1)

Aykut Bozkurt
Aykut Bozkurt

Reputation: 679

You can temporarily switch table access method to row mode to delete or update the table. Then after the operation you can switch back to columnar access method. An example usage is shown below:

-- create table and fill with generated data until 20 days before
CREATE TABLE logs (
  id int not null,
  log_date timestamp
);

-- set access method columnar
SELECT alter_table_set_access_method('logs', 'columnar');

-- fill the table with generated data which goes until 20 days before
INSERT INTO logs select i, now() - interval '1 hour' * i from generate_series(1,480) i;
    
-- now you want to drop last 10 days data, you can switch to row access method temporarily to execute delete or updates
SELECT alter_table_set_access_method('logs', 'heap');
DELETE FROM logs WHERE log_date < (now() - interval '10 days');

-- switch back to columnar access method
SELECT alter_table_set_access_method('logs', 'columnar');

A better alternative for log archiving: We are creating a whole copy of the source table to have a table with new access method. The bigger the table, the more resources will be consumed. A better option is that if you can divide your log table into partitions of days or months, you will only need to change access method for single partition. Note that you should set access method for each partition separately. Columnar currently do not support to set access method of partitioned table directly.

Learn more:

Upvotes: 1

Related Questions