PostgreSQL Vacuum Process

-

VACUUM:

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are 
deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.

Vacuum Options:

1.VACUUM:

VACUUM  simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. 
        However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table.

Syntax:

vacuum table_name;
vacuum table_name (column_name);

2.FULL:

Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. 
        Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

Syntax:

vacuum full table_name;
vacuum full table_name (column_name);

3.FREEZE:

Freezing is this process of marking old live tuples. Selects aggressive “freezing” of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero.
Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

Syntax:

vacuum freeze table_name;
vacuum full freeze table_name;
vacuum freeze table_name (column_name);
vacuum full freeze table_name (column_name);

4.VERBOSE:

Prints a detailed vacuum activity report for each table.

Syntax:

vacuum verbose table_name;
vacuum verbose table_name (column_name);

vacuum full verbose table_name;
vacuum full verbose table_name (column_name);

vacuum freeze verbose table_name;
vacuum freeze verbose table_name (column_name);

vacuum full freeze verbose table_name;
vacuum full freeze verbose table_name (column_name);

5. ANALYZE:

Updates statistics used by the planner to determine the most efficient way to execute a query.

Syntax:

vacuum analyze table_name;
vacuum analyze table_name (column_name);

vacuum verbose analyze table_name;
vacuum verbose analyze table_name (column_name);

vacuum full verbose analyze table_name;
vacuum full verbose analyze table_name (column_name);

vacuum freeze verbose analyze table_name;
vacuum freeze verbose analyze table_name (column_name);

vacuum full freeze verbose analyze table_name;
vacuum full freeze verbose analyze table_name (column_name);

Example:

muthu=# create table siva(id int,name text);
CREATE TABLE
muthu=# alter table siva set (autovacuum_enabled =off);
ALTER TABLE
muthu=# insert into siva values (generate_series(1,500000),'thanigai');
INSERT 0 500000
muthu=# \dt+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | siva | table | postgres | 21 MB | 
(1 row)

muthu=# update siva set name ='chandru' where id > 250000;
UPDATE 250000
muthu=# \dt+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | siva | table | postgres | 32 MB | 
(1 row)

muthu=# \x 
Expanded display is on.

Check Dead Tuple Count and size:

muthu=# select * from pg_stat_all_tables where relname='siva';
-[ RECORD 1 ]-------+-------
relid               | 24725
schemaname          | public
relname             | siva
seq_scan            | 1
seq_tup_read        | 500000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 500000
n_tup_upd           | 250000
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 500000
n_dead_tup          | 250000
n_mod_since_analyze | 750000
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

muthu=# create extension pgstattuple ;
CREATE EXTENSION
muthu=# select * from pgstattuple('siva');
-[ RECORD 1 ]------+---------
table_len          | 33218560
tuple_count        | 500000
tuple_len          | 18250000
tuple_percent      | 54.94
dead_tuple_count   | 250000
dead_tuple_len     | 9250000
dead_tuple_percent | 27.85
free_space         | 105020
free_percent       | 0.32

Vacuum Process:

muthu=# vacuum ANALYZE VERBOSE siva;
INFO:  vacuuming "public.siva"
INFO:  "siva": removed 250000 row versions in 1352 pages
INFO:  "siva": found 250000 removable, 500000 nonremovable row versions in 4055 out of 4055 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 669
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.17 s, system: 0.00 s, elapsed: 0.24 s.
INFO:  vacuuming "pg_toast.pg_toast_24725"
INFO:  index "pg_toast_24725_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_24725": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 669
There were 0 unused item pointers.
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.
INFO:  analyzing "public.siva"
INFO:  "siva": scanned 4055 of 4055 pages, containing 500000 live rows and 0 dead rows; 30000 rows in sample, 500000 estimated total rows
VACUUM
muthu=# \dt+
List of relations
-[ RECORD 1 ]---------
Schema      | public
Name        | siva
Type        | table
Owner       | postgres
Size        | 32 MB
Description | 

muthu=# select * from pgstattuple('siva');
-[ RECORD 1 ]------+---------
table_len          | 33218560
tuple_count        | 500000
tuple_len          | 18250000
tuple_percent      | 54.94
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 10105020
free_percent       | 30.42

Vacuum Full Process:

muthu=# vacuum FULL VERBOSE ANALYZE siva ;
INFO:  vacuuming "public.siva"
INFO:  "siva": found 0 removable, 500000 nonremovable row versions in 4055 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.56 s, system: 0.04 s, elapsed: 1.58 s.
INFO:  analyzing "public.siva"
INFO:  "siva": scanned 2703 of 2703 pages, containing 500000 live rows and 0 dead rows; 30000 rows in sample, 500000 estimated total rows
VACUUM
muthu=# select * from pgstattuple('siva');
-[ RECORD 1 ]------+---------
table_len          | 22142976
tuple_count        | 500000
tuple_len          | 18250000
tuple_percent      | 82.42
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 67292
free_percent       | 0.3

muthu=# \dt+
List of relations
-[ RECORD 1 ]---------
Schema      | public
Name        | siva
Type        | table
Owner       | postgres
Size        | 21 MB
Description | 
muthu=# update siva set name ='siva' where id > 250000;
UPDATE 250000
muthu=# \dt+
List of relations
-[ RECORD 1 ]---------
Schema      | public
Name        | siva
Type        | table
Owner       | postgres
Size        | 32 MB
Description | 

Vacuum  freeze Process:

muthu=# vacuum FREEZE ANALYZE siva ;
VACUUM
muthu=# select * from pgstattuple('siva');
-[ RECORD 1 ]------+---------
table_len          | 33218560
tuple_count        | 500000
tuple_len          | 17500000
tuple_percent      | 52.68
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 10105020
free_percent       | 30.42

muthu=# \dt+
List of relations
-[ RECORD 1 ]---------
Schema      | public
Name        | siva
Type        | table
Owner       | postgres
Size        | 32 MB
Description | 
muthu=# vacuum full freeze analyze siva;
VACUUM
muthu=# \dt+
List of relations
-[ RECORD 1 ]---------
Schema      | public
Name        | siva
Type        | table
Owner       | postgres
Size        | 21 MB
Description | 

muthu=# select * from pgstattuple('siva');
-[ RECORD 1 ]------+---------
table_len          | 22142976
tuple_count        | 500000
tuple_len          | 17500000
tuple_percent      | 79.03
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 67292
free_percent       | 0.3

muthu=# vacuum ANALYZE siva (name);
VACUUM
muthu=# vacuum FULL ANALYZE siva (name);
VACUUM
muthu=# vacuum freeze ANALYZE siva (name);
VACUUM
muthu=# vacuum full freeze ANALYZE siva (name);
VACUUM

Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportionally to the number of PostgreSQ
Read more

PostgreSQL Migration Using MTK

-
Enterprisedb Migration Toolkit: EDB Migration Toolkit is a powerful command-line tool that offers granular control of the migration process. Migration Toolkit facilitates migration of database objects and data to an EDB Postgres Advanced Server (Advanced Server) or PostgreSQL database from: Oracle MySQL Microsoft SQL Server Sybase which objects migrate to postgres Oracle Objects: Schemas   Tables    List-Partitioned Tables         Range-Partitioned Table         Constraints   Indexes   Triggers         Table Data   Views     Materialized Views         Packages         Procedures   Functions       Sequences       Users/Roles   Profiles   Object Types   Object Type Methods   Database Links     Queues       MySQL Objects: Schemas Tables Constraints Indexes Table Data Microsoft SQL Server Objects: Schemas Tables Constraints Indexes Table Data Sybase Obj
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma
Read more