Then we can move to the transitional setup in one quick transaction.
POSTGRESQL ALTER TABLE UPDATE
UPDATE part_table SET id = NEW.id, data = NEW.data INSERT INTO part_table VALUES(NEW.id, NEW.data) ĭELETE FROM part_table WHERE id = OLD.id ĭELETE FROM old_orig_table WHERE id = OLD.id create or replace function part_v_trigger() But before that, we’ll need a trigger function to handle all the insert, update and delete operations for the view. 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. (like orig_table including defaults including indexes including constraints) In this case we’re going to use four ranges on the data field: create table part_table Now we’re going to set up the partitioning structure. It won’t necessarily work for every situation, particularly tables with very heavy write loads, but it could work for many.įirst let’s set up our sample table and populate it with some data, 10 million rows in this case: create table orig_tableĬreate index orig_data_index on orig_table(data) Ĭreate index orig_id_index on orig_table(id) Here is a recipe for dealing with the problem. 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. It’s got so big that managing it without partitioning it is getting increasingly difficult. Let’s say you have an application that has a huge table and that needs to be available all the time.
PostgreSQL 9 Administration Cookbook – 3rd Edition.
PostgreSQL High Availability Cookbook – 2nd Edition.
0 kommentar(er)
