Top 10 Maintenance Tasks on the PostgreSQL PaaS

Top 10 Maintenance Tasks on the PostgreSQL PaaS: Why DBAs Still Matter in the Cloud Era

There’s a common misconception that moving databases to the cloud eliminates the need for database administrators (DBAs). Many assume, “The cloud provider will manage everything.” While cloud platforms like Amazon RDS/Aurora, Azure Flexible Server, CitusDB, and Google CloudSQL/AlloyDB simplify repetitive tasks, they don’t replace the critical role of DBAs and DevOps teams in ensuring performance, security, and reliability.
In this presentation, Shailesh Rangani, Practice Lead for PostgreSQL Services at Datavail, shares insights from his PGDay Chicago 2023 session on the Top 10 Maintenance Tasks for PostgreSQL PaaS environments. These tasks are essential for organizations that want to maintain optimal database health and avoid costly downtime—even after migrating to a managed cloud service.
Key Highlights:


Design Best Practices
Learn how proper schema design and indexing strategies impact performance in cloud environments.


Database Parameter Tuning
Understand which parameters need adjustment for workload optimization.


SQL Tuning
Explore techniques for improving query efficiency and reducing resource consumption.


AutoVacuum Threshold Tuning
Prevent table bloat and maintain database health by configuring AutoVacuum effectively.


TABLE/INDEX Maintenance
Discover methods for handling bloated tables and indexes to keep performance consistent.

Partition Table Maintenance & Data Purging
Implement strategies for managing large datasets and improving query response times.

Auditing & Compliance
Ensure security and regulatory compliance through robust auditing practices.

Index Review
Regularly evaluate index usage to optimize storage and query performance.

Sequence/INT Wraparound Review
Avoid application failures by monitoring sequence limits and integer wraparound issues.

TransactionID Wraparound
Learn how to prevent catastrophic database outages caused by transaction ID exhaustion.

Why This Matters
Cloud adoption doesn’t eliminate complexity—it changes it. DBAs remain critical for proactive monitoring, tuning, and maintenance to ensure high availability and compliance. This session provides actionable steps to keep your PostgreSQL PaaS environment running smoothly.
Learning Objectives:

Understand the top maintenance tasks for PostgreSQL in cloud environments.
Learn best practices for tuning and auditing.
Explore strategies for preventing performance degradation and outages.
Gain insights into real-world scenarios and lessons learned.

👉 Download the full presentation and learn more here:
https://www.datavail.com/resources/top-10-maintenance-tasks-postgresql-paas/

1. Top 10
Maintenance
Tasks on the
PostgreSQL PaaS
PRESENTED BY:
Shailesh Rangani
Director and Global Practice Lead –PostgreSQL
Datavail

2. www.datavail.com 2
Shailesh Ranganiis Practice Lead of PostgreSQL
Services with 18+ years’ experience in the database
domain.
He holds certifications on cloud platforms like AWS,
Azure, and OCI, along with database platforms like
Oracle, PostgreSQL, MongoDB, and Db2 LUW.
He is an expert in the design, deployment,
administration, and management of data-intensive
applications that enable organizations to effectively
analyze and process large volumes of structured and
unstructured data.
S
Shailesh
Rangani
Director & Global
Practice Lead -
PostgreSQL,
Datavail

3. www.datavail.com 3
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 3

4. What is PostgreSQL
PaaS

5. www.datavail.com 5
Scan the QR Code
For a Chance to Win a Dunkin’
Donuts Gift Card and Coffee Tumbler!

6. www.datavail.com 6
PostgreSQL PaaS Example
Amazon RDS Amazon Aurora Azure Flexible Server Azure Cosmos DB for
PostgreSQL
AlloyDB for
PostgreSQL

