Heap only tuples, otherwise known as HOT, is an optimization Postgres uses to reduce the amount of I/O necessary for updates. Due to MVCC, an update in Postgres consists of finding the row being updated, and inserting a new … Read the rest
Write-ahead logging, or as it’s commonly referred to, WAL, is an optimization Postgres uses to minimize disk I/O while still preventing data loss. Intuitively, whenever a transaction completes, a record of every single change that transaction made must have been … Read the rest
In the last post, I discussed MVCC and how it allows Postgres to create snapshots of the current state of the database. One piece I didn’t mention is how Postgres handles failed transactions. The answer is through an object called … Read the rest
MVCC, which stands for multiversion concurrency control, is one of the main techniques Postgres uses to implement transactions. MVCC lets Postgres run many queries that touch the same rows simultaneously, while keeping those queries isolated from each other.
Postgres handles … Read the rest
Transactions are one of the main features of Postgres that make Postgres a great database. When code is ran in a transaction, Postgres provides a set of guarantees about the behavior of the code. Altogether, this set of guarantees is … Read the rest
As mentioned previously, indexes are a datastructure that allow for both quickly locating rows that satisfy some condition and for obtaining rows sorted by some order. To execute a regular index scan, Postgres scans through the index to find … Read the rest
Query execution in Postgres proceeds in two steps. First Postgres plans the query. This is where Postgres decides how it’s going to execute the query. The result of planning is a query plan which details specifically what kind of scans … Read the rest