Database

Internals of MVCC in Postgres: Hidden Costs of Updates vs Inserts

Why do sometimes updates feel slower than inserts in Postgres? The answer lies deep in how Postgres manages data versions behind the scenes.At the core of Postgres’ ability to handle concurrent transactions efficiently is Multiversion Concurrency Control (MVCC).In this article, I’ll explore how MVCC works in Postgres and how it impacts both writes and reads, and the performance differences between inserts and updates.-- pageinspect extension to view underlying data storage, we will use it soon! CREATE EXTENSION IF NOT EXISTS...

Read

Locking in Databases and Isolation Mechanisms

should become a familiar term if you are dealing with locks. In a large application accessed by thousands of users, concurrency is inevitable. Your application should be able to handle multiple requests simultaneously. When you execute operations concurrently, the results can be conflicting. For e.g. if you are reading a row while someone else is writing to it simultaneously, then you are bound to get inconsistent data. If we execute these transactions sequentially, then we don’t need concurrency control. But...

Read