How can I (quickly) test if a postgres partition has any rows in it?
I have a partitioned postgres table ‘TABLE_A’, partitioned by date-range. The name of each individual partition indicates the date-range i.e. TABLE_A_20220101 (1st Jan this year) TABLE_A_20220102 (2nd Jan 2022)
The table includes many years of data, so it includes several thousand individual partitions, each partition contains many millions of rows.
Is there a quick way of testing if a partition has any data in it? There are several solutions I’ve found, but they all involve
count(*) and all take ages.
Please note – I’m NOT trying to accurately determine the row-count, just determine if each partition has any rows in it.
You can use an
select exists (select * from partition_name limit 1)
That will return
partition_name contains at least one row
Answered By – a_horse_with_no_name
Answer Checked By – Cary Denson (BugsFixing Admin)