Skip to main content

Welcome to DBA Master – Database Tips, Tricks, and Tutorials

Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Post a Comment

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

 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


Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database 19C Performance Tunning - PART 1

Advantages: 1. Improved Query Performance •    Optimized SQL execution plans lead to faster query response times. •    Reduces unnecessary full table scans and improves indexing strategies. •    Parallel execution tuning speeds up large data processing tasks. 2. Better Resource Utilization •    Efficient use of CPU, memory, disk I/O, and network resources. •    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache. •    Helps in load balancing across multiple instances in RAC (Real Application Clusters). 3. Increased System Scalability •    Ensures that the database can handle a growing number of users and transactions. •    Proper tuning allows scaling without degrading performance. •    Optimized parallel processing ensures better performance on multi-core servers. 4. Lower Infrastructure Costs •    Reduces the need for add...
Post a Comment
Read more

Oracle RMAN Backup And Restore

RMAN: (Oracle 8) RMAN (Recovery Manager) is a utility provided by Oracle Database to perform backup, restore, and recovery operations. It is a command line tool. Features of RMAN in Oracle 19c Comprehensive Backup Capabilities: Full and incremental backups. Block-level backups for efficient data storage. Archived redo log backups. Fast Recovery Area (FRA) integration for centralized backup storage. Efficient Recovery Options: Point-in-time recovery (PITR). Complete and incomplete recovery. Flashback database capabilities for quick undo of changes. Multitenant Database Support: RMAN fully supports container databases (CDBs) and pluggable databases (PDBs). Provides flexibility to back up and recover individual PDBs or entire CDBs. Automatic Space Management: Manages disk space in the FRA. Automatically deletes obsolete backups and archived logs. Data Deduplication and Compression: Backup optimization through block-level deduplication. Built-in compression algorithms to reduce storage req...
Post a Comment
Read more

Oracle 19c Database Software Installation in OEL8

 Pre-requisites for OS level:            Set the static IP Address     Disable the Firewall (systemctl stop firewalld & systemctl disable firewalld)     set SELINUX=permissive on /etc/selinux/config  ##Need to restart the server use init 6 Oracle Installation Pre-requisites Methods     Automatic Setup     Manual Setup      Automatic requisites Setup: (avoid step 1 to step 5): dnf install -y oracle-database-preinstall-19c Install the dependencies: curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm Manual Setup: step 1: Add the karenl parameters and values vi /etc/sysctl.conf     fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel....
Post a Comment
Read more