KingbaseES 普通表在线改为分区表案例

对大表进行分区，但避免长时间锁表

假设您有一个应用程序，该应用程序具有一个巨大的表，并且需要始终可用。它变得如此之大，以至于在不对其进行分区的情况下对其进行管理变得越来越困难。但是，您又不能使表脱机以修改为分区表。

这是处理问题的秘诀。它不一定适用于所有情况，特别是具有非常重的写入负载的表，但它可能适用于许多情况。

首先，让我们设置我们的示例表，并用一些数据填充它，在本例中为 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;

我们的应用程序应该保持完整的功能，并且没有意识到我们在进行更改。