postgresql 同步SQL数据库中的表与查询结果的最佳方法是什么？

doinxwow 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(158)

我经常发现自己处于一种情况，我想用一些复杂查询的结果来更新数据库表。
通常，我们有一个分布在许多表中的数据负载，所有这些数据都经过了很好的规范化，这样就不会有任何重复。但是复杂查询的结果经常需要，所以我们将结果保存到一个单独的（非规范化的）表中（我称之为results）。这使我们能够快速访问results中的数据，但代价是它总是稍微过时，我们必须定期同步。
我的问题是：更新非规范化的results表的最佳方法是什么？
我已经尝试了以下方法，但两者都有自己的问题。

方法1

删除结果中的所有内容，然后插入长查询中的所有内容。

DELETE FROM results;
INSERT INTO results (SELECT ... long complicated query here)

这方面的问题：

我们暂时完全无法访问results
插入步骤给数据库带来了很大的压力，需要大量的磁盘访问（写入所有数据和更新索引等）
方法2

使用单独的DELETE、UPDATE和INSERT查询修补results表。
1.查找并删除results中不再存在于复杂查询中的行
1.更新results中在复杂查询中已更改的行
1.在复杂查询中出现的results中插入新行
这意味着对results的访问不会中断，但实现起来要困难得多。我需要执行复杂查询3次，并以不同的方式与results进行联接，以获得DELETE、UPDATE和INSERT部分。
有没有比上面提到的两种方法更好的方法？理想情况下，可以修补results表，而不是从头开始重建，但没有执行单独的DELETE、UPDATE和INSERT查询的复杂性。
注意，我们使用的是Postgres，但我对解决这个问题的建议非常感兴趣。

postgresql

来源：https://stackoverflow.com/questions/76456431/what-is-the-best-way-to-synchronise-a-table-in-an-sql-database-with-the-results

1条答案

按热度按时间

2vuwiymt1#

我注意到了我第一条评论的缺点。这是一个进步。
使用相同的表，但使用新的batch_id列。并使用视图带来最新的数据。您可以保留表名，或使用新名称并将视图创建为结果，这样更改不会影响业务。

-- Assuming your results table now has a batch_id column
CREATE TABLE results (id INT, c1 INT, c2 INT, c3 INT, batch_id INT);

-- Insert new batch of data with a new batch ID
INSERT INTO results (id, c1, c2, c3, batch_id)
SELECT id, c1, c2, c3, :new_batch_id
FROM (/* your complicated query here */);

-- Create or replace the view that points to the latest batch
CREATE OR REPLACE VIEW latest_results AS
SELECT id, c1, c2, c3
FROM results
WHERE batch_id = (SELECT MAX(batch_id) FROM results);

-- Then, to clean up old batches of data (if necessary):
DELETE FROM results WHERE batch_id < (SELECT MAX(batch_id) FROM results);

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 同步SQL数据库中的表与查询结果的最佳方法是什么？

1条答案

相关问题

热门标签

最新问答