Postgresql – Approaches for deleting unnecessary records from tables

deletepostgresqltruncate

We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older than 3 months. I was planning to do it as follows as discussed in that question here:

Select the (relatively few) surviving rows into a temporary table.
Truncate the table.
Re-insert the survivors.

But all devices are working without interruption and inserting values into the DB so I can not Truncate and rename the temp_tables since the system should be on all the time. Or can I? If not, do I have to do it by DELETE VACUUM?

By the way we delete all unnecessary records which are older than 3 months just once, afterwards we have to repeat this process every month for the 4.th month previous (I hope that part was clear). If we repeat this process at the beginning of 05.2013, we have to delete the unnecessary records of the month 01.2013. How can I automatize this process? Should I better write a .bat file for scheduled task or is there any better approaches?

Best Answer

You seem to be misinterpreting a part of my advice to your previous question:

so I can not Truncate and rename the temp_tables since the system should be on all the time.

There was no renaming involved. After TRUNCATE you run an INSERT. The only blocking operation is the TRUNCATE. I quote the manual:

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table. (...) If concurrent access to a table is required, then the DELETE command should be used instead.

INSERT may still work, if it is not time-critical and allowed to wait until the lock is released. TRUNCATE is usually very fast, if you run it in a separate transaction, it should only block for a couple of milliseconds. Note: separate transaction, not separate session! Your temporary table lives and dies with the session.

The drawback of a separate transaction: if you lose your session before you are able to re-insert from the temporary table, you loose data. To be sure, you could use a plain table instead. (Which would offer the alternative path to drop the old and rename the new table.)

Either way, your updated question makes clear that you want to run this repeatedly, accumulating old rows in the same table. In this case, TRUNCATE is not a good option anyway. You can always just use a plain DELETE. Considerably slower with big tables, but concurrent INSERT is not blocked at all. The autovacuuming daemon will have to do some more work, too.

It's mostly a trade-off between speed and security.

SQL

The DELETE command for the slow and sure method could look like this:

DELETE FROM TABLE tbl t
USING (
   SELECT created_at FROM tbl WHERE created_at < now() - (interval '3 month')
   ) d
LEFT   JOIN (
   SELECT min(created_at) AS created_at
   FROM   tbl
   GROUP  BY date_trunc('minute', created_at)
   WHERE  created_at < now() - (interval '3 month')
   ) x USING (created_at)
WHERE  x.created_at IS NULL
AND    d.created_at = t.created_at;

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

PostgreSQL – Truncate vs Delete/Vacuum Full for Deleting Table Rows

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:

Select the (relatively few) surviving rows into a temporary table.
Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
Optimizing bulk update performance in PostgreSQL
Truncate the table.
Re-insert the survivors.

This way, views stay intact. They would prohibit you from dropping involved tables.
You also do not need any vacuuming, no dead rows at all this way. While being at it, you may want to ORDER BY your rows for the INSERT for optimized SELECT performance.

Here and here are closely related cases with more details on SO.

Best Answer

SQL

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

PostgreSQL – Truncate vs Delete/Vacuum Full for Deleting Table Rows

Related Question