sql - Postgres按周划分

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

我想按周创建一个表分区(记录数量大到每月都可以)。之所以要每周一次，是因为我需要算法的数据，该算法将在过程中查找日期。

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

    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来命名分区或开始datadYYYYMMDD。

我想在插入时使用类似这样的方法来检查分区:

 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

如果分区不存在，则在插入之前创建分区，但是考虑到插入的记录数，这将导致效率低下。

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

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

最佳答案

好的，让我们为自己创建一个函数来处理该问题!

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或帮助触发器使所有这些工作正常进行，请告诉我。

关于sql - Postgres按周划分，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16049396/