citusdata / citusdata/citus

Push down modifying CTEs in distributed INSERT ... SELECT

Open
#1,741 5 comments 0 reactions 0 assignees View on GitHub
performance
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

To do rollups using INSERT...SELECT, you need to a way of tracking which data has already been processed to avoid rolling it up twice, which is difficult given current limitations of both distributed INSERT...SELECT and INSERT...SELECT via the coordinator.

A common pattern is to the delete the raw data that you are adding to the rollup. That way, the second time you run the command, it will only process new data.

We would want something like the following to be pushed down:

```
CREATE TABLE data (key int);
CREATE TABLE rollup (key int primary key, count int);

SELECT create_distributed_table('data', 'key');
SELECT create_distributed_table('rollup', 'key');

WITH deleted_rows AS (DELETE FROM data RETURNING *)
INSERT INTO rollup (key, count)
SELECT key, count(*) FROM deleted_rows GROUP BY key
ON CONFLICT (key) DO UPDATE SET count = rollup.count + EXCLUDED.count;
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.