管理大型Postgres数据库的3个技巧

关系数据库PostgreSQL（也称为Postgres）越来越受欢迎，企业和公共部门在全球范围内使用它。随着这种广泛的采用，数据库变得比以往任何时候都更大。在Crunchy Data，我们经常处理超过20TB的数据库，而我们现有的数据库仍在继续增长。我的同事David Christensen和我收集了一些关于管理包含大型表的数据库的技巧。

大型表

生产数据库通常由许多表组成，这些表具有不同的数据、大小和模式。最终得到一个巨大且难以管理的数据库表是很常见的，它比数据库中的任何其他表都大得多。此表通常存储活动日志或带时间戳的事件，并且对于您的应用程序或用户来说是必需的。

非常大的表可能会因多种原因而带来挑战，但常见的原因之一是锁。对表进行定期维护通常需要锁，但对大型表进行锁定可能会使您的应用程序崩溃或导致交通拥堵和许多麻烦。我有一些关于执行基本维护的技巧，例如添加列或索引，同时避免长时间运行的锁。

添加索引问题：索引创建会在创建过程期间锁定表。如果您有一个大型表，这可能需要数小时。

CREATE INDEX ON customers (last_name)

解决方案：使用CREATE INDEX CONCURRENTLY功能。此方法将索引创建分为两个部分，一部分使用短暂的锁来创建索引，该索引立即开始跟踪更改，但最大限度地减少应用程序阻塞，然后是索引的完全构建，之后查询可以开始使用它。

CREATE INDEX CONCURRENTLY ON customers (last_name)

添加列

在数据库的生命周期中，添加列是一个常见的请求，但是对于大型表来说，这可能会很棘手，同样，也是由于锁定。

问题：当您添加一个带有调用函数的默认值的新列时，Postgres需要重写表。对于大型表，这可能需要数小时。

解决方案：将操作分解为多个步骤，以达到基本语句的总体效果，但保留对锁定时机的控制。

添加列

ALTER TABLE all_my_exes ADD COLUMN location text

添加默认值

ALTER TABLE all_my_exes ALTER COLUMN location

SET DEFAULT texas()

使用UPDATE添加默认值

UPDATE all_my_exes SET location = DEFAULT

添加约束

问题：您想要添加一个检查约束以进行数据验证。但是，如果您使用直接的方法添加约束，它将在验证表中所有现有数据时锁定表。此外，如果在验证的任何时候出现错误，它将回滚。

ALTER TABLE favorite_bands 

ADD CONSTRAINT name_check

CHECK (name = 'Led Zeppelin')

解决方案：告诉Postgres关于约束，但不要验证它。在第二步中验证。这将在第一步中进行短时间的锁定，以确保所有新的/修改的行都符合约束，然后在单独的步骤中验证以确认所有现有数据都通过约束。

告诉Postgres关于约束，但不要强制执行它

ALTER TABLE favorite_bands

ADD CONSTRAINT name_check

CHECK (name = 'Led Zeppelin') NOT VALID

然后在创建后VALIDATE它

ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check

还想了解更多？

David Christensen和我将于3月9日至10日在加利福尼亚州帕萨迪纳举行的SCaLE Postgres Days上。Postgres社区的许多优秀人士也将在那里。加入我们吧！