2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Andrew's PlanetPostgreSQL3 / Partitioning a large table without a long-running lock
Andrew Dunstan

Partitioning a large table without a long-running lock

July 21, 2020/8 Comments/in Andrew's PlanetPostgreSQL /by Andrew Dunstan

Let’s say you have an application that has a huge table and that needs to be available all the time. It’s got so big that managing it without partitioning it is getting increasingly difficult. But you can’t take the table offline to create a new partitioned version of the table, which would take a great deal of time since this is a huge table.

Here is a recipe for dealing with the problem. It won’t necessarily work for every situation, particularly tables with very heavy write loads, but it could work for many.

First let’s set up our sample table and populate it with some data, 10 million rows in this case:

create table orig_table
( id serial not null,
  data float default random()
);

create index orig_data_index on orig_table(data);
create index orig_id_index on orig_table(id);

insert into orig_table (id)
select nextval('orig_table_id_seq')
from generate_series(1,100000);

Now we’re going to set up the partitioning structure. In this case we’re going to use four ranges on the data field:

create table part_table
(like orig_table including defaults including indexes including constraints)
partition by range(data);

create table part_table_p1
partition of part_table
for values from (minvalue) to (0.25);

create table part_table_p2
partition of part_table
for values from (0.25) to (0.5);

create table part_table_p3
partition of part_table
for values from (0.5) to (0.75);

create table part_table_p4
partition of part_table
for values from (0.75) to (maxvalue);

We’re going to rename the original table and then create a view with that name which is a union of the rows in the new partitioned table and the old non-partitioned table. But before that, we’ll need a trigger function to handle all the insert, update and delete operations for the view.

create or replace function part_v_trigger()
returns trigger
language plpgsql
as
$TRIG$

begin
    IF TG_OP = 'INSERT'
    THEN
        INSERT INTO part_table VALUES(NEW.id, NEW.data);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
        DELETE FROM part_table WHERE id = OLD.id;
        DELETE FROM old_orig_table WHERE id = OLD.id;
        RETURN OLD;
    ELSE -- UPDATE
        DELETE FROM old_orig_table WHERE id = OLD.id;
        IF FOUND
        THEN
            INSERT INTO part_table VALUES(NEW.id, NEW.data);
        ELSE
            UPDATE part_table SET id = NEW.id, data = NEW.data
                WHERE id = OLD.id;
        END IF;
        RETURN NEW;
    END IF;
end

$TRIG$;

Then we can move to the transitional setup in one quick transaction. Since we won’t be adding new tuples to the old non-partitioned table any more, we disable autovacuum on it.

BEGIN;

    ALTER TABLE orig_table RENAME TO old_orig_table;

    ALTER TABLE old_orig_table SET(
      autovacuum_enabled = false, toast.autovacuum_enabled = false
   );

    CREATE VIEW orig_table AS
    SELECT id, data FROM old_orig_table
    UNION ALL
    SELECT id, data FROM part_table
    ;

    CREATE TRIGGER orig_table_part_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE on orig_table
    FOR EACH ROW
    EXECUTE FUNCTION part_v_trigger();

COMMIT;

Note that all inserts and updates are steered to the partitioned table even if the row being updated is from the old table. We’re going to use that fact to move all the old rows in batches. What we need is a looping program that selects a small number of old table rows to move and updates them so that they are moved. Here is the sample program I used – it’s written in Perl but should be pretty easy for most readers to follow even if not Perl-savvy.

#! /bin/perl

use strict;
use DBI;

my $move_rows = qq{
  WITH oldkeys AS
  (
    SELECT id
    FROM old_orig_table
    LIMIT 10000
  )
  UPDATE orig_table
  SET id = id
  WHERE ID IN (SELECT id FROM oldkeys)
};

my $dbh = DBI->connect("dbi:Pg:dbname=tpart;host=/tmp;port=5711",
    '','',{AutoCommit => 0, RaiseError => 1, PrintError => 0}
);

my $rows_done;
do
{
    $rows_done = $dbh->do($move_rows);
    $dbh->commit;
    if ($rows_done != 0) # it will be 0e0 which is 0 but true
    {
        sleep 2;
    }
} until $rows_done == 0 || ! $rows_done;
print "done\n";
$dbh->disconnect;

This program can be safely interrupted if necessary. There are other ways of writing it. If speed is an issue a slightly more complex piece of SQL can be used which avoids calling the trigger.

Once there are no more rows left in the original table, we can replace the view with the fully partitioned table. In a separate transaction (because it can take some time and it’s not critical) we finally drop the old non-partitioned table.

BEGIN;
    DROP VIEW orig_table CASCADE;
    DROP FUNCTION part_v_trigger();
    ALTER SEQUENCE orig_table_id_seq OWNED BY part_table.id;
    ALTER TABLE part_table RENAME TO orig_table;
COMMIT;

BEGIN;
    DROP TABLE old_orig_table;
COMMIT;

Our application should have remained fully functional and blissfully unaware of the changes we have been making under the hood while we were making them.

 

Tags: Partitioning, postgresql partioning
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
8 replies
  1. datawrangler
    datawrangler says:
    December 2, 2020 at 4:41 pm

    Hi

    Thank you for writing this up. Some cool tricks in here.

    I’d like to ask, why did you say that this may not work for write heavy work-loads.
    Has this got something to do with the triggers?

    Thanks!

    Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      December 2, 2020 at 7:21 pm

      Yeah, the trigger could impose a heavy performance penalty.

      Reply
  2. Filipe Roque
    Filipe Roque says:
    June 18, 2021 at 2:30 pm

    instead of using the Common table expression oldkeys and the update on orig_table I found this to better in perfomance:

    BEGIN;
    CREATE TEMPORARY TABLE oldkeys(id bigint);

    INSERT INTO oldkeys (id) SELECT id FROM old_orig_table LIMIT 10000;

    INSERT INTO part_table(id, data)
    SELECT id, data
    FROM public.old_orig_table
    WHERE ID IN (SELECT id FROM oldkeys);

    DELETE FROM old_orig_table WHERE ID IN (SELECT id FROM oldkeys);
    COMMIT;

    Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      June 20, 2021 at 1:53 pm

      Interesting, thanks for the info. You should probably create the temp table with ON COMMIT DROP.

      Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      June 20, 2021 at 1:58 pm

      Actually, the reason this works faster is probably not because of the temp table use but because it’s avoiding the trigger.

      Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      June 21, 2021 at 12:18 pm

      Here’s a version of the query that still uses CTEs but avoids use of the trigger:

      WITH olddata AS
      (
      SELECT *
      FROM old_orig_table
      LIMIT 10000
      ),
      delold AS
      (
      DELETE
      FROM old_orig_table
      WHERE ID IN (SELECT id FROM olddata)
      )
      INSERT INTO part_table
      SELECT *
      FROM olddata;

      Reply
  3. Niels Andersen
    Niels Andersen says:
    December 28, 2021 at 10:22 pm

    Shouldn’t the last section be:

    WITH olddata AS
    (
    SELECT *
    FROM old_orig_table
    LIMIT 10000
    ),
    delold AS
    (
    DELETE
    FROM old_orig_table
    WHERE ID IN (SELECT id FROM olddata)
    RETURNING id, data
    )
    INSERT INTO part_table
    SELECT *
    FROM delold;

    Reply
    • Andrew Dunstan
      Andrew Dunstan says:
      February 25, 2022 at 12:08 pm

      You could write it that way. I think either way will work.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
How to Monitor PostgreSQL 12 Performance with OmniDB – Part 2 Webinar: Being Committed – A Review of Transaction Control Statements...
Scroll to top
×