PgSQL · 应用案例 · 什么情况下可能表膨胀

postgres=# select relname from pg_class   
where reltablespace in   
(select oid from pg_tablespace where spcname='pg_global')   
and relkind='r';    
        relname          
-----------------------  
 pg_authid  
 pg_subscription  
 pg_database  
 pg_db_role_setting  
 pg_tablespace  
 pg_pltemplate  
 pg_auth_members  
 pg_shdepend  
 pg_shdescription  
 pg_replication_origin  
 pg_shseclabel  
(11 rows)

postgres=#   
select relname from pg_class where relkind='r'   
and relnamespace ='pg_catalog'::regnamespace   
except   
select relname from pg_class where reltablespace in   
(select oid from pg_tablespace where spcname = 'pg_global')   
and relkind='r';

         relname           
-------------------------  
 pg_language  
 pg_sequence  
 pg_largeobject  
 pg_policy  
 pg_ts_template  
 pg_attrdef  
 pg_operator  
 pg_ts_parser  
 pg_depend  
 pg_attribute  
 pg_ts_config  
 pg_conversion  
 pg_inherits  
 pg_subscription_rel  
 pg_publication  
 pg_foreign_table  
 pg_largeobject_metadata  
 pg_ts_dict  
 pg_statistic  
 pg_init_privs  
 pg_opfamily  
 pg_type  
 pg_am  
 pg_default_acl  
 pg_proc  
 pg_index  
 pg_rewrite  
 pg_statistic_ext  
 pg_constraint  
 pg_opclass  
 pg_partitioned_table  
 pg_namespace  
 pg_trigger  
 pg_enum  
 pg_amop  
 pg_event_trigger  
 pg_collation  
 pg_foreign_server  
 pg_foreign_data_wrapper  
 pg_user_mapping  
 pg_description  
 pg_cast  
 pg_publication_rel  
 pg_aggregate  
 pg_transform  
 pg_extension  
 pg_class  
 pg_seclabel  
 pg_amproc  
 pg_range  
 pg_ts_config_map  
(51 rows)

postgres=# select pg_create_logical_replication_slot('a','test_decoding');  
 pg_create_logical_replication_slot   
------------------------------------  
 (a,0/92C9C038)  
(1 row)

postgres=# select * from pg_get_replication_slots();  
 slot_name |    plugin     | slot_type | datoid | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn   
-----------+---------------+-----------+--------+-----------+--------+------------+------+--------------+-------------+---------------------  
 a         | test_decoding | logical   |  13585 | f         | f      |            |      |      1982645 | 0/92C9BFE8  | 0/92C9C038  
(1 row)

postgres=# select pg_xact_commit_timestamp(xmin),pg_xact_commit_timestamp(catalog_xmin) from pg_get_replication_slots();  
psql: ERROR:  could not get commit timestamp data  
HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.

postgres=# select pg_walfile_name(restart_lsn) from pg_get_replication_slots();  
     pg_walfile_name        
--------------------------  
 000000010000000000000092  
(1 row)

postgres=# select * from pg_stat_file('pg_wal/000000010000000000000092');  
   size   |         access         |      modification      |         change         | creation | isdir   
----------+------------------------+------------------------+------------------------+----------+-------  
 16777216 | 2019-06-29 22:56:16+08 | 2019-07-01 09:50:16+08 | 2019-07-01 09:50:16+08 |          | f  
(1 row)

postgres=# select * from pg_ls_waldir() where name='000000010000000000000092';  
           name           |   size   |      modification        
--------------------------+----------+------------------------  
 000000010000000000000092 | 16777216 | 2019-07-01 09:50:16+08  
(1 row)

postgres=# create table b(id int);  
CREATE TABLE  
postgres=# insert into b values (1);  
INSERT 0 1

postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);  
    lsn     |   xid   |      data        
------------+---------+----------------  
 0/92C9C0C0 | 1982645 | BEGIN 1982645  
 0/92CA4A40 | 1982645 | COMMIT 1982645  
(2 rows)

postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);  
    lsn     |   xid   |                 data                    
------------+---------+---------------------------------------  
 0/92CA4A78 | 1982646 | BEGIN 1982646  
 0/92CA4A78 | 1982646 | table public.b: INSERT: id[integer]:1  
 0/92CA4AE8 | 1982646 | COMMIT 1982646  
(3 rows)

postgres=# delete from b;  
DELETE 1

postgres=# vacuum verbose b;  
psql: INFO:  vacuuming "public.b"  
psql: INFO:  "b": removed 1 row versions in 1 pages  
psql: INFO:  "b": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1982648  
There were 0 unused item identifiers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
psql: INFO:  "b": truncated 1 to 0 pages  
DETAIL:  CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s  
VACUUM

postgres=# create table c (id int);  
CREATE TABLE  
postgres=# drop table c;  
DROP TABLE  
postgres=# create table c (id int);  
CREATE TABLE  
postgres=# drop table c;  
DROP TABLE

postgres=# vacuum verbose pg_class;  
psql: INFO:  vacuuming "pg_catalog.pg_class"  
psql: INFO:  "pg_class": found 0 removable, 465 nonremovable row versions in 13 out of 13 pages  
DETAIL:  2 dead row versions cannot be removed yet, oldest xmin: 1982646  
There were 111 unused item identifiers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
VACUUM

