- 博客(433)
- 资源 (1)
- 收藏
- 关注
RSS订阅
翻译 Postgresql管理系列-第九章 WAL(Write Ahead Logging)介绍
事务日志是数据库的重要组成部分，因为即使数据库发生系统故障，也要求所有数据库管理系统不能丢失任何数据。它是数据库系统中所有更改和操作的历史记录日志，以确保没有数据因故障而丢失，例如电源故障或导致服务器崩溃的其他服务器故障。由于日志包含有关已执行的每个事务充足的信息，因此数据库服务器应能够通过在服务器崩溃时重放事务日志中的更改和操作来恢复数据库集群。在计算机科学领域，WAL是Write Ahead...
2019-07-11 13:53:02
2746
4
翻译 Postgresql管理系列-第五章 并发控制
当数据库中多个事务并发运行时，并发控制机制用于保持一致性和隔离性，这两个特性是ACID的两个属性。有三种广泛的并发控制技术,即多版本并发控制(MVCC),严格两阶段锁(S2PL),和乐观并发控制(OCC),每个技术又有许多变体。在MVCC,每个写操作会创建一个新版本的数据项,同时保留旧版本。当一个事务读取数据项,系统选择一个版本,以确保事物之间的隔离性。MVCC的主要优势是,“读不会阻塞写，写也...
2019-07-09 14:36:31
1867
翻译 Postgresql管理系列-第四章Foreign Data Wrappers
本章将介绍两个技术上有趣和实用的功能: 外部数据包装 (FDW) 和并行查询。目前只有FDW的介绍，并行查询还在筹划中。4.1. Foreign Data Wrappers (FDW)2003年, 在 SQL 标准中添加了一个访问远程数据的规范, 称为 “SQL Management of External Data (SQL/MED)”。自9.1版以来, PostgreSQL一直在开发此功...
2019-06-12 17:15:37
1534
翻译 Postgresql管理系列-第三章Query Processing(下半部分-join operation介绍)
接第三章的上半部分3.5. 连接操作PostgreSQL 支持三个联接操作: 嵌套循环联接、合并联接和哈希联接。PostgreSQL 中的嵌套循环联接和合并联接有几种变体。下面, 我们假设读者熟悉这三个连接的基本行为。如果您不熟悉这些术语, 请参阅 [1, 2]。但是, 那里没有太多关于混合哈希联接与 PostgreSQL 支持的倾斜的解释, 因为将在此处进行更详细的解释。请注意, Pos...
2019-06-10 17:28:57
978
翻译 Postgresql管理系列-第三章Query Processing(上半部分-代价评估计算)
正如官方文档中所述，PostgreSQL支持SQL2011标准大量功能。查询处理是PostgreSQL中最复杂的子系统，它可以高效地处理支持的SQL。本章概括描述了查询处理的过程;特别是，查询优化方面。本章包括以下三个部分：第1部分：第3.1节。本节概述了PostgreSQL中的查询处理过程。第2部分：第3.2节~3.4。本部分介绍了获取一个表查询的最佳计划所遵循的步骤。在第3.2和3....
2019-06-10 10:17:22
1394
翻译 Postgresql管理系列-第八章Buffer Manager
缓冲区管理器管理共享内存和持久存储之间的数据传输, 并可能对DBMS的性能产生重大影响。PostgreSQL缓冲区管理器的工作效率非常高。在本章中, 介绍了PostgreSQL缓冲区管理器。第一部分是概述,接下来的部分介绍了以下主题:缓冲区管理器结构缓冲管理器锁缓冲管理器如何工作环形缓冲器清理脏页图8.1 缓冲区管理器，存储和后端进程之间的关系8.1. 概述本节介绍了在后续...
2019-06-05 10:42:46
2697
翻译 Postgresql管理系列-第七章Heap Only Tuple and Index-Only Scans
本章介绍与索引扫描相关的两个功能, 即Heap Only Tuple和Index-Only Scans。7.1. Heap Only Tuple (HOT)HOT是在8.3版中引入的, 当更新的行存储在旧行所在的page时，可以有效地使用索引和表的页。HOT有效的减少了vacuum操作。源码目录README.HOT描述了HOT的细节。 本章简要介绍了HOT。首先, 7.1.1章节介绍了如何在...
2019-06-03 11:58:39
1407
翻译 Postgresql管理系列-第六章Vacuum Processing
vacuum操作是一个维护进程, 便于PostgreSQL的持久运行。它的两个主要任务是删除dead tuples和冻结事务id, 第5.10节中简要的提到了这两个问题。为了删除dead tuples, vacuum提供了两种模式, 即并发vacuum和full vacuum。并发vacuum(通常简称 “VACUUM”)为表文件的每一页删除dead tuples, 而其他事务可以在此过程中读取...
2019-05-31 15:53:46
1421
1
翻译 Postgresql管理系列-第十章 基础备份和PITR(Point-in-Time Recovery)
数据库备份大概可以分为两类：逻辑备份和物理备份。它们两者有长处也有短处，比如，逻辑备份需要花费大量的时间，特别是需要花费大量的时间备份大的数据库，而且需要更多的时间来恢复数据库。相反的，物理备份相对在较短的时间内备份和恢复数据库，所以在实际系统中，物理备份是非常重要和有用的功能。在postgresql中，在线物理全备份从8.0版本就有了，并且运行的整个数据库集群的快照作为一个基...
2019-04-11 14:00:40
1166
翻译 Postgresql管理系列-第十一章 流复制管理
同步流复制是是从9.1版本实现的，可以是一主多从的模式，在postgresql里主叫master,备节点叫standby主备是基于日志传送的技术实现同步，主节点持续发送wal数据，备节点重放接受到的wal数据主要介绍以下几个话题流复制如何启动的 主备之间如何传送数据 主节点如何管理多个备节点 主节点如何发现失败的备节点一.开始流复制在流复制中，三个进程协同工作，wal...
2019-01-14 11:41:59
985
翻译 Postgresql管理系列-第二章 进程和内存结构
在本章中，总结了PostgreSQL中的流程体系结构和内存体系结构，以帮助阅读后续章节。如果您已经熟悉它们，可以跳过本章1.进程结构Postgresql 是一个C/S架构的关系型数据库，由多个后台进程管理数据库，下面分别介绍一些这些进程postgres server process 是所有进程的父进程backend process 每一个客户端的连接都有一个后端进程存在...
2018-05-21 12:54:37
2705
翻译 Postgresql管理系列-第一章 逻辑结构和物理结构
1.数据库的逻辑结构Database cluster: 由postgresql server管理的数据库的集合，下面由多个database组成databsase: 由各种数据库对象构成，比如下图中的table，indexes, view,function,sequence...所有数据库对象都有各自的oid(object identifiers),oid是一个无符号的四字节整...
2018-05-18 16:34:36
3078
4
原创 Greenplum多级分区表添加分区报错ERROR: no partitions specified at depth 2
一般来说，我们二级分区表都会使用模版，如果没有使用模版特性，那么就会报ERROR: no partitions specified at depth 2类似的错误。因为没有模版，必须要显式指定分区。建表的时候，最好添加二级分区以后的模版，模版也可以后面变更，如果不加模版，添加分区的时候，必须指定子分区，所以分区级别越多，越复杂。当然我们在建表的时候，如果没有指定，那么后面也可以通过alter table 语句进行添加。下面我们通过一个例子看一下。
2023-08-18 14:18:48
954
原创 PostgreSQL中如何配置Huge page的数量
在了解如在PG中如何配置大页之前，我们先要对大页进行一定的了解，为什么要配置大页，配置大页的好处有哪些。我们日常的操作系统中，程序不直接使用内存，而是使用虚拟内存地址来处理内存分配，避免计算的复杂性和物理地址映射到应用程序内存空间的复杂性。虚拟地址模型在应用程序读取或写入内存时立即将虚拟地址转换为相应的物理内存地址。这种映射结构存储在page tables中，这是一种分层组织的查找表。
2023-07-28 18:18:38
1625
原创 PostgreSQL中HOT对cluster的作用
PG中cluster的作用是根据表的索引重新构建一张表，并且表根据该索引进行排序，索引必须提前建好。注意：cluster操作加ACCESS EXCLUSIVE锁，会阻塞其它任何操作。
2023-06-28 11:42:47
994
原创 PostgreSQL中的行锁
FOR UPDATE：该模式允许修改任何元组字段，甚至删除整个元组FOR NO KEY UPDATE：对除主(唯一)键外的字段更新，此更改也不影响外键FOR SHARE：当我们需要读取一行，但不允许其他事务更改它时,使用该模式FOR KEY SHARE：读该行的键值，但只允许对除键外的其他字段更新。在检查外键约束时会自动使用该锁。
2023-05-22 17:54:45
2248
翻译 PostgreSQL逻辑复制内部是如何工作的
逻辑复制是一种将数据更改从发布者复制到订阅者的方法。被定义为发布的节点称为发布者。被定义为订阅的节点称为订阅者。逻辑复制允许对数据复制和安全性进行细粒度控制。逻辑复制使用发布和订阅模型，其中一个或多个订阅者可以订阅发布者节点上的一个或多个发布者。订阅者从其订阅的发布中提取数据，并可能随后重新发布数据，允许级联复制或更复杂的配置。
2023-05-06 17:29:19
689
原创 PostgreSQL中创建索引的消极影响
DML导致写放大如果一张表的索引比较多，DML可能会导致写放大，添加索引后，我们可能会看到SELECT语句的性能有所提高。从概念上讲，表上的每个DML语句都需要更新表的所有索引。尽管有很多优化可以减少写放大，但仍然是一个相当大的开销。内存使用的增加，索引需要更多的缓存要使用的索引页必须在内存中，无论是否有查询使用它们，因为它们需要通过事务进行更新。这样就导致表页可用的内存变得更少了。索引越多，有效缓存所需的内存就越多。由于随机的写入和读取索引，索引需要在缓存中存储更多的页面。
2023-04-28 17:47:38
772
原创 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
276
原创 Postgresql中null值和空字符串
首先null不是一个空字符串，也不是一个为零的值，上图，Oracle将NULL和空字符串都视为NULL。与PostgreSQL类似，SQL Server也将NULL视为NULL，将空字符串视为空字符串。这可以防止我们在具有唯一约束的列中插入多个空值。在SQL Server中，在一个列上存在唯一约束时，只允许插入一个NULL和一个空字符串。null和空字符串在不同的数据库中表现不一样，找了一张图，可以很清晰的对比了解。在Oracle中，存在唯一约束的列可以存储任意数量的NULL项和空字符串。
2023-02-10 17:31:58
7145
原创 PostgreSQL中offset...limit分页优化常见手段
大部分开发人员习惯使用order by offset limit进行分页，使用该方法可能会导致扫描的数据放大，因为offset的行会被扫描。表现就是一般offset的行比较小的情况也，也就是翻页，是很快的，但是一旦offset的值很大，翻页的数量很大，那么一定会变慢。如上我们看到的，翻页越多，性能越差，唯一的好处，就是书写简单。
2023-02-07 17:05:01
3668
原创 Postgresql之添加字段插件pg_migrate安装使用
最近，开发同学给大表加字段很是痛苦，加字段是DDL要锁表，尤其要加的字段是个变量，那么pg是会重写表的，可以参考我之前的文章，网上搜了一下，有个插件可以用来实现该操作。是一款叫pg_migrate的插件，地址如下：https://pgxn.org/dist/pg_migrate/0.1.1/
2023-02-03 16:26:35
615
原创 Postgresql中使用union all数据类型不一致导致的查询性能问题
那么分析一下原因，首先在src/backend/optimizer/prep/prepjointree.c源码中看下pull_up_subqueries_recurse()函数的内容，可以找到以下片段，片段告诉我们，如果是简单的子查询，那么就会扁平化的追加该对象，也就是我们上面执行计划看到的Append下面有两个同级的索引扫描。通过以上例子我们可以看到a字段类型一致的情况下，union all前后的两个子句走的都是索引查询，而a字段类型不一致的情况，走的是全表扫描，致使语句查询性能低下。
2022-12-21 17:29:14
2306
原创 如何在一台服务器同一个端口运行多个pgbouncer
https://github.com/systemd/systemd/commit/54255c64e6d223deb7d3863e426e78c443fda37c的原因，systemd中的ReusePort选项在222之前的版本中不能用于此目的。另外，有一个多线程的连接池可以替换需要多个pgbouncer的情况，可以参考https://github.com/yandex/odyssey测试一下。如下图，可以配置多个pgbouncer在同一台机器的同一个端口，充分利用系统资源。
2022-12-01 10:58:08
415
原创 Postgresql事物快照介绍
一个数据页包含了每一行的多个版本，每一行的可见版本一起构成一个快照。快照只包含在创建快照时当前已提交的数据，在这个特定的时刻提供了一个一致性的视图，这个视图我们就可以叫做快照。为了确保数据的隔离性，每一个事物都有自己的快照，这就意味着不同的事物在不同的时间点可以看到不同的快照。但是单个快照内部是一致的。在 Read Committed 隔离级别中，每个语句开始都会有一个快照，并且在该语句执行期间，仍然保持活动状态。
2022-11-08 11:46:05
1414
原创 Postgresql中如何处理逻辑复制冲突
在Postgresql中，随着逻辑复制的广发使用，在逻辑复制中会出现各种各样的问题，今天介绍一下，如果逻辑复制出现冲突，我们该如何解决。
2022-11-01 14:44:28
828
原创 PostgreSQL使用LISTEN/NOTIFY按需自动创建分区
为了对之前的写的LISTEN/NOTIFY使用有更进一步的认识，参考其他博客做个实例，看看具体使用LISTEN/NOTIFY是如何实现自动添加分区表的。测试基于13.6版本，在数据库中创建测试表，触发器函数，以及触发器。以下为C写的客户端API。在客户端调用API测试。插入测试数据进行测试。
2022-10-14 10:25:15
625
原创 PostgreSQL中LISTEN和NOTIFY介绍使用
LISTEN/NOTIFY是postgresql的一个功能特性，是一个异步的查询接口，避免的一直轮询数据库。可以在sql中使用，也可以在C，jdbc里的API进行调用。
2022-10-11 14:09:22
1340
原创 Postgresql添加列并填充默认值注意事项
从11大版本以后，PG优化了添加带有默认值的列操作，11版本以前只要添加带有默认值的列，表会被重写，不管默认值是常量还是变量，所以在加字段的时候需要特别注意，重写表的锁是Access Exclusive，最重的锁，整张表是无法访问的。附上修改字段类型是否需要重写表的规则，如果新旧字段类型是二进制兼容的，从小到大修改，则不需要重表，如果从大往小改，则需要重写表。我们通过实例看下11版本以后的效果,测试环境PG版本为13.6。
2022-09-21 17:13:30
2595
原创 Postgres16版本中FROM子查询别名可以省略不写了
因为在oracle中是可以不需要写别名的，所以从oracle迁移至postgresql会觉的让人不爽，但是从16版本开始，from子句的别名可以省略不写了。希望能看到16版本最终提交该补丁吧。
2022-08-24 18:01:18
1831
原创 举例了解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
4878
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
原创 Postgresql插件之pg_stat_monitor介绍
介绍一款Percona公司开发的插件pg_stat_monitor，该插件已经GA，就是正式发布了，说明各方面已经比较稳定，可以放心使用。首先该插件是基于pg_stat_statements开发的一个插件，可以回溯历史，查看那些查询对数据库系统有影响。目前支持如下版本：该插件有一个“桶”的概念。桶是一个可配置的时间片段。可以将查询统计数据添加分解到定时的桶，从而查看查询在一段时间内的性能变化，而不是将所有数据存储在单个大桶中。注意，默认值最多为10个桶，每个桶包含60秒的数据(配置可以修改)。所以该插
2022-05-12 15:12:18
992
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
1395
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
3401
GREENPLUM管理向导
2018-04-28
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人