PostgreSQL is a great open source project for many reasons. One of the reasons I like it is because of the design of buffer blocks addressing. In this blog, I am going to explain a possible way to share a Primary’s buffer blocks with a Standby. If you want to know more about how buffer tag works, you can refer to my previous blog.
2. Primary and Standby
In Postgres, a Primary is an active database which accepts connections and performs read-write SQL operations; a Standby is a copy of the active database, and it also accepts connection but only for read-only SQL operations. Because the Standby needs to perform the ‘copy’ action in order to synchronize with Primary database, technically they are not exactly
equal if a SQL query is time sensitive. Then, the question is can we let a Standby to be equal to a Primary?
The answer is
probably!. I recently did some experimental tests to see if a Standby can access Primary’s buffer blocks in order to check the data which has not been replicated yet. The results is very positive.
3. How to share it
To achieve this experimental remote buffer blocks access, here are a few things I did:
On Primary side,
- start a simple dedicated TCP/IP server to listen for the buffer tag request from Standby
- check if the buffer block in memory for received buffer tag
- If found and dirty then sends the 8KB buffer block to Standby
- If not found then simply answer not found
On Standby side,
- add a simple TCP/IP client for buffer manager
- send buffer tag request to Primary when buffer manager needs to load a data block from disk
- add 8KB data block to shared buffer if received from Primary
- load data block from disk if buffer block not found on Primary
- skip buffer tag check to avoid use cashed data blocks
With these basic changes, a Standby can access Primary’s buffer blocks and get the same query results even the data has not been replicated yet (in my experimental test, I actually also skip the head redo on Standby and let Standby to access Primary’s base cluster, disable the storage write actives).
In this blog, I introduced a way to experiment the access of Primary’s buffer blocks, and hope this can be a topic for further study if someone want to learn PostgreSQL in deep.
A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.