Cron job to remove logically redundant entries in Postgres SQL

[Abhilash28coder]

I have a requirement to delete records from a Postgres SQL table which has more than 200 million records. The table is not having any primary key.

The sample table (Bookmark is the name of table) content is as below:

    systemId     filename            mindatetime                    maxdatetime
      70277        monitor_1.dat    2019-04-21 08:00:00 AM        2019-04-21 03:10:00 PM
      10006        monitor_2.dat    2019-04-25 10:00:00 AM        2019-04-25 11:30:00 AM
      10006        monitor_3.dat    2019-04-28 08:00:00 AM        2019-04-28 10:00:00 AM
      10006        monitor_3.dat    2019-04-28 09:00:00 AM        2019-04-28 11:00:00 AM
      10006        monitor_3.dat    2019-04-28 07:00:00 AM        2019-04-28 04:00:00 PM
      8368        monitor_1.dat    2019-05-21 11:00:00 AM        2019-05-21 11:30:00 AM
      8368        monitor_7.dat    2019-05-21 06:00:00 AM        2019-05-21 11:00:00 AM
      8368        monitor_5.dat    2019-05-23 08:00:00 AM        2019-05-23 10:00:00 AM

The cron job should run on a given schedule to delete the records which are logically redundant.

To explain this let's take the case of systemId '10006' where filename is 'monitor_3.dat' having 3 entries with min and max date timestamp of the same day.

Logically we can delete the entries having mindatetime 08:00:00 AM and 09:00:00 AM, maxdatetime 10:00:00 AM, 11:00:00 AM as that interval is being covered by the other entry which has mindatetime as 7 AM and maxdatetime as 4 PM.

So those entries would fall under this interval and the job should identify such entries in the entire table and delete them.

My resultant output table content in this case should be:

    systemId     filename            mindatetime                    maxdatetime
      70277        monitor_1.dat    2019-04-21 08:00:00 AM        2019-04-21 03:10:00 PM
      10006        monitor_2.dat    2019-04-25 10:00:00 AM        2019-04-25 11:30:00 AM
      10006        monitor_3.dat    2019-04-28 07:00:00 AM        2019-04-28 04:00:00 PM
      8368        monitor_1.dat    2019-05-21 11:00:00 AM        2019-05-21 11:30:00 AM
      8368        monitor_7.dat    2019-05-21 06:00:00 AM        2019-05-21 11:00:00 AM
      8368        monitor_5.dat    2019-05-23 08:00:00 AM        2019-05-23 10:00:00 AM

The table size is more than 20Gb on disk so I was exploring writing a sql procedure or job to achieve this but not able to make much progress. Any ideas or suggestions for overcoming this complex scenario?

[Rabbit]

What happens if they only partially overlap? What happens if they fully overlap but over two different entries? What happens if they overlap but the start and end are the same? You need to clearly define the requirements otherwise you're going to run into trouble down the road.

Whatever the case may be, the answer will probably be to join the table to itself to find overlapping entries. How you formulate that join will depend on what you need to happen in the scenarios above.