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.

PostgreSQL COMMIT: What It Does Internally

In PostgreSQL, a COMMIT statement is used to finalize a transaction and make its changes permanent in the database. When a COMMIT statement is executed, the following things happen internally in PostgreSQL: After a transaction […]

MySQL Ransactions: What Happens to Uncommitted Transactions After a Crash

In MySQL, if the server crashes after an update and the transaction was not committed, the changes made by the transaction will be rolled back during the crash recovery process. This is because MySQL uses […]

SELECT * SQL Performance: Why SELECT * Is Bad

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

PostgreSQL Latches: How They Are Implemented

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

PostgreSQL Parallel Queries: How to Implement Them 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 […]

PostgreSQL Checkpoint Queues: How Long Queues Influence 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 Undo Blocks: How They Are Implemented 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 […]

PostgreSQL Deadlock: Possible Scenarios Explained

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

InnoDB Ordered vs Random Index Insertion: Performance Impact

The impact of ordered versus random index insertion in InnoDB depends on several factors, including the size and complexity of the table, the frequency of inserts, and the workload of the database. Ordered index insertion […]

The Data Transformation Company

Data Architecture, Engineering and Operations for SQL, NoSQL, NewSQL, Cloud Native Data Platforms, Analytics and AI