7. www.datavail.com 7
PostgreSQL -IaaS Vs PaaS
Database Installation/ConfigurationCustomer Managed Cloud Provider Managed
Backup Configuration Customer Managed Cloud Provider Managed
OS Patching Customer Managed Cloud Provider Managed
DB Patching Customer Managed Cloud Provider Managed
Monitoring DB operations Customer Managed Cloud Provider Managed
Disaster Recovery Customer Managed Cloud Provider Managed
Storage management Customer Managed Cloud Provider Managed
Advanced Threat Protection Customer Managed Cloud Provider Managed
DB Recovery Customer Managed Cloud Provider Managed
High Availability Customer Managed Cloud Provider Managed
Auto Scaling/Read Replica Customer Managed Cloud Provider Managed
PostgreSQL -IaaS PostgreSQL -PaaSTasks

8. So…
Do We Need
a DBA?
Is the
DBA Role
Changing?

9. Top 10 Maintenance
Tasks on the
PostgreSQL PaaS

10. Design Best
Practices

11. www.datavail.com 11
1: Design Best Practices
Object Naming Standards
Users/Roles
Data type selection
Table Partitioning
Use of DB Extensions
sc_parts-Schema
tb_location -Table
inx_location_id -Index
vw_location -View
fn_get_parts_info() -Function
•read only role for parts DBparts_ro
•read/write role for parts DBprarts_rw
•read only role for parts except PII
data
parts_restricted_ro
•deployment role for parts DBparts_deploy
SmallIntvs Int vs BigInt
Char vs Varchar
Geometric Types
Network Address Types
Arrays
Range
List
Hash
TimescaleDB
pgCrypto
PostGIS
uuid-ossp
hstore
fuzzystrmatch

12. DB Parameter
Tuning

13. www.datavail.com 13
2: DB Parameter Tuning
Reporting and Logging
Memory management
Checkpoint
Auto Vacuum
Resource Usage
log_checkpoints
log_connections
log_lock_waits
log_temp_files
log_autovacuum_min_duration
log_statement
log_min_duration_statement
shared_buffers
work_mem
autovacuum_work_mem
maintenance_work_mem
temp_buffers
checkpoint_timeout
checkpoint_completion_target
checkpoint_warning
checkpoint_flush_after
max_wal_size
autovacuum_analyze_scale_factor
autovacuum_vacuum_scale_factor
autovacuum_max_workers
autovacuum_naptime
autovacuum_work_mem
autovacuum_freeze_max_age
max_parallel_workers
max_parallel_maintenance_workers
max_parallel_workers_per_gather
bgwriter_lru_maxpages
bgwriter_delay

14. www.datavail.com 14
2: DB Parameter Tuning
pgTune:
https://github.com/le0pard/pgtune
Inputs from Monitoring/Logs
Example 1:
•LOG: checkpoints are occurring too frequently
(X seconds apart)
•HINT: Consider increasing the configuration parameter
"max_wal_size"
Example 2:
•Too many temp files getting generated
•HINT: Consider tuning the configuration parameter “work_mem“ or possible query tuning

15. SQL
Tuning

16. www.datavail.com 16
3: SQL Tuning
What if you can get a daily report on…
Top long running SQLs
Top frequent SQLs (Number of execution)
Top SQLs generating most Temp files
Top SQLs doing physical reads
Top SQLs waiting for the locks to get released
Possible Solution:
pgBadgerreport
pgCollector
pg_stat_statement

17. Auto Vacuum
Tuning

18. www.datavail.com 18
4: Auto Vacuum Tuning
Why does PostgreSQL need Vacuum?
•PostgreSQL doesn’t physically remove records as part of Update & Delete
•Which causes “dead tuple” and the autovacuumprocess cleans the dead tuples
The idea is to tune the autovacuumprocess in such a way that..
•It cleans up dead tuples often and in small chunks
•Doesn’t cause a performance impact (don’t perform too often which may impact OS resources)
AutovacuumTuning Parameters:
•autovacuum_analyze_scale_factor
•autovacuum_vacuum_scale_factor
•autovacuum_max_workers
•autovacuum_naptime
•autovacuum_work_mem
•autovacuum_vacuum_cost_limit
•autovacuum_vacuum_cost_delay
•autovacuum_freeze_max_age

