PostgreSQL – Fetch Partitions Where Values Are Present

partitioningpostgresql

I have table partitioned by range of dates (monthly).

Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the partitions.

What would be the query to do that?

Say, I have:

partitions: calendars_y2021_m01, calendars_y2021_m02, calendars_y2021_m03
list of dates: 2021-01-01, 2021-02-01, 2021-02-02

I want to:

select partitions from calendars
where dates in (2021-01-01, 2021-02-01, 2021-02-02)

That should give me all the rows from partitions: calendars_y2021_m01, calendars_y2021_m02

The only solution I got myself is to identify the target partitions and fetch them one-by-one with python loop, but that is slow.

I am using RANGE partitioning. Selecting from calendars will scan the partitions for the specific dates. I just want to get all the data from the selected partitions into memory to avoid that.

If I select * from calendars where dates in (list of dates) PostgreSQL will first (1) define target partitions, then (2) SeqScan the partitions for the values in list of dates. I want to skip the second operation and get the entire partitions.

Once I have the partitions, I will process them in memory with python pandas. That is way faster, then seq scan. Also, I am just biased towards pythonic tools.

Best Answer

SELECT DISTINCT
       format(
          'calendars_y%s_m%s',
          lpad(CAST(extract (year FROM q) AS text), 4, '0'),
          lpad(CAST(extract (month FROM q) AS text), 2, '0'))
FROM (VALUES (DATE '2021-01-01'),
             (DATE '2021-02-01'),
             (DATE '2021-02-02')) AS q(q);

       format        
---------------------
 calendars_y2021_m01
 calendars_y2021_m02
(2 rows)

But I seriously doubt that it is a good idea to do that on foot. You will be better off letting the PostgreSQL optimizer figure out which partitions to scan.

Related Solutions

Postgresql – postgres fetch record from partitions without the check constraints

A few details are still not clear, but let's see, what we can do now. First, having about 100M rows and 231 partitions sounds not that good. The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive. I think it is quite possible that yearly partitions would be enough. Alternatively, if you really want to fetch a whole month at a time, create monthly partitions.

Now to the actual problem.

It is not quite clear to me why you have rows in the parent table. The usual way of partitioning is that the parent is empty, and every row is redirected to one of the children.

At the same time, is you have an index on posted_at of the parent table (as you have it on the children), finding rows in the parent based on the timestamp is easy.

On the other hand, while I'm not sure which column shared_parent_id refers to, you can define an index on it, too - looking rows up based on this will be easy, too.

The only thing still has to be added is tell your query to look for parents in the parent table only. Let's have a look at a possible query:

WITH child_messages AS (
    SELECT shared_parent_id, {other interesting columns}
      FROM messages
     WHERE posted_at {matches your needs}
)
SELECT *
  FROM child_messages

UNION ALL

SELECT shared_parent_id, {other interesting columns}
  FROM ONLY messages -- this way it does not go to the children
 WHERE {unclear column} IN (SELECT shared_parent_id FROM child_messages);

The WITH query may pick up rows from the parent, too - this you may or may not want, adjust the query accordingly.

Furthermore, the performance might not be ideal, in this case there is room for tweaking the query (eg. a JOIN instead of the IN(), pushing the query in the WITH clause into a (sub)query and so on).

And a final notice: varchar(255) is usually a sign of a value of unknown-before length - if you really want to constrain it, you may want to choose a meaningful limit. Otherwise, an unlimited varchar (or text) has a slight performance advantage in PostgreSQL over the limited ones. Furthermore, from your example it seems that shared_parent_id is a number (integer) - use the best fitting type.

Sql-server – Table partitioning and future values

Empty partitions have negligible effect on query performance, so having up to 12 empty partitions, one per month, should not be a problem.

Having said that, you could simply automate the creation of partitions, the way I detail in this answer. That means you'd only ever have a single empty partition, assuaging any fears about performance impacts from too many empty partitions.

Table partitioning is not a panacea for increasing performance and can result in lower performance than a non-partitioned table. The primary use-case for partitioning is to enable the easy swapping out of old data, and swapping in of new data, using the ALTER TABLE ... SWITCH syntax. This answer shows the advantages of using ALTER TABLE ... SWITCH to ease data management.

In order to get some performance improvement via partitioning you need to use the partitioning key in queries against the partitioned table. Queries that don't use the partitioning key in the WHERE clause or in a JOIN clause must inspect all partitions for rows that match the selection criteria.

Best Answer

Related Solutions

Postgresql – postgres fetch record from partitions without the check constraints

Sql-server – Table partitioning and future values

Related Question