PostgreSQL
关注
分享
扫一扫
文章平均质量分 75
小怪兽ysl
云和恩墨PG技术顾问,PG ACE,中国开源软件推进联盟PG分会系列技术丛书《快速掌握PostgreSQL版本新特性》副主编，中国PG分会认证讲师、恩墨学院PG讲师,PGfans年度MVP，拥有PGCM，MYSQL(OCP8.0), OBCP,OGCA等十多项数据库认证。PG中文社区分享过《PG逻辑复制槽与故障转移》《PostgreSQL15版本新特性解读》PG中国技术大会活动嘉宾。LFAPAC开源布道者，Linux基金会Research Localization伙伴。曾参与《深入理解开源项目办公室》《软件材料清单（SBOM）与网络安全准备度》官方中文版翻译
展开
专栏收录文章
- 默认排序
- 最新发布
- 最早发布
- 最多阅读
- 最少阅读
-
【在PostgreSQL基础上openGauss新增的一些优化器相关选项】
此参数设置为正数时意为直接将设置的值作为估算limit的行数，为负数时代表使用百分比的形式设置默认的估算值，负数转换为默认百分比，即-5代表5%。enable_change_hjcost 控制优化器在Hash Join代价估算路径选择时，是否使用将内表运行时代价排除在Hash Join节点运行时代价外的估算方式。enable_index_nestloop 控制优化器对内表参数化索引扫描嵌套循环连接规划类型的使用。cost_weight_index 设置index_scan的代价权重。原创 2025-01-22 00:58:12 · 198 阅读 · 0 评论 -
【PG14对提升嵌套循环连接的性能的优化】
假设y = f(x)，memoization意味着我们可以在任何计算中用y替代f(x)。例如，无论你计算多少次UPPER(‘x’)，你总会得到’X’。如果这种函数的计算成本很高，并且只有很少的可能输入值，那么维护一个映射所有先前输入值的哈希图并使用它来查找已知（或至少是频繁的）值，而不是再次计算它们，这样的效率会很高。PostgreSQL-14里的这个enable_memoize选项，可以决定是否缓存嵌套循环连接内参数化扫描的结果，这意味着优化器可以用仅取决于计算输入值的缓存值替换任何计算。原创 2025-01-22 00:57:24 · 230 阅读 · 0 评论 -
【PostgreSQL数据库常用优化器选项及表连接/聚集计算适用场景】
对于hash算法来说，数组的长度肯定是大于group by的字段的distinct值的个数的，且跟这个值应该呈线性关系，group by后面的值越唯一，使用的内存也就越大。需要注意的是，JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。Hash 聚合是种常用的数据处理算法，他会对如sum,avg,max,min 等group by 操作进行数据的分组和聚合计算，在处理的过程中，会将数据分成多个组，每个组具有相同的分组键，聚合计算会对该组中的数据进行合并计算。原创 2025-01-22 00:56:42 · 683 阅读 · 0 评论 -
【PostgreSQL数据库-Tried to send an out-of-range integer as a 2-byte value: 53568报错】
其实日志的报错部分往上翻，其实也能大致看出这个报错的SQL，涉及到了大量的绑定变量。报错的位置只有一处，如下是42.3.10版本pgjdbc的报错部分，其中，判断条件里Short.MIN_VALUE和Short.MAX_VALUE的值分别为-32768和32767，表示SQL 解析过程中， SQL中的参数的个数最大不超过32767。但其实简单测试下就可以发现，列的长度不够，报的错误和这个是不一样的。原创 2025-01-06 17:26:40 · 703 阅读 · 0 评论 -
【PostgreSQL数据库备库查询冲突：canceling statement due to conflict with recovery】
有些业务场景，为了分担主库的访问压力，备库（hot_standby=on）常常需要对外提供只读服务。此时备库既要接受并重放通过流协议传过来的wal数据，同时也要对外提供只读查询服务，这两个任务同时进行，有时候会产生冲突。通常情况下，备库如果有有个wal，replay进程在进行wal回放, 回放过程中有query和回放的内容发生了冲突，（通常是备库的慢查询SQL导致）。那么wal回放会进行等待，等待时间取决于max_standby_streaming_delay参数。原创 2025-01-06 17:25:14 · 862 阅读 · 0 评论 -
【PostgreSQL使用pg_filedump工具解析数据文件以恢复数据】
PostgreSQL数据库里如果只是一般的数据文件损坏,。数据页损坏，可以使用zero_damaged_pages=on来跳过损坏的数据块来读取数据，然后将数据导到新表中，当然，这部分损坏的数据可能找不回来的。，而且数据目录下的某些文件损坏，可能需要结合其他的环境拷贝相应文件尝试是否能拉起数据库，但是如果这种方式也无法拉起库，数据库无法启动了。这种情况我们可能需要通过工具直接从数据文件中读取数据，例如使用Oracle中的ODU等等的工具。原创 2024-11-21 04:21:51 · 1132 阅读 · 0 评论 -
【PostgreSQL里的子查询解析】
子查询是一种嵌套在其他SQL查询中的查询方式，也可以称作内查询或嵌套查询。当一个查询是另一个查询的条件时，就称之为子查询。子查询的语法格式与普通查询相同，但其在查询过程中起着临时结果集的作用，为主查询提供所需数据或对检索数据进行进一步的限制。子查询最重要的方面是有所谓的相关子查询和不相关子查询。原创 2024-09-13 23:04:22 · 1057 阅读 · 0 评论 -
【PostgreSQL-patroni维护命令】
optime/leader是主库最后一次操作后的lsn位置，是持久节点不会因为session到期，删除该key值。1342177280。原创 2024-09-13 22:52:23 · 1446 阅读 · 0 评论 -
【PostgreSQL里的restartpoint重启点】
不知道大家有没有关注过，配置文件里archive_cleanup_command参数的注释部分有着这么一句"command to execute at every restartpoint",意思是在每个restartpoint时执行的命令。提起checkpoint大家可能比较熟悉，对于这个restartpoint，可能有的人就不太了解了。一个restartpoint相当于恢复期间的检查点，并建立恢复可以前滚而无需重新replay整个恢复日志的点。是在PostgreSQL-8.2提交的。原创 2024-09-13 22:51:15 · 793 阅读 · 0 评论 -
【PGSQL 修改整个schema所有表owner】
【代码】【PGSQL 修改整个schema所有表owner】原创 2024-09-13 22:31:33 · 873 阅读 · 0 评论 -
【PostgreSQL数据库表膨胀的一些原因】
PostgreSQL表膨胀的原因主要有两个：一个是垃圾数据，即dead tuple行数太多未及时清理，导致不能及时提供能重用的空间，二是数据页之间存在空闲空间。原创 2024-09-13 22:00:50 · 879 阅读 · 0 评论 -
【PostgreSQL里vacuum但是无法回收死元组的原因】
PostgreSQL数据库里的vacuum/autvacuum在我们日长的使用中可能会遇到很多问题，例如vacuum被阻塞，vacuum时间长，vacuum成功执行后，仍旧无法回收死元组等等。，本文主要介绍PostgreSQL的vacuum成功执行后，仍旧无法回收死元组的几种原因。原创 2024-09-13 21:59:47 · 850 阅读 · 0 评论 -
【GBase 8c V5_3.0.0 分布式数据库常用几个SQL】
以管理员用户 gbase，登录数据库主节点。接数据库，并执行如下 SQL 语句查看连接数。原创 2024-09-13 21:58:02 · 1003 阅读 · 0 评论 -
【GBase 8c V5_3.0.0 分布式数据库常用维护命令】
使用gha_ctl monitor查看节点运行情况(跟dcs的地址和端口)原创 2024-09-13 21:52:11 · 678 阅读 · 0 评论 -
【GBase 8c V5_3.0.0 分布式部署(单机安装)】
GBase 8c数据库分布式形态采用share nothing的分布式架构，计算节点和存储节点分离。节点间通过高速网络进行通信，所有节点都有主从互备，确保系统的极致高可用。本文主要包含GBase 8c V5_3.0.0 分布式部署(单机安装)的简单流程，供测试。GBase 8c分布式数据库实际包含CN、DN、GTM和HA管理组件，各个组件具体介绍如下：CN：协调器，采用完全对等的部署方式。对外提供接口，负责进行SQL解析和优化、生成执行计划，并协调数据节点进行数据查询和写入。在功能上CN上只存储系统的全局原创 2024-09-13 21:50:01 · 1385 阅读 · 0 评论 -
【为什么我在 POSTGRESQL 中 COMMIT 很慢？】
原文标题：WHY DO I HAVE A SLOW COMMIT IN POSTGRESQL?原文作者：Laurenz Albe原文地址：https://www.cybertec-postgresql.com/en/why-do-i-have-a-slow-commit-in-postgresql/翻译：阎书利有时，我们的一位客户会查看数据库中最耗时的语句（使用pg_stat_statements或pgBadger），并发现COMMIT排名靠前。转载 2024-05-31 12:35:57 · 243 阅读 · 0 评论 -
【PostgreSQL17新特性之-explain命令新增选项】
PostgreSQL17-beta1版本近日发布了，新版本里，explain命令新增了两个选项，分别是MEMORY 和SUMMARY。原创 2024-05-31 12:37:18 · 957 阅读 · 0 评论 -
【PostgreSQL17新特性之-COPY FROM的ON_ERROR容错选项】
经过测试，对于copy from的不同错误类型，就算选择了同样的ON_ERROR ignore选项，行为也可能不一样。原创 2024-05-30 15:23:34 · 1229 阅读 · 0 评论 -
【PostgreSQL17新特性之-冗余IS [NOT] NULL限定符的处理优化】
David Rowley的相关邮件里也强调了：当我们优化Min/Max聚合时，规划器添加的IS NOT NULL qual会使重写的计划忽略NULL，这可能会导致索引选择不佳的问题。在PostgreSQL16版本及以前,就算原本的列上有非空索引，查询条件带有NULL和NOT NULL，也感知不到，依然会去扫描表去评估，增加额外的计划和执行的开销。在执行一个带有IS NOT NULL或者NOT NULL的SQL的时候，通常会对表的每一行，都会进行检查以确保列为空/不为空，这是符合常理的。原创 2024-05-30 15:21:59 · 578 阅读 · 0 评论 -
【PostgreSQL17新特性之-事务级别超时参数transaction_timeout】
PostgreSQL数据库里有多个和会话相关的参数，PostgreSQL17-beta1版本新增了一个transaction_timeout参数，来限制事务的持续时间。当前的一些和会话相关的超时参数如下。原创 2024-05-29 18:19:11 · 1178 阅读 · 0 评论 -
【PostgreSQL17新特性之-新增系统视图】
PostgreSQL-17-beta1版本目前已经发布了，每个版本随着功能的增加和一些相关优化，会对部分视图进行调整，以及增加新的视图。目前的PostgreSQL-17-beta1新版本新增了两个视图，分别为pg_wait_events和pg_stat_checkpointer。pg_stat_checkpointer视图包含检查点信息，pg_wait_events 主要包含等待事件的描述。原创 2024-05-29 18:17:36 · 1813 阅读 · 0 评论 -
【PostgreSQL支持中文的全文检索插件(zhparser)】
PostgreSQL本身是支持全文检索的，提供两个数据类型（tsvector,tsquery），并且通过动态检索自然语言文档的集合，定位到最匹配的查询结果。而一个tsvector的值是唯一分词的分类列表，把一话一句词格式化为不同的词条，在进行分词处理的时候tsvector会自动去掉分词中重复的词条，按照一定的顺序装入。要支持中文的全文检索需要额外的中文分词插件，zhparser就是其中一种，是基于Simple Chinese Word Segmentation(SCWS)中文分词库实现的一个PG扩展。原创 2024-05-14 17:31:55 · 3712 阅读 · 0 评论 -
【PostgreSQL里的子查询解析】
子查询是一种嵌套在其他SQL查询中的查询方式，也可以称作内查询或嵌套查询。当一个查询是另一个查询的条件时，就称之为子查询。子查询的语法格式与普通查询相同，但其在查询过程中起着临时结果集的作用，为主查询提供所需数据或对检索数据进行进一步的限制。子查询最重要的方面是有所谓的相关子查询和不相关子查询。原创 2024-05-14 13:36:57 · 1279 阅读 · 0 评论 -
【PostgreSQL里insert on conflict do操作时的冲突报错分析】
最近在巡检PostgreSQL的数据库的时候，发现部分数据库里存在大量的如下报错。原创 2024-04-16 15:25:14 · 2538 阅读 · 1 评论 -
【PostgreSQL autovacuum清理死元组的相关优化】
如果有未提交的预定义语句，它们会阻止移除死元组。需要使用 COMMIT PREPARED或ROLLBACK PREPARED提交或回滚这些语句。//查询未提交的预定义语句的SQL如下。原创 2024-04-09 15:14:55 · 1430 阅读 · 0 评论 -
【PostgreSQL的指标采集工具--pgmetrics】
pgmetrics是用go语言写的一款PostgreSQL的健康监控指标采集软件。可以连接到数据库，获取当前数据库的相关信息，如果连接的是本地数据库，则同时会采集服务器的状态信息。可以把采集的结果以json或者text或者csv的形式存储。原创 2024-03-20 10:06:01 · 498 阅读 · 0 评论 -
【PostgreSQL里pg_filenode.map文件的意义】
通常情况下，PostgreSQL中每张表在磁盘上都有与之相关的文件，而这些文件的名字便是relfilenode，我们可以通过pg_class的relfilenode字段去查询。对于一张普通表，其relfilenode和oid默认是一样的。系统表有的也是。但当我们对该表进行了例如vacuum full、truncate之类的操作，表重建后，那么relfilenode便会发生变化。原创 2024-03-19 09:25:43 · 879 阅读 · 0 评论 -
【PostgreSQL的变长字段数据超过多少会写入到TOSAST表】
通常PostgreSQL里如果一个元祖的变长字段的数据量，超过2KB，则PostgreSQL会尝试进行压缩，把元组控制在2KB之内，如果不能满足2KB之内的需求，就需要独立的toast表来存储了。原创 2024-03-11 08:55:52 · 645 阅读 · 0 评论 -
【PostgreSQL实现psql连接时候提示用户的密码有效时间】
我这里写了两种，一种是只显示查看自己登陆的用户的有效期剩余时间，一种是一并显示数据库里所有的设置了有效期的用户的剩余可使用天数，以及打印出剩余时间小于七天甚至已经过期的用户。首先是涉及到的判断是否需要修改密码和有效期的查询SQL，这里根据距离过期前7天为标准作为是否需要修改密码的提示。2.一并显示数据库里所有的设置了有效期的用户的剩余可使用天数，以及打印出剩余时间小于七天甚至已经过期的用户。函数需要在所有的数据库中创建，否则psql登陆会有如下的warning，但不影响使用。更改完之后重启数据库。原创 2024-02-21 09:09:26 · 1027 阅读 · 0 评论 -
PostgreSQL限制密码的有效期(每次增加180天)
创建用户时，可以使用如下语句，自动在当前时间的基础上增加180天，作为这个用户密码的有效时间。等到密码到期后或者临近的时候，可以使用SQL修改用户密码，并且重新定义密码的有效期。（也可以直接指定时间，下面方法是为了不用自己算增加180天的天数）把生成的SQL粘贴执行。可以通过pg_roles这个视图查看用户的密码有效时间。原创 2024-02-20 09:01:23 · 2298 阅读 · 0 评论 -
PostgreSQL使用session_exec和file_fdw实现失败次数锁定用户策略
缺陷：实测发现锁用户后，进去解锁特定用户。只能允许一次登陆，应该再次登陆的时候，触发函数，把之前的日志里的错误登陆的信息也计算到登录次数里了。需要删除对应的pg_log，才能使foreign table信息清理掉，来重制该用户的密码错误记录。解锁同时需要删除pg_log下csv文件里的对应有改用户登陆失败的日志记录，重制密码登录错误的记录，否则可能会重复计算之前的错误登陆记录。用户是否被锁/是否允许登陆，可以查看pg_roles系统视图里的rolcanlogin字段。使用如下语句，可以查询登陆失败的记录。原创 2024-02-19 16:46:10 · 1483 阅读 · 1 评论 -
PostgreSQL里实现计算多个数字的排列组合
在进行排列组合的时候，每一次需要知道是否有重复的值，并过滤出已经排列过的值。这个可以创建支持可变参数的函数来实现。下边的函数用到了聚合判断，并且可变参数使用variadic标记的数组。然后下边使用创建的判断是否有重复数据的函数以及使用SQL实现获取所有的排列组合。根据4的阶乘可以得到，总共应该有24种，阶乘可以使用factorial函数。然后是如何使用这个函数结合查询语句对一组数据进行排列组合。例如，一张表里有1234这四个值。想使用四个值做排列组合。先创建一个测试的表，里边存放要进行排列组合的数据。原创 2024-02-19 09:01:15 · 776 阅读 · 0 评论 -
【PostgreSQL灵活使用psql执行SQL的一些方式】
可以不进入数据库，在命令行，使用psql 的-c选项跟上需要执行的SQL。来获取SQL的执行结果可以使用psql的选项对查询结果进行一些处理。原创 2024-02-01 16:44:40 · 2271 阅读 · 0 评论 -
【灵活设置PostgreSQL的PROMPT1客户端提示符】
psql。原创 2024-02-01 16:38:53 · 1632 阅读 · 0 评论 -
【PostgreSQL采用MD5密码认证时密码和pg_authid里rolpassword的关系】
通过上述测试可以发现，pg_authid系统表里的rolpassword字段的MD5码 ="md5"字符串+ md5(pwd+username)），MD5在理论上是几乎无法破解的，虽然不能反向解析，但是如果获取到了这个MD5处理后的字符串，可以通过撞库方式获取MD5算法处理前的字符，用预先计算好的MD5散列值与已知的散列值进行比较，以查找匹配的明文，从而获取到用户名和密码。但是这种方法需要预先有一定的可能的明文和计算MD5散列值的能力。原创 2024-01-23 13:54:44 · 1907 阅读 · 0 评论 -
【pgBackRest备份工具使用】
pgBackRest是PostgreSQL数据库的一个开源备份工具，支持全量、增量和差异备份。这一点优于PG原生的备份工具pg_basebackup，因为pg_basebackup不支持增量备份，并且支持备份完整性检查等等。pgBackRest v2.49是当前的稳定版本。项目主页：https://pgbackrest.org/Github链接：https://github.com/pgbackrest/pgbackrest优点：支持本地或远程操作， 但是远程操作需要配置SSH。原创 2024-01-16 14:30:08 · 1910 阅读 · 1 评论 -
【PostgreSQL在线创建索引(CIC)功能的锁分析以及使用注意】
前一篇文章提到了普通创建索引会阻塞DML操作而PostgreSQL里可以使用create index concurrently 在线创建索引(CIC)功能，降低创建索引在表上申请的锁的级别，ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突，不会阻塞表上的DML操作。原创 2024-01-08 09:53:20 · 1657 阅读 · 0 评论 -
【PostgreSQL创建索引的锁分析和使用注意】
PostgreSQL里有很多可以加锁的对象：表、单个页、单个元组、事务ID（包括虚拟和永久ID）和普通数据库对象等等，常规锁的locktype主要有以下几种。有时候通过pg_locks查询的时候，根据pid会查到许多的锁，但是这些锁并不一定都是加在表上的，根据locktype以及relation过滤出不同对象上的锁。/**/原创 2024-01-08 09:49:47 · 1631 阅读 · 0 评论 -
【PostgreSQL的WAL日志解析工具pg_waldump/pg_xlogdump】
PostgreSQL利用日志文件来防止断电之类的故障导致的数据丢失，任何试图修改数据库的操作都会写一份日志记录到磁盘，这个日志称为XLOG/WAL。在数据库定位问题时，就可以使用pg_xlogdump/pg_waldump来解析XLOG/WAL日志，包括日志类型、对应的事务号、修改的文件等等。pg_xlogdump/pg_waldump是查不出数据的，要查出数据，首先要有数据结构，然后要有数据。数据结构是需要查数据库的元数据才能查到，pg_xlogdump没办法做到。原创 2024-01-04 17:30:30 · 2939 阅读 · 1 评论 -
【PostgreSQL查看SQL执行过程主机的资源使用情况】
在分析SQL的时候，可以临时设置client_min_messages=log结合如下几个参数分别查看SQL在explain, parser, execute 过程系统资源使用分析。生产环境不建议开启，建议临时分析会话级别开启，用于定位问题。原创 2024-01-04 17:29:09 · 863 阅读 · 0 评论