PostgreSQL :如何为具有大量插入和删除的时间序列设计数据库模式

Question

我需要根据日期和时间插入数据(分钟粒度就足够了( 但我也会删除正在处理的所有行

所以你猜对了，有些事情正在执行，但不是现在，所以我想你可以说延迟的行动。

expiry | action
2020-01-30T10:45 | Action1
2020-01-30T10:45 | Action2
2020-02-05T00:00 | Action3
2020-02-05T00:00 | Action4

我将在一天中的某些日期和时间有数百万行，而在其他日期和时间则没有。 我会做这样的事情

SELECT expiry,action FROM <table> WHERE expiry > '2020-01-30T00:00' AND expiry < '2020-01-30T23:59'

然后最终我会做一些类似的事情

DELETE FROM <table> WHERE expiry > '2020-01-30T00:00' AND expiry < '2020-01-30T23:59'

甚至

DELETE FROM <table> WHERE expiry < '2020-01-30T23:59'

在Postgres中建模的最佳方法是什么？ 我需要注意什么？

Answer 1

看看你是否可以使用Timescaledb，一些基准和信息在这里：

链接

还有其他几种选择可以在 PG 中完成此操作，但以防万一，如果 PG 不是您正在查看的唯一内容。

编辑

---

创建一个函数来创建分区和索引

下降函数 test_partition_creation((;

CREATE OR REPLACE FUNCTION test_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
create_query text;
index_query text;
BEGIN
FOR create_query, index_query IN SELECT
'create table test_'
|| TO_CHAR( d, 'YYYY_MM' )
|| ' ( check( time >= date '''
|| TO_CHAR( d, 'YYYY-MM-DD' )
|| ''' and time < date '''
|| TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
|| ''' ) ) inherits ( test );',
'create index test_'
|| TO_CHAR( d, 'YYYY_MM' )
|| '_time on test_' 
|| TO_CHAR( d, 'YYYY_MM' )
|| ' ( time );'
FROM generate_series( $1, $2, '1 month' ) AS d
LOOP
EXECUTE create_query;
EXECUTE index_query;
END LOOP;
END;
$$
language plpgsql;

给定时间段的分区创建

SELECT test_partition_creation( '2010-01-01', '2012-01-01' ) ;

触发器函数创建

CREATE OR REPLACE FUNCTION test_partition_function()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'insert into test_'
|| to_char( NEW.TIME, 'YYYY_MM' )
|| ' values ( $1, $2 )' USING NEW.id, NEW.TIME ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

触发激活

CREATE TRIGGER test_partition_trigger
BEFORE INSERT
ON test
FOR each ROW
EXECUTE PROCEDURE test_partition_function() ;

有关更多详细信息，请参阅此链接。