19. www.datavail.com 19
4: Auto Vacuum Tuning
Let’s say the PARTS table size is 100 million
records
Default autovacuumtriggering criteria:
•autovacuum_vacuum_scale_factor
(20%) +
autovacuum_vacuum_threshold (50)
Autovacuumwill get triggered when
20,000,000 records get modified
(delete/update)
This could happen in the middle of the day
or at peak traffic points
Possible Solution:
alter table tb_partsset
(autovacuum_vacuum_scale_factor =0.02,
autovacuum_vacuum_scale_factor =50);
autovacuumwill get triggered when 2,000,000 records
get modified
alter table tb_partsset
(autovacuum_vacuum_scale_factor =0,
autovacuum_vacuum_scale_factor =10000);
autovacuumwill get triggered when 10,000 records get
modified

20. The TABLE/INDEX
Bloats

21. www.datavail.com 21
5: The TABLE/INDEX Bloats
What is BLOAT?
It happens when Table or Indexes are updated
An update is a delete and insert operation
Space used by delete is available for reuse but only after
vacuum
Speed of dead tuple vacuum < speed of delete/update on the
table
Dead tuple being clean/second < Dead tuple being
generated/second

22. www.datavail.com 22
5: The TABLE/INDEX Bloats
How to Find Bloat
•Schedule job once a week which sends report on top bloated Tables/Indexes
How to Prevent
•Tune auto vacuum parameters
Maintenance tasks on bloated tables/indexes
•Full Vacuum
•REINDEX
•pg_pack(online full vacuum)
•Adjust fillfactorfor table and index
•ALTER TABLE <table_name> SET ( fillfactor= 90);

23. Table
Partitions

24. www.datavail.com 24
6: Table Partitions
PostgreSQL Partitioning Types:
•Range, List, and Hash
Migrating stand alone table to partition table
Creation of the new partitions
Detach and Attach partitions
Drop partitions
Move old partitions to AWS S3 bucket or Azure Blob Storage
pg_partmanis possible option for the partition maintenance
Purge partitions/tables based on predefined policies/GDPR needs

25. Updating
Statistics

26. www.datavail.com 26
7: Updating Statistics
Analyze (auto analyze) command updates statistics for the planner in pg_statstable
What is the sample size the analyze command uses?
•Total number of rows analyzed = 300 ×default_statistics_target= 30,000
For smaller table, this sample size may be good enough
For larger table, planner may not have precise information causing stale information
Possible Solution
•Increase default_statistics_targetto larger value (default is 100)
•Increase sampling size for given columns
•ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> SET STATISTICS 200;

27. www.datavail.com 27
7: Updating Statistics

28. Indexes
Review

29. www.datavail.com 29
8: Indexes Review
Unused Indexes
Missing Foreign Key indexes
Duplicate indexes
Bloated Indexes
Corrupted indexes
•index "inx_index_name" contains corrupted page at block 67845
•To Detect corruption, use amcheckextension
•To fix, REINDEX <INX_INDEX_NAME>

30. Sequences
Review

31. www.datavail.com 31
9: Sequences Review
Data type selection while creating Sequence
Modification of the data type
Monitoring Sequence Wraparound

32. Transaction
Wraparound

33. www.datavail.com 33
10: Transaction Wraparound
The transaction ID is a 32-bit number = 4 billions
•2 billion in visible past and rest is for future transactions
WARNING: database "PARTSDB" must be vacuumed within 76939934
transactions
HINT: To avoid a database shutdown, execute a database -wide VACUUM
in that database.
ERROR: database is not accepting commands to avoid wraparound data
loss in database "PARTSDB"
HINT: Stop the postmaster and vacuum that database in single -user
mode.

34. www.datavail.com 34
10:
Transaction
Wraparound
Monitoring Sequence
Wraparound
•Monitor :
age(datfrozenxid)

35. www.datavail.com 35
Scan the QR Code
For a Chance to Win a Dunkin’
Donuts Gift Card and Coffee Tumbler!

36. www.datavail.com 36

37. www.datavail.com 37
Thank You
Shailesh Rangani
Director & Global Practice Lead : PostgreSQL Services
[email protected]
[phone]