PostgreSQL:系统表pg_attribute膨胀解决方案

场景模拟

​ 执行一段脚本模拟数据库大量删除表创建表导致pg_attribute膨胀

• 执行脚本

select pg_relation_size('pg_attribute');
create table test001(id int4,name text);
create table test002(id int4,name text);
create or replace function f_tmp() returns void as $$
declare
begin
create temp table temp_test001 as  select * from test001;
insert into test002 select * from temp_test001 limit 1;
end;
$$
language plpgsql;
 select pg_relation_size('pg_attribute');

• 执行脚本效果

ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
           368640
(1 row)

ysys=# create table test001(id int4,name text);
CREATE TABLE
ysys=# create table test002(id int4,name text);
CREATE TABLE
ysys=# create or replace function f_tmp() returns void as $$
ysys$# declare
ysys$# begin
ysys$# create temp table temp_test001 as  select * from test001;
ysys$# insert into test002 select * from temp_test001 limit 1;
ysys$# end;
ysys$# $$
ysys-# language plpgsql;
CREATE FUNCTION
ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
           368640
(1 row)

• 利用shell脚本完成1000次执行调度

$ cat /home/ysys/cde.sql
select f_tmp()
$ cat d1000.sh

#!/bin/bash

for i in {1..1000}
do
  psql -f /home/ysys/cde.sql -d ysys
done
$ chmod +x d1000.sh
$ ./d1000.sh

• 脚本可能需要多执行几遍查看膨胀效果(vacuum)

ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
          1490944

方案一 推荐方案

vacuum full pg_attribute;

方案二（测试通过)

第一步要找到pg_attribute会有几个文件(表文件,索引文件)

ysys=# \d+ pg_attribute
                        Table "pg_catalog.pg_attribute"
    Column     |   Type    | Modifiers | Storage  | Stats target | Description
---------------+-----------+-----------+----------+--------------+-------------
 attrelid      | oid       | not null  | plain    |              |
 attname       | name      | not null  | plain    |              |
 atttypid      | oid       | not null  | plain    |              |
 attstattarget | integer   | not null  | plain    |              |
 attlen        | smallint  | not null  | plain    |              |
 attnum        | smallint  | not null  | plain    |              |
 attndims      | integer   | not null  | plain    |              |
 attcacheoff   | integer   | not null  | plain    |              |
 atttypmod     | integer   | not null  | plain    |              |
 attbyval      | boolean   | not null  | plain    |              |
 attstorage    | "char"    | not null  | plain    |              |
 attalign      | "char"    | not null  | plain    |              |
 attnotnull    | boolean   | not null  | plain    |              |
 atthasdef     | boolean   | not null  | plain    |              |
 attisdropped  | boolean   | not null  | plain    |              |
 attislocal    | boolean   | not null  | plain    |              |
 attinhcount   | integer   | not null  | plain    |              |
 attcollation  | oid       | not null  | plain    |              |
 attacl        | aclitem[] |           | extended |              |
 attoptions    | text[]    |           | extended |              |
 attfdwoptions | text[]    |           | extended |              |
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

第二步尽量在这个期间不要出现新表的创建

ysys=# create table test003 as select * from pg_attribute where 1 = 0;
SELECT 0
ysys=# begin;
BEGIN
ysys=# lock pg_attribute;
LOCK TABLE
ysys=# insert into test003 select * from pg_attribute;
INSERT 0 2493
ysys=# commit;
COMMIT
ysys=# select count(1) from test003;
 count
-------
  2493
(1 row)

ysys=# select count(1) from pg_attribute;
 count
-------
  2493
(1 row)

第三步 只要获取当前pg_attribute的relfilenode就可以了

​ 索引是通过(attrelid,attname)(attrelid, attnum)元组创建,后期需要重新reindex即可

ysys=# select pg_relation_filepath(oid) from pg_class where relname='pg_attribute';
 pg_relation_filepath
----------------------
 base/28460/1249
(1 row)

ysys=# select pg_relation_filepath(oid) from pg_class where relname='test003';
 pg_relation_filepath
----------------------
 base/28460/46499
(1 row)

第四步 cp复制即可

[ysys@gh003 ~]$ cd /usr/local/pgsql/data/base/28460/
[ysys@gh003 28460]$ cp 1249 /home/ysys/1249_bak
[ysys@gh003 28460]$ cp 46499 1249
[ysys@gh003 28460]$ ls -lt
total 12744
-rw-------. 1 ysys ysys  376832 Jul 16 00:26 1249
-rw-------. 1 ysys ysys  376832 Jul 16 00:21 46499

第五步 需要重启

pg_ctl stop -m fast
pg_ctl start

第六步 重建索引

ysys=# reindex table pg_attribute;
REINDEX

​ 逻辑上来说,也可以创建类似索引提供出来替换目录