自定义博客皮肤VIP专享

*博客头图:

格式为PNG、JPG,宽度*高度大于1920*100像素,不超过2MB,主视觉建议放在右侧,请参照线上博客头图

请上传大于1920*100像素的图片!

博客底图:

图片格式为PNG、JPG,不超过1MB,可上下左右平铺至整个背景

栏目图:

图片格式为PNG、JPG,图片宽度*高度为300*38像素,不超过0.5MB

主标题颜色:

RGB颜色,例如:#AFAFAF

Hover:

RGB颜色,例如:#AFAFAF

副标题颜色:

RGB颜色,例如:#AFAFAF

自定义博客皮肤

-+

Focus on PostgreSQL

PostgreSQL技术爱好者

  • 博客(351)
  • 资源 (4)
  • 收藏
  • 关注

原创 PostgreSQL优化案例——游标与索引选择

为什么会出现这种情况呢,这其实是因为使用游标的SQL会根据cursor_tuple_fraction参数进行自动优化,而该参数默认是0.1,表示只检索前10%的行进行预估,这就和limit有点异曲同工的味道了。因为对于这张表,优化器认为数据是均匀分布的,而实际上,数据分布是不均匀的,c1=200 and c2=200的记录在表的末端。之前有写过一个案例,order by limit因为数据分布不均而选择了错误的索引,这是由于优化器没法判断数据的分布关系,默认认为数据分布是均匀的所导致的。

2022-09-14 17:59:41 217 1

原创 PostgreSQL如何查看带有绑定变量SQL的通用执行计划

当我们在PostgreSQL中分析一些历史的SQL问题时,往往看到的SQL都是带有绑定变量的。不仅如此,对于这些带有绑定变量的SQL,我们甚至没法像在Oracle中一样获取一个预估的执行计划。当然还有点需要注意的,如果估计成本高于先前执行的平均成本时就不会选择通用计划了,所以我们可以人为的控制前5次的平均成本,让其达到一个很高的值,这一点我们可以增加cpu_operator_cost的值来实现。我们似乎只能去通过带入值去获取相应的执行计划了,这对于那些绑定变量很多的SQL来说无疑是十分繁琐的。

2022-09-13 17:47:53 102 2

原创 为什么vacuum full也无法回收空间?

今天在处理个磁盘告警时,对一些膨胀率较高的表进行了vacuum full操作,但是经过漫长的等待之后发现竟然没有回收多少空间,一查死元组竟然没啥变化,坑爹啊!至于原因是啥,我们先来简单了解下vacuum full的过程。我们都知道vacuum full本质上是创建了一张新的表,会创建该表的一个新拷贝,并且在操作完成之前都不会释放旧的拷贝。因此在进行vacuum full操作的时候是会加上一个ACCESS EXCLUSIVE级别的锁,所以一般只有当我们需要从表中回收大量磁盘空间的,即膨胀率很高

2022-03-25 21:55:18 939

原创 长事务与失效的索引查询

最近刚写了一篇文章介绍了下长事务,以及一些长事务常见的危害,如无法及时的垃圾回收导致表膨胀之类的问题,最近刚好又碰到一个问题也是长事务所导致的。上周六早上接到同事电话,说某个库CPU一直很高,看了下全是某张大表的全表扫描导致,但是奇怪的是相关的查询都有用到索引列,不知道为啥查询全部都没走索引。当我连上去查看时发现确实如此,如果只是某个查询不走索引那可能是SQL本身写的有问题,但是这张表相关的所有SQL都不走索引,那自然会想到是索引本身的原因了。那是不是索引失效了呢?经过检查发现这张表上的索引

2022-03-08 09:46:56 183

原创 坑爹的标量子查询

