Memory Management with the Psycopg2 Adapter for PostgreSQL (Part 1)
Introduction to memory management in psycopg2
A memory leak is when the system is running out of free memory because an application or program fails to release objects and variables stored in a sector, or if a program fails to automatically manage memory (also known as “garbage collection”) properly. This article will demonstrate strategies for better memory management with the psycopg2 Python adapter for PostgreSQL.
NOTE: PostgreSQL also has a memory management library called SPI you can connect to to assist in streamlining your psycopg2 Python application’s efficiency.
Prerequisites to using the psycopg2 adapter for PostgreSQL
The code in this article has only been tested on Python 3, and Python 2.7 is now deprecated and losing support, so it’s recommended that you have a version of Python greater than
3.4installed, and that you run your scripts usingpython3. Use thepython3 -Vcommand in a terminal window to verify that it’s installed.You’ll need the PIP3 package manager for Python 3 working on your machine or server in order to install the
psycopg2library using thepip3command. Install the adapter using the following command in a UNIX terminal or Windows command prompt:pip3 install psycopg2.The PostgreSQL server should be installed and running, and you should have some database and table record data you can test the code on.
Install the psutil for library for Python 3
In UNIX systems there’s a program called ps (process status) that helps with managing processes that are running, and Python has its own PS library called psutil that can help manage memory while the script is running.
Some installations of Python may not come with psutil pre-installed. You can use the pip3 list command to check what packages are already installed, or use the following command, in a UNIX terminal, to install it:
1 | sudo python3 -m pip install psutil |
Or you can also install it with just the pip3 install commands, like in the following example:
1 | pip3 install psutil |
Create a Python script and import libraries to connect to PostgreSQL
The following Python code imports the psycopg2 adapter’s sql and connect libraries, as well as the psutil, sys, and gc libraries to help manage your system’s memory:
1 2 3 4 5 6 7 8 | # import the psycopg2 database adapter for PostgreSQL from psycopg2 import connect, sql # import Python's 'sys' and 'psutil' libraries import sys, psutil # import Python's garbage collection library import gc |
Use Python’s psutil library to check how memory is being used
Here’s an example of how you can use the psutil library to get some feedback about your system’s available memory while running a Python script.
Declare a Python function that returns a string about the system’s available memory
The following code will declare a Python function that returns a string telling you about the system’s available memory:
1 2 3 4 5 6 7 8 9 10 | def get_avail_memory(): # call the psutil virtual_memory() method mem = psutil.virtual_memory() # calculate the available memory avail_mem = ((mem.total - mem.used) / mem.total)*100 # concatenate a string for the memory avail_mem = "available memory: {0:.2f}%".format( avail_mem ) return avail_mem |
It should be noted that the virtual_memory() method for psutil also has an integer attribute called available that represent’s your system’s available memory:
1 | available=7777312768 |
Evaluate the available memory and invoke Python’s garbage collection
You can use the available attribute to change how your PostgreSQL application behaves in the case of a memory leak, or if some SQL transaction has exhausted your system’s available resources:
1 2 3 4 5 6 7 8 | if psutil.virtual_memory().available < 1000: """ DO SOMETHING HERE TO FREE MEMORY WHEN AVAILABILITY DROPS BELOW 1000 """ # explicitly invoke Python's garbage collection gc.collect() |
NOTE: Python generally has decent garbage collection automated, but the above gc.collect() method call is a way for you to explicitly force Python to free up some resources.
Access the ‘closed’ attribute for psycopg2 objects
All psycopg2 objects have a closed attribute that will indicate if the cursor or connection has already been closed. The following is an example of how you can use Python to print the attribute for a cursor and connection object:
1 2 3 | # check if the PostgreSQL connection and cursor objects are closed print ("connection.closed:", conn.closed) # 0 == open print ("cursor.closed:", cursor.closed) # False == open |
NOTE: The connection object’s closed attribute will have a value of either 1 (closed) or 0 (open), but the cursor object will have a boolean value of True (closed) or False (open).
Evaluate the psycopg2 cursor object’s ‘closed’ attribute
Here’s how you can evaluate the cursor’s closed attribute in order to execute some code:
1 2 3 4 5 | if cursor.closed == False: """ DO SOMETHING HERE IF CURSOR IS OPEN. """ |
Close the psycopg2 connection and cursor objects
The best way to free up resources is to just call the psycopg2 object’s close() method once all the SQL transactions are complete. Here’s how to explicitly close them by invoking the method call:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | print ("\nconnecting to PostgreSQL") conn = connect ( dbname = "some_database", user = "objectrocket", host = "localhost", password = "mypass" ) # instantiate a cursor object from the connection cursor = conn.cursor() # check if the cursor is closed if cursor.closed == False: print ("closing cursor") cursor.close() # check if the connection is closed if conn.closed == 0: print ("closing connection") conn.close() |
Use Python’s ‘memoryview’ library to verify that the cursor is closed
Python’s built-in memoryview library accepts a bytes string object and returned the memory address for the Python object. Here’s how you can pass the psycopg2 cursor object to the memoryview() function, and have it return the memory address for the cursor if it’s still open:
1 2 3 4 5 6 | try: # cast a bytes string from cursor cursor_bytes = bytes(cursor) print ("memoryview cursor:", memoryview(cursor_bytes)) except Exception as err: print ("memoryview() cursor error:", err) |
Verify that the PostgreSQL objects are closed
Once the psycopg2 objects are closed you can explicitly force Python to free up the resources by calling the built-in garbage collection library’s collect() method:
1 2 3 4 5 | # explicitly invoke Python's garbage collection gc.collect() # call the psutil function again print (get_avail_memory()) |
Conclusion to memory management while using psycopg2
This concludes part one of a two-part series explaining how to better manage a server or system’s memory while running a psycopg2 Python application for PostgreSQL. Part 2 of the series will show you how you can make changes to the application’s SQL statement executions and transactions to help it save memory and work more efficiently.
Just the Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | #!/usr/bin/python3 # -*- coding: utf-8 -*- # import the psycopg2 database adapter for PostgreSQL from psycopg2 import connect, sql # import Python's 'sys' and 'psutil' libraries import sys, psutil # import Python's garbage collection library import gc # declare a function to check avail memory with psutil def get_avail_memory(): # call the psutil virtual_memory() method mem = psutil.virtual_memory() # calculate the available memory avail_mem = ((mem.total - mem.used) / mem.total)*100 # concatenate a string for the memory avail_mem = "available memory: {0:.2f}%".format( avail_mem ) return avail_mem print ("\nconnecting to PostgreSQL") conn = connect ( dbname = "some_database", user = "objectrocket", host = "localhost", password = "mypass" ) |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started