Importance of PostgreSQL Vacuum Tuning To Optimize Database Performance PRESENTED BY Suyog Pagare
www.datavail.com 2 15 years in Database Administration Specialties: Database Migrations, Performance tuning Microsoft Certified in Azure Solution Architect Expert AWS Certified Solutions Architect AWD DB specialty S Suyog Pagare Lead DBA - PostgreSQL, Datavail Connect or follow me on LinkedIn https://www.linkedin.com/in/suyog- pagare-5a226918/
www.datavail.com 3 Fill out your session evaluation form for a chance to win a LEGO set. Put the PostgreSQL Pieces Together CHOOSE YOUR FAVORITE
www.datavail.com 4 Datavail at a Glance Delivering a superior approach to leverage data through application of a tech-enabled global delivery model & deep specialization in databases, data management, and application services. $25 M Invested in IP that improves the service experience and drives efficiency 16 + Years building and operating mission critical data and application systems 1,000 + Employees staffed 24x7, resolving over 2,000,000 incidents per year www.datavail.com 4
Agenda Database Bloat What is Vacuum and Autovacuum Best Practices for Tuning Autovacuum Dead tuples Not Yet Removable Pg_repack Other DB Parameter Tuning
www.datavail.com 6 Key Maintenance Tasks Autovacuum Manual Vacuum Analyze Index Maintenance Introduction to PostgreSQL Maintenance PostgreSQL is an advanced, open-source relational database system. Like any database, PostgreSQL requires regular maintenance to perform optimally.
www.datavail.com 7 Causes of Bloat Frequent updates and deletes: PostgreSQL uses a multi-version concurrency control (MVCC) system, which can leave behind dead tuples. No Vacuum. Inefficient use of indexes: Unused or poorly maintained indexes can also contribute to bloat. Symptoms of Database Bloat Increase Disk Usage Degraded Performance Longer Backup and Restore Times How to Identify Bloat Pgstattuple extension Pg_stat_user_tables: To monitor dead tuples and live tuple counts Understanding Database Bloat Definition: Database bloat refers to the unnecessary consumption of disk space in a database due to outdated or deleted data, and inefficiencies in storage.
www.datavail.com 8 Query to Find Bloat SELECT c.oid, n.nspname || '.' || c.relname AS ObjectName, pg_stat_get_live_tuples(c.oid) AS LiveTuples, pg_stat_get_dead_tuples(c.oid) AS DeadTuples, CASE WHEN pg_stat_get_live_tuples(c.oid) > 0 THEN (pg_stat_get_dead_tuples(c.oid)::numeric / pg_stat_get_live_tuples(c.oid))::numeric(18,3) * 100 ELSE 0 END AS DeadtupleRatio, pg_table_size(n.nspname || '.' || c.relname) AS tbl_sz, pg_size_pretty(pg_table_size(n.nspname || '.' || c.relname)) AS tbl_sz_prty, pg_size_pretty(pg_indexes_size(n.nspname || '.' || c.relname)) AS tbl_idx_sz_prty, (SELECT count(1) FROM pg_indexes i WHERE i.schemaname || '.' || i.tablename = n.nspname || '.' || c.relname) AS idx_cnt, CASE WHEN pg_table_size(n.nspname || '.' || c.relname) > 0 THEN (pg_indexes_size(n.nspname || '.' || c.relname)::numeric / (pg_indexes_size(n.nspname || '.' || c.relname) + pg_table_size(n.nspname || '.' || c.relname))) * 100 ELSE 0 END AS idx_tbl_ratio, relhastriggers FROM pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' ORDER BY pg_table_size(n.nspname || '.' || c.relname) DESC LIMIT 30;
www.datavail.com 9 Consequences of Ignoring Bloat Performance Degradation High Storage Costs Operations Challenges Transaction Id Wraparound
www.datavail.com 10 Transaction ID Wraparound What is Transaction ID (Xid) Wraparound Issue
www.datavail.com 11 Preventing and Managing Bloat Vacuum….!!! It removes dead tuples Reclaim storage back to the OS Maintain visibility map
www.datavail.com 12 Vacuum Types of Vacuum Vacuum Vacuum Full Syntax Vacuum verbose table_name; vacuum (index_cleanup true) table_name; Analyze
www.datavail.com 13 Autovacuum Automate solution to execute Vacuum No manual intervention needed Triggers it based on the defined thresholds Does not reclaim the free space
www.datavail.com 14 Understandin g Autovacuum and Autoanalyze Benefits of Autovacuum Automates Maintenance Prevents Data Corruption Optimize Performance Formula Autovacuum = autovacuum_vacuum_scale_factor * number of rows + autovacuum_vacuum_threshold Autoanalyze = autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_threshold Check status using table: pg_stat_vacuum_progress
www.datavail.com 15 Important Parameters Autovacuum_vacuum_threshold/ Autovacuum_scale_factor Autovacuum_analyze_threshold/ Autoanalyze_scale_factor Autovacuum_max_workers Autovacuum_naptime Autovacuum_cost_delay Max_worker_processes Max_parallel_workers Max_parallel_maintenance_workers Default_stats_target Tuning of Autovacuum Parameters
www.datavail.com 16 Setup a cronjob daily to execute vacuum based of % of table bloat Use vacuumdb utility to run vacuum with parallel jobs Setup aggressive Autovacuum for specific tables Monitor vacuum process For faster operation, set high maintenance_work_mem and execute manual vacuum for faster execution Best Practices
www.datavail.com 17 Dead tuple Not Yet Removed Sometimes even after manual vacuum, dead tuples are not removed. Long running transaction Abandoned replication slot DDL transaction is in process Orphan Prepared transactions SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; Standby with hot_standby_feedback=on SELECT application_name, client_addr, backend_xmin FROM pg_stat_replication ORDER BY age(backend_xmin) DESC;
www.datavail.com 18 Pg_repack Purpose Efficiently removes table and index bloat, reducing storage footprint and improving query speed. Bloat Reduction Reorganizes tables and indexes, eliminating wasted space caused by deleted or updated tuples. Pre-checks Ensure sufficient disk space and backups before running. pg_repack is a PostgreSQL extension used for reclaiming space and optimizing database performance without requiring a full table lock. Online Operation Performs maintenance tasks without significant downtime, allowing for continuous database availability.
How to Run pg_repack Install pg_repack Add pg_repack to shared_preload_libraries Create extension pg_repack on database Run command to execute pg_repack • pg_repack -d mydb You can execute pg_repack for specific table as well • pg_repack -t mytable -d mydb
Other DB Parameter Tuning https://pgtune.leopard.in.ua/ https://pgconfigurator.cybertec-postgresql.com/ https://www.pgconfig.org https://www.dbtune.com/
www.datavail.com 21 Fill out your session evaluation form for a chance to win a LEGO set. Put the PostgreSQL Pieces Together CHOOSE YOUR FAVORITE
www.datavail.com 22
www.datavail.com 23 Thank You Suyog Pagare Lead DBA: PostgreSQL Services Suyog.Pagare@datavail.com +1 720-756-4881

