Postgresql – Postgres 11 – Range Partitioning on a Date Column with NULL values

nullpartitioningpostgresql

We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we have a requested date column we do all of our querying by but the only records which have a value for this are the parent records.

Since all of our queries are filtering on the parent results initially and use this date, we want to paritition the table on this date range but understand this isn't possible due to there being null values on the records for the child records.

Aside from creating a "dummy" value (some arbitrary date that would never be caught in our filter) on each and every child record in the database, what other option do we have? Is it not possible to somehow create a separate partition for all of the null values that we never query for anyway?

Best Answer

PostgreSQL 11 allows you create a default partition for range-partitioned tables, which will receive the NULL values.

You can add a check constraint demanding the column be NULL, if you want to prevent other rows not covered by existing partitions from going into that default table.

create table for_nulls PARTITION OF parent_table (check (date_requested is null)) DEFAULT;

Related Solutions

Sql-server – Why is replication attempting to distribute NULL values into Identity columns

After much trial and error we think the answer was and has always been triggers! Despite being disabled in the subscriber they kept on being re-enabled which was causing a second insert command to run for affected replicated commands.

Basically check your triggers!

Sql-server – Quickly change NULL column to NOT NULL

@ypercube's answer does manage this partially as a metadata only change.

Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.

Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-M lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.

Once the Sch-M lock is acquired the operation should be pretty quick however.

One problem with this is that even if you know the column in fact has no NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.

CREATE TABLE T (X INT NULL)

INSERT INTO T 
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL 
    CHECK (X IS NOT NULL) ; 

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

Compare this with the simpler

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.

A possible half way house might be to add the check constraint WITH CHECK. This will be slower than WITH NOCHECK as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.

Best Answer

Related Solutions

Sql-server – Why is replication attempting to distribute NULL values into Identity columns

Sql-server – Quickly change NULL column to NOT NULL

Related Question