The Hidden Transaction
Even if your query isn’t a transaction, it is a transaction, bahaha.Hey, I’m Vinay, and recently i was reading about how PostgreSql handles queries internally, and was genuinely curious to understand.
I use PostgreSql all the time in my projects anyway, so it just made sense to deep dive in internal working of Postgresql. While going through it, i found a behavior most people don't even realise exists, and figured i should write about it.
Let's first talk about how a query even reaches the backend process ?
So, Postmaster is the main server process that is always listening for incoming connections on a specified port number (5432 is default), and whenever this process recieves a connection request, it spaws a new "backend process" dedicated to handling that client's session.
this child"backend process"manages all the interactions with client from parsing of the query to the exectuion.
Inside the backend process: parsing and transaction determination
Once the backend process receives the query, it first invokes theRAW PARSER (built with FLEX and BISON) to check SQL syntax and produce the initial"Parse Tree"
Note: FLEX and BISON transformscan.l andgram.y files to C file, these auto-generated C files implement the lexar grammer.
By inspecting the first node of the Parse tree , PostgreSQL figures out whether the query should be treated as an Implicit or Explicit transaction.
The transaction is handled in two primary ways only, Autocommitand Explicit transaction. PostgreSql by default operates in autocommit mode, meaing each individual statement is treated as a seprate transaction.
After transaction determination, the PostgreSql calls StartTransactionCommand function to initiate an active Transaction Context using (XID, snapshot, Locks)
Operations and steps encapsulated within the transaction context
After parsing and transaction determination, further critical processes take place inside a transaction context, but but but... what even is a transaction context?
So, basically transaction context is an active environmentwhere PostgreSQL keeps track of locks, changes, and snapshots for a query, making sure everything can either be safely committed or completely rolled back incase something goes wrong
(1) Semantic analysis and rewriting: This phase actually ensures that the query is not only syntactically correct but also semantically valid and optimized for executioin, and once the PostgreSql has completed the semantic analysis and query rewriting phase, ...It proceeds to the
(2) Planning and optimization : In this stage, the planner evaluates multiple strategies tocreate plan tree in the most efficient way based on factors like: Cost estimation, Index consideration, and Join strategies ...now the
(3) Executorwalks in, and start traversing the plan tree, and execute each node(scans, joins, filters) as specified.
the executor intracts with shared memory structures, such as shared buffers, to read pages efficiently, and for write operations, the executor changes are recorded in the WAL buffer (Write-Ahead Log) to ensure durability.
Important Executing within the transaction context guarantees that all operations are atomic and isolated, adhering to the ACID properties, if an error occurs, the transaction can be rolled back, reverting all changes.
Executor returning back the control to Transaction Manager
Once the executor has processed the query plan, it returns the control to the transaction manager.
In PostgreSql, transaction finalisation depends on wheather the transaction is explicit or implit. For explicit transactions, the transaction manager maintains the transaction context until it encounters an explicit COMMIT or ROLLBACK command from the client.
In contrast, implicit transactions operate under PostgreSql's default autocommit mode, so upon successful execution, the transaction manager automatically commits the changes.
before finalising the commit, PostgreSql writes the transaction's changes to the WAL (Write-Ahead Log), and releases all the acquired locks. This helps durability, so even in any crash happens, the changes can be recovered.
And finally here it returns a success acknowledgment to the client, and the transaction manager terminates the transaction context, freeing allocated resources.
Summary time
"Even if your query isn't a transaction, it is a transation" ,So we saw this behavior of PostgreSQL happening above, where every query, whether a simple SELECT or a multiple queries wrapped inside BEGIN/COMMIT runs inside a transation context to ensure ACID applies.
We also saw postgreSQL automatically wrapping standalone queries into implicit transactions and treating BEGIN/COMMIT blocks as explicit transactions, it tracks changes, locks, and snapshots during execution so that everything can be either safely committed or fully rolled back. This hidden transactional layer is what keeps your database consistent and reliable without you even realising it.