主页 / dba / 问题 / 174659
Accepted
brauliobo
Asked: 2017-05-27 03:06:21 +0800 CST

没有表锁的 CLUSTER 替代方案

  • 772

由于频繁的新记录和更新记录导致索引和存储碎片，我正面临性能下降和存储使用量增加的问题。

VACUUM 没有多大帮助。

不幸的是，CLUSTER 不是一个选项，因为它会导致停机并且 pg_repack 不适用于 AWS RDS。

我正在寻找 CLUSTER 的 hacky 替代品。在我的本地测试中似乎运行良好的一个是：

begin;

create temp table tmp_target as select * from target;

delete from target;
insert into target select * from tmp_target order by field1 asc, field2 desc;

drop table tmp_target;

commit;

ctid看起来正确的顺序是：

select ctid, field1, field2 from target order by ctid;

问题是：这看起来不错吗？它是否会为导致应用程序停机的查询锁定target表？SELECT有没有办法列出事务中涉及的锁？

与集群命令锁定副本上的只读查询相关

postgresql locking

2 个回答

  1. Best Answer

    如果它只是关于表格膨胀，VACUUM FULL那么工具就是这样做的。（VACUUM如果它可以在末尾机会性地截断物理文件，则只收缩物理文件。）但是，VACUUM FULL也对表进行独占锁定，就像CLUSTER.

    在没有独占锁的情况下做同样的事情pg_repack是您正在寻找的工具。不幸的是，亚马逊似乎不允许这样做。

    手动解决方案引入了各种极端情况和竞争条件。在我们讨论最佳解决方案之前，您需要准确指定您的表和可能的访问模式。

    无论哪种方式，您当前的解决方案都不好。您在删除所有行（锁定它们）后进行排序操作，这不会缩短查询的停机时间（锁定持续时间）SELECT。

    如果您没有并发写访问权限并且没有依赖对象，这可能会更好：

    BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;

    1. 在拿锁之前在后台排序。
      严格来说，INSERTwithoutORDER BY可以自由地以任何物理顺序写入行。但是，实际上，它会使用普通SELECT * FROM（或TABLE简称）复制临时表的当前物理顺序。

    2. 使用TRUNCATEinstead of DELETE，这对于大表来说更快。请注意某些含义，例如TRUNCATE不适用于 FK 约束。阅读手册以了解全部内容。

    您可能希望在之前删除现有索引TRUNCATE并在之后重新创建INSERT以加快速度。

    有关的：

    • 5

  2. pg_repack现在在 RDS上可用。

    • 2

相关问题