关系型数据库 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
然后在创建后验证它:
ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check
David Christensen 和我将在 3 月 9 号到 10 到在加州帕萨迪纳参加 SCaLE 的 Postgres Days。很多来自 Postgres 社区的优秀人士也会在那里。加入我们吧!
责任编辑:庞桂玉 来源: Linux中国 Postgres数据库(责任编辑:娱乐)
赵明亲自爆料荣耀MagicOS 8.0 荣耀将在AI领域遥遥领先? -
vivo Y100官宣10月27日发布:打造千元机体验新起点
正业国际(03363.HK)全年纯利下降33.35% 每股基本盈利人民币11分
SSH协议怎么样?为什么正常的连接突然报错Identification问题