hardbyte
commented
5 years ago For the flask app in backend/database/util.py we have a single point where we get a database connection. In theory being attached to the application context it shares one db connection for a whole request, but perhaps we should be using a connection pool here. See example gist
Some links on connection pooling:
- psycopg2 has connection pooling classes - https://pynative.com/psycopg2-python-postgresql-connection-pooling/ and https://bbengfort.github.io/observations/2017/12/06/psycopg2-transactions.html
- A whole new library/container could manage this - https://pgbouncer.github.io/
Example using psycopg2:
import psycopg2
from psycopg2 import pool
try:
threaded_postgreSQL_pool = psycopg2.pool.ThreadedConnectionPool(5, 20,user = "postgres",
password = "pass@#29",
host = "127.0.0.1",
port = "5432",
database = "postgres_db")
if(threaded_postgreSQL_pool):
print("Connection pool created successfully using ThreadedConnectionPool")
# Use getconn() method to Get Connection from connection pool
ps_connection = threaded_postgreSQL_pool.getconn()
if(ps_connection):
print("successfully recived connection from connection pool ")
ps_cursor = ps_connection.cursor()
ps_cursor.execute("select * from mobile")
mobile_records = ps_cursor.fetchmany(2)
print ("Displaying rows from mobile table")
for row in mobile_records:
print (row)
ps_cursor.close()
#Use this method to release the connection object and send back ti connection pool
threaded_postgreSQL_pool.putconn(ps_connection)
print("Put away a PostgreSQL connection")
except (Exception, psycopg2.DatabaseError) as error :
print ("Error while connecting to PostgreSQL", error)
finally:
#closing database connection.
# use closeall method to close all the active connection if you want to turn of the application
if (threaded_postgreSQL_pool):
threaded_postgreSQL_pool.closeall
print("Threaded PostgreSQL connection pool is closed")
gusmith
This log shows a failure case where the flask api dies when the database refuses connections:
The postgres container logs aren't very interesting: