![](https://img-blog.csdnimg.cn/20201014180756754.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
PostgreSQL
howard_shooter
交流微信:17625988619
展开
-
pgvector 索引的混合查询
当 where 的条件查询出的记录多到一定程度时(估计selectivity不同了),优化器便会选择另一个执行计划,如下图,会先按照向量索引查询出最近的若干向量,然后再对这些最邻近向量所在行,按其它字段进行条件过滤,这种情况使用了向量索引。在上面的查询中,返回的最邻近向量数只有一个,没有达到5个的需求,在不改变执行计划的前提下,可以增大where的查询范围,即按照向量索引查询后,过滤条件放宽一些,或许可以得到更多结果,然后再用limit选取头几条。可以看到,这里并没有使用pgvector所特有的向量索引。原创 2024-05-17 14:59:51 · 233 阅读 · 0 评论 -
通过TID查询记录
PostgreSQL的rowid是TID,包括这条记录在表中的页号和页内偏移(item pointer索引),用形如(0,35)的格式给出,在PG中,可以直接用TID查询出记录,例如:其中 ctid 就是指这一条记录的 tid,也可以在查询时显示ctid:原创 2024-05-17 14:03:02 · 91 阅读 · 0 评论 -
PG的事务ID回卷逻辑
PG的处理方法,简单的说,就是在事务ID还没用完以前,把数据库中所有的tuple处理一遍,将以前的事务(不活跃的事务)修改的(包括插入)tuple中的事务ID改为2(或设置infomask),表示这个tuple对于以后的事务(不管是多少的事务ID)都是可见的,即freeze。按照上面代码的算法,对于任何事物ID,例如100,如果另一个事务ID比它大,但是没有超过这个事务ID后的半圆,例如2^31+100,就认为是在它的后面,那么事务100的tuple,对事务2^31+100就是可见的。原创 2024-05-10 15:14:26 · 372 阅读 · 0 评论 -
valgrind 是个啥?
m->msg开始sizeof (struct rawmsg)大小的内存,标记为未定义(初始化)的内存,如果没有这个宏,memcheck仿真cpu会认为这块内存是已经被初始化的,因为前面调用了memset,而memcheck仿真cpu会监视memset,这个宏在需要重用一块内存时使用。当然,不可能真的为进程地址空间的每个字节,分配记录其属性的bitmap,因为这些bitmap本身也占用地址空间的字节,memcheck只记录被客户程序触及到的进程地址空间字节的属性,未触及到的字节的属性,应该有办法压缩表示。原创 2024-04-09 11:21:44 · 922 阅读 · 0 评论 -
编写 PG extension 时,work_mem 和 maintenance_work_mem的作用
从PG内核编程的角度看,其实backend进程在做上述操作时,也可以不参考这两个参数,而是直接按需分配内存,但是这样,有可能单个backend进程占用的内存太大,把整个计算机的内存都占满了,所以DBA要对每个backend进程占用的内存的上限,做一个限制,当达到这个限制,就采取其它策略,或执行失败,就像单个进程的out of memory。这两个参数在官网都有描述,都是一个backend进程在做工作时,需要用到的内存的参考,也都是给每个backend自己用做参考的,不是全局的。原创 2024-04-08 15:46:43 · 232 阅读 · 0 评论 -
PG 的 Extension 是个啥?
以前,对于编写PG的Extension一直有一种神秘感,虽然知道PG的Extension,除了SQL脚本创建一堆PG对象外,最难以捉摸的就是so库了,但是对于这个so库到底可以做到哪些,还是不明朗,到底这个扩展能让PG扩展成什么样?是想把PG改成什么样都可以吗?在执行 CREATE EXTENSION XXX 时,PG会先加载so文件,然后执行sql脚本,加载到内存的so文件就是PG内核的一部分了,其中(so)的代码基本上就可以调用PG内核的任何接口函数,使用PG内核提供的基础设施了。原创 2024-04-07 17:20:58 · 209 阅读 · 0 评论 -
pg_config.h
与pg_congfig.h对应的,还有一个pg_config_manual.h,这个不是configure生成的,一般用于developer调试,里面也可以设置一些编译后不可变的或默认的内核配置。既然是编译时设置,通过修改configure,也可以将git commit的版本号加进去,这样数据库运行时可以查到可执行文件对应的代码版本。这个头文件是编译PG时configure生成的,configure命令行中的设置,可以反映到这个文件中的宏定义。一般用于一些编译后不可变或者默认的PG内核配置。原创 2024-04-07 16:53:29 · 129 阅读 · 0 评论 -
PG 中的 MAXALIGN 及对齐分配内存(MemoryContextAllocAligned)
它的实现原理也颇为简单,简单地说就是分配略大于 size + alignto 的一块内存,这样,无论返回的地址是否按照 alignto对齐,在这块内存中总能找到alignto对齐的地址,而且这个对齐地址其后面,可用内存一定大于需要的内存size。有时内存地址的对齐,需要大于MAXALIGN,这时可以用 BUFFERALIGN,它返回最接近输入数字(大于)且能整除32的数。在PG源码中,MAXALIGN这个宏,返回最接近输入数字(大于)且能整除8的数,仅此而已。“CPU访问对齐的地址性能更高”原创 2024-04-07 16:39:12 · 259 阅读 · 0 评论 -
给 PostgreSQL 内核源码增加参数
最近一个工作,需要给PG增加一个参数,作为删除输入字符串中 \0 的开关,研究了一下怎么给内核增加参数。其实如果不深究PG内核参数的整个体系架构,增加参数一点也不难,下面记录一下之前的工作(写给自己看,细节不再详述):1. 在你自己维护的PG模块的c文件里增加一个全局变量,这个变量就是用来存储参数值的。例如,我在postgres.c里增加了变量:/* GUC variable for removing '\0' in bind text string*/bool bind_t...原创 2022-01-29 14:59:05 · 1079 阅读 · 0 评论 -
什么是 MultiXactId?
并发事务实现中,有时某个事务需要对上万行加锁,此时锁对象不适于放在内存中,PG采取的策略是,将加锁事务的ID设置到被锁记录的xmax中,同时设置t_infomask表示记录被锁,以此表示记录被锁住(共享锁或互斥锁)。但是,当多个事务都要对某行记录,加共享锁时,又怎样表达呢?上述的机制,看起来没法表示多个事务对记录加共享锁的情况。解决办法就是,用一个特殊的事务ID(MultiXactId)代替所有加锁的事务ID,然后建立MultiXactId与这些事务ID的对应关系。例如:对于上图的场景,如果原创 2021-09-14 11:02:09 · 566 阅读 · 0 评论 -
hybrid hash join with skew
两趟hybrid hash join在前面的算法基础上有一点点改进,就是对第一个表分区的时候,第一个表的第一个分区留在内存,不写到磁盘,因为计算写到磁盘,稍后还要从磁盘读到内存,于是干脆留在内存,节省了IO,但是第一个表的其它分区还是要写到磁盘的,因为内存不够,然后第二个表做hash分区时,对于每个tuple,先看看是不是hash映射到第一个表的第一个分区(驻留在内存的),如果是,尝试与这个分区里的记录JOIN(具体怎么JOIN就简单了),如果JOIN成功就输出,JOIN不成功就抛弃。原创 2023-07-18 15:14:31 · 165 阅读 · 0 评论 -
在 PostgreSQL Extension 中使用 MemoryContext
MemoryContext可以理解成是一种类,整个backend进程,以它为节点连城一个树型结构,没个模块的内存分配创建一个MemoryContext,挂到backend的树中,然后这个模块所以需要的内存都在这个MemoryContext里分配,当这个模块的生命周期结束,就销毁这个MemoryContext,在其中分配的内存也全部被释放,如果这个MemoryContext由子树,子树所管理的内存也一起释放。原创 2024-04-07 13:52:25 · 173 阅读 · 0 评论 -
使用debezuim,实现PG同步Oracle
还可以将逻辑变更读出,给自己写的应用程序,例如jdbc就有逻辑复制功能,debezuim就是利用这个功能。jdbc的逻辑复制其实是利用了logical decoding功能,需要一个插件,调用插件时向它传参数,每个插件的参数会不同。使用wal2json直接decode出来的,是完整的数据,但是到了debezuim,就只有很短的字符串了。逻辑复制有一个限制是,不能捕获大对象变更,但是这里不要误会,表中的bytea列不是大对象,这种列可以存1g的数据,但它不算大对象,大对象是另一种概念。原创 2024-02-05 11:40:52 · 428 阅读 · 0 评论 -
LSM树适合磁盘的NoSQL,B+树适合SSD
如果用SSD那么B+树的随机读取的性能问题就没有了,尤其是有了持久内存,B+树将是更适合OLTP场景的数据结构。如果数据持久化到磁盘,LSM树的读写性能要比B+树要好,但是在事务约束的情况下,LSM的是否存在性能问题?原创 2023-12-04 17:21:05 · 131 阅读 · 0 评论 -
PostgreSQL basebackup备份和恢复
备份和恢复分为逻辑和物理,这里指物理备份和恢复。PG的物理备份依赖basebackup,这差不多就是数据目录的拷贝,还依赖归档日志。恢复分为完全恢复和PITR恢复,它们都需要归档日志,它们关键的差别是,PITR需要设置postgresql.conf中的参数recovery_target_time,还有PITR时,PG服务器恢复完后是只读状态,需要手动执行select pg_wal_replay_resume(),这样,其实是让管理员确认,恢复是否满足期望。原创 2023-10-27 17:14:27 · 553 阅读 · 0 评论 -
vacuum full table释放表占用的空间
表经过一系列删改操作后,虽然空间可以被重复使用,但空间并不一定释放,给操作系统,vacuum full tablename,可以对表的空间进行碎片整理和释放,但是这样会锁住表。原创 2023-10-27 14:14:11 · 356 阅读 · 0 评论 -
pg_rman 的编译和使用
下载地址:代码由日本电信的运维团队维护。针对不同的PostgreSQL版本,使用不同的分支编译,我的PG版本试12.6,对应的分支为REL_12_STABLE。pg_rman的编译要依赖PG的源码,或者是包含PG头文件和库文件的PG安装包,我之前从源码编译安装了PG,所以只要设置了PATH和LD_LIBRARY_PATH就行,本质上,编译pg_rman时,是使用了PG安装文件中的pg_config,来设置头文件和库文件的编译选项。原创 2023-10-07 18:21:32 · 285 阅读 · 0 评论 -
PostgreSQL流复制中的同步与异步
对于同步复制时,备库宕机导致主库不可用,反而降低可用性的问题,可以配置两个备库,一个与主库同步复制(sync),一个异步复制(async),如果同步复制的备库宕机,异步复制的备库立刻接替同步复制,这样就可避免同步复制反而降低可用性了。上面FIRST、ANY两种配置都可以实现这种策略。要配置同步复制,关键在主库配置两个参数:synchronous_commit 和synchronous_standby_names。这种配置经过测试时可以的。原创 2023-05-30 15:33:27 · 1251 阅读 · 0 评论 -
PostgreSQL 查询连接的客户端
主要是查询 pg_stat_activity , 我一般用这个SQL:select pid, usename, application_name, client_hostname from pg_stat_activity;注意,这里的pid是服务端对应服务进程的pid,不是客户端进程的pid。原创 2021-12-21 15:27:23 · 1067 阅读 · 0 评论 -
PostgreSQL查询数据库、表的大小
通过SQL查询数据库和表的大小原创 2022-03-01 09:15:33 · 722 阅读 · 0 评论 -
PostgreSQL 查询被锁阻塞的连接
主要是查pg_lock,再结合pg_stat_activity和pg_class,我用的SQL是:select relation::regclass, s.pid, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid;可以根据查出来的pid或客户端,找到阻塞的连接和进程,手动杀掉。...原创 2021-12-21 15:44:07 · 1362 阅读 · 0 评论 -
PostgreSQL 审计插件 pgaudit
审计pgaudit插件,不是PostgreSQL自带的,需另外下载安装GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension选择版本编译:到pgaudit目录下执行:make install USE_PGXS=1 PG_CONFIG=/mnt/disk01/hadb/postgresql/bin/pg_config就会安装到/mnt/disk01/hadb/postgresql中,这个安装是可移植的,安装完直接复制postgresql作.原创 2022-03-29 14:31:50 · 732 阅读 · 0 评论 -
PostgreSQL 查询某个库的字符编码
select pg_encoding_to_char(encoding) from pg_database where datname = 'benchmark';原创 2021-12-21 15:55:07 · 4773 阅读 · 0 评论 -
使用debezuim,实现PG同步Oracle
使用debezuim,实现PG同步Oracle原创 2022-10-09 11:25:52 · 269 阅读 · 0 评论 -
benchmarksql 的安装和使用
TPC-C测试的结果主要有两个指标,即流量指标(Throughput,简称tpmC)和性价比(Price/Performance,简称Price/tpmC)。流量指标(Throughput,简称tpmC):按照TPC组织的定义,流量指标描述了系统在执行支付操作、订单状态查询、发货和库存状态查询这4种交易的同时,每分钟可以处理多少个新订单交易。所有交易的响应时间必须满 足TPC-C测试规范的要求,且各种交易数量所占的比例也应该满足TPC-C测试规范的要求。在这种情况下,流量指标值越大说明系统的联机事务处原创 2022-03-20 21:40:42 · 2515 阅读 · 0 评论