对大表进行分区，但避免长时间锁表
假设您有一个应用程序，该应用程序具有一个巨大的表，并且需要始终可用。它变得如此之大，以至于在不对其进行分区的情况下对其进行管理变得越来越困难。但是，您又不能使表脱机以修改为分区表。
这是处理问题的秘诀。它不一定适用于所有情况，特别是具有非常重的写入负载的表，但它可能适用于许多情况。
首先，让我们设置我们的示例表，并用一些数据填充它，在本例中为 1000 万行：
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);
现在，我们将设置分区结构。在本例中，我们将在数据字段上使用四个范围：
create table part_table
(like orig_table including defaults including indexes including constraints)
partition by range(data)
(
partition part_1 values LESS THAN (0.25),
partition part_2 values LESS THAN (0.5),
partition part_3 values LESS THAN (0.75),
partition part_table values LESS THAN (maxvalue)
);
我们将重命名原始表，然后使用该名称创建一个视图，该名称是新分区表和旧的非分区表中行的并集，需要一个触发器来处理视图的所有插入、更新和删除操作。然后，我们可以在一个快速事务中转到过渡设置。由于我们不会再向旧的非分区表添加新元组，因此我们禁用了它的AUTOVACUUM。
\set SQLTERM /
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 or replace TRIGGER orig_table_part_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE
on orig_table
FOR EACH ROW
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;
/
COMMIT;
/
\set SQLTERM ;
请注意，即使正在更新的行来自旧表，所有插入和更新都将定向到分区表。我们将利用这一事实批量移动所有旧行。我们需要的是一个循环程序，它选择少量的旧表行来移动并更新它们，以便移动它们。这是我使用的示例程序 - 它是用Perl编写的，但对于大多数读者来说应该很容易理解，即使不是Perl精通。
--多次执行迁移语句
WITH oldkeys AS
(
SELECT id
FROM old_orig_table
LIMIT 10000
)
UPDATE orig_table
SET id = id
WHERE ID IN (SELECT id FROM oldkeys);
select 'orig_table' as tab, count(*) as cnt from orig_table
union all
SELECT 'old_orig_table' as tab, count(*) as cnt FROM old_orig_table
union all
SELECT 'part_table' as tab, count(*) as cnt FROM part_table;
tab | cnt
----------------+--------
orig_table | 100000
old_orig_table | 80000
part_table | 20000
(3 行记录)
如有必要，可以安全地中断此程序。还有其他书写方法。
一旦原始表中不再有数据行，我们就可以用完全分区的表替换视图。在独立的事务中（因为它可能需要一些时间（锁等待），并且并不重要），我们最终删除了旧的非分区表。
BEGIN;
DROP VIEW orig_table CASCADE;
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;
我们的应用程序应该保持完整的功能，并且没有意识到我们在进行更改。
