postgres inheritence partitioning - ghdrako/doc_snipets GitHub Wiki

The table inheritance for partitioning tables is the old way of table partition, before version 10, where you had to programmatically partition data with functions and triggers. However, this approach requires more maintenance and development effort than declarative partitions. The partition data is not automatically distributed to appropriate partitions after update of partition data.

With the inheritance method, you have to insert data into partitions by triggers. This method is for downward compatibility. This method does not update the partitions for the data movement by the upgrade command.

drop table if exists weather_data cascade;
CREATE TABLE weather_data (
    location_id   int not null,
    entrydate     date not null,
    avg_temp      int,
    rainfall      int
);
-- Create table for each day
     DO $$
DECLARE
    day DATE;
BEGIN
     FOR day IN SELECT generate_series('2024-06-01'::DATE, 
'2024-06-7'::DATE, '1 day'::INTERVAL)::DATE
    LOOP
         EXECUTE 'CREATE TABLE weather_data_' || to_char(day, 
'YYYYMMDD') || ' () INHERITS (weather_data)';
    END LOOP;
END $$;

Partition Insert Procedure

CREATE OR REPLACE FUNCTION weather_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.entrydate >= '2024-06-01'AND NEW.entrydate < 
'2024-06-02') THEN
        INSERT INTO weather_data_20240601 VALUES (NEW.*);
       ELSIF (NEW.entrydate >= '2024-06-02' AND NEW.entrydate< 
'2024-06-03') THEN
        INSERT INTO weather_data_20240602 VALUES (NEW.*);
      ELSIF (NEW.entrydate >= '2024-06-03' AND NEW.entrydate< 
'2024-06-04') THEN
        INSERT INTO weather_data_20240603 VALUES (NEW.*);
     ELSIF (NEW.entrydate >= '2024-06-04' AND NEW.entrydate< 
'2024-06-05') THEN
        INSERT INTO weather_data_20240604 VALUES (NEW.*);
ELSIF (NEW.entrydate >= '2024-06-05' AND NEW.entrydate< 
'2024-06-06') THEN
        INSERT INTO weather_data_20240605 VALUES (NEW.*);
     ELSIF (NEW.entrydate >= '2024-06-06' AND NEW.entrydate< 
'2024-06-07') THEN
        INSERT INTO weather_data_20240606 VALUES (NEW.*);
    ELSE
         RAISE EXCEPTION 'Date out of range for weather_insert_
trigger() function!';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER insert_weather_data_trigger
BEFORE INSERT ON weather_data
FOR EACH ROW EXECUTE FUNCTION weather_insert_trigger();

Constraint exclusion

• https://postgresqlco.nf/doc/en/param/constraint_exclusion/
• https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION

Dla nowoczesnego partycjonowania(PG12+) parametr nieprzydatny

To parametr planisty zapytań w PostgreSQL, który mówi optymalizatorowi, czy może pominąć niektóre tabele/podtabele przy wykonywaniu zapytania, na podstawie ograniczeń (constraints).

CREATE TABLE faktury (
  id serial,
  data date,
  amount int
);

CREATE TABLE faktury_2023_01 (
  CHECK (data >= '2023-01-01' AND data < '2023-02-01')
) INHERITS (faktury);

CREATE TABLE faktury_2023_01 (
  CHECK (data >= '2023-02-01' AND data < '2023-03-01')
) INHERITS (faktury);

SELECT * FROM faktury WHERE data = '2023-01-10';

Aby planista(optymalizator) mogl odrzucic niepotrzebne partycje i szukac tylko w faktury_2023_01 potrzebne jest ustawienie constraint exclusion

Wartości constraint_exclusion

• off – nie pomija niczego (wszystkie partycje brane pod uwagę),
• on – pomija na podstawie constraintów w zapytaniach niezależnie od typu zapytania (SELECT, INSERT, UPDATE),
• partition – domyślne od PostgreSQL 12; pomija tylko przy klasycznym partycjonowaniu (PARTITION BY).

Set parameter constraint exclusion to on or partition. If WHERE clause contain partition key optimizer using check constraint to remove partition from scaning

select * from pg_setting where name = 'constraint_exclusion';