PostgreSQL多层分区

Question

我已经使用PostgreSQL数据库分区了一段时间。我的数据库已经增长了很多，并且使用分区做得很好。不幸的是，我现在似乎遇到了速度的另一个障碍，正在尝试找出更多加速数据库的方法。

我的基本设置如下： 我有一个名为database_data的主表，所有分区都继承自该表。我选择每个月一个分区，并将它们命名为：database_data_YYYY_MM，这样做很好。

通过分析我的数据使用情况，我注意到，我大多数情况下只对表进行插入操作，只有一些更新操作。然而，更新也仅发生在某种类型的行上：我有一个名为channel_id的列（指向另一个表的FK）。我更新的行始终具有一组可能有50个ID的channel_id，因此这将是区分从未更新的行和潜在更新的行的绝佳方式。

我想，如果我使用分区来每个月拥有一个“仅插入”数据表和一个“潜在更新”数据表，那么我的设置将进一步加快速度，因为我的更新每次只需要检查较少的行。

当然，我可以使用我现在使用的“简单”分区，为每个月添加另一个名为database_data_YYYY_MM_update的表，并将特殊约束条件添加到该表和database_data_YYYY_MM表中，以便查询规划程序区分这两个表。

然而，我正在考虑，我有时会对给定月份的所有数据执行操作，无论是否可更新。在这种情况下，我可以JOIN这两个表，但是对于这样的查询可能有更简单的方法。

现在是我的真正问题：

PostgreSQL是否支持“两层”分区？我的意思是，与其为每个月份都有两个继承自主表的表，我将直接从主表继承一个名为database_data_YYYY_MM的表，然后再从该表继承两个表，一个用于仅插入的数据（例如database_data_YYYY_MM_insert），另一个用于可更新的数据（例如database_data_YYYY_MM_update）。

这样做会加速查询规划吗？如果中间表被消除，我猜测同时消除两个表会更快。

显然，这里的优点是，我可以通过简单地使用table database_data_YYYY_MM来操作某个月份的所有数据，并且对于我的更新，直接使用子表即可。

还有一些我没有想到的缺点吗？

谢谢您的想法。

---

编辑1：

我认为一个模式不是回答我的问题所必需的，但如果它有助于理解，我将提供一个示例模式：

CREATE TABLE database_data (
    id bigint PRIMARY KEY,
    channel_id bigint,    -- This is a FK to another table
    timestamp TIMESTAMP WITH TIME ZONE,
    value DOUBLE PRECISION
)

我在database_data表上有一个触发器，可以按需生成分区：

CREATE OR REPLACE FUNCTION function_insert_database_data() RETURNS TRIGGER AS $BODY$
DECLARE
    thistablename TEXT;
    thisyear INTEGER;
    thismonth INTEGER;
    nextmonth INTEGER;
    nextyear INTEGER;
BEGIN
    -- determine year and month of timestamp
    thismonth = extract(month from NEW.timestamp AT TIME ZONE 'UTC');
    thisyear = extract(year from NEW.timestamp AT TIME ZONE 'UTC');

    -- determine next month for timespan in check constraint
    nextyear = thisyear;
    nextmonth = thismonth + 1;
    if (nextmonth >= 13) THEN
        nextmonth = nextmonth - 12;
        nextyear = nextyear +1;
    END IF;

    -- Assemble the tablename

    thistablename = 'database_datanew_' || thisyear || '_' || thismonth;

    -- We are looping until it's successfull to catch the case when another connection simultaneously creates the table
    -- if that would be the case, we can retry inserting the data
    LOOP
        -- try to insert into table
        BEGIN
            EXECUTE 'INSERT INTO ' || quote_ident(thistablename) || ' SELECT ($1).*' USING NEW;
            -- Return NEW inserts the data into the main table allowing insert statements to return the values like "INSERT INTO ... RETURNING *"
            -- This requires us to use another trigger to delete the data again afterwards
            RETURN NEW;
        -- If the table does not exist, create it
        EXCEPTION
            WHEN UNDEFINED_TABLE THEN
                BEGIN
                    -- Create table with check constraint on timestamp
                    EXECUTE 'CREATE TABLE ' || thistablename || ' (CHECK ( timestamp >= TIMESTAMP WITH TIME ZONE '''|| thisyear || '-'|| thismonth ||'-01 00:00:00+00''
                        AND timestamp < TIMESTAMP WITH TIME ZONE '''|| nextyear || '-'|| nextmonth ||'-01 00:00:00+00'' ), PRIMARY KEY (id)
                        ) INHERITS (database_data)';
                    -- Add any trigger and indices to the table you might need
                    -- Insert the new data into the new table
                    EXECUTE 'INSERT INTO ' || quote_ident(thistablename) || ' SELECT ($1).*' USING NEW;
                    RETURN NEW;
                EXCEPTION WHEN DUPLICATE_TABLE THEN
                    -- another thread seems to have created the table already. Simply loop again.
                END;
            -- Don't insert anything on other errors
            WHEN OTHERS THEN
                RETURN NULL;
        END;
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_database_data
BEFORE INSERT ON database_data
FOR EACH ROW EXECUTE PROCEDURE function_insert_database_data();

就样本数据而言：假设我们只有两个通道：1和2。1是仅插入数据的，2是可更新的。

我的两层方法可能如下：

主表：

CREATE TABLE database_data (
    id bigint PRIMARY KEY,
    channel_id bigint,    -- This is a FK to another table
    timestamp TIMESTAMP WITH TIME ZONE,
    value DOUBLE PRECISION
)

中间表：

CREATE TABLE database_data_2015_11 (
    (CHECK ( timestamp >= TIMESTAMP WITH TIME ZONE '2015-11-01 00:00:00+00' AND timestamp < TIMESTAMP WITH TIME ZONE '2015-12-01 00:00:00+00)),
    PRIMARY KEY (id)
) INHERITS(database_data);

分区：

CREATE TABLE database_data_2015_11_insert (
    (CHECK (channel_id = 1)),
    PRIMARY KEY (id)
) INHERITS(database_data_2015_11);

CREATE TABLE database_data_2015_11_update (
    (CHECK (channel_id = 2)),
    PRIMARY KEY (id)
) INHERITS(database_data_2015_11);

当然，接下来我需要在中间表上再创建一个触发器，以便按需创建子表。

- Tim

---

没有代码、模式和示例数据，这个话题就离题了。 - Clodoaldo Neto

Answer 1

这是一个聪明的想法，但可悲的是它似乎不起作用。如果我有一个父表，有1000个直接子项，并运行一个应该只从一个子项中提取的SELECT，那么explain analyze会给我大约16毫秒的计划时间。另一方面，如果我只有10个直接子项，它们都有10个子项，而这些子项都有10个子项，我得到的查询计划时间约为29毫秒。我很惊讶——我真的以为它会起作用！

这里是一些我用来生成我的表的Ruby代码：

0.upto(999) do |i|
  if i % 100 == 0
    min_group_id = i
    max_group_id = min_group_id + 100
    puts "CREATE TABLE datapoints_#{i}c (check (group_id > #{min_group_id} and group_id <= #{max_group_id})) inherits (datapoints);"
  end
  if i % 10 == 0
    min_group_id = i
    max_group_id = min_group_id + 10
    puts "CREATE TABLE datapoints_#{i}x (check (group_id > #{min_group_id} and group_id <= #{max_group_id})) inherits (datapoints_#{i / 100 * 100}c);"
  end
  puts "CREATE TABLE datapoints_#{i + 1} (check (group_id = #{i + 1})) inherits (datapoints_#{i / 10 * 10}x);"
end

- Paul A Jungwirth