一名数据库爱好者的专栏

【代码】postgresql 表、索引的膨胀率监控。

【代码】postgresql 批量创建分区表。

postgresql 数据库已有分区表添加主键的最优方案

In the previous articles we discussed PostgreSQL indexing engine and the interface of access methods, as well as B-trees, GiST, SP-GiST, GIN, RUM, and BRIN. But we still need to look at Bloom indexes.BloomGeneral conceptA classical Bloom filter is a dat

In the previous articles we discussed PostgreSQL indexing engine, the interface of access methods, and the following methods: B-trees, GiST, SP-GiST, GIN, and RUM. The topic of this article is BRIN indexes.BRINGeneral conceptUnlike indexes with which we

We have already discussed PostgreSQL indexing engine, the interface of access methods, and main access methods, such as: hash indexes, B-trees, GiST, SP-GiST, and GIN. In this article, we will watch how gin turns into rum.RUMAlthough the authors claim th

We have already got acquainted with PostgreSQL indexing engine and the interface of access methods and discussed hash indexes, B-trees, as well as GiST and SP-GiST indexes. And this article will feature GIN index.GIN“Gin?.. Gin is, it seems, such an Amer

We’ve already discussed PostgreSQL indexing engine, the interface of access methods, and three methods: hash index, B-tree, and GiST. In this article, we will describe SP-GiST.SP-GiSTFirst, a few words about this name. The “GiST” part alludes to some sim

In the previous articles, we discussed PostgreSQL indexing engine, the interface of access methods, and two access methods: hash index and B-tree. In this article, we will describe GiST indexes.GiSTGiST is an abbreviation of “generalized search tree”. Th

We’ve already discussed PostgreSQL indexing engine and interface of access methods, as well as hash index, one of access methods. We will now consider B-tree, the most traditional and widely used index. This article is large, so be patient.BtreeStructure

The first article described PostgreSQL indexing engine, the second one dealt with the interface of access methods, and now we are ready to discuss specific types of indexes. Let’s start with hash index.HashStructureGeneral theoryPlenty of modern progra

InterfaceIn the first article, we’ve mentioned that an access method must provide information about itself. Let’s look into the structure of the access method interface.PropertiesAll properties of access methods are stored in the “pg_am” table (“am” sta

IntroductionThis series of articles is largely concerned with indexes in PostgreSQL.Any subject can be considered from different perspectives. We will discuss matters that should interest an application developer who uses DBMS: what indexes are available

os: centos 7.6db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # # yum list installed |grep -i postgrespostgresql10.x86_64 10.18-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

os: centos 7.6.1810db: postgresql 10postgresql 没有 mysql 数据库 show create table 命令,所以查看表的定义就稍微麻烦一些。本次先试下 pgddl版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # # yum list installed|grep -i postgrepostgresql10.x86_64 10

os: centos 7.6.1810db: postgresql 10pg_pathman 1.5.12版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core)# yum list installed |grep -i postgrepostgresql10.x86_64 10.18-1PGDG.rhel7 @pgdg10 postgresql10-c

引言对于index-only-scan，熟悉PostgreSQL的应该都知道，又称为覆盖索引，也即"仅索引扫描"，是一种"常见"的优化方法。Index-only scans are a major performance feature added to Postgres 9.2. They allow certain types of queries to be satisfied just by retrieving data from indexes, and not from tables. T

os: centos 7.6db: postgresql 13版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # # su - postgres$ psql -c "select version();" version -

os: centos 7.6.1810db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.16-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

os: centos 7.6.1810db: postgresql 10版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core) # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.16-1PGDG.rhel7 @pgdg10 postgresql10-contrib.x86_64

os: centos 7.8db: postgresql 13.1版本# cat /etc/centos-releaseCentOS Linux release 7.8.2003 (Core)# # yum list installed |grep -i postgresqlpostgresql13.x86_64 13.1-1PGDG.rhel7 @pgdg13 postgresql13-contrib.x86_64

os: centos 7.8db: postgresql 13.1版本# cat /etc/centos-releaseCentOS Linux release 7.8.2003 (Core)# # yum list installed |grep -i postgresqlpostgresql13.x86_64 13.1-1PGDG.rhel7 @pgdg13 postgresql13-contrib.x86_64

os: centos 7.4.1708db: postgresql 11.9版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # su - postgres$ psql -c "select version();" version

------------------------------------------------------------------------------- syscat.sql--- sample queries to the system catalogs------ Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group-- Portions Copyright (c) 1994, Regents

os: centos 7.8.2003db: postgresql 13.0版本# cat /etc/centos-releaseCentOS Linux release 7.8.2003 (Core)# # su - postgres$ psql -c "select version();" version

1.倒排索引原理倒排索引来源于搜索引擎的技术，可以说是搜索引擎的基石。正是有了倒排索引技术，搜索引擎才能有效率的进行数据库查找、删除等操作。在详细说明倒排索引之前，我们说一下与之相关的正排索引并与之比较。1.1正排索引在搜索引擎中，正排表是以文档的ID为关键字，表中记录文档中每个字的位置信息，查找时扫描表中每个文档中字的信息直到找出所有包含查询关键字的文档。正排表结构如图1所示，这种组织方法在建立索引的时候结构比较简单，建立比较方便且易于维护;因为索引是基于文档建立的，若是有新的文档加入，直接为该文

正常的 create index 是会阻塞 dml 操作的，在生产环境需要添加 concurrently 参数。postgresql 8.2 版本就支持 concurrently 参数create index concurrently idx_index_name on table_name(column_name);参考:https://www.postgresql.org/docs/8.2/sql-createindex.html...

https://mp.weixin.qq.com/s/g6j3WsBTGQipgEfrkzObRwWITH btree_index_atts AS ( SELECT pg_namespace.nspname, indexclass.relname AS index_name, indexclass.rel

os: centos 7.4db: postgresql 10.11oid、tableoid、ctid、xmin、xmax、cmin、cmax 这些都是 postgresql 数据库表的隐藏列.起着不同的作用.版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresqlpostgresql10.x86_64

os: centos 7.4.1708db: postgresql 10.11oracle 数据里经常用到物化视图，想不到 postgresql 9.3 也加入了这么先进的功能。版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.11-2PGDG.rh

pg单条记录，单个表，单个数据库的最大限制是多少？下面是一些限制：单个数据库最大尺寸？ 无限制（已存在有 32TB 的数据库）单个表的最大尺寸？ 32 TB一行记录的最大尺寸？ 400 GB一个字段的最大尺寸? 1 GB一个表里最大行数？ 无限制一个表里最大列数？ 250-1600 （与列类型有关）一个表里的最大索引数量？ 无限制当然，实际上没有真正的无限制，还是要受系统可用磁盘空间、可用内存/交换区的制约。 事实上，当上述这些数值变得异常地大时，系统性能也会受很大影响。单表的最

os: centos 7.4.1708db: postgresql 10.11create tablepostgres=# create table tmp_t0( id bigint, name varchar(100), memo varchar(100));postgres=# insert into tmp_t0select id, md5(id::varchar) as name, md5(md5(id::varchar) ) as me

os: centos 7.4db: postgresql 10.11rum 是大名鼎鼎 postgrespro 开源的一个 extensionThe rum module provides access method to work with RUM index. It is based on the GIN access methods code.GIN index allows to perform fast full text search using tsvector and tsquery

os: centos 7.4.1708db: postgresql 13beta1postgresql 12.3版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # su - postgresLast login: Fri Jun 19 10:46:55 CST 2020 on pts/0$$$ psql -c "select version();"

os: centos 7.4.1708db: postgresql 10.11版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresqlpostgresql11.x86_64 11.8-1PGDG.rhel7 @pgdg11postgresql11-contrib.x

os: centos 7.4db: postgresql 10.11Hash Join (散列连接) 实现可以理解为使用驱动表(小表)用来建立 hash map ，依次读取驱动表的数据，对于每一行数据根据连接条件生成一个 hash map 中的一个元組(生成 hashkey)，再和被驱动表比较 hashkey，如果不相同则直接丢弃，如果相同再次过一遍join condition和filter，满足条件的数据集返回。Hash Join (散列连接) 不依赖索引，一般会选择个小表当驱动表。Hash J

os: centos 7.4db: postgresql 10.11Merge Join (排序合并连接)如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。最主要一点是数据已经排序了。Merge Join (排序合并连接)需要首先对两个表按照关联的字段进行排序，分别从两个表中取出一行数据进行匹配，如果合适放入结果集；不匹配将较小的那行丢掉继续匹配另一个表的下一行，依次处理直到将两表的数据取完。Merge Join (排序合并连接)的很大一部分开销花在

os: centos 7.4db: postgresql 10.11Nested Loop 比较适合两个表的数据量都比较少的情况(最简单的 table join 方式)外层驱动表过滤后的数据量较少。内层被驱动表的关联列上最好有高效索引(主键或者唯一性索引)。版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # su - postgres$$ psql -c "select version();"

with tmp_tab as ( select n.nspname as schemaname, c.oid as reloid, c.relname , case c.relkind when 'r' then 'table' when 'm' then 'materialized view' when 's' then 'special' when 'f' then 'foreign table'

os: centos 7.4db: postgresql 10.11版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresqlpostgresql10.x86_64 10.11-2PGDG.rhe...