PostgreSQL
文章平均质量分 56
PostgreSQL相关知识分享
魂醉
这个作者很懒,什么都没留下…
展开
-
PostgreSQL中如何配置Huge page的数量
在了解如在PG中如何配置大页之前,我们先要对大页进行一定的了解,为什么要配置大页,配置大页的好处有哪些。我们日常的操作系统中,程序不直接使用内存,而是使用虚拟内存地址来处理内存分配,避免计算的复杂性和物理地址映射到应用程序内存空间的复杂性。虚拟地址模型在应用程序读取或写入内存时立即将虚拟地址转换为相应的物理内存地址。这种映射结构存储在page tables中,这是一种分层组织的查找表。原创 2023-07-28 18:18:38 · 1629 阅读 · 0 评论 -
PostgreSQL中HOT对cluster的作用
PG中cluster的作用是根据表的索引重新构建一张表,并且表根据该索引进行排序,索引必须提前建好。注意:cluster操作加ACCESS EXCLUSIVE锁,会阻塞其它任何操作。原创 2023-06-28 11:42:47 · 994 阅读 · 0 评论 -
Postgresql中序列正确使用建议
优先使用identity column方式,因为避免了很多坑使用序列类型,最好直接使用bigserial,避免出现序列值耗尽的情况如果是自己创建的序列,然后设置列的默认值,则使用owned by方式绑定到列。如果序列使用的是int4类型的,消耗比较慢,在业务可接受的时候,可以选择负数,这样就有2倍的值可用。int修改为bigint一定要注意,不要直接改,因为会重写表,可以尝试通过本篇文章的方法进行列替换,回填旧数据的方式。原创 2023-03-14 16:53:36 · 1788 阅读 · 1 评论 -
Postgresql之虚拟索引插件hypopg
虚拟索引是指实际上并不存在的索引,不需要花费CPU、磁盘等任何资源来创建索引。它可以帮助我们了解特定的索引是否可以使用索引提高查询性能,而不必花费资源来创建它们。在oracle中,很早就有virtual index的实现,可以很好判断索引是否可以被使用。今天介绍一款PG虚拟索引的插件hypopg,可以达到同样的效果。虚拟索引不能用于EXPLAIN ANALYZE语句,因为语句要真实执行SQL语句。而索引实际上并不存在,它不能使用一个不存在的索引。原创 2023-03-09 19:46:01 · 277 阅读 · 0 评论 -
Postgresql中的unlogged table
unlogged表的优缺点原创 2023-02-28 15:31:10 · 744 阅读 · 0 评论 -
PostgreSQL只监听unix socket
Postgresql只监听unix socket如何设置?原创 2023-02-27 17:33:08 · 540 阅读 · 0 评论 -
Postgresql中null值和空字符串
首先null不是一个空字符串,也不是一个为零的值,上图,Oracle将NULL和空字符串都视为NULL。与PostgreSQL类似,SQL Server也将NULL视为NULL,将空字符串视为空字符串。这可以防止我们在具有唯一约束的列中插入多个空值。在SQL Server中,在一个列上存在唯一约束时,只允许插入一个NULL和一个空字符串。null和空字符串在不同的数据库中表现不一样,找了一张图,可以很清晰的对比了解。在Oracle中,存在唯一约束的列可以存储任意数量的NULL项和空字符串。原创 2023-02-10 17:31:58 · 7154 阅读 · 0 评论 -
PostgreSQL中offset...limit分页优化常见手段
大部分开发人员习惯使用order by offset limit进行分页,使用该方法可能会导致扫描的数据放大,因为offset的行会被扫描。表现就是一般offset的行比较小的情况也,也就是翻页,是很快的,但是一旦offset的值很大,翻页的数量很大,那么一定会变慢。如上我们看到的,翻页越多,性能越差,唯一的好处,就是书写简单。原创 2023-02-07 17:05:01 · 3677 阅读 · 0 评论 -
Postgresql之添加字段插件pg_migrate安装使用
最近,开发同学给大表加字段很是痛苦,加字段是DDL要锁表,尤其要加的字段是个变量,那么pg是会重写表的,可以参考我之前的文章,网上搜了一下,有个插件可以用来实现该操作。是一款叫pg_migrate的插件,地址如下:https://pgxn.org/dist/pg_migrate/0.1.1/原创 2023-02-03 16:26:35 · 619 阅读 · 0 评论 -
Postgresql中使用union all数据类型不一致导致的查询性能问题
那么分析一下原因,首先在src/backend/optimizer/prep/prepjointree.c源码中看下pull_up_subqueries_recurse()函数的内容,可以找到以下片段,片段告诉我们,如果是简单的子查询,那么就会扁平化的追加该对象,也就是我们上面执行计划看到的Append下面有两个同级的索引扫描。通过以上例子我们可以看到a字段类型一致的情况下,union all前后的两个子句走的都是索引查询,而a字段类型不一致的情况,走的是全表扫描,致使语句查询性能低下。原创 2022-12-21 17:29:14 · 2308 阅读 · 0 评论 -
如何在一台服务器同一个端口运行多个pgbouncer
https://github.com/systemd/systemd/commit/54255c64e6d223deb7d3863e426e78c443fda37c的原因,systemd中的ReusePort选项在222之前的版本中不能用于此目的。另外,有一个多线程的连接池可以替换需要多个pgbouncer的情况,可以参考https://github.com/yandex/odyssey测试一下。如下图,可以配置多个pgbouncer在同一台机器的同一个端口,充分利用系统资源。原创 2022-12-01 10:58:08 · 416 阅读 · 0 评论 -
Postgresql事物快照介绍
一个数据页包含了每一行的多个版本,每一行的可见版本一起构成一个快照。快照只包含在创建快照时当前已提交的数据,在这个特定的时刻提供了一个一致性的视图,这个视图我们就可以叫做快照。为了确保数据的隔离性,每一个事物都有自己的快照,这就意味着不同的事物在不同的时间点可以看到不同的快照。但是单个快照内部是一致的。在 Read Committed 隔离级别中,每个语句开始都会有一个快照,并且在该语句执行期间,仍然保持活动状态。原创 2022-11-08 11:46:05 · 1415 阅读 · 0 评论 -
Postgresql中如何处理逻辑复制冲突
在Postgresql中,随着逻辑复制的广发使用,在逻辑复制中会出现各种各样的问题,今天介绍一下,如果逻辑复制出现冲突,我们该如何解决。原创 2022-11-01 14:44:28 · 829 阅读 · 0 评论 -
PostgreSQL 15如何改善了逻辑复制中的通信
现在,我将分别介绍这篇博文开头提到的两个问题是如何改进/修复的。翻译 2022-10-24 14:02:08 · 320 阅读 · 0 评论 -
PostgreSQL使用LISTEN/NOTIFY按需自动创建分区
为了对之前的写的LISTEN/NOTIFY使用有更进一步的认识,参考其他博客做个实例,看看具体使用LISTEN/NOTIFY是如何实现自动添加分区表的。测试基于13.6版本,在数据库中创建测试表,触发器函数,以及触发器。以下为C写的客户端API。在客户端调用API测试。插入测试数据进行测试。原创 2022-10-14 10:25:15 · 625 阅读 · 0 评论 -
PostgreSQL中LISTEN和NOTIFY介绍使用
LISTEN/NOTIFY是postgresql的一个功能特性,是一个异步的查询接口,避免的一直轮询数据库。可以在sql中使用,也可以在C,jdbc里的API进行调用。原创 2022-10-11 14:09:22 · 1340 阅读 · 0 评论 -
Postgresql添加列并填充默认值注意事项
从11大版本以后,PG优化了添加带有默认值的列操作,11版本以前只要添加带有默认值的列,表会被重写,不管默认值是常量还是变量,所以在加字段的时候需要特别注意,重写表的锁是Access Exclusive,最重的锁,整张表是无法访问的。附上修改字段类型是否需要重写表的规则,如果新旧字段类型是二进制兼容的,从小到大修改,则不需要重表,如果从大往小改,则需要重写表。我们通过实例看下11版本以后的效果,测试环境PG版本为13.6。原创 2022-09-21 17:13:30 · 2595 阅读 · 0 评论 -
Postgres16版本中FROM子查询别名可以省略不写了
因为在oracle中是可以不需要写别名的,所以从oracle迁移至postgresql会觉的让人不爽,但是从16版本开始,from子句的别名可以省略不写了。希望能看到16版本最终提交该补丁吧。原创 2022-08-24 18:01:18 · 1833 阅读 · 0 评论 -
举例了解PostgreSQL中表膨胀的原理
以前写过一些文章,都是说明如何避免膨胀,以及如何处理膨胀的。PG中的膨胀是由于MVCC机制和存储引擎决定的,今天用实例说明一下,PG中膨胀的原理。查看xmin,xmax,由于都是插入,所以只有xmin有数据,而且1~5每条都有单独的事物ID,6 ~ 10因为在一个事物,所以有一样的事物ID。在PG中,表是堆表,数据是无序的,是从page底部逐个tuple填充的。默认page大小为8KB。为了查看page的结构,我们需要安装插件pageinspect。lp可以理解行的ID号t_xmin是插入的事物ID。...原创 2022-08-10 18:01:54 · 1452 阅读 · 3 评论 -
Postgresql中如何终止正在执行的查询
在使用数据库过程中,我们难免要终止一些正在执行的查询等语句,比如不合理的超长大事物,对数据库性能有影响的偶发性查询。一般在pg中使用以下两个函数终止相关查询,这里不在详述两个函数的区别。直接给出官方文档解释:pg_cancel_backend 调用系统信号 SIGINT 对应信号2pg_terminate_backend 调用系统信号 SIGTERM 对应信号15--定义在/usr/include/asm/signal.h#define SIGHUP 1#define S原创 2022-05-31 17:01:48 · 4882 阅读 · 1 评论 -
Postgresql14对逻辑复制中大事物的增强
订阅端增加了一个参数和一个视图如下:#参数:ALTER SUBSCRIPTION my_subscription SET(STREAMING = ON);#新增视图postgres=# \d pg_stat_replication_slots View "pg_catalog.pg_stat_replication_slots" Column | Type | Collation | Nullable | Defa原创 2022-05-18 11:03:24 · 329 阅读 · 0 评论 -
Postgresql插件之pg_stat_monitor介绍
介绍一款Percona公司开发的插件pg_stat_monitor,该插件已经GA,就是正式发布了,说明各方面已经比较稳定,可以放心使用。首先该插件是基于pg_stat_statements开发的一个插件,可以回溯历史,查看那些查询对数据库系统有影响。目前支持如下版本:该插件有一个“桶”的概念。桶是一个可配置的时间片段。可以将查询统计数据添加分解到定时的桶,从而查看查询在一段时间内的性能变化,而不是将所有数据存储在单个大桶中。注意,默认值最多为10个桶,每个桶包含60秒的数据(配置可以修改)。所以该插原创 2022-05-12 15:12:18 · 993 阅读 · 2 评论 -
Postgresql一条建表语句导致逻辑复制槽堆积大量wal日志
应需求需要抽取一张表的两个字段,所以先本地测试了一下,为了只是看效果,所以只执行了30秒后,取消了,如果要执行完,差不多要2分多钟。melotall=> \dt+ user_assets_info List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+--------原创 2022-05-10 09:28:34 · 1396 阅读 · 1 评论 -
Postgresql中如何正确删除role
关于删除role的一些理论首先,删除用户不能使用DROP ROLE … CASCADE,不能级联删除用户。也就是不能删除依赖的对象。因为角色可以拥有数据库对象,并且可以拥有访问其他数据库对象的权限,所以删除角色通常不仅是执行DROP role的问题。该角色拥有的任何对象必须先被删除或重新分配给其他角色;并且必须回收授予该角色的一切权限。对象的所有权可以通过alter命令修改,如下:ALTER TABLE bobs_table OWNER TO alice;这里就该REASSIGN OWNED B原创 2022-05-05 15:15:00 · 3403 阅读 · 0 评论 -
PostgreSQL将在15版本支持MERGE INTO语法
MERGE语法可以参考如下:https://www.postgresql.org/docs/devel/sql-merge.html[ WITH with_query [, ...] ]MERGE INTO target_table_name [ [ AS ] target_alias ]USING data_source ON join_conditionwhen_clause [...]where data_source is{ source_table_name | ( source原创 2022-05-04 16:06:20 · 2986 阅读 · 0 评论 -
Postgresql逻辑复制报错could not start WAL streaming: ERROR: replication slot “x“is active for PID xxx
先看日志错误:#发布端报错如下:2022-04-01 10:18:23.812 CST,"postgres","hank",4666,"10.4.9.250:49138",624660ef.123a,1,"idle",2022-04-01 10:18:23 CST,10/0,0,ERROR,55006,"replication slot ""sub"" is active for PID 4575",,,,,,,,,"sub"2022-04-01 10:18:28.826 CST,"postgres"原创 2022-04-01 11:18:52 · 3437 阅读 · 0 评论 -
Postgresql审计插件pgaudit使用说明
最近由于审计,rds的审计成了问题,因为很多权限没开放出来,但是很多云厂商提供了pgaudit插件,这里简单介绍下吧,虽然没有达到想要的预期。pgaudit和postgresql版本兼容性匹配列表:pgAudit v1.6.X is intended to support PostgreSQL 14.pgAudit v1.5.X is intended to support PostgreSQL 13.pgAudit v1.4.X is intended to support PostgreSQL原创 2022-03-28 16:54:09 · 2630 阅读 · 0 评论 -
Postgresql查询优化相关插件
进行SQL优化之前,我们要找出相应的SQL,如何找,可以通过以下一些插件进行抓取。pg_stat_statements 可以根据执行SQL的时长找出相关查询语句,具体使用可以参考https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache是一个用于检测系统和用户CPU使用比较高的模块。如果你的系统检测CPU使用率比较高,则可以使用该模块来查看是哪些查询导致了这种情况。需要单独下载使用:http://a转载 2022-03-07 15:30:40 · 916 阅读 · 0 评论 -
Postgresql动态共享内存类型
linux为多个进程通信提供了不同的IPC机制,如:System V , POSIX 和 MMAP,所以Postgresql共享内存管理也支持以上类型。在Postgresql中可以使用dynamic_shared_memory_type参数指定共享内存类型。默认使用posix,修改参数后需要重启数据库。dynamic_shared_memory_type = posix # the default is the first option原创 2022-03-02 18:06:47 · 2113 阅读 · 0 评论 -
Postgresql中如何执行动态DDL语句
什么是动态SQL在执行PL/pgSQL函数或SQL时,有时需要生成动态命令,因为命令涉及不同表或数据类型,仅在运行时才能确定具体对象或值。这时就很适合使用动态SQL。这里不过多解释,看下实例就很好理解了。生成动态SQL的几种方式使用 || 进行拼接#查看序列当前使用值并加1postgres=# select 'ALTER SEQUENCE '||sequencename||' RESTART '||last_value+1 from pg_sequences where last_value原创 2022-02-24 11:17:48 · 1683 阅读 · 0 评论 -
Postgresql中TCP keepalive相关设置使用
数据库连接描述提起TCP keepalive相关的设置,就避不开数据库连接这个话题,目前大部分使用环境,数据库连接都是长连接,也就是说连接可以复用。由于数据库建立连接和HTTP连接还不一样,HTTP是无状态的,新建连接的代价比较小,但是和数据库建立连接代价高多了,因为数据库连接不是无状态的;比如,在关闭连接的情况下,将会丢失打开的事务、临时表和prepare语句。而且连接进程都是由postgres主进程fork,fork也有相应的代价,所以数据库有一些空闲会话是正常的,但是保持太长时间大量的空闲会话也是原创 2022-02-22 17:48:39 · 5373 阅读 · 0 评论 -
Postgresql Extended Statistics(自定义统计信息)
正常情况下,数据库只会按单列收集统计信息,无法捕捉到任何关于跨列的相关性信息。扩展统计信息是从10版本引入,可以通过命令 CREATE STATISTICS 创建多列相关的统计信息。因为一个表一般有多个列,多列的任意组合很大,所以自动收集多列统计信息不现实,而我们可以根据自己的需求自己来创建多列的统计信息,这样可以让相关查询执行计划更加准确。下面举例介绍几种扩展统计的几种类型:Functional DependenciesMultivariate N-Distinct CountsMultiva原创 2022-01-25 20:20:13 · 948 阅读 · 0 评论 -
Postgresql主备切换后逻辑复制槽处理的几种方式
到目前为止的版本,PG原生版本不支持逻辑复制槽的failover,这使得以下几种使用情况必须注意:在主备切换之后,需要手工去创建复制槽,而且订阅端极有可能丢失数据,因为创建复制槽的过程会有时间间隙。pg_upgrade进行大版本升级时,slot也会被清理这里我给出几种方式可以进行slot的failover,有喜欢的可以尝试一下可以使用拷贝文件的方式,正常情况standby节点pg_replslot目录下没有文件,需要从主节点拷贝该文件目录到备节点,然后重启备节点即可,在备节点提升为主节点之前,原创 2022-01-17 18:06:03 · 2772 阅读 · 0 评论 -
Postgresql之Covering Indexes介绍
Covering Indexes for B-trees (INCLUDE)从11开始引入Covering indexes for GiST (INCLUDE) 从12开始引入目前仅支持以上类型的索引(B-trees,GiST)。为什么使用覆盖索引呢?覆盖索引可以不需要回表,即减少了IO,使用index-only scan扫描即可拿到需要的数据。通过下图,我们可以比较和普通的B-tree的区别如图:左边是索引,后面是table,最底层的叶子节点,是一个双向链表。查找数据的时候,我们就会遍历这原创 2022-01-11 17:32:58 · 593 阅读 · 0 评论 -
Postgresql存储引擎zheap介绍以及测试
这款存储引擎也是Cybertec公司开发的,目前处于测试阶段,因为Postrgesql的表都是以heap形式存储的,MVCC机制中对删除或者更新的行使用的是根据事物号设置可见或者不可见,所以导致Postgresql中的表在频繁更新和删除的情况下,极易膨胀。而zheap则是类似引入了undo的技术,oracle和mysql就是使用此技术,所以不会有表膨胀的情况。有兴趣的同学可以安装试玩一下,生产千万不要用哦,bug很多。github地址:https://github.com/cybertecpostgr原创 2021-12-30 17:18:50 · 2970 阅读 · 1 评论 -
Postgresql在线分区插件之pg_rewrite使用
在使用数据库的过程中,有一些表开始无法估量大小,后面表变大后,需要做分区表,那么是一件比较烦人的事情,现在好了,有了cybertec公司这款插件,轻松实现。说到这个功能,Oracle从很早就有了,叫在线重定义,而且锁粒度也很小,DML不影响。这个功能还是比较实用的,现在PG终于也有了类似的功能。前置条件:支持13或者13以后的版本设置PG_CONFIG环境变量指向PostgreSQL安装的PG_CONFIG命令make && make install配置postgresql.c原创 2021-12-30 14:00:54 · 1177 阅读 · 3 评论 -
Postgresql之amcheck验证索引完整性
Postgresql提供了该插件验证索引或者是表的逻辑一致性。比如系统升级后,collate和原来不一致,数据库里索引的顺序和该collate不匹配。create extension if not exists amcheck;set statement_timeout to 0;do $$declare r record; sql text; ts_pre timestamptz; e_message text; e_detail text; e_context text;原创 2021-12-20 14:58:26 · 1031 阅读 · 0 评论 -
Postgresql鲜为人知的一些功能(二)
接Postgresql鲜为人知的一些功能(一),继续介绍下面的一些功能。引用$符号如果我们在数据库中存储一段文本,如果文本中有单引号,那么我们需要’来转义。db=# SELECT 'John''s Pizza'; ?column────────────── John's Pizza但是,文本中如果有很多需要的转义字符的时候,那将变的很麻烦,pg提供了$$符号的方法,例子如下:db=# SELECT $$a longstring with new linesand 'single翻译 2021-12-08 16:51:31 · 1305 阅读 · 0 评论 -
Postgresql鲜为人知的一些功能(一)
无意之中看到一篇文章,感觉很有意思,特翻译记录一下,题目写的是鲜为人知,但是对于一些玩PG的老鸟,其实并不鲜为人知。个人感觉文章太长不便阅读,故分为两篇文章描述。目录upsert的时候获取更新和插入的行数列级权限多个模糊匹配在不推进的情况下查找序列的当前值将\copy与多行SQL一起使用主键自动生成的方式生成数据透视表的两种方法$符号的引用数据库对象的注释为每个数据库保留一个单独的psql_history保留关键字大写sleep间隔函数说明获取没有子查询的组中的第一行或最后一行翻译 2021-12-07 20:57:45 · 1124 阅读 · 0 评论 -
Postgresql中使用varchar(n)比varchar()和text节省空间吗?
我们看下PG中的字符类型:名称描述character varying(n), varchar(n)有长度限制的变长字符类型character(n), char(n)定长字符类型,插入字符长度不够,空格补充text没有长度限制的变长字符类型简要说明:varchar(n) 和 char(n) 分别是character varying(n) 和 character(n)的别名,如果不指定n值,那么character varying可以存储任意尺寸的字符串,有点类似于原创 2021-12-02 15:28:13 · 3313 阅读 · 0 评论