Articles by Shiv Iyer

About Shiv Iyer

Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.

Why SELECT * from is bad for SQL performance?

Using SELECT * can be bad for SQL performance for several reasons, including the following: Overall, it is best practice to only select the columns that are needed for a query, rather than using SELECT […]

Understanding the output from SHOW ENGINE INNODB STATUS\G command

The output from the command “SHOW ENGINE INNODB STATUS\G” in MySQL provides a detailed status report of the InnoDB storage engine. Here’s a breakdown of the various sections of the output: The “SHOW ENGINE INNODB […]

How PL/Python is implemented in PostgreSQL?

PL/Python is a procedural language for PostgreSQL that allows you to write stored procedures, triggers, and functions in Python. The PL/Python language is implemented as an extension module for PostgreSQL, which means that it’s not […]

How Latches are implemented in PostgreSQL?

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, […]

Configuring PostgreSQL for optimal usage of available RAM

PostgreSQL has several configuration parameters that control its memory usage. Here is a detailed description of some of the most important memory-related parameters: It is important to monitor the memory usage of the database process […]

How to implement parallel queries in PostgreSQL 15?

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 […]

How long checkpoint queues influence PostgreSQL recovery?

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 […]

PostgreSQL Error locking mutex 22

The error message “Error locking mutex 22” in PostgreSQL is typically caused by a problem with the system’s memory management or by a misconfiguration in the database. This error message usually occurs when PostgreSQL is […]

How are undo blocks implemented in PostgreSQL internally?

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 […]

What are the possible scenarios for deadlock in PostgreSQL?

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 […]