无法理解!向来傲娇的PostgreSQL会为了性能引入“垃圾”特性
digoal约 5 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
无法理解!一向傲娇的PostgreSQL会引入这么“垃圾”的特性
妥协了? check和foreign key 约束要引入假设为真(NOT ENFORCED)了? tom lane老师不说这是booshit了？
为了性能值得吗? 例如
大量导入数据时, 明知数据约束一定为真的情况下, 数据库不做检测(可以减少cpu消耗/IO消耗, 特别是foreign key的检测). 导入完成后再改成需要检测. 假设应用可以保证约束为真, 把责任推给应用.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e2fccf81cec6fd45698dde9fae0ab570
Add support for NOT ENFORCED in CHECK constraints
author Peter Eisentraut <[email protected]>
Sat, 11 Jan 2025 09:45:17 +0000 (10:45 +0100)
committer Peter Eisentraut <[email protected]>
Sat, 11 Jan 2025 09:52:30 +0000 (10:52 +0100)
commit ca87c415e2fccf81cec6fd45698dde9fae0ab570
tree f9e1f5fc7637f0baf91566f4d8a333ddb60960b1 tree
parent 72ceb21b029433dd82f29182894dce63e639b4d4 commit | diff
Add support for NOT ENFORCED in CHECK constraints
This adds support for the NOT ENFORCED/ENFORCED flag for constraints,
with support for check constraints.
The plan is to eventually support this for foreign key constraints,
where it is typically more useful.
Note that CHECK constraints do not currently support ALTER operations,
so changing the enforceability of an existing constraint isn't
possible without dropping and recreating it. This could be added
later.
Author: Amul Sul <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Reviewed-by: jian he <[email protected]>
Tested-by: Triveni N <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com
+ <varlistentry id="sql-createtable-parms-enforced">
+ <term><literal>ENFORCED</literal></term>
+ <term><literal>NOT ENFORCED</literal></term>
+ <listitem>
+ <para>
+ When the constraint is <literal>ENFORCED</literal>, then the database
+ system will ensure that the constraint is satisfied, by checking the
+ constraint at appropriate times (after each statement or at the end of
+ the transaction, as appropriate). That is the default. If the
+ constraint is <literal>NOT ENFORCED</literal>, the database system will
+ not check the constraint. It is then up to the application code to
+ ensure that the constraints are satisfied. The database system might
+ still assume that the data actually satisfies the constraint for
+ optimization decisions where this does not affect the correctness of the
+ result.
+ </para>
+
+ <para>
+ <literal>NOT ENFORCED</literal> constraints can be useful as
+ documentation if the actual checking of the constraint at run time is
+ too expensive.
+ </para>
+
+ <para>
+ This is currently only supported for <literal>CHECK</literal>
+ constraints.
+ </para>
+ </listitem>
+ </varlistentry>
文末彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航老司机开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: