PostgreSQL教程（十四）：数据库维护

1。恢复磁盘空间：

在PostgreSQL，数据行删除或使用DELETE和UPDATE语句实际上并没有删除更新，但只有在旧版行的物理地址，该行的状态被删除或过期。因此，当数据集中的数据变化非常频繁，由表所占用的空间将会在一段时间后变大，但数据量可能不会有太大变化。为了解决这个问题，你需要进行真空作业，经常有在数据变化频繁的数据表。

真空指令有真空和真空两种形式，它们之间的区别如下表所示：

没有真空
真空
真空全
删除大量数据之后
只有删除数据的状态，并且不能记录和重用空间。
如果被删除的记录是在表的末尾，占用的空间将被释放并返回给操作系统。如果不是数据结束时，命令将删除的数据空间的重新设置可占领指定的表或索引，所以在未来的新数据插入，将优先使用的空间，空间是直到用完再用，然后再考虑磁盘使用新的页。
删除的数据是否位于数据表的结尾，这些数据占用的空间将被物理释放并返回到操作系统。
执行效率

因为政府只运作，效率更高。
In the current version of PostgreSQL (v9.1), the command will generate a data file for the specified table or index, and import the available data in the original file to the new file, then delete the original data file.Therefore, in the import process, more space is required for the current disk to be used for this operation.This shows that the execution efficiency of the command is relatively low.
无论是被删除的数据占用的物理空间规划到操作系统。
不能uff0c
不能uff0c
会议
当执行真空命令时，对表的其他操作是否可以同时执行。

因为操作是一个共享锁，所以可以与其他操作并行执行。
由于操作需要使用指定表上的独占锁，所以基于操作表的任何操作将在操作执行期间暂停，知道操作已经完成。
推荐使用
当数据为空时，可以使用截断操作，因为操作将物理地清空数据表，并将其占用的空间返回给操作系统。
为了确保磁盘页的数量可以保持在相对稳定的值，可以定期执行操作，例如在一天或一周中相对较小的数据操作时间。
考虑到操作的开销和排除其他错误，推荐的方法是定期监视具有大量数据的表。只有当磁盘的磁盘占用接近临界值时，才会考虑执行一次操作，即使如此，还需要注意选择较少的数据操作来完成操作的时间。
执行后其他操作的效率
对于查询，由于存在大量的磁盘页面碎片，效率会逐渐降低。
与不执行任何真空操作相比，它更有效，但插入的效率将降低。
执行此操作后，基于表的所有操作效率都将大大提高。
两。更新计划统计：

当选择最优路径，PostgreSQL查询计划需要参考相关数据表的统计信息来创建查询的最合理的方案，通过分析得到了这些统计数据的命令。你可以直接调用命令，或者说是在真空中指挥一个可选的步骤，如真空anaylyze table_name，将执行先抽真空然后执行分析。至于回收空间（真空），它一直在频繁更新的表一定频率分析，使表的统计信息是永远在一个相对较新的状态，那么它将基于该表的查询优化非常有益。然而，对于不经常更新的数据表中，不需要执行此操作。

我们可以提供特定的表，甚至运行分析，领域特定的表，所以我们可以根据实际情况，分析手术只对一部分信息更新更加频繁，这不仅可以节约的统计信息所占用的空间，还可以提高运营效率分析。这里的附加说明的需要，分析是一个非常快的操作，即使在大量的表格数据，因为它采用了随机抽样方法进行抽样，而不是每一行中的数据进行读取和分析。因此，可以考虑对整个数据库定期执行命令。

事实上，我们甚至可以通过以下命令调整指定字段的采样率，例如：

复制代码代码如下所示：

修改表的数据修改列的test_col集统计200
注：取值范围为0～1000，值越低，采样率越低，分析结果的准确性越低，但分析命令执行速度越快，如果值设置为- 1，则字段的采样率将恢复到系统当前的默认采样值。我们可以通过以下命令获得当前系统的默认采样值。

复制代码代码如下所示：

Postgres = #显示default_statistics_target；

default_statistics_target

---------------------------

一百

（1行）
从上面的结果可以看出，数据库的默认采样值是100（10%）。

三、真空分析实例：
复制代码代码如下所示：

# 1。创建测试数据表。

Postgres = #创建表的数据（我的整数）；

创建表

# 2。为测试表创建索引。

Postgres = #创建数据索引testtable_idx（我）；

创建索引

# 3。创建批插入测试数据的函数。

Postgres = #创建或替换功能test_insert（返回）的整数作为美元

声明

最小的整数；

马克斯整数；

开始

select count（*）为最小的数据；

马克斯：= min + 10000；

为了我，Max LOOP。

插入的数据值（I）；

结束循环；

返回0；

结束；

语言plpgsql美元；

创建函数

# 4。批处理将数据插入到测试表（执行四次）

Postgres = #选择test_insert（）；

test_insert

—

零

（1行）

# 5。确认四批插入都是成功的。

Postgres = # select count（*）从数据；

计数

-----

四万零四

（1行）

6的#。分析测试表，关于表的统计信息更新到PostgreSQL系统表。

Postgres = #分析数据；

分析

# 7。看目前的测试表和索引占用的页面数量（通常是每一页是8K）。

Postgres = #选择relname，relfilenode，relpages从pg_class哪里relname = 'testtable'or relname relname = =；