postgres=# vacuum verbose pg_attribute ;  
psql: INFO:  vacuuming "pg_catalog.pg_attribute"  
psql: INFO:  "pg_attribute": found 0 removable, 293 nonremovable row versions in 6 out of 62 pages  
DETAIL:  14 dead row versions cannot be removed yet, oldest xmin: 1982646  
There were 55 unused item identifiers.  
Skipped 0 pages due to buffer pins, 55 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
VACUUM

postgres=# begin;  
BEGIN  
postgres=# delete from a;  
DELETE 1

db1=# create table b(id int);  
CREATE TABLE  
db1=# insert into b values (1);  
INSERT 0 1  
db1=# delete from b;  
DELETE 1  
db1=# vacuum verbose b;  
psql: INFO:  vacuuming "public.b"  
psql: INFO:  "b": removed 1 row versions in 1 pages  
psql: INFO:  "b": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1982671  
There were 0 unused item identifiers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
psql: INFO:  "b": truncated 1 to 0 pages  
DETAIL:  CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s  
VACUUM

switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))  
{  
  case HEAPTUPLE_DEAD:

    *  
     * Ordinarily, DEAD tuples would have been removed by  
     * heap_page_prune(), but it's possible that the tuple  
     * state changed since heap_page_prune() looked.  In  
     * particular an INSERT_IN_PROGRESS tuple could have  
     * changed to DEAD if the inserter aborted.  So this  
     * cannot be considered an error condition.  
     *  
     * If the tuple is HOT-updated then it must only be  
     * removed by a prune operation; so we keep it just as if  
     * it were RECENTLY_DEAD.  Also, if it's a heap-only  
     * tuple, we choose to keep it, because it'll be a lot  
     * cheaper to get rid of it in the next pruning pass than  
     * to treat it like an indexed tuple. Finally, if index  
     * cleanup is disabled, the second heap pass will not  
     * execute, and the tuple will not get removed, so we must  
     * treat it like any other dead tuple that we choose to  
     * keep.  
     *  
     * If this were to happen for a tuple that actually needed  
     * to be deleted, we'd be in trouble, because it'd  
     * possibly leave a tuple below the relation's xmin  
     * horizon alive.  heap_prepare_freeze_tuple() is prepared  
     * to detect that case and abort the transaction,  
     * preventing corruption.  
     */  
    if (HeapTupleIsHotUpdated(&tuple) ||  
        HeapTupleIsHeapOnly(&tuple) ||  
        params->index_cleanup == VACOPT_TERNARY_DISABLED)  
      nkeep += 1;  
    else  
      tupgone = true; * we can delete the tuple */  
    all_visible = false;  
    break;

  case HEAPTUPLE_RECENTLY_DEAD:

    *  
     * If tuple is recently deleted then we must not remove it  
     * from relation.  
     */  
    nkeep += 1;  
    all_visible = false;  
    break;

*       HeapTupleSatisfiesVacuum()  
 *                visible to any running transaction, used by VACUUM

/*  
 * HeapTupleSatisfiesVacuum  
 *  
 *      Determine the status of tuples for VACUUM purposes.  Here, what  
 *      we mainly want to know is if a tuple is potentially visible to *any*  
 *      running transaction.  If so, it can't be removed yet by VACUUM.  
 *  
 * OldestXmin is a cutoff XID (obtained from GetOldestXmin()).  Tuples  
 * deleted by XIDs >= OldestXmin are deemed "recently dead"; they might  
 * still be visible to some open transaction, so we can't remove them,  
 * even if we see that the deleting transaction has committed.  
 */  
HTSV_Result  
HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,  
                                                 Buffer buffer)  


        *  
         * Deleter committed, but perhaps it was recent enough that some open  
         * transactions could still see the tuple.  
         */  
        if (!TransactionIdPrecedes(HeapTupleHeaderGetRawXmax(tuple), OldestXmin))  
                return HEAPTUPLE_RECENTLY_DEAD;  


                else if (TransactionIdDidCommit(xmax))  
                {  
                        *  
                         * The multixact might still be running due to lockers.  If the  
                         * updater is below the xid horizon, we have to return DEAD  
                         * regardless -- otherwise we could end up with a tuple where the  
                         * updater has to be removed due to the horizon, but is not pruned  
                         * away.  It's not a problem to prune that tuple, because any  
                         * remaining lockers will also be present in newer tuple versions.  
                         */  
                        if (!TransactionIdPrecedes(xmax, OldestXmin))  
                                return HEAPTUPLE_RECENTLY_DEAD;  

                        return HEAPTUPLE_DEAD;  
                }

Hot Standby feedback message (F)  
Byte1('h')  
Identifies the message as a Hot Standby feedback message.  

Int64  
The client's system clock at the time of transmission, as microseconds since midnight on 2000-01-01.  

Int32  
The standby's current global xmin, excluding the catalog_xmin from any replication slots. If both this value and the following catalog_xmin are 0 this is treated as a notification that Hot Standby feedback will no longer be sent on this connection. Later non-zero messages may reinitiate the feedback mechanism.  

Int32  
The epoch of the global xmin xid on the standby.  


Int32  
The lowest catalog_xmin of any replication slots on the standby. Set to 0 if no catalog_xmin exists on the standby or if hot standby feedback is being disabled.  


Int32  
The epoch of the catalog_xmin xid on the standby.