PostgreSQL Performance

PostgreSQL Internals

How Pessimistic Locking works in PostgreSQL?

Pessimistic locking in PostgreSQL relies on explicit lock commands, allowing transactions to acquire locks on specific database objects. These locks prevent other transactions from accessing or modifying the locked objects until the system releases the […]
No Picture
PostgreSQL Internals

Understanding PostgreSQL Page Structure

“Unlocking PostgreSQL’s Storage Power: Exploring the Architecture and Implementation of Page Structure” The page structure in PostgreSQL forms the backbone of its storage system, efficiently storing and retrieving data on disk. Let’s examine the architecture […]
No Picture
PostgreSQL Troubleshooting

Troubleshooting PostgreSQL queries not using indexes

When PostgreSQL queries are not utilizing indexes effectively, it can significantly impact query performance. Here are some troubleshooting steps to identify and resolve issues with queries not using indexes efficiently: Remember that improving index utilization requires […]
No Picture
PostgreSQL Troubleshooting

How to control automatic maintenance activities in PostgreSQL?

In PostgreSQL, there are several automatic maintenance activities that occur to keep the database healthy and optimized. These activities include: auto vacuuming, auto analyzing, and auto checkpointing.  PostgreSQL Maintenance Activities While these automated processes are crucial for maintaining […]
PostgreSQL Internals

Configuring PostgreSQL for optimal UNDO and REDO operations

To configure PostgreSQL for optimal UNDO and REDO operations, you must consider several configuration parameters that directly control the behavior of the database engine. Specifically, these parameters determine how PostgreSQL manages transaction logging and recovery, […]
No Picture
PostgreSQL Internals

How PostgreSQL uses maintenance_work_mem for VACUUM?

In PostgreSQL, the maintenance_work_mem configuration parameter controls how much memory the system allocates for maintenance operations, including VACUUM. When you run a VACUUM operation, PostgreSQL sorts and processes data pages. This process helps reclaim disk […]