不知道大家在看pg的执行计划时主要关注什么呢,cost还是执行时间?最近碰到一个奇葩的SQL,执行计划里cost显示1.9亿,时间执行时间只有1秒多,大致如下:EXPLAIN ANALYZE select count(*) from (select (-> SELECT-> string_agg(lsd4.itemname, ',') bb-> FROM-> (-> SELECT-> lsd3.itemcode,-> lsd3.itemname-

2022-03-03 15:39:09 200

原创 PostgreSQL什么才算是长事务

我们在很多地方应该都听到过长事务的危害,比方说长事务会导致表膨胀之类的。那么在PostgreSQL中什么才算是长事务呢?首先,在PostgreSQL的官方文档中并没有所谓“长事务”这一定义,似乎大家约定俗称的把一个执行了很长却没有提交的事务认为是“长事务”了,而在不同的数据库中关于长事务的定义往往也不尽相同,那么在PostgreSQL中什么是长事务呢?打个比方,如下所示,我在一个会话中通过begin开启一个事务,然后执行了个简单的查询语句后迟迟不提交,这算不算长事务呢?bill=# begin;BE

2022-03-02 15:05:56 765 4

原创 从一个简单的SQL来聊聊等价改写

今天看到一个库的cpu告警,去看了下top sql就发现了一个奇葩的SQL。不得不吐槽下,都2022年了,还有在索引列上用函数的写法。。其实也很好理解这个SQL是希望查询时间列是当天(current_date)的,但是由于该时间列是timestamp类型的,而current_date的结果是date类型的,所以开发人员将这两个时间用函数进行了截断。那这个SQL该怎么优化呢,可能大部分人第一反应是加个函数索引不就好了,不过说实话函数索引大部分都显得有些多余,尤其是该列上都已经存在一个索

2022-02-16 17:30:22 520

原创 PostgreSQL分析日志连接数

某些时候碰到一些诸如连接风暴之类的问题时,如果数据库没有做相关的监控,我们可以通过分析日志获取连接的情况。下面分享一些PostgreSQL中分析日志中连接数的脚本:–统计一天内每小时的session请求数egrep ‘^2022-02-09’ postgresql-02-09.csv |grep authentication |awk ‘{print $1 " " $2}’ |awk -F: ‘{print $1 }’ |sort |uniq -c–指定的一小时每分钟session请求数e

2022-02-10 14:05:07 375

原创 MatrixDB Mars存储引擎

Mars存储引擎是MatrixDB自研的存储类型。简单测试下其和Greenplum传统的堆表和列存储表的区别。--不同存储引擎对比1、Heap优点:Heap表为传统PostgreSQL数据库提供的存储类型,又称堆表。该类型的表支持大量并发读写、事务、索引等特性。 缺点:表压缩低,查询性能较弱。2、AppendOnly优点:列存储,相较于堆表提供了更高效的I/O和存储,适合于在少量列上计算数据聚集。 缺点:不适合数据会被频繁修改的OLTP场景。3、Mars优点:支持

2022-02-08 10:02:08 458

原创 PostgreSQL误删pg_filenode.map怎么办

今天在网上看到有人问误删pg_filenode.map该如何恢复或者重建,解决这个问题前我们先来了解下pg_filenode.map文件。对于PostgreSQL中的每张表在磁盘上都有与之相关的文件,而这些文件的名字便是relfilenode,我们可以通过pg_class的relfilenode字段去查询。但是有一部分特殊的表我们会发现其对应的该字段为0,官方文档的解释为:0表示这是一个“映射”关系,其磁盘文件名取决于低层状态。那么哪些表的relfilenode字段会是0呢?这些relfilenode为

2022-01-18 11:01:34 526

原创 PostgreSQL常用SQL优化技巧

PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似,因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方,今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。1、标量子查询与filter当一个查询在select和from之间,那么这种子查询就是标量子查询。实际应用中,很多人在写SQL时为了方便会写一堆标量子查询的SQL,在表数据不大时,一般并不会有什么影响,但当数据量较大时,往往会对性能造成巨大影响。因为标量子查询类似于

2022-01-07 16:12:25 3581 2

原创 PostgreSQL 为什么vacuum空表却无法回收空间?

在PostgreSQL的表的空间管理中,有种类似Oracle “高水位”的概念。即如果一张表尾部的page为空,那么可以通过vacuum去回收这部分的空间。因为在PostgreSQL中索引指向的是每行数据的ctid,这个是物理地址,假如我们删除page中在最前面的数据,这个时候如果表大小变小的话,是不是就意味着后面的数据的ctid要往前移动,那此时就会出现一个问题,索引中key对应的ctid不准确了。这里顺便分享个比较实用的脚本,我们都知道使用vacuum full进行磁盘空间回收时需要????2倍的磁

2021-12-29 14:45:35 717

原创 PostgreSQL 神奇的limit

最近碰到这样一个SQL引发的性能问题,SQL内容大致如下:SELECT *FROM t1WHERE id = 999AND (case $1 WHEN 'true' THEN info = $2 ELSE info = $3 end) limit 1;开发反应这条SQL加上limit 1之后过了一段时间从原先的索引扫描变成了全表扫描,一个简单的limit 1为何会产生这样的影响,我只取一条数据不是应该更快了吗?下面我们就从这条SQL开始说起。首先我们先看下这个表结构,

2021-12-23 14:49:10 1102 1

原创 PostgreSQL为什么index only scan不返回ctid

我们都知道在PostgreSQL中使用索引扫描时,是通过索引中存储的ctid去表中得到数据的。同时在PostgreSQL中如果要查询的列都在索引中,我们还可以使用index only scan。既然如此,当我们在查询中用到ctid时,是否还能使用index only scan呢?按理来说是没有问题的,例如在Oracle中:SQL> select rowid,id from t1 where id = 1;---------------------------------------------

2021-12-21 17:19:31 318

原创 PostgreSQL 一则索引失效案例引发的思考

前段时间碰到个奇怪的索引失效的问题,实际情况类似下面这样:bill=# begin;BEGINbill=*# create index idx_t1 on t1(id);CREATE INDEXbill=*# explain select * from t1 where id = 1; QUERY PLAN---------------------------------------------------- Seq Scan on t1 (cost

2021-12-03 16:15:28 1852

原创 PostgreSQL 从HOT到PHOT

1、HOT概述PostgreSQL中,由于其多版本的特性,当我们进行数据更新时,实际上并不是直接修改元数据,而是通过新插入一行数据来进行间接的更新。而当表上存在索引时,由于新插入了数据,那么索引必然也需要同步进行更新,这在索引较多的情况下,对于更新的性能影响必然很大。为了解决这一问题,pg从8.3版本开始就引入了HOT(Heap Only Tuple)机制。其原理大致为,当更新的不是索引字段时,我们通过将旧元组指向新元组,而原先的索引不变,仍然指向旧元组,但是我们可以通过旧元组作为间接去访问到新的元组,

2021-11-15 15:54:39 676

原创 PostgreSQL pg_dropcache修改(兼容pg13)

pg_dropcache可以用来清空shared_buffer,但由于pg多版本的特性,脏页也会直接drop, 所以可能会导致数据不一致,所以不建议在生产环境随便使用。不过有的时候我们做一些测试的时候可能需要清空缓存,那倒是可以用pg_dropcache来试试。但是比较可惜,这个插件只支持到pg12,pg13开始就不支持了,因为在pg13中DropRelFileNodeBuffers这个函数做了调整。PG12:extern void DropRelFileNodeBuffers(RelFileNod

2021-10-29 16:25:49 197

原创 PostgreSQL子查询别名改造(兼容Oracle)

在PostgreSQL中子查询必须得加上别名,即使我们在其它地方不会再引用到这个别名。否则便会报以下错误:postgres=# select * from (select * from t1 limit 5);ERROR: subquery in FROM must have an aliasLINE 1: select * from (select * from t1 limit 5); ^HINT: For example, FROM (SELE

2021-10-27 21:24:44 982

原创 VSCode调试PostgreSQL配置

昨天折腾了很久才配置好,这里简单记录下。打开lauch.json菜单 View -> Command Palette,输入launch,选择 Debug: Open launch.json选择 C++ (GDB/LLDB)编辑 launch.json 文件注意:根据你的PG环境,修改相关的路径(建议写成绝对路径)GDB配置:{ "version": "0.2.0", "configurations": [ { "name": "(gdb) 附加

2021-10-14 10:28:24 633

原创 PostgreSQL存储过程中事务与捕获异常的问题

PostgreSQL11开始支持了存储过程的写法,同时也允许了在存储过程中嵌入事务。今天刚好碰到一个相关的问题。do language plpgsql $$BEGIN FOR i IN 0..9 LOOP INSERT INTO t1(id) VALUES (i); IF i % 2 = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOO

2021-09-27 21:24:50 1455

原创 PostgreSQL如何获取系统视图/函数对应的源码

我们在使用PostgreSQL的时候可能经常会碰到这样的情况,对于某个系统视图的某个字段不知道是干啥的,看了官方文档后也一脸懵逼,不知道是啥意思。这个时候如果我们可以看到该视图或者该字段对应的源码,那么想要理解起来就会方便多了。以pg_stat_database这个视图为例,它的两个字段tup_returned和tup_fetched的官方文档解释如下:tup_returned:这个数据库中查询返回的行数;tup_fetched:这个数据库中查询获取的行数。看完估计你也和我一样懵逼,返回、获取

2021-09-13 14:38:59 869

原创 PostgreSQL自定义函数实现十六进制转十进制

在PostgreSQL我们可以直接通过下面的方式来将十六进制转换成十进制:bill@bill=>select x'ab'::int; int4------ 171(1 row)但是这仅仅只能针对某个固定的字符串,如果是下面这种情况就没办法直接转换了:bill@bill=>select x(substring('abcd',1,2))::int;ERROR: function x(text) does not existLINE 1: select x(substring(

2021-08-24 13:30:00 1240

原创 PostgreSQL中wal日志具备幂等性吗?

数据库日志与幂等性最近在看《The Internals of PostgreSQL》这本书时发现这样一段话:In short, the redo (replay) operation of non-backup block is not idempotent. Therefore, to preserve the correct replaying order, non-backup block records should replay if and only if its LSN is greate

2021-08-16 10:49:22 1535 2

原创 PostgreSQL Buffer Manager与hash算法

1、概述为了加速数据库对数据的访问,我们需要通过buffer cache来将磁盘的数据块缓存,那么在PostgreSQL中是如何对buffer进行管理的呢?说的直接点,我要在buffer中访问某个page,数据库怎么去判断buffer中是否存在呢,如果存在又是怎么定位到这个page呢?很简单,通过hash算法。在数据库中似乎hash算法随处可见,hash索引、hash连接等等。之所以使用hash算法,自然是因为其速度快、效率高了。在PostgreSQL几乎内存管理中所用的都是hash算法。在介绍Pos

2021-08-02 15:39:13 2703

原创 PostgreSQL t_bits计算方法

在PostgreSQL的page头部使用t_bits来表示null值的列。定义如下:bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */之前看的时候以为其长度就是8个bit位,用1个字节来存储。今天和别人聊的时候才发现如果只是8个bit位那完全不够啊,pg中表最多允许1600列,那不是应该需要1600位?于是建个70列的表测试下看看:bill@bill=>create table t1(bill(# c1 int, c2

2021-07-28 14:48:28 3114

原创 PostgreSQL 从cmin/cmax到combo cid

cmin和cmax介绍cmin和cmax是PostgreSQL中表的系统字段之一,用来判断同一个事务内的其他命令导致的行版本变更是否可见。即在事务中每个命令都应该能看到其之前执行的命令的变更。很多人都通过测试都会发现在同一张表中cmin和cmax总是相等的,所以认为这两个是同一个概念,其实准确来说这两者的含义并不相同:cmin:插入事务中的命令标识符(从0开始)。cmax:删除事务中的命令标识符(从0开始)。简单来说,cmin和cmax都是表示tuple的command id,即cmin是产生

2021-07-27 11:30:39 3012

原创 PostgreSQL如何计算age

今天看到一个问题,我们在查看对象的age时,为什么索引这类对象的age总是2147483647:bill@bill=>select relname,age(relfrozenxid) from pg_class where relname in ('t1','idx_t1'); relname | age---------+------------ t1 | 673 t1 | 666 t1 | 665 t1

2021-07-26 19:56:05 1712

原创 从数据库启动日志看PostgreSQL的崩溃恢复

背景今天碰到朋友问我个问题,数据库启动时日志中的这个“invalid record length at 3/EAA68B8: wanted 24, got 0”里面的wanted 24是啥意思。而数据库正常启动时的日志其实并不是上面那样,而是如下图所示:上图的日志是因为数据库异常关闭后重启导致的,例如进程直接被kill。为了搞清楚上面日志中的那条记录是啥意思,我们来一起研究下数据库异常关闭后恢复的过程。崩溃恢复概述首先我们要清楚数据库异常关闭和正常的停库有啥区别。当数据库异常关闭时,数据库的

2021-07-21 16:43:19 2452

原创 PostgreSQL OOM最佳实践

OOM是Linux中一个比较常见的情况,PostgreSQL数据库触发OOM现象就是数据库进程被KILL了。OOM发生的原因有很多,这里我们从OOM的产生以及如何在PostgreSQL中预防OOM发生来进行研究。OOM介绍什么是OOM?OOM(out-of-memory),顾名思义就是内存溢出了,之所以会出现这种情况和内存分配的overcommit有关。Linux为了保证在有限的内存中尽可能多的运行更多的进程,在内存分配策略中提出了overcommit的策略,即允许内存溢出。之所以可以这么做是因为我

2021-07-21 15:33:12 5083 1

原创 简单聊聊PostgreSQL buffer与OS cache

0、概述缓存可以说是数据库中相当重要的一部分,很多性能相关的问题都与之息息相关。那么我们今天就聊聊在PostgreSQL中的缓存。1、为什么需要缓存?在数据库中似乎我们最关心的是磁盘IO,经常会听到数据库IO存在瓶颈之类的问题。这也是为什么在数据库中我们需要缓存。打个比方,对于计算机而言,1个CPU周期是0.3ns,如果把这个时间当成是我们平时生活中的1s。那么固态硬盘1次IO的时间大约是50-150μs,这意味着什么呢?相当于我们的2到6天。这么一对比就可想而知,哪怕你的磁盘IO再快,相较于在内

2021-07-19 14:36:01 4886 1

原创 PostgreSQL——真的非要无脑关闭NUMA吗?

相较于Oracle,pg对操作系统的依赖要高很多,因此对于pg的调优很大一部分都在操作系统层面。最近刚好有看到别人聊关于numa这个话题,那么我们就一起来看看pg中numa该如何使用吧。很多有经验的DBA对于numa的建议都是直接关掉,Oracle中更是建议如此,似乎这已经成为大家一种约定俗成的习惯了。对于大部分情况来说的确如此,但是我们还是得弄清楚为啥这么做,当然你也可以说我不管什么系统我就无脑统统关掉numa,那我就只能什么是NUMA?在单CPU时代,CPU与内存的交互大致如下:随着多CP

2021-07-16 14:19:09 5047 1

原创 PostgreSQL copy协议与事务

今天看到有人问“PostgreSQL中的copy命令是在同一个事务中执行的吗”。其实我想之所以提出这个问题,主要是想知道在执行copy语句时如果出现错误导致中断,那么数据是会导出/入部分,还是会像事务一样回滚。为了解答这个问题,我们先来简单了解下copy协议。什么是copy协议?我们使用客户端和数据库进行交互时,都必须要遵守PostgreSQL数据库的通信协议才可以。让我们比较熟悉的协议有TCP/IP 协议和 HTTP 协议等。而PostgreSQL在TCP/IP 协议之上实现了一套基于消息的通信协议

2021-07-14 11:27:56 5675 1

原创 PostgreSQL 切勿kill -9去杀进程

熟悉Oracle的人可能会去经常使用kill -9杀进程,因为有的时候使用SQL命令去杀进程,一些进程的状态只是会被置为"killed"状态,其锁定的资源仍然没有释放,可能会收到提示:ORA-00031: session marked for kill因此甚至还建议大家去使用kill -9直接去操作系统上杀掉进程。又比如我们Oracle停库的时候常常都是先停监听,再手动kill掉LOCAL=NO的进程,然后再去停库。但是要注意了,切勿在PostgreSQL中使用kill -9去杀掉进程!首先我们

2021-07-13 11:35:50 2551

原创 PostgreSQL授权普通用户kill会话权限

作为DBA,可能经常回听到开发人员说“我有个SQL执行太久了,帮我kill下”“我有张表好像锁住了,帮我kill下”。时间久了确实挺烦的,此时我们肯定想的是让他们自己去kill,但是又不能直接给开发或者用户超级用户的权限,那么怎么只授权普通用户kill会话的权限呢?–方法1:PostgreSQL9.6开始,新增了默认角色pg_signal_backend,这个角色具有 cancel query、terminate 其它会话的权限。例子:bill@bill=>create user user01

2021-07-09 15:55:41 2173

原创 PostgreSQL如何对URL进行解析

尽管PostgreSQL中支持大量的数据类型,但是对于URL似乎并没有一个相应的类型能够去存储。那么对于URL的数据我们在数据库中要怎么去处理呢?首先还是需要看你要存储的URL数据是用来干嘛的,如果仅仅只是为了在以后查询的时候打印输出,那么用varchar或者text之类的类型不是就可以了吗?但是有的时候我们对于这些URL数据并不仅仅是简单的查询,我们可能希望通过这些URL能够查询一些额外的信息,例如URL的使用的协议、主机名等等。这里我们便可以使用ts_debug函数来进行解析了。函数ts_debu

2021-07-09 14:03:44 2177

原创 PostgreSQL获取用户下所有对象

有时我们需要对数据库用户下的对象进行审计时,可以使用下面脚本直接获取用户下所有对象。查看postgres用户下所有对象:select nsp.nspname as SchemaName ,cls.relname as ObjectName ,rol.rolname as ObjectOwner ,case cls.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW

2021-07-08 11:22:09 2352 1

原创 PostgreSQL如何修改表的字段顺序

MySQL中我们可以使用类似下列的语句,在某个指定的字段后添加字段:alter table test add column c1 int after id; 那么在PostgreSQL是否可以实现类似的功能呢?或者说修改表当前的字段顺序呢?比较简单的方法就是将表删除后重建。还有一种比较常见的方法就是通过视图来代替,例如:bill@bill=>create view v_t1 as select c1,c3,c2 from t1;CREATE VIEWbill@bill=>sel

2021-07-05 10:09:31 6642

原创 PostgreSQL index monitor——监控表上索引使用频率

我们需要去监控哪些大表的查询并没有去使用索引:SELECT relname, CASE idx_scan WHEN 0 THEN 'Insufficient data' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY

2021-07-05 09:21:52 2415

原创 源码探究PostgreSQL中的Fast Path Locking

1、概述pg_locks视图中有个fastpath字段,官方文档中对其解释是“True if lock was taken via fast path, false if taken via main lock table”,那么我们不禁要问,什么样的lock是通过fast path获得的?这个fast path lock又是什么呢?首先我们要知道,在数据库启动的时候会初始化共享内存区域,共享内存是一个统称,实际上有很多共享内存区,比如锁也是一块。这也就决定了锁其实是数据库中很宝贵的一部分资源,在pg中

2021-07-01 15:59:51 2271

原创 聊聊PostgreSQL中的VFD机制

1、什么是文件描述符在操作系统中,为了高效地管理文件,当进程打开或创建一个文件时,操作系统会为该文件分配一个文件描述符(文件句柄),通过该文件描述符来唯一标识该文件,其本质上就是用来管理文件的索引。理论上来说,我们的操作系统内存有多少就可以打开多少的文件描述符,但实际上一般最大打开文件数会是系统内存的10%(以KB来计算),我们可以通过sysctl -a | grep fs.file-max命令查看。同时为了防止单个进程过度使用文件描述符,也会对单个进程进行限制,可以通过ulimit -n查看,一般默

2021-06-30 15:06:55 2135

PostgreSQL内核扩展入门.pdf

PostgreSQL内核扩展入门

2021-03-08

PostgreSQL awr

PostgreSQL中实现获取Oracle awr报告的类似功能

2020-10-23

达梦数据库常见问题FAQ_V1.0.docx

达梦数据库常见问题介绍,学习达梦常见的问题汇总及问题解答

2020-05-27

PostgreSQL11.2-CN-v1.0.pdf

PostgreSQL11.2中文文档

2020-04-29

空空如也

TA创建的收藏夹 TA关注的收藏夹

TA关注的人

提示
确定要删除当前文章?
取消 删除