relname relfilenode relpages | |

--------------- + ------------- + ----------

数据| 17601 | 157

testtable_idx | 17604 | 90

# 8。批处理删除数据。

Postgres = #删除数据在哪里＜30000；

删除30003

# 9。执行真空和分析更新系统表并记录表和索引的高水位。

什么# 10。这里需要添加的是，如果删除的话，上述删除的数据都位于数据表的前面。

#如我> 10000，所以在真空的实现分析，数据表将减少物理。

Postgres = #真空分析数据；

分析

# 11。检查删除后的测试表和索引的结果，并通过真空分析更新系统统计信息。

Postgres = #选择relname，relfilenode，relpages从pg_class哪里relname = 'testtable'or relname relname = =；

relname relfilenode relpages | |

--------------- + ------------- + ----------

数据| 17601 | 157

testtable_idx | 17604 | 90

（2行）

# 12。将批处理转换为两次，然后对表进行分析以更新其统计数据。

Postgres = #选择test_insert（）；执行两次。

test_insert

—

零

（1行）

Postgres = #分析数据；

分析

# 13。这时，您可以看到数据表中的页面数量仍然是高水位标记的数量，索引页的数量也增加了。

#是其内部的实现相关，但在插入后，对索引的网页数量会继续增加。

Postgres = #选择relname，relfilenode，relpages从pg_class哪里relname = 'testtable'or relname relname = =；

relname relfilenode relpages | |

--------------- + ------------- + ----------

数据| 17601 | 157

Testtable_idx | 17604 | 173

（2行）

Postgres = #选择test_insert（）；

test_insert

—

零

（1行）

Postgres = #分析数据；

分析

# 14。可以看到索引上的页面数量没有继续增加。

Postgres = #选择relname，relfilenode，relpages从pg_class哪里relname = 'testtable'or relname relname = =；

relname relfilenode relpages | |

--------------- + ------------- + ----------

数据| 17601 | 157

testtable_idx | 17604 | 173

（2行）

# 15。重新批处理删除数据。

Postgres = #删除数据在哪里＜30000；

删除19996

# 16。从下面的查询显示，在执行完真空完全命令之后，测试表和索引占用的页数

#确实减少了物理空间的占用，他们已经缩小了。

Postgres = #真空全数据；

真空

Postgres = #选择relname，relfilenode，relpages从pg_class哪里relname = 'testtable'or relname relname = =；

relname relfilenode relpages | |

--------------- + ------------- + ----------

数据| 17602 | 118

testtable_idx | 17605 | 68

（2行）
四。定期重建索引：

在PostgreSQL，需要重建索引（索引索引）经常和频繁的数据更新数据表，B树索引，只有索引页将彻底被反复使用，对于那些可用的空间只有部分的索引页将不被重用，如果大多数的页面的关键索引被删除，只留下很少的一部分，所以页面将不会被释放，重用。在这种极端的情况下，因为每个索引页的利用率很低，一旦数据量明显增加，这将导致巨大的索引文件，这不仅降低了查询的效率，但也可能整个磁盘空间被完全填充。

另一个性能优势也存在于索引的重建中，因为在新的索引中，物理页面中的逻辑连接通常是链接在一起的，这样可以提高连续读取磁盘页面的概率，从而提高IO的运行效率：

# 1。此时已经在这个表中插入了大约6万个数据，下面的SQL语句将查询索引占用的磁盘空间。

复制代码代码如下所示：

Postgres = #选择relname pg_relation_size（OID） / 1024'k'as大小从pg_class哪里| | relkind =我和relname = 'testtable_idx；

relname大小|

---------------- + ------

Testtable_idx 1240K |

（1行）

# 2。删除数据表中的大部分数据。

Postgres = #删除数据在哪里> 20000；

删除50006

# 3。分析一个表，以便后面的SQL语句将继续查看索引所占用的空间。

Postgres = #分析数据；

分析

# 4。从这个查询的结果可以看出索引所使用的空间并没有减少，而是与前一个完全相同。

Postgres = #选择pg_relation_size（'testtable_idx '） / 1024'k的大小| |；

大小

。

1240k

（1行）

# 5。重建索引。

Postgres = #重建索引testtable_idx；

重建

# 6。看看重建指数实际所占的空间，可以看出指数的规模已经缩小了。

Postgres = #选择pg_relation_size（'testtable_idx '） / 1024'k的大小| |；

大小

。

368k

（1行）

记得的最后一件事# 7。是在索引重建之后必须分析数据表。

Postgres = #分析数据；

分析
五。观察磁盘的使用：

1。检查数据表占用的磁盘页数。

复制代码代码如下所示：

# relpages只能通过真空，进行更新，和几个DDL命令，如创建索引。通常一个页面的长度为8K字节。

Postgres = #选择relfilenode，从pg_class哪里relname = 'testtablerelpages；

relfilenode relpages |

------------- + ----------

16412 | 79

（1行）
2。查看指定数据表的索引名称和索引占用的磁盘页数。

复制代码代码如下所示：

Postgres = #选择c2.relname，从pg_class C，pg_class C2 c2.relpages，pg_index我

在c.relname = 'testtable'and c.oid = i.indrelid和c2.oid = i.indexrelid

为了c2.relname；

relname relpages |

--------------- + ----------

testtable_idx | 46

（1行）