Pgbouncer Routing and Rewriting for PostgreSQL using Python.

Akash Srinivasan
Jun 24 · 4 min read
https://tinyurl.com/y3dqpf34

Query Routing:

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.

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.

[databases]
pgbouncerDB = dbname=mydb host=<> port=5432
pgbouncerDB1 = dbname=mydb1 host=<> port=5432
pgbouncerDB2 = dbname=mydb2 host=<> port=5432
routing_rules_py_module_file = ./routing_rules.py
def routing_rules(username, query):
if "tablea" in query:
return "pgbouncerDB1"
elif "tableb" in query:
return "pgbouncerDB2"
else:
return None

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.

Query Rewrite:

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.

rewrite_query_py_module_file = ./rewrite_query.py

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.

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;"
else:
new_query = query
return new_query

Conclusion:

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.

Tensult Blogs

Stories on Cloud computing, Analytics, Automation and Security

Thanks to Sumit.

Akash Srinivasan

Written by

Tensult Blogs

Stories on Cloud computing, Analytics, Automation and Security