Memory Management with the Psycopg2 Adapter for PostgreSQL (Part 1)

Written by orkb

October 08, 2019

PostgreSQL
Psycopg2
Python

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.4 installed, and that you run your scripts using python3. Use the python3 -V command 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 psycopg2 library using the pip3 command. 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:

sudo python3 -m pip install psutil

Or you can also install it with just the pip3 install commands, like in the following example:

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:

# 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:

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:

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:

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:

# 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:

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:

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:

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)

Screenshot of IDLE for Python 3 getting the memory address of an object with memoryview

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:

# 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

#!/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