How to delete the oldest partition?

mosheg

When you need to drop the oldest partition consider the following example:

CREATE TABLE public.my_table
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY DATE(order_date);

\set DEMO_ROWS 10

INSERT INTO public.my_table
WITH myrows AS (SELECT
row_number() over()    AS order_no,
NOW() - RANDOMINT(30) AS order_date
FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
SELECT NOW() + :DEMO_ROWS - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
SELECT order_no, order_date, 'something', order_date AS ship_date
FROM myrows
ORDER BY order_no;
COMMIT;

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;

-- Option A:
\set OLDEST `vsql -XAtc "SELECT max(order_date)::date from public.my_table;"`
\set OLDEST '''':OLDEST''''
SELECT DROP_PARTITIONS ('my_table', :OLDEST, :OLDEST);

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;

-- Option B:
WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;

CREATE OR REPLACE FUNCTION my_drop(d2d DATE) RETURN VARCHAR(100)
   AS BEGIN
     RETURN 'SELECT DROP_PARTITIONS (''my_table'', ''' || d2d || ''' , ''' || d2d || ''');';
   END;

\t
\o | vsql
SELECT my_drop((SELECT max(order_date)::DATE from public.my_table));
\o
\t

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;

Here is the run time output:

CREATE TABLE public.my_table
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY DATE(order_date);
CREATE TABLE
INSERT INTO public.my_table
WITH myrows AS (SELECT
row_number() over()    AS order_no,
NOW() - RANDOMINT(30) AS order_date
FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
SELECT NOW() + 10 - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
SELECT order_no, order_date, 'something', order_date AS ship_date
FROM myrows
ORDER BY order_no;
 OUTPUT
--------
     10
(1 row)

COMMIT;
COMMIT

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;
    date    |     Status
------------+----------------
 2020-05-01 | Will keep
 2020-05-02 | Will keep
 2020-05-06 | Will keep
 2020-05-07 | Will keep
 2020-05-07 | Will keep
 2020-05-10 | Will keep
 2020-05-13 | Will keep
 2020-05-20 | Will keep
 2020-05-22 | Will keep
 2020-05-24 | Will be droped
(10 rows)

SELECT DROP_PARTITIONS ('my_table', '2020-05-24', '2020-05-24');
  DROP_PARTITIONS
-------------------
 Partition dropped
(1 row)

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;
    DATE
------------
 2020-05-01
 2020-05-02
 2020-05-06
 2020-05-07
 2020-05-07
 2020-05-10
 2020-05-13
 2020-05-20
 2020-05-22
(9 rows)

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;
    date    |     Status
------------+----------------
 2020-05-01 | Will keep
 2020-05-02 | Will keep
 2020-05-06 | Will keep
 2020-05-07 | Will keep
 2020-05-07 | Will keep
 2020-05-10 | Will keep
 2020-05-13 | Will keep
 2020-05-20 | Will keep
 2020-05-22 | Will be droped
(9 rows)

CREATE OR REPLACE FUNCTION my_drop(d2d DATE) RETURN VARCHAR(100)
   AS BEGIN
     RETURN 'SELECT DROP_PARTITIONS (''my_table'', ''' || d2d || ''' , ''' || d2d || ''');';
   END;
CREATE FUNCTION

Showing only tuples.
SELECT my_drop((SELECT max(order_date)::DATE from public.my_table));
  DROP_PARTITIONS
-------------------
 Partition dropped
(1 row)

Tuples only is off.
SELECT DATE(order_date) FROM public.my_table ORDER BY 1;
    DATE
------------
 2020-05-01
 2020-05-02
 2020-05-06
 2020-05-07
 2020-05-07
 2020-05-10
 2020-05-13
 2020-05-20
(8 rows)

sahil_kumar

That's really helpful! Thanks