- 博客(356)
- 资源 (4)
- 收藏
- 关注
RSS订阅原创 PostgreSQL中int类型达到上限的一些处理方案
使用int类型作为表的主键在pg中是很常见的情况，但是pg中int类型的范围在-2147483648到2147483647，最大只有21亿，这个在一些大表中很容易就会达到上限。一旦达到上限，那么表中便没办法在插入数据了，这个将会是很严重的问题。
2024-02-27 17:15:29
1174
原创 PostgreSQL12中浮点数输出算法优化带来的小问题
乍一看其实能看出明显的区别，由于::符号的优先级是高于*的，所以没加括号导致结果不同。这个是什么意思呢，由于前面我们输出的是float类型，当extra_float_digits=1时最多保留17位，但是由于新的浮点数输出算法，只要二进制相同，那么只保留到能得到该二进制的最小位数即可。换句话说，12.839999999999998和12.84的二进制值是一样的，大家可以计算下，都是1100.110101110000101001，也就是说对于该数而言，两者是相等的。
2023-10-24 11:31:28
475
原创 pg_dump的一些小技巧
今天在微信群里看到有人在问，pg_dump能不能像Oracle一样从列表中读取指定的表名然后导出呢？尽管pg_dump支持导出的-t选项进行模糊匹配，也支持-T来排序不需要导出的表。但实际应用中我们往往经常会碰到需要碰到比方说用户给了一个表清单，说要把这些表都导出来，这种时候如果一张张去匹配，不仅工作量大，而且pg_dump的命令也会很长。
2023-03-13 17:06:29
1682
1
原创 Endless lseek导致的SQL异常
可以看到卡住的SQL拿出来单独执行是没有问题的，但是仔细观察可以发现，该执行计划中很多never executed的节点，说明下面的这些部分实际都没有执行，那是因为pgawr_names这张表是空的，因此对于这个嵌套循环来说，被驱动的部分其实就没有必要执行了。回头再去看BUG #15455: Endless lseek，其实也是类似的原因，由于在会话中表数据量变化较多，而统计信息无法更新，同时SQL中有嵌套循环，导致性能异常，陷入了endless lseek中。哎，没有办法，只能自己接着分析了。
2023-02-22 17:22:01
880
原创 PostgreSQL中group by的“陷阱”
其实想想这也是有道理的，如果group by中是主键，那么必然该行是唯一的，group by之后即使不加上后面的其它列，仍然是固定的分组。这点看上去不符合SQL语法规范的，其实是PostgreSQL中对于group by子句的优化，具体可以参考remove_useless_groupby_columns函数。第一感就是检查下两边的表结构是否一致，发现果然有些不同，测试环境该表的id字段是唯一约束，而生产环境该id字段则是主键。发现其实并非如此，即使和主键的效果是等价的，但语法上就会报错了。
2022-11-01 11:14:41
1582
原创 PostgreSQL优化案例——游标与索引选择
为什么会出现这种情况呢，这其实是因为使用游标的SQL会根据cursor_tuple_fraction参数进行自动优化，而该参数默认是0.1，表示只检索前10%的行进行预估，这就和limit有点异曲同工的味道了。因为对于这张表，优化器认为数据是均匀分布的，而实际上，数据分布是不均匀的，c1=200 and c2=200的记录在表的末端。之前有写过一个案例，order by limit因为数据分布不均而选择了错误的索引，这是由于优化器没法判断数据的分布关系，默认认为数据分布是均匀的所导致的。
2022-09-14 17:59:41
837
1
原创 PostgreSQL如何查看带有绑定变量SQL的通用执行计划
当我们在PostgreSQL中分析一些历史的SQL问题时，往往看到的SQL都是带有绑定变量的。不仅如此，对于这些带有绑定变量的SQL，我们甚至没法像在Oracle中一样获取一个预估的执行计划。当然还有点需要注意的，如果估计成本高于先前执行的平均成本时就不会选择通用计划了，所以我们可以人为的控制前5次的平均成本，让其达到一个很高的值，这一点我们可以增加cpu_operator_cost的值来实现。我们似乎只能去通过带入值去获取相应的执行计划了，这对于那些绑定变量很多的SQL来说无疑是十分繁琐的。
2022-09-13 17:47:53
695
2
原创 为什么vacuum full也无法回收空间？
今天在处理个磁盘告警时，对一些膨胀率较高的表进行了vacuum full操作，但是经过漫长的等待之后发现竟然没有回收多少空间，一查死元组竟然没啥变化，坑爹啊！至于原因是啥，我们先来简单了解下vacuum full的过程。我们都知道vacuum full本质上是创建了一张新的表，会创建该表的一个新拷贝，并且在操作完成之前都不会释放旧的拷贝。因此在进行vacuum full操作的时候是会加上一个ACCESS EXCLUSIVE级别的锁，所以一般只有当我们需要从表中回收大量磁盘空间的，即膨胀率很高
2022-03-25 21:55:18
2138
1
原创 长事务与失效的索引查询
最近刚写了一篇文章介绍了下长事务，以及一些长事务常见的危害，如无法及时的垃圾回收导致表膨胀之类的问题，最近刚好又碰到一个问题也是长事务所导致的。上周六早上接到同事电话，说某个库CPU一直很高，看了下全是某张大表的全表扫描导致，但是奇怪的是相关的查询都有用到索引列，不知道为啥查询全部都没走索引。当我连上去查看时发现确实如此，如果只是某个查询不走索引那可能是SQL本身写的有问题，但是这张表相关的所有SQL都不走索引，那自然会想到是索引本身的原因了。那是不是索引失效了呢？经过检查发现这张表上的索引
2022-03-08 09:46:56
434
原创 坑爹的标量子查询
不知道大家在看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
405
原创 PostgreSQL什么才算是长事务
我们在很多地方应该都听到过长事务的危害，比方说长事务会导致表膨胀之类的。那么在PostgreSQL中什么才算是长事务呢？首先，在PostgreSQL的官方文档中并没有所谓“长事务”这一定义，似乎大家约定俗称的把一个执行了很长却没有提交的事务认为是“长事务”了，而在不同的数据库中关于长事务的定义往往也不尽相同，那么在PostgreSQL中什么是长事务呢？打个比方，如下所示，我在一个会话中通过begin开启一个事务，然后执行了个简单的查询语句后迟迟不提交，这算不算长事务呢？bill=# begin;BE
2022-03-02 15:05:56
2024
6
原创 从一个简单的SQL来聊聊等价改写
今天看到一个库的cpu告警，去看了下top sql就发现了一个奇葩的SQL。不得不吐槽下，都2022年了，还有在索引列上用函数的写法。。其实也很好理解这个SQL是希望查询时间列是当天(current_date)的，但是由于该时间列是timestamp类型的，而current_date的结果是date类型的，所以开发人员将这两个时间用函数进行了截断。那这个SQL该怎么优化呢，可能大部分人第一反应是加个函数索引不就好了，不过说实话函数索引大部分都显得有些多余，尤其是该列上都已经存在一个索
2022-02-16 17:30:22
893
原创 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
633
原创 MatrixDB Mars存储引擎
Mars存储引擎是MatrixDB自研的存储类型。简单测试下其和Greenplum传统的堆表和列存储表的区别。--不同存储引擎对比1、Heap优点：Heap表为传统PostgreSQL数据库提供的存储类型，又称堆表。该类型的表支持大量并发读写、事务、索引等特性。 缺点：表压缩低，查询性能较弱。2、AppendOnly优点：列存储，相较于堆表提供了更高效的I/O和存储，适合于在少量列上计算数据聚集。 缺点：不适合数据会被频繁修改的OLTP场景。3、Mars优点：支持
2022-02-08 10:02:08
793
原创 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
1094
原创 PostgreSQL常用SQL优化技巧
PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似，因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方，今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。1、标量子查询与filter当一个查询在select和from之间，那么这种子查询就是标量子查询。实际应用中，很多人在写SQL时为了方便会写一堆标量子查询的SQL，在表数据不大时，一般并不会有什么影响，但当数据量较大时，往往会对性能造成巨大影响。因为标量子查询类似于
2022-01-07 16:12:25
6565
2
原创 PostgreSQL 为什么vacuum空表却无法回收空间？
在PostgreSQL的表的空间管理中，有种类似Oracle “高水位”的概念。即如果一张表尾部的page为空，那么可以通过vacuum去回收这部分的空间。因为在PostgreSQL中索引指向的是每行数据的ctid,这个是物理地址,假如我们删除page中在最前面的数据,这个时候如果表大小变小的话,是不是就意味着后面的数据的ctid要往前移动,那此时就会出现一个问题,索引中key对应的ctid不准确了。这里顺便分享个比较实用的脚本，我们都知道使用vacuum full进行磁盘空间回收时需要????2倍的磁
2021-12-29 14:45:35
1296
原创 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
3326
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
484
原创 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
3801
原创 PostgreSQL 从HOT到PHOT
1、HOT概述PostgreSQL中，由于其多版本的特性，当我们进行数据更新时，实际上并不是直接修改元数据，而是通过新插入一行数据来进行间接的更新。而当表上存在索引时，由于新插入了数据，那么索引必然也需要同步进行更新，这在索引较多的情况下，对于更新的性能影响必然很大。为了解决这一问题，pg从8.3版本开始就引入了HOT（Heap Only Tuple）机制。其原理大致为，当更新的不是索引字段时，我们通过将旧元组指向新元组，而原先的索引不变，仍然指向旧元组，但是我们可以通过旧元组作为间接去访问到新的元组，
2021-11-15 15:54:39
949
原创 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
601
原创 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
3315
原创 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
1388
原创 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
3828
原创 PostgreSQL如何获取系统视图/函数对应的源码
我们在使用PostgreSQL的时候可能经常会碰到这样的情况，对于某个系统视图的某个字段不知道是干啥的，看了官方文档后也一脸懵逼，不知道是啥意思。这个时候如果我们可以看到该视图或者该字段对应的源码，那么想要理解起来就会方便多了。以pg_stat_database这个视图为例，它的两个字段tup_returned和tup_fetched的官方文档解释如下：tup_returned：这个数据库中查询返回的行数；tup_fetched：这个数据库中查询获取的行数。看完估计你也和我一样懵逼，返回、获取
2021-09-13 14:38:59
1464
原创 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
2234
原创 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
1699
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
3002
原创 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
3241
原创 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
3294
原创 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
2032
原创 从数据库启动日志看PostgreSQL的崩溃恢复
背景今天碰到朋友问我个问题，数据库启动时日志中的这个“invalid record length at 3/EAA68B8: wanted 24, got 0”里面的wanted 24是啥意思。而数据库正常启动时的日志其实并不是上面那样，而是如下图所示：上图的日志是因为数据库异常关闭后重启导致的，例如进程直接被kill。为了搞清楚上面日志中的那条记录是啥意思，我们来一起研究下数据库异常关闭后恢复的过程。崩溃恢复概述首先我们要清楚数据库异常关闭和正常的停库有啥区别。当数据库异常关闭时，数据库的
2021-07-21 16:43:19
3523
原创 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
5355
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
5159
1
原创 PostgreSQL——真的非要无脑关闭NUMA吗？
相较于Oracle，pg对操作系统的依赖要高很多，因此对于pg的调优很大一部分都在操作系统层面。最近刚好有看到别人聊关于numa这个话题，那么我们就一起来看看pg中numa该如何使用吧。很多有经验的DBA对于numa的建议都是直接关掉，Oracle中更是建议如此，似乎这已经成为大家一种约定俗成的习惯了。对于大部分情况来说的确如此，但是我们还是得弄清楚为啥这么做，当然你也可以说我不管什么系统我就无脑统统关掉numa，那我就只能什么是NUMA？在单CPU时代，CPU与内存的交互大致如下：随着多CP
2021-07-16 14:19:09
5394
1
原创 PostgreSQL copy协议与事务
今天看到有人问“PostgreSQL中的copy命令是在同一个事务中执行的吗”。其实我想之所以提出这个问题，主要是想知道在执行copy语句时如果出现错误导致中断，那么数据是会导出/入部分，还是会像事务一样回滚。为了解答这个问题，我们先来简单了解下copy协议。什么是copy协议？我们使用客户端和数据库进行交互时，都必须要遵守PostgreSQL数据库的通信协议才可以。让我们比较熟悉的协议有TCP/IP 协议和 HTTP 协议等。而PostgreSQL在TCP/IP 协议之上实现了一套基于消息的通信协议
2021-07-14 11:27:56
6238
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
3587
原创 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
2434
原创 PostgreSQL如何对URL进行解析
尽管PostgreSQL中支持大量的数据类型，但是对于URL似乎并没有一个相应的类型能够去存储。那么对于URL的数据我们在数据库中要怎么去处理呢？首先还是需要看你要存储的URL数据是用来干嘛的，如果仅仅只是为了在以后查询的时候打印输出，那么用varchar或者text之类的类型不是就可以了吗？但是有的时候我们对于这些URL数据并不仅仅是简单的查询，我们可能希望通过这些URL能够查询一些额外的信息，例如URL的使用的协议、主机名等等。这里我们便可以使用ts_debug函数来进行解析了。函数ts_debu
2021-07-09 14:03:44
2743
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人