Importance of PostgreSQL Vacuum Tuning to Optimize Database Performance

  • 1.
    Importance of PostgreSQL Vacuum Tuning ToOptimize Database Performance PRESENTED BY Suyog Pagare
  • 2.
    www.datavail.com 2 15 yearsin Database Administration Specialties: Database Migrations, Performance tuning Microsoft Certified in Azure Solution Architect Expert AWS Certified Solutions Architect AWD DB specialty S Suyog Pagare Lead DBA - PostgreSQL, Datavail Connect or follow me on LinkedIn https://www.linkedin.com/in/suyog- pagare-5a226918/
  • 3.
    www.datavail.com 3 Fill outyour session evaluation form for a chance to win a LEGO set. Put the PostgreSQL Pieces Together CHOOSE YOUR FAVORITE
  • 4.
    www.datavail.com 4 Datavail at aGlance Delivering a superior approach to leverage data through application of a tech-enabled global delivery model & deep specialization in databases, data management, and application services. $25 M Invested in IP that improves the service experience and drives efficiency 16 + Years building and operating mission critical data and application systems 1,000 + Employees staffed 24x7, resolving over 2,000,000 incidents per year www.datavail.com 4
  • 5.
    Agenda Database Bloat What isVacuum and Autovacuum Best Practices for Tuning Autovacuum Dead tuples Not Yet Removable Pg_repack Other DB Parameter Tuning
  • 6.
    www.datavail.com 6 Key MaintenanceTasks Autovacuum Manual Vacuum Analyze Index Maintenance Introduction to PostgreSQL Maintenance PostgreSQL is an advanced, open-source relational database system. Like any database, PostgreSQL requires regular maintenance to perform optimally.
  • 7.
    www.datavail.com 7 Causes ofBloat Frequent updates and deletes: PostgreSQL uses a multi-version concurrency control (MVCC) system, which can leave behind dead tuples. No Vacuum. Inefficient use of indexes: Unused or poorly maintained indexes can also contribute to bloat. Symptoms of Database Bloat Increase Disk Usage Degraded Performance Longer Backup and Restore Times How to Identify Bloat Pgstattuple extension Pg_stat_user_tables: To monitor dead tuples and live tuple counts Understanding Database Bloat Definition: Database bloat refers to the unnecessary consumption of disk space in a database due to outdated or deleted data, and inefficiencies in storage.
  • 8.
    www.datavail.com 8 Query toFind Bloat SELECT c.oid, n.nspname || '.' || c.relname AS ObjectName, pg_stat_get_live_tuples(c.oid) AS LiveTuples, pg_stat_get_dead_tuples(c.oid) AS DeadTuples, CASE WHEN pg_stat_get_live_tuples(c.oid) > 0 THEN (pg_stat_get_dead_tuples(c.oid)::numeric / pg_stat_get_live_tuples(c.oid))::numeric(18,3) * 100 ELSE 0 END AS DeadtupleRatio, pg_table_size(n.nspname || '.' || c.relname) AS tbl_sz, pg_size_pretty(pg_table_size(n.nspname || '.' || c.relname)) AS tbl_sz_prty, pg_size_pretty(pg_indexes_size(n.nspname || '.' || c.relname)) AS tbl_idx_sz_prty, (SELECT count(1) FROM pg_indexes i WHERE i.schemaname || '.' || i.tablename = n.nspname || '.' || c.relname) AS idx_cnt, CASE WHEN pg_table_size(n.nspname || '.' || c.relname) > 0 THEN (pg_indexes_size(n.nspname || '.' || c.relname)::numeric / (pg_indexes_size(n.nspname || '.' || c.relname) + pg_table_size(n.nspname || '.' || c.relname))) * 100 ELSE 0 END AS idx_tbl_ratio, relhastriggers FROM pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' ORDER BY pg_table_size(n.nspname || '.' || c.relname) DESC LIMIT 30;
  • 9.
    www.datavail.com 9 Consequences ofIgnoring Bloat Performance Degradation High Storage Costs Operations Challenges Transaction Id Wraparound
  • 10.
    www.datavail.com 10 Transaction ID Wraparound Whatis Transaction ID (Xid) Wraparound Issue
  • 11.
    www.datavail.com 11 Preventing andManaging Bloat Vacuum….!!! It removes dead tuples Reclaim storage back to the OS Maintain visibility map
  • 12.
    www.datavail.com 12 Vacuum Types ofVacuum Vacuum Vacuum Full Syntax Vacuum verbose table_name; vacuum (index_cleanup true) table_name; Analyze
  • 13.
    www.datavail.com 13 Autovacuum Automate solutionto execute Vacuum No manual intervention needed Triggers it based on the defined thresholds Does not reclaim the free space
  • 14.
    www.datavail.com 14 Understandin g Autovacuum and Autoanalyze Benefitsof Autovacuum Automates Maintenance Prevents Data Corruption Optimize Performance Formula Autovacuum = autovacuum_vacuum_scale_factor * number of rows + autovacuum_vacuum_threshold Autoanalyze = autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_threshold Check status using table: pg_stat_vacuum_progress
  • 15.
    www.datavail.com 15 Important Parameters Autovacuum_vacuum_threshold/Autovacuum_scale_factor Autovacuum_analyze_threshold/ Autoanalyze_scale_factor Autovacuum_max_workers Autovacuum_naptime Autovacuum_cost_delay Max_worker_processes Max_parallel_workers Max_parallel_maintenance_workers Default_stats_target Tuning of Autovacuum Parameters
  • 16.
    www.datavail.com 16 Setup acronjob daily to execute vacuum based of % of table bloat Use vacuumdb utility to run vacuum with parallel jobs Setup aggressive Autovacuum for specific tables Monitor vacuum process For faster operation, set high maintenance_work_mem and execute manual vacuum for faster execution Best Practices
  • 17.
    www.datavail.com 17 Dead tupleNot Yet Removed Sometimes even after manual vacuum, dead tuples are not removed. Long running transaction Abandoned replication slot DDL transaction is in process Orphan Prepared transactions SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; Standby with hot_standby_feedback=on SELECT application_name, client_addr, backend_xmin FROM pg_stat_replication ORDER BY age(backend_xmin) DESC;
  • 18.
    www.datavail.com 18 Pg_repack Purpose Efficiently removestable and index bloat, reducing storage footprint and improving query speed. Bloat Reduction Reorganizes tables and indexes, eliminating wasted space caused by deleted or updated tuples. Pre-checks Ensure sufficient disk space and backups before running. pg_repack is a PostgreSQL extension used for reclaiming space and optimizing database performance without requiring a full table lock. Online Operation Performs maintenance tasks without significant downtime, allowing for continuous database availability.
  • 19.
    How to Runpg_repack Install pg_repack Add pg_repack to shared_preload_libraries Create extension pg_repack on database Run command to execute pg_repack • pg_repack -d mydb You can execute pg_repack for specific table as well • pg_repack -t mytable -d mydb
  • 20.
    Other DB ParameterTuning https://pgtune.leopard.in.ua/ https://pgconfigurator.cybertec-postgresql.com/ https://www.pgconfig.org https://www.dbtune.com/
  • 21.
    www.datavail.com 21 Fill outyour session evaluation form for a chance to win a LEGO set. Put the PostgreSQL Pieces Together CHOOSE YOUR FAVORITE
  • 22.
  • 23.
    www.datavail.com 23 Thank You SuyogPagare Lead DBA: PostgreSQL Services Suyog.Pagare@datavail.com +1 720-756-4881

