PostgreSQL Internals

In PostgreSQL, hard parsing optimization typically takes more time when a query is submitted for the first time or when the database statistics or query planner configuration has changed significantly. This is because hard parsing […]

PostgreSQL uses latches to provide synchronization between different threads accessing shared resources. Latches are lightweight and efficient mechanisms that provide mutual exclusion between threads. Here is a table describing all the PostgreSQL latches, their purpose, […]

PostgreSQL 15 introduces a new parallel query feature called “Partitionwise Joins” which can improve the performance of queries involving large tables. Here’s how to implement parallel queries using this feature: Here’s an example query that […]

Checkpoint queues can have a significant impact on the recovery process in PostgreSQL. Checkpoints are a mechanism that PostgreSQL uses to write dirty data from memory to disk, in order to ensure that the data […]

In PostgreSQL, undo blocks are implemented using a mechanism called Multi-Version Concurrency Control (MVCC), which allows multiple versions of a row to exist in the database at the same time. When a transaction updates or […]

Deadlock is a situation where two or more transactions are blocked, waiting for each other to release locks that they hold. In PostgreSQL, deadlocks can occur in several scenarios, such as: To prevent deadlocks in […]