sql - Postgres 按周划分

Question

我可以想象按日期(尤其是日志)对表进行分区是一种广泛使用的方法，但我找不到适合我的问题的答案。

我想按周创建一个表分区(记录的数量太大以至于不能按月创建)。每周一次的原因是我需要一个算法的数据，该算法将在流程中查找日期。

我的问题是我希望它创建考虑周的分区并使用我必须手动创建的“典型”方法。像这样。

    CREATE TABLE measurement_y2013w01 (
CHECK ( logdate >= DATE '2013-01-07' AND logdate < DATE '2013-01-14' ) 
    ) INHERITS (measurement);

    CREATE TABLE measurement_y2006w02 (
CHECK ( logdate >= DATE '2013-01-14' AND logdate < DATE '2013-01-21' ) 
    ) INHERITS (measurement);

... 

但我希望它自动生成。我不想每周都创建一个分区。

我的命名规则是分区命名为 yYYYYwWW 或开始数据为 dYYYYMMDD。

我想在插入时使用这样的东西检查分区:

 SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child,
    child.relname       AS child_schema
FROM pg_inherits
    JOIN pg_class parent        ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child         ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace

如果分区不存在，则在插入之前创建它，但考虑到插入的记录数量，这种接缝效率很低。

我的另一个选择是每周运行一个外部进程来创建这个分区，但我试图避免这种情况。

是否有我缺少的更有效的解决方案，例如用于月度检查？

Answer 1

好的，让我们自己创建一个函数来处理它!

CREATE OR REPLACE FUNCTION create_partition_measurement( DATE, DATE )
returns void AS $$
DECLARE
create_query text;
BEGIN
    FOR create_query IN SELECT

        'CREATE TABLE measurement_' || TO_CHAR( d, 'YYYY_WW' ) || ' (
        CHECK ( EXTRACT(YEAR FROM logdate) = EXTRACT(YEAR FROM TIMESTAMP ''' || d || ''') AND EXTRACT(WEEK FROM logdate) = EXTRACT(WEEK FROM TIMESTAMP ''' || d || ''') )
        ) INHERITS (measurement);'

        FROM generate_series( $1, $2, '1 week' ) AS d LOOP

        EXECUTE create_query;

    END LOOP;
END;
$$
language plpgsql;

有了这个你现在可以调用类似的东西了

SELECT create_partition_measurement ('2015/02/08','2015/03/01'); 

并创建您的分区。自动化的第一步，完成。

我使用以下测试表在自己的数据库中测试了所有这些:

CREATE TABLE measurement (id INT NOT NULL PRIMARY KEY, id_user INT NOT NULL, logdate TIMESTAMP NOT NULL);

使用上述函数创建分区后，我能够:

• 将数据插入正确的分区；
• 尝试将一周的数据插入另一周的分区时出错；
• 自动创建分区数周，并且；
• 如果我尝试在一个已经存在的一周内创建一个分区，则会出现错误。

这应该足够了 =)

现在，关于自动化创建过程。我使用一个简单的 cron 脚本每月为我调用此函数，并使用几个监控脚本来确保一切正常工作。cron 看起来像这样:

0 0 1 * * /var/lib/postgresql/create_partitions.sh

脚本将使用当前日期和当前日期 + 1 个月运行命令。它看起来像这样:

startDate=`date "+%Y/%m/%d"`
endDate=`date -u -d "+1 month -$(date +%d) days" "+%Y/%m/%d"
psql -U "$dbUser" -w -c "SELECT create_partition_measurement('$startDate','$endDate');"

如果您需要在表中包含索引、PK、FK，或帮助使用触发器来完成所有这些工作，请告诉我。