Postgresql原理与实战
文章平均质量分 91
全面介绍Postgresql,以实例为线索,深入学习Postgresql的体系结构、功能特色、优化方法以及集群部署与运维等等。
HymanLiuTS
年过而立,又酸又臭,忙时敲代码,闲时读书写文章。
展开
-
Postgresql杂谈 24——针对Postgresql的文件系统调优
自数据库诞生之后,数据库优化就是一个各家公司绕不开的课题,也是面试造飞机时逃不开的知识点。因为当一个系统发展到了一定的规模,瓶颈往往都在数据库上,也因此催生了各种数据库的优化技术。某里的子柳就在《淘宝技术这十年》中提到,最初淘宝的系统架构中数据库部分就是1主2备的mysql,顶住了日均30W个PV(Page View)。再以后随着用户规模越来越大,使用连接池+单个oracle,然后就是在单个oracle基础之上的硬件的向上拓展,机器从服务器到小型机,存储从普通硬盘到SAN,再到NAS,总而言之...原创 2021-09-19 08:28:40 · 1244 阅读 · 0 评论 -
Postgresql杂谈 23——Postgresql中的全文检索
今天我们来聊一下全文检索,想必做搜索相关业务朋友对这个概念不会陌生,尤其是做搜索引擎,或者类似CSDN、知乎类的社区网站,全文检索是逃不开的业务。文,即文章、文档。全文搜索就是给定关键词,在所有的文档数据中找到符合关键词的文档。不管是哪种业务模式下的全文检索功能,其实大体的实现思路类似,如下所示: 使用文字进行描述,就是:(1)获取原始文档数据。(2)对文档进行分析,分词(所为分词,就是按照分词符,如空格,将一句话分隔成若干的单词)(3)存档存入数据库,并通...原创 2021-08-30 16:59:11 · 7858 阅读 · 0 评论 -
Postgresql杂谈 22——Postgresql中的模糊匹配
Postgresql对模糊查询的支持,主要有三种方法:传统的like操作符、SQL99新增的SIMILAR TO操作符以及POSIX正则表达式。除了前面两种SQL标准的模糊查询手段,Postgresql对正则表达式具有强大的支持,这就使得Postgresql可以支持任意字符串的匹配查找,下面我们分别对这三种方法进行学习。一、LIKE操作符1.1 “%”代表0个或任意个数的任意字符 %代表了0个以上的任意字符,为了验证%的使用,下面先看下测试表里面的数据:stock...原创 2021-08-27 08:36:13 · 7012 阅读 · 0 评论 -
Postgresql杂谈 21——宽索引和Index—Only Scans、HEAP—Only Tuples技术
究竟什么样的索引才算是好索引(本文中的索引是指Oracle、SQLServer、Postgresql使用的基于BTree结构的普通索引,并非指Postgrsql GIN、Gist此类特色索引)?一、针对索引数据顺序性的优化 我们知道BTree索引的数据结构本质是一个多路平衡查找树,这种结构也就意味着,对数据的查找效率的和数据顺序紧密相关。比如对于一个多列索引(A\B\C),首先保证索引按照A排序,在A相等的前提下按照B再进行排序,接着在B相等的前提下,又按照C进行排序。这种有...原创 2021-08-25 09:24:27 · 1056 阅读 · 0 评论 -
Postgresql杂谈 20—详解Postgresql中的Checkpoint、WAL日志和热备份恢复
本文中,我们共同学习下Postgresql的WAL日志。WAL,是Write Ahead Log的简称,翻译过来就是预写日志,或者叫做重做日志。相信大家对数据库事务的四大特性ACID(原子性、一致性、隔离性和持久性)已经非常熟悉了,今天我们先从数据库数据的持久性说起。数据的持久性是指,数据库中的事务中的数据一旦提交,便可以做到持久化,只要你不删除,那么数据将会永远存在数据库中。一、有关CheckPoint 现在我们就来讨论下这个提交的过程。我们直到数据库中数据的存放位置最终...原创 2021-08-23 09:25:22 · 3901 阅读 · 2 评论 -
Postgresql杂谈 19—详解Postgresql中的多版本更新机制
MVCC(Multi-Version Concurrency Conctrol,多版本控制并发),是数据库中一种常见的存在并发时保证数据一致性的方法。包括Oracle、Mysql、Postgresql在内许多主流数据库都有一套自己的MVCC方法实现。从实现逻辑上来讲,大体分为两种: 第一种,就是Oracle和Mysql采用的,在更新数据时,把原来的数据放到回滚段中,如果有其它事务需要读取原来的数据,则需要从回滚段中进行读取。 第二种,是Postgresql采用的...原创 2021-08-20 08:47:42 · 1419 阅读 · 0 评论 -
Postgresql杂谈 18—Postgresql中的备份和恢复(二)
上一篇文章中,我们主要学习了Postgresql的逻辑备份和恢复,接着上一篇的内容,今天我们介绍下Postgresql的物理备份。所谓物理备份,就是针对数据库的数据文件或者目录进行备份,物理备份的好处就是简单高效,不需要针对每个数据库对象进行备份,只需要将所有数据一次性的打包。不足之处在于,它不支持部分备份只能进行全量备份。物理备份有冷备份和热备份的说法,冷备份是指备份时需要先停止当前数据库的运行,保证在备份期间不会有新的数据库的改动。而热备份是线上备份,备份是不影响数据库的访问。 ...原创 2021-08-18 08:37:01 · 737 阅读 · 0 评论 -
Postgresql杂谈 17—Postgresql中的备份和恢复(一)
本文主要介绍Postgresql的数据库备份和还原。数据库备份是防止数据丢失的一种最简单有效的方式。通过数据库的备份和还原,我们可以将数据库回退到历史上任意一个时间点,进而排除错误的数据。也可以快速克隆线上的数据库环境,协助我们在线下进行缺陷的复现和回归。Postgresql中数据库的备份的方式大体分为两种——逻辑备份和物理备份,下面我们分别进行介绍。 在介绍备份的方法之前,笔者先介绍下用到的需要进行备份的数据库test,在test数据库中有两个模式如下:test=# \...原创 2021-08-16 10:17:29 · 1996 阅读 · 0 评论 -
Postgresql杂谈 16—Postgresql中的锁机制
今天,我们学习下Postgresql中的锁机制。锁是数据库事务的基础,通过锁才能保证数据库在并发时能够保证数据的安全和一致,才能够达到事务的一致性和隔离性。但是任何事物都有它的两面性,引入锁同样会增加性能开销和可能的死锁问题,不过好在都存在解决方法。Postgresql和其它数据库一样,从锁作用的对象上来说可以分为表锁、行锁两种,从排他性来说可以大体分为共享锁和排他锁,但是由于Postgresql是通过多版本的方式对数据库进行更新,它也引入了Access锁。接下来我们就对这些内容分类进行学习。...原创 2021-08-13 10:09:13 · 6950 阅读 · 1 评论 -
Postgresql杂谈 15—Postgresql中的事务
本文主要学习下Postgresql中事务。事务是数据库中非常重要的一个逻辑结构,可以说是数据库数据安全和准确的一个最重要的保证。同时,它也大大提高了数据库的易用性,因为通过数据库事务,我们在操作数据库进行增删改查时不必再考虑并发引起的问题,而只要专注地实现自己的功能。与其它关系型数据库一样,Postgresql的 事务也具有ACID(原子性、一致性、隔离性和持久性)四大特性,通过这四大特性,Postgresql才有了上述功能特点。但除了这些通用的特性之外,Postgresql的事务也有自己的特...原创 2021-08-11 09:20:38 · 6202 阅读 · 0 评论 -
Postgresql杂谈 14—Postgresql执行计划中的扫描和连接类型
一、全表扫描 全表扫描,也叫顺序扫描,扫描时把表中所有的数据块从头到尾遍历一边,找到复合条件的数据块。全表扫描在在explain中使用Seq Scan表示:stock_analysis_data=# explain (analyze,verbose,buffers,costs,timing) select * from t1 where id=10; QUERY PLAN ...原创 2021-08-09 09:49:18 · 1715 阅读 · 0 评论 -
Postgresql杂谈 13—Postgresql中的BRIN索引
一、BRIN索引原理 本文我们继续学习下Postgresql中另外一个比较有特色的索引——BRIN索引。BRIN索引是Block Range Index索引的简写,它将数据在磁盘上的block按照一定的数目进行分组,这个数目可以通过创建BRIN时的参数pages_per_range进行设置,默认是128。分组之后,计算每组的取值范围。在 查找数据时,会遍历这些取值范围,排除掉不在范围之内的分组。 与其它索引不同:(1)BTree等其它索引在查找数据时是根据数...原创 2021-08-06 08:19:26 · 2014 阅读 · 0 评论 -
Postgresql杂谈 12—深入学习GIN索引
今天,我们深入学习下Potgresql的GIN索引。GIN索引时Generalized Inverted Index的缩写,意思是广义的倒排索引。GIN索引和Gist索引类似,都是一个通用的索引框架,我们可以基于此框架开发自定义的GIN索引。一、GIN索引的内部结构 了解Java内部HashMap结构的应该了解,Java8之后的HashMap内部结构是Hash Table+链表或者Hash Table+红黑树。其实GIN也采用了类似的思想,只不过是Entry Tree+Po...原创 2021-08-04 09:05:52 · 2016 阅读 · 0 评论 -
Postgresql杂谈 11—深入学习SP-Gist索引
本文,我们深入学习下Posgresql中的SP-Gist索引。SP-Gist是Space-Partition Gist(空间分区Gist索引)的简写。和Gist索引一样,它也是一个索引框架,但是相比较于Gist,它优化了索引算法,提高了索引的性能。在此框架下,可以实现如下三种索引:quad-tree(四叉树) k-d tree(K维树) radix tree(基数树) 同时,Postgresql也实现了以下几种类型的SP-Gist索引的操作类,我们可以在这些类型上直接建立...原创 2021-08-02 08:23:44 · 1163 阅读 · 0 评论 -
Postgresql杂谈 10—Postgresql中的分区表
一、关于分区表 表分区是在大数据优化中的一种常见的分表方案,通过将大数据按照一定的规则(最常见的是按照时间)进行分表处理,将逻辑上的一个大表分割成物理上的几块表,插入数据时,数据会自动插入到不同的分区表中,从而实现查询或者其它操作的性能优化。相比于一个大表,分区表具有以下优点:(1)当查询或者更新一个分区的大部分记录时,采用顺序扫描而不是随机扫描,可以获得巨大的性能提升。(2)使用不频繁的历史数据可以转移到一些低廉的存储介质上,而热数据放到性能较好的存储介质上,可以最大限度的减少成本...原创 2021-07-31 23:15:03 · 2825 阅读 · 1 评论 -
Postgresql杂谈 09—Postgresql中的Gist索引的深入学习
本文,我们进一步学习下Gist索引。Gist是Generalized Search Tree的意思,意思是通用搜索树,底层结构也是一种平衡树,它是一套索引模板,可以支持用户实现自定义的索引。相比于BTree索引,BTree索引可以建立在任意类型之上,但是BTree只支持<、=、>操作符,而Gist索引可以支持@>、&&等复杂运算的操作符。一、Gist索引的存储结构 在《Postgresql杂谈 04—Postgresql中的四种常规索引》一...原创 2021-07-29 09:01:23 · 1431 阅读 · 0 评论 -
Postgresql杂谈 08—Postgresql中的BTree索引存储结构和查询过程分析
一、关于BTree 本文我们深入学习下Postgresql的BTree索引。我们知道,BTree是一种多路平衡查找树,被广泛应用于数据库的索引之中。与我们之前接触过的最多的二叉树不同,多路树的意思是每个节点不止一个子节点。而查找树是一种顺序树,也就是说对于树种每一个节点来说,它的左子树上所有的节点都不大于它,而它的右子树上所有的节点都不小于它。由于这种特性,理论上B树查找的时间复杂度可以达到二分查找的时间复杂度O(logn)。BTree的查询效率和树的高度有关,为了降低树的高度,提高查询效率...原创 2021-07-27 10:54:38 · 1518 阅读 · 0 评论 -
Postgresql杂谈 07—Postgresql中的函数索引和部分索引
本文笔者主要介绍下Postgresql中两种比较特殊的索引——函数索引和部分索引。函数索引,顾名思义,索引字段经过了某个函数的处理,以达到在某种特殊的场景下走索引的目的。而部分索引,就是针对表中的部分字段建立索引,而达到查询其中这部分数据走索引的目的。一、函数索引 函数索引,一种典型的应用场景,就是用表中的某一字符串类型的字段构建查询条件进行查询时,期望忽略大小写,笔者通过下面的例子来介绍下函数索引的用法。 首先,创建一个users表,用来作为数据源:...原创 2021-07-24 15:41:26 · 2439 阅读 · 0 评论 -
Postgresql杂谈 06—Postgresql中的范围和数组类型
本文主要介绍下Postgresql的另外两种特殊的类型Range类型(范围类型)和数组类型。两种类型,适用于不同的场景,但是最终的目的相同,就是使用传统的数据类型,建立常规的索引无法满足查询的性能要求。而使用范围类型(通过使用gist索引)、使用数组类型(通过使用Gin索引)可以大幅度的加快特定场景下数据的查询速度。下面笔者就分别介绍下这两种数据类型的使用。一、Range类型1.1 Range类型的使用 在使用Range类型之前,我们先建立一些测试数据,用于Range类...原创 2021-07-22 11:45:23 · 2365 阅读 · 0 评论 -
Postgresql杂谈 05—Postgresql中的JSON和JSONB类型
一、JSON和JSONB Postgresql相比较与其它关系型数据库,很大的一个优势在于它内置了很多中特殊的数据类型,像inet、MAC,本文所介绍的json和jsonb类型也是Postgresql特有的2种数据类型,其实他们的本质都是存储jon格式的数据,但是不同的是json格式是按照原样的json字符串进行存储,包括字符串中的重复键、空格、顺序也都会原样保存;而jsonb格式会对原样的json字符串进行解析,会删除重复的键以及空格,而且也不会保证键的顺序,在存储时会转换成二进制。因此,...原创 2021-07-20 10:48:27 · 3774 阅读 · 0 评论 -
Postgresql杂谈 04—Postgresql中的五种常规索引
一、索引的分类 Postgresql中索引一共分为5种,每一种都有它合适的应用场景,我们在使用时要根据不同业务的特点,选择合适的索引,这样才能加快sql语句的查询效率。下面,我们将就每种不同的索引,介绍其特点。2.1 B树索引 这是我们最常用的索引结构了,B树是一颗多路平衡查找树,每个节点包含多个键,而且这些键对应的指针一般指向磁盘上同一个数据块,目的是一次从磁盘读取一个数据块,减少磁盘IO操作,加快查询的效率。 B树索引的结构如下所示:...原创 2021-07-18 11:47:48 · 2911 阅读 · 0 评论 -
Postgresql杂谈 03—单机安装Postgresql双版本数据库
本文主要介绍在单机上使用yum和源码两种方式分别安装postgresql11和postgresql12的方法,这在生产环境中基本不会这么干,只是为了研究下postgresql的安装过程。一、通过yum方式安装 yum方式安装postgresql相对来说比较简单,如果一切顺利,全程不会需要进行其它比较麻烦的操作,但是笔者在安装的过程中,也遇到了不少拦路虎,所以问题从来都会和你不期而遇。作为一个程序猿,每天在与问题的挣扎中也习以为常。1.1 更新yum数据源 ...原创 2021-07-15 10:36:00 · 2185 阅读 · 0 评论 -
Postgresql杂谈 02—Postgresql体系介绍
一、Postgresql的内存结构 所谓的内存结构,就是Postgresql在运行时的内存构成,即有多少个进程,每个进程的作用是什么。我们先来看一张PG服务器的运行时内存结构图: 接下来,我们分别介绍了Postgresql各个进程的作用。1.1 主进程PostMaster postmaster进程是PG数据库最主要的管理进程,它的作用主要有两个:(1)可以启动和关闭数据库的实例。/usr/pgsql-11/bin/post...原创 2021-07-13 08:46:20 · 1224 阅读 · 1 评论 -
Postgresql杂谈 01—访问控制配置文件pg_hba.conf的介绍
一、文件结构介绍 PostgresSql的访问控制文件pg_hba.conf位于/var/lib/pgsql/11/data,主要的作用是控制控制哪些IP地址可以访问数据库。# TYPE DATABASE USER METHOD# "local" is for Unix domain socket connections onlylocal all all ...原创 2021-07-12 16:04:31 · 3546 阅读 · 0 评论