管理大型 Postgres 数据库的 3 个技巧
创始人
2024-03-02 14:42:25
0

在处理庞大的数据库时,请尝试这些方便的解决方案,以解决常见的问题。

关系型数据库 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 社区的优秀人士也会在那里。加入我们吧!


via: https://opensource.com/article/23/2/manage-large-postgres-databases

作者:Elizabeth Garrett Christensen 选题:lkxed 译者:geekpi 校对:wxy

本文由 LCTT 原创编译,Linux中国 荣誉推出

相关内容

不直接使用表名访问Post...
可以使用PostgreSQL元数据系统中的信息来访问表,例如查询s...
2025-01-12 12:01:41
不直观的PostgreSQ...
PostgreSQL在字符串排序方面的行为有时可能会让人感到困惑,...
2025-01-12 12:00:53
不允许postgresql...
您可以使用postgresql提供的“ALTER TABLE”命令...
2025-01-11 11:30:30
不需要转储文件,只使用数据...
要在PostgreSQL中还原数据库而不需要转储文件,可以使用pg...
2025-01-10 16:30:20
不小心从用户postgre...
如果不小心从用户postgres上删除了超级用户权限,可以通过以下...
2025-01-10 09:31:26
不同域的“PostgreS...
要实现“不同域的”PostgreSQL SSPI身份验证,可以使用...
2025-01-09 23:02:24

热门资讯

使用 KRAWL 扫描 Kub... 用 KRAWL 脚本来识别 Kubernetes Pod 和容器中的错误。当你使用 Kubernet...
Helix:高级 Linux ... 说到 基于终端的文本编辑器,通常 Vim、Emacs 和 Nano 受到了关注。这并不意味着没有其他...
通过 SaltStack 管理... 我在搜索Puppet的替代品时,偶然间碰到了Salt。我喜欢puppet,但是我又爱上Salt了:)...
Epic 游戏商店现在可在 S... 现在可以在 Steam Deck 上运行 Epic 游戏商店了,几乎无懈可击! 但是,它是非官方的。...
如何在 Github 上创建一... 学习如何复刻一个仓库,进行更改,并要求维护人员审查并合并它。你知道如何使用 git 了,你有一个 G...
2024 开年,LLUG 和你... Hi,Linuxer,2024 新年伊始,不知道你是否已经准备好迎接新的一年~ 2024 年,Lin...
Bazzite:专为 Stea... 为 Linux 桌面或者 Steam Deck 做好游戏准备,听起来都很刺激!对于一个专为 Linu...
Motrix:一个漂亮的跨平台... 一个开源的下载管理器,提供了一个简洁的用户界面,同时提供了跨平台操作的所有基本功能。在这里了解关于它...
Bash 脚本中如何使用 he... here 文档 here document (LCTT 译注:here 文档又称作 heredoc ...
使用 dialog 和 jq ... 为何选择文字用户界面(TUI)?许多人每日都在使用终端,因此, 文字用户界面 Text User I...