0

BACKGROUND

I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a limit on the early date picker. I could impose an artificial, arbitrary value, but I'd rather have a quick query that fetches the earliest found partition table, possibly the latest as well, without knowing the date ranges of those partitions so we can get the earliest found date.

Partitions are created using a function that I believe is called in a cron job every month that creates a month+1 partition. They are named after timestamps, but only use year and month.

PROBLEM

Is there something that allows me do so this (pseudocode):

select min(date_column) from parted_table.earliest_partition;

For example, if a monthly table partition is created every new month starting from 2018, I would expect earliest_partition to be table_2018_01

select min(date_column) from table_2018_01

How would I get table_2018_01 without knowing that 2018_01 was the earliest created partition?

Disclosure: I'm working with someone else's code so I am not fully familiar with the system.

postgres 9.4

tl;dr I guess what I'm looking for is a query that searches table names and returns the earliest one. Should be doable through some built in pg_ table?

  • It occured to me this may not be dba-specific, so apologies if this is on the wrong stack site. – NuclearPeon Jul 9 at 20:13
  • 2
    Define "earliest". The one with the earliest values in date_column? The one create first? Always disclose your version of Postgres. And an actual table definition would disambiguate which partitioning method is used. – Erwin Brandstetter Jul 10 at 0:06
  • @ErwinBrandstetter earliest meaning the oldest table partition. I could search for the oldest date, but that would scan through every partition. I want immediately to access the oldest partition and scan through just that table. – NuclearPeon Jul 10 at 18:27
  • Note that generally table names have little relation (outside the designer's mind) to table contents. Nothing, apart from the partition definition, prevents you from storing data from July 2019 in a table named table_2018_01. So, do you use table names as the criteria, or actual partition definitions? – mustaccio Jul 10 at 18:33
0

First off, many thanks to those who commented and helped me get to this answer. I was not as detailed as I should have been in posting the question. I believe this is the answer I seek:

select table_name from information_schema.tables where table_name like 'table_%' order by table_name limit 1;

It should return the earliest table partition name given the naming conventions we use on them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.