Hi folks, in this blog, we are going to take a look into query routing and rewrite pgbouncer-rr setup for PostgreSQL using Python.
If you are new to Pgbouncer-rr concept, please refer to this blog.
Below is a brief introduction about pgbouncer-rr.
Note: rr stands for Routing and Rewriting.
Route: It sends queries to different server from one client connection.
Rewrite: We can change or alter the client query programmatically.
We are using python for routing and rewriting and PostgreSQL as a database. So we expect some basic understanding of these technologies. Now let’s dive into the concept.
From the title, we can easily understand this Routing features. What is routing? It’s a process of moving a packet of data from source to destination. Instead of data packets here, we are going to route query from one client connection to different server connection and this is called Query Routing. In this Routing Feature, we are going to use python routing function to map client connection to server connection. If we send a query from the client connection this python routing function will be called for each and every query. It must be ensured that your Python routing functions correctly handle any server-specific grammar in your queries.
NOTE: In Query Routing cross-database joins or multi-server transactions do not work!
The Python routing function is dynamically loaded by pgbouncer-rr, from the file you specify in the configuration:
routing_rules_py_module_file = /etc/pgbouncer-rr/routing_rules.py
Simple Query Routing Example:
The main concept for query routing in pgbouncer is to route the query. Database 1 has a table ‘tablea’ and DataBase 2 has table ‘tableb’. You can send a query to both the tables without knowing which table is in which database, and you can access both the tables from one database.
Create an entry with a key ‘pgbouncerDB’ in the [database] section for pgbouncer configuration. This entry is used to connecting directly from client connection to DataBase ‘mydb’. Create additional entries in pgbouncer [database] section.
NOTE: Give the unique key name such as ‘pgbouncerDB1’ and ‘pgbouncerDB2’.
pgbouncerDB = dbname=mydb host=<> port=5432
pgbouncerDB1 = dbname=mydb1 host=<> port=5432
pgbouncerDB2 = dbname=mydb2 host=<> port=5432
In this pgbouncer.ini configuration file, we have to mention the python routing function file such as
routing_rules_py_module_file = ./routing_rules.py
This is the python routing_rules function:
def routing_rules(username, query):
if "tablea" in query:
elif "tableb" in query:
In this routing_rules function, we are passing the username associated with client and client query string to the function. If the client sends a query “SELECT * FROM tablea;” it will match to the first condition and it will assign to pgbouncerDB1. If the client sends a query “SELECT * FROM tableb;” it will match to the second condition and it will assign to pgbouncerDB2. Any query that does not match either conditions it returns none and the server connection remains unchanged.
The keys should remember before getting into the code:
- Ensure all dbkey values are defined in [database] section of the pgbouncer ini file.
- Routing function called from pgbouncer-rr so does not change the name.
- Implement REGEX (regular expression) rules defined in routing table objects.
- Test by calling routing_rules() with sample queries, and validating dbkey values returned.
Here is an alternative function for the same use case, but the routing logic is defined in a separate data structure using REGEX (regular expressions) to find the table matches. In Python, the first thing to do is to import the regex module into your script with import re. The “re” module provides excellent support for the regular expression.
From this above python code, we can easily observe the concept of pgbouncer Routing. You will most likely want to implement more rules, but take care to avoid unintended matches. Write test cases to call your function with different inputs and validate the output dbkey values.
In this feature, we can alter the client query bt programmatically before they are sent to the server. It provides the opportunity to manipulate application queries en route to the server without modifying application code.
The rewrite function is also implemented in the python function. It dynamically loaded from an external module specified in the configuration.
rewrite_query_py_module_file = ./rewrite_query.py
Implementing a query rewrite function is straightforward when the queries have fixed formats that are easily detectable and easily manipulated using regular expression search or replace logic in the Python function. We already discussed REGEX (regular expression) in query routing. It is much more challenging to build a rewrite function to handle SQL statements with arbitrary format and complexity.
Simple Query Rewrite Example:
The main concept of query rewriting is to rewrite or modify the client query. Let me explain with an example. If we send a query “SELECT name FROM products;” to rewriting_query function. In this function we already assign strings “q1” and “q2” followed by some conditions if the given query match with “q1” the client query will change to “SELECT * FROM products ORDER BY name;” this will store in new_query and it will return the new_query. If the query is not matching with any other condition simply it will return the query.
This is the python rewriting_query function:
def rewrite_query(username, query):
q1="SELECT name FROM products"
q2="SELECT name FROM users"
if re.match(q1, query):
new_query = "SELECT * FROM products ORDER BY name;"
elif re.match(q2, query):
new_query = "SELECT * FROM users ORDER BY name;"
new_query = query
In “mydb1” database we creating a table name “products” with the column as “name” and “price”. In “mydb2” database we creating a table name “users” with the column as “s.no” and “name”.
By this query rewriting feature, we can change the particular client query without modifying the application code.
I hope that I was able to explain about query routing and rewriting in pgbouncer. Let me know if you face any difficulties and do share your responses in the comment section below.