随笔分类 - postgresql
摘要：一、为什么是VACUUM？ 每当更新或删除PostgreSQL表中的行时，都会留下死元组。VACUUM摆脱了它们，以便空间可以重复使用。如果一个表没有被清理，它就会变得臃肿，这会浪费磁盘空间并减慢表的顺序扫描（以及索引扫描）。VACUUM还负责冻结表行，以避免在事务ID计数器环绕时出现问题，但这是另
阅读全文
摘要：一、 最高效方法 测试环境验证，6600万行大表，删除2200万重复数据仅需3分钟 delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id)
阅读全文
摘要：1、创建序列 CREATE SEQUENCE if not exists test_mergetable_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 999999999 START 1 CACHE 1; //或者： create sequence if not ex
阅读全文
摘要：Wait Event TypeWait Event NameDescription LWLock ShmemIndexLock Waiting to find or allocate space in shared memory. OidGenLock Waiting to allocate or
阅读全文
摘要：前言 最近这段时间一直使用pg 数据库插入更新大量的数据，发现pg数据库有时候插入数据非常慢，这里我对此问题作出分析，找到一部分原因，和解决办法。 一 死元祖过多 提起pg数据库，由于他的构造，就不得不说他的元祖。 1.1 什么是元祖？ 在Postgresql做delete操作时，数据集(也叫做元组
阅读全文
摘要：一、概述 repmgr是一套开源工具，用于管理PostgreSQL服务器群集内的复制和故障转移。它支持并增强了PostgreSQL的内置流复制，该复制流提供了一个读/写主服务器以及一个或多个只读备用数据库，其中包含主服务器数据库的近实时副本。可以设置热备份服务器、监控复制、执行管理任务（故障转移、手
阅读全文
摘要：一、错误信息 执行 pg_dump 命令备份，提示 cache lookup failed for type… 错误。 二、错误分析 根据上面日志中的提示信息，可以确定 222222 这个 ID 号，在 pg_type 表中无法查找到。 分析 pg_proc 表结构。查看 pg_proc 表中对 p
阅读全文
摘要：背景 国内的应用，在文本排序上基本都是按照拼音来进行排序的。 在不同的字符集中，汉字的编码可能不一样，比如UTF8和GBK，其中GBK是按拼音的顺序进行编码的，而UTF8则不是。 所以如果你的数据库使用了UTF8编码，对中文字段进行排序时，可能得到的并不是按拼音排序的结果。 在PostgreSQL中
阅读全文
摘要：PostgreSQL的MVCC是直接在原表通过增加新tuple来实现的，决定了它在大结果集count的时候性能不会太理想，因为需要对大结果集做可见性判断将会是一项繁重的工作，比如下面这种SQL： select count(*) from big_tab; 单纯依靠DB进行优化，确实不是一件容易的事情
阅读全文
摘要：今天我们要探讨的是 custom执行计划和通用执行计划。这一技术在 Oracle中被称为绑定变量窥视。但 Postgresql中并没有这样的定义，更严格地说，Postgresql叫做custom执行计划和通用执行计划。 什么是custom执行计划，什么是通用执行计划，我们先来看一个例子，我创建了一个
阅读全文
摘要：Postgresql 提供了对于分区表 global index 的支持。global index 不仅提供了对于唯一索引功能的改进（无需包含分区键），而且在性能上相比非global index （local index）有很大的提升（无法提供分区条件情况下）。以下举例说明二者在性能方面的差异。 1
阅读全文
摘要：背景 对于多数数据库，dba技能之一就是查找锁。锁的存在有效合理的在多并发场景下保证业务有序进行。下面我们看一下Postgresql中查找阻塞的方法。 1、找到"被阻塞者"，获取被堵塞的PID select distinct pid from pg_locks where not granted;
阅读全文
摘要：1、前言 在处理用户问题过程遇到一个问题。用户通过pg_dump导出 bytea 对象时，当行的大小超过 1G时，会报错： [v8r6c5b41@dbhost01 ~]$ sys_dump -t t1_bytea -f 1.dmp -U system testsys_dump: error: Dum
阅读全文
摘要：1、前言 PostgreSQL 聚合算法有两种，HashAggregate and GroupAggregate 。我们知道GroupAggregate 需要对记录进行排序，而 HashAggregate 则无需进行排序，通常 HashAggregate 要快很多。 但是，我们经常会看到优化器使用
阅读全文
摘要：一、前言 PostgreSQL为每个收到查询产生一个查询计划。 选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。 你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。 二、执行计划命令 EXPLAIN [ ( op
阅读全文
摘要：1、修改参数 2、创建两个用户 创建a1 create user a1 connection limit-1 password '123456'; alter user a1 SUPERUSER; grant create on database "test" to "a1" with grant
阅读全文
摘要：一、简介 PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。 二、示例一 #插入1000W数据 postgres=# insert into t select id,i
阅读全文
摘要：PostgreSQL运行日志可以实现日志输出记录，默认是没有启动记录。这个日志一般是记录服务器与DB的状态，比如各种Error信息，定位慢查询SQL，数据库的启动关闭信息，发生checkpoint过于频繁等的告警信息，诸如此类。 一、常用日志参数 项目默认值设定值说明 logging_collect
阅读全文
摘要：一、示例 select 8/(100-3) as c1, round(8/(100-3) ,4) as c2, round(8/(100-3)::numeric ,4) as c3, 8/(100-3)::numeric as c4 ; -[ RECORD 1 ] c1 | 0 c2 | 0.000
阅读全文
摘要：一、具体方法 一般情况下直接执行 drop role xxx; 就可以把这个用户删除。但是很多时候会因为用户有依赖而报错。 二、权限依赖 postgres=# create role test with login; CREATE ROLE postgres=# grant all on datab
阅读全文
