说明
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。
- 还没有人评论，欢迎说说您的想法！