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.

PostgreSQL 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),'muthu');

INSERT 0 500000

muthu=# \dt+

List of relations

--------+------+-------+----------+-------+-------------

(1 row)

muthu=# update siva set name ='chandru' where id > 250000;

UPDATE 250000

muthu=# \dt+

List of relations

--------+------+-------+----------+-------+-------------

(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

Search This Blog

DBA Master