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
Author: malisper
Postgres MVCC
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 in Postgres
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
Speeding up Postgres Index Scans with Index-Only Scans
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
How Postgres Plans Queries
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
explain.despez.com
The site explain.depesz.com is a wonderful tool for analyzing Postgres query plans. It takes the ordinary output of EXPLAIN or EXPLAIN ANALYZE and will pull out the relevant information from the query plan. Here’s what a query from the last … Read the rest
Using Explain Analyze in Postgres
In the last post, we discussed EXPLAIN and how it can be used to obtain the query plan for a query. EXPLAIN ANALYZE is a variation of EXPLAIN that provides additional information about the query. In addition to displaying all … Read the rest
Using Explain in Postgres
If you are ever doing any sort of query optimization in Postgres, knowledge of EXPLAIN is an absolute must. EXPLAIN let’s you see what algorithms Postgres is using under the hood in order to execute a query. Let’s look at … Read the rest
Postgres Merge Joins
This is the last part of a three part series examining the Postgres join algorithms.
While a hash join is usually the fastest join algorithm, it is only so when it can be performed in memory. When Postgres thinks the hash … Read the rest
Postgres Hash Joins
This is part two of a three part series examining the Postgres join algorithms.
Of the join algorithms Postgres has available, the hash join is usually the fastest. The main downside is hash joins only work where the join condition is an … Read the rest