Editor's Notes

  • #7 Start about Insert, Update, Delete statement. MVCC model. Rows marked invisible are called dead tuple Undo log maintained inside the table No space reclaimed Dead tuples accumulation Data consistency is maintained Past and Latest image Every row of Postgres has row version Transacion isolation so that each user wont be able to view the data from other transactions Locks acquired for querying the data don’t conflict with locks acquired for writing Increase disk tuple=increase bloat Increased Disk Usage: Unexpected growth in database size without a corresponding increase in data volume.Degraded Performance: Slower query response times due to larger table scans and increased I/O.Longer Backup and Restore Times: Larger database size leads to longer data transfer and processing times.
  • #9 Slow sequential scans, Eats up the storage, if dead tuples> Live tuples postgresql wont be able to update the table as xid. Performance Degradation: Increased I/O and slower query performance.Higher Storage Costs: Paying for additional storage that is not being efficiently used.Operational Challenges: Longer maintenance windows and potential downtime during necessary operations like vacuuming and backups.
  • #10 What is Transaction ID (XID)? PostgreSQL uses a 32-bit counter called Transaction ID (XID) to uniquely identify transactions. Every transaction in PostgreSQL gets a unique XID. Xmin is the start of the transaction and xmax is when transaction was last changed\updated, diff between xmin and xmax is called transaction age. Wraparound Issue: Due to the 32-bit nature of XIDs, they can only count up to approximately 2^32 (around 4 billion) before they wrap around to zero. If the database does not properly handle this wraparound, it can cause older transactions to be perceived as being in the future, leading to data visibility and corruption issues. Limited transaction ID, once upper limit is reached, ids get wraparound. Difficult for Postgres whether this transaction is in past or future. Postgres shutdown the database to protect data. We get warning message before 10 million in postgres logs and before 1 million database will goes into read only mode. Why is XID Wraparound a Concern? Data Corruption Risk: Unhandled XID wraparound can result in the inability to distinguish between old and new data, potentially leading to data corruption. Inaccessibility of Data: Transactions might become invisible or appear as uncommitted. Database Downtime: If XID wraparound is not properly managed, it can lead to an unplanned downtime as corrective actions must be taken.
  • #11 Slow sequential scans, Eats up the storage, if dead tuples> Live tuples postgresql wont be able to update the table as xid. Performance Degradation: Increased I/O and slower query performance.Higher Storage Costs: Paying for additional storage that is not being efficiently used.Operational Challenges: Longer maintenance windows and potential downtime during necessary operations like vacuuming and backups.
  • #12 Vacuum full reclaim storage back to OS, high on resource consumption, holds up lock, slower than vacuum, require extra storage disk space. By default index cleanup not done, so set index_cleanup=true.
  • #13 Slow sequential scans, Eats up the storage, if dead tuples> Live tuples postgresql wont be able to update the table as xid. Performance Degradation: Increased I/O and slower query performance.Higher Storage Costs: Paying for additional storage that is not being efficiently used.Operational Challenges: Longer maintenance windows and potential downtime during necessary operations like vacuuming and backups.
  • #14 Prevents xid wraparound, faster query execution, faster seq scan, better db performance.
  • #15 Autovacuum_vacuum_threshold 50, analyze_threshold 50 , vacuum_scale_factor .2 (20%), analyze_scale_factor 10%. Autovacuum_naptime 1 min, autovacuum_max_worker 3, Autovacuum_cost_delay: Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used. If this value is specified without units, it is taken as milliseconds. The default value is 2 milliseconds. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters. Max_worker_processes, 8, Sets the maximum number of background processes that the system can support. This parameter can only be set at server start. The default is 8. When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server. When changing this value, consider also adjusting max_parallel_workers, max_parallel_maintenance_workers, and max_parallel_workers_per_gather. Max_parallel_workers 8, Sets the maximum number of workers that the system can support for parallel operations. The default value is 8. When increasing or decreasing this value, consider also adjusting max_parallel_maintenance_workers and max_parallel_workers_per_gather. Also, note that a setting for this value which is higher than max_worker_processes will have no effect, since parallel workers are taken from the pool of worker processes established by that setting. Max_parallel_maintenance_workers, 2, Sets the maximum number of parallel workers that can be started by a single utility command. Currently, the parallel utility commands that support the use of parallel workers are CREATE INDEX only when building a B-tree index, and VACUUM without FULL option. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested number of workers may not actually be available at run time. If this occurs, the utility operation will run with fewer workers than expected. The default value is 2. Setting this value to 0 disables the use of parallel workers by utility commands.
  • #16 autovacuum_freeze_max_age: This is the max age (in no. of transactions) a table’s pg_class.relfrozenxid can attain before vacuum is forced in order to prevent XID wraparound. Default is 200m which is ~10% of the possible 2³¹ values (wraparound). Now 10% may seem too less and one might be tempted to increase it. But that would just delay the inevitable forced vacuum process further (thereby also increasing the time it’d take to complete). The right value can be found out based on your transaction burn rate i.e. how fast the XIDs will be used up. But we might just be able to get away with the default value itself if other things are configured correctly (as explained a bit later).
  • #17 Long running transactions: You can use the pg_terminate_backend() function to terminate the database session that is blocking your VACUUM. Setup statement_timeout or log_min_duration_statement_timeout A replication slot is a data structure that keeps the PostgreSQL server from discarding information that is still needed by a standby server to catch up with the primary. If replication is delayed or the standby server is down, the replication slot will prevent VACUUM from deleting old rows. Use the pg_drop_replication_slot() function to drop replication slots that are no longer needed. 3. Orphan prepared transaction: During two-phase commit, a distributed transaction is first prepared with the PREPARE statement and then committed with the COMMIT PREPARED statement. Once Postgres prepares a transaction, the transaction is kept “hanging around” until it Postgres commits it or aborts it. It even has to survive a server restart! Normally, transactions don't remain in the prepared state for long, but sometimes things go wrong and the administrator has to remove a prepared transaction manually. 4. Normally, the primary server in a streaming replication setup does not care about queries running on the standby server. Thus, VACUUM will happily remove dead rows which may still be needed by a long-running query on the standby, which can lead to replication conflicts. To reduce replication conflicts, you can set hot_standby_feedback = on on the standby server. Then the standby will keep the primary informed about the oldest open transaction, and VACUUM on the primary will not remove old row versions still needed on the standby. Vacuum_defer_cleanup_age: Specifies the number of transactions by which VACUUM and HOT updates will defer cleanup of dead row versions.