CODEWITHSUNDEEP
postgresql
Bikash Lama
Bikash Lama

Reputation: 187

Data inserting into Master Table after Partition

I am working on a database table on postgres database that is more than 1 TB and has around 2 billions of records. So, I decided to partition the table based on "timestamp" column.

-- Step 1. Create Partition Tables

CREATE TABLE bigtable_y2019 (
    CHECK (timestamp >= '2019-01-01' AND timestamp < '2020-01-01')
) INHERITS (bigtable);

CREATE TABLE bigtable_y2020 (
    CHECK (timestamp >= '2020-01-01' AND timestamp < '2021-01-01')
) INHERITS (bigtable);

-- Step 2. Create index on the key column (timestamp)

CREATE UNIQUE INDEX bigtable_y2019_pkey ON bigtable_y2019 USING btree (id);
CREATE INDEX bigtable_y2019_timestamp ON bigtable_y2019 (timestamp);

CREATE UNIQUE INDEX bigtable_y2020_pkey ON bigtable_y2020 USING btree (id);
CREATE INDEX bigtable_y2020_timestamp ON bigtable_y2020 (timestamp);

-- Step 3. Create Function

CREATE OR REPLACE FUNCTION bigtable_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.timestamp >= '2020-01-01' AND NEW.timestamp < '2021-01-01') THEN
        INSERT INTO bigtable_y2020 VALUES (NEW.*);
    ELSIF (NEW.timestamp >= '2019-01-01' AND NEW.timestamp < '2020-01-01') THEN
        INSERT INTO bigtable_y2019 VALUES (NEW.*);    
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the bigtable_insert_trigger() function!';
    END IF;
    -- My understanding was this should have prevented inserting data into master table
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- Step 4. Enable Trigger ON BEFORE INSERT EVENT and execute the function

CREATE TRIGGER insert_bigtable_trigger BEFORE INSERT ON bigtable FOR EACH ROW EXECUTE FUNCTION bigtable_insert_trigger();

-- Step 5. Set enable_partition_pruning and contstraint_exclusion to ON

SET enable_partition_pruning = ON;
SET constraint_exclusion = ON;

These above steps insert the record on not only child table but on Parent table as well which I am trying to avoid.

So, I tried to create another trigger for AFTER INSERT event to remove the parent table. It is not a best approach but my work around to see how it is working.

--Since, RETURN NULL in tripdetail_insert_trigger is not avoiding insertion on master table, I have created a workaround to remove that record from master table.

CREATE OR REPLACE FUNCTION bigtable_mastertable_record_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM ONLY bigtable WHERE id = NEW.id;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER delete_bigtable_mastertable_record_trigger AFTER INSERT ON bigtable FOR EACH ROW EXECUTE FUNCTION bigtable_mastertable_record_delete_trigger();

There is sync between parent table and child tables. If record gets inserted into child table so does in parent table and if record gets deleted in either of those, then record gets deleted in another as well.

But, I am trying to insert the new records into corresponding child table based on timestamp and eventually make the parenttable empty which it should be based on the working principles of table partitioning.

Upvotes: 1

Views: 3746

Answers (2)

Daniel P.
Daniel P.

Reputation: 369

When using the partitioning capabilities of PostgreSQL 11, the parent table will only be a definition and will NOT contain any entries. See the section Declarative Partitioning here https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

If you have an existing database following steps are needed:

  1. Rename old table

    ALTER TABLE bigtable RENAME TO bigtable_pre_partitioning;

  2. Create partitioned table (define structure, index, range key)

    CREATE TABLE bigtable ( id int not null, logdate date not null ) PARTITION BY RANGE (logdate);

  3. Create partition

    CREATE TABLE bigtable_2020_01 PARTITION OF bigtable FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');

  4. Create another partition

    CREATE TABLE bigtable_2020_02 PARTITION OF bigtable FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

  5. Attach old partition (this takes a very long time depending on how much data you have)

    ALTER TABLE bigtable ATTACH PARTITION bigtable_pre_partitioning FOR VALUES FROM (MINVALUE) TO ('2020-01-01');

  6. Now you can insert entries directly into the bigtable parent and they will land in the correct partition.

    INSERT INTO bigtable ...

Some things to note:

  • The primary key must contain the range key
  • If you create an index on the parent table -> new partition will inherit it
  • If you create a constraint on the parent table -> new partition will inherit it
  • You can create a default table where all entries land if you have no matching partition range

Hope this helps. The article linked above contains a section about best practices what is quite helpful.

Upvotes: 2

Vibhor Kumar
Vibhor Kumar

Reputation: 314

enable_partition_pruning was added in PostgreSQL 11. If you are using PostgreSQL 11, then why don't you use table partition capability in PostgreSQL 11 https://www.postgresql.org/docs/11/ddl-partitioning.html

Upvotes: 1

Related Questions