PostgreSQL 中系統表 pg_attribute 膨脹現象

PostgreSQL 一覽表 pg_attribute存儲關于表列的資訊，資料庫中每張表中的行都會對應在該系統表 pg_attribute 中。既然存儲的是資料庫中表字段相關的資訊，那麼對表所做的修改都會通過該表記錄。如建立表指定的列，修改表，修改表的資料類型等等。

說明

   PostgreSQL 一覽表 pg_attribute存儲關于表列的資訊，資料庫中每張表中的行都會對應在該系統表 pg_attribute 中。既然存儲的是資料庫中表字段相關的資訊，那麼對表所做的修改都會通過該表記錄。如建立表指定的列，修改表，修改表的資料類型等等。

建立自定義函數檢視某張表的資訊

postgres=# CREATE OR REPLACE FUNCTION f_get_table_column_info(varchar,varchar)
postgres-# RETURNS TABLE
postgres-# (
postgres(# 模式名稱             varchar,
postgres(# 表名稱               varchar,
postgres(# 表所屬表空間          varchar,
postgres(# 表對應列名稱          varchar,
postgres(# 表對應列資料類型oid   oid,
postgres(# 表對應列順序編号      integer,
postgres(# 辨別列               text
postgres(# )
postgres-# AS
postgres-# $FUNCTION$
postgres$# SELECT a.schemaname,
postgres$#        a.tablename,
postgres$#        a.tablespace,
postgres$#        d.attname,
postgres$#        d.atttypid,
postgres$#        d.attnum,
postgres$#        d.attidentity
postgres$# FROM pg_tables a,
postgres$#      pg_class b,
postgres$#      pg_attribute d
postgres$# WHERE b.oid = d.attrelid
postgres$#   AND a.tablename = b.relname
postgres$#   AND d.attrelid = $1::regclass
postgres$#   AND a.schemaname = $2
postgres$#   AND a.schemaname !~ 'pg_catalog|information_schema'
postgres$#   AND d.attname !~ 'xmin|xmax|cmax|cmin|ctid|tableoid';
postgres$# $FUNCTION$
postgres-# LANGUAGE SQL;
CREATE FUNCTION      

建立測試表

postgres=# CREATE TABLE tab_product
postgres-# (                  
postgres(#     id int generated by default as identity
postgres(#     (start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
postgres(#     product_name varchar(80),
postgres(#     product_date date,
postgres(#     product_vendor varchar(80)
postgres(# );
CREATE TABLE      

檢視表字段資訊

postgres=# SELECT * FROM f_get_table_column_info('tab_product','public');
 模式名稱 |   表名稱    | 表所屬表空間 |  表對應列名稱  | 表對應列資料類型oid | 表對應列順序編号 | 辨別列 
----------+-------------+--------------+----------------+---------------------+------------------+--------
 public   | tab_product |              | id             |                  23 |                1 | d
 public   | tab_product |              | product_name   |                1043 |                2 | 
 public   | tab_product |              | product_date   |                1082 |                3 | 
 public   | tab_product |              | product_vendor |                1043 |                4 | 
(4 rows)      

  當然，這裡并非僅僅對該表存儲的是資料庫中的表列做描述，而是為了處理在系統表中依然存在表膨脹的現象，如在生産環境中，通常會做删除表或者删除 **schema** 的動作，在 **PostgreSQL** 中，隻要有對表或者 **schema** 的删除動作，那麼就會造成該表 **pg_attribute** 的膨脹。如下：

在某個schema下建立表

postgres=# CREATE TABLE s1.tab_product
postgres-# (
postgres(#     id int generated by default as identity
postgres(#     (start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
postgres(#     product_name varchar(80),
postgres(#     product_date date,
postgres(#     product_vendor varchar(80)
postgres(# );
CREATE TABLE      

檢視表列資訊

postgres=# SELECT * FROM f_get_table_column_info('tab_product','s1');
 模式名稱 |   表名稱    | 表所屬表空間 |  表對應列名稱  | 表對應列資料類型oid | 表對應列順序編号 | 辨別列 
----------+-------------+--------------+----------------+---------------------+------------------+--------
 s1       | tab_product |              | id             |                  23 |                1 | d
 s1       | tab_product |              | product_name   |                1043 |                2 | 
 s1       | tab_product |              | product_date   |                1082 |                3 | 
 s1       | tab_product |              | product_vendor |                1043 |                4 | 
(4 rows)      

檢視表 pg_attribute 的大小

postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
 pg_size_pretty 
----------------
 464 kB
(1 row)      

此處對 s1 schema 進行删除重建10000次

[postgres@pgnode01 ~]$ cat drop_rebuild_s1.sh
#!/bin/bash
CONNINFO="psql -U postgres -d postgres -Atq -c"
DROP_REBUILDSCHEMA="DROP SCHEMA IF EXISTS s1 CASCADE;CREATE SCHEMA IF NOT EXISTS s1"
CREATE_TABLE="
CREATE TABLE IF NOT EXISTS s1.tab_product
(
    id int generated by default as identity
    (start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
    product_name varchar(80),
    product_date date,
    product_vendor varchar(80)
) "
for i in {1..10000};do
    if [  $i -le 10000 ];then
        ${CONNINFO} "${DROP_REBUILDSCHEMA}"
        ${CONNINFO} "${CREATE_TABLE}"
        echo "$i"
    else
        exit 1;
    fi
    i=$i+1;
done      

檢視 pg_attribute 大小

postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
 pg_size_pretty 
----------------
 4248 kB
(1 row)      

對 pg_attribute 執行 VACUUM FULL

postgres=# VACUUM FULL pg_attribute ;
VACUUM
postgres=# SELECT pg_size_pretty(pg_relation_size('pg_attribute'));
 pg_size_pretty 
----------------
 440 kB
(1 row)      

     當對表執行完 VACUUM FULL 操作時，該膨脹的空間會返還給作業系統

結語

   在實際生産環境中，有可能會忽略該表膨脹問題，會導緻資料庫中的表和資料沒多少，但是資料庫特别大，那麼說明有可能是資料庫系統表中的部分表發生了膨脹現象。該現象也會出現在基于 pg 開發的其他資料庫中，如 Greenplum。