Pandas v Psycopg

A Postgres database speed test. Who wins?

Following on from a story I wrote comparing the speed of Pandas and Polars libraries in terms of reading and writing data — from and to — a Postgres database I thought it might be interesting to do a similar comparison between Pandas and Psycopg2.

If you need to get data from or to a Postgres database table from or to a local file, read on for the winner.

You can find the Pandas v Polars article at the link below:

Pandas

I don’t think I need to explain much about what Pandas is. Its use in Python code is ubiquitous and is one of the main tools that people use to load, explore, visualise and process large amounts of data in Python.

Psycopg

Psycopg is one of the most popular PostgreSQL database libraries for the Python programming language. It implements the Python Database API Specification v2.0, allowing Python applications to communicate with PostgreSQL databases.

Psycopg is designed for efficiency and thread safety. It provides a high-level, Pythonic interface for connecting to a PostgreSQL database, executing SQL statements, managing transactions, and fetching results, while also offering low-level access to PostgreSQL-specific features for advanced use cases.

Using Psycopg, Python applications can perform a variety of database operations. These include executing SQL queries and commands, manipulating large object storage in PostgreSQL, managing transactions, and handling notifications from the PostgreSQL database.

The library also supports a variety of PostgreSQL features, such as prepared statements, multiple cursors, asynchronous notifications, and COPY commands for bulk data transfers. Additionally, it supports advanced data types and methods provided by PostgreSQL, including geometric types, arrays, hstore, JSON, and others.

Psycopg’s connection pooling functionalities facilitate efficient use of resources in multi-threaded environments. Furthermore, it’s capable of handling many concurrent connections, making it a good fit for web application backends and data-intensive applications.

In summary, Psycopg is an essential tool for Python developers working with PostgreSQL databases. It combines the power of PostgreSQL with the ease and expressiveness of Python, making it an ideal choice for database-driven Python applications.

Pandas v Psycopg

For the purposes of this speed test our objective is to get data from a Postgres database table into a CSV file on your local PC or laptop. We will also go in the reverse direction, i.e. assume we have a local data file in CVS format that we want to populate a Postgres database table with.

All the code will be Python v3.11, running on a Jupyter Notebook. The version of Pandas we are using is 2.0.3 , using the default numpy backend for data typing and we will be using version 2.9.3 of the Psycopg library.

If you want to try out the below code snippets you will obviously need access to a Postgres database.

For Pandas, getting data from a database table to a local file system involves two steps, the first is reading the database table into a dataframe and the second is writing the dataframe to a file. Pandas doesn’t interact with databases directly. Instead, it farms out that aspect to third-party database “engines”, one of the most popular of which is SQLAlchemy.

Here is some code that does that. I used the %%time magic in Jupyter to time the operation. If you want to find out more about Jupyter magics and other useful tips on using Jupyter see my article below:

Postgres -> CSV file using Pandas

The Pandas code is very straightforward. Make sure you have the following libraries pip installed first:

pip install pandas psycopg2 sqlalchemy

Substitute your own values for the database credentials etc …

from sqlalchemy import create_engine
from sqlalchemy.sql import text
import pandas as pd

query = f"""
        SELECT *
        FROM your_table
    """
# Create the SQLAlchemy engine with your postgres database connection string
engine = create_engine('postgresql://your_user:your_pass@your_hostname:your_port/your_dbname')
# Query the database and save the result in a DataFrame
with engine.connect() as conn:
    df=pd.read_sql_query(text(query), con = conn)
# Write the DataFrame to a CSV file
df.to_csv("output.csv", index=False)

I ran the above code against three tables containing increasing data volumes. Each table was comprised of 10 integer columns. Here are the results (timings in seconds):

Postgres -> CSV file using Pandas
===================================

Name     |   Rows        |  Columns    |   Disk Space taken |Time to read   | File output size |
+---------+---------------+-------------+--------------------+---------------+------------------+
| Table_1 |  22,500       |    10       |   1.5 MB           |.48            |   485 KB         |
| Table_2 |  2,880,000    |    10       |   187 MB           | 31            |    62 MB         |
| Table_3 |  11,500,000   |    10       |   1300 MB          |253            |   495 MB         |
+---------+---------------+-------------+--------------------+---------------+------------------+

Note, that if your input table is really huge you may run into memory issues. If that is the case you can try using a chunking strategy to read and write the data.

Postgres -> CSV file using Psycopg2

One of the fastest ways to get data into and out of a Postgres database to CSV files using Psycopg is to use the copy_export command. Under the hood, it uses PostgreSQL’s COPY command which is a fast way to load large datasets into and out of PostgreSQL because it avoids the overhead of executing individual SQL INSERT commands.

Here is the code I used.

import psycopg2

# Set up a connection to the PostgreSQL database
connection = psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
)
# Create a cursor object
cur = connection.cursor()
# Define the COPY command
copy_cmd = "COPY your_table TO STDOUT WITH (FORMAT CSV, HEADER)"
# Open the file to write to and execute the COPY command
with open('output.csv', 'w') as f:
    cur.copy_expert(copy_cmd, f)
# Close the connection
connection.close()

I ran this against the same three test Postgres tables and the timings (in seconds) are shown below:

Postgres -> CSV file using Psycopg2
===================================

Name     |   Rows        |  Columns    |   Disk Space taken |Time to read    | File output size |
+---------+---------------+-------------+--------------------+---------------+------------------+
| Table_1 |  22,500       |    10       |   1.5 MB           |.20            |   485 KB         |
| Table_2 |  2,880,000    |    10       |   187 MB           |5              |    62 MB         |
| Table_3 |  11,500,000   |    10       |   1300 MB          |43             |   495 MB         |
+---------+---------------+-------------+--------------------+---------------+------------------+

So, to summarize the timings on getting Postgres data to a local file, the Psycopg2 method is approx 4–6 times faster on the tested data volumes.

CSV -> Postgres using Pandas

For Pandas, once again we have a two-stage process. Read the file into a dataframe, then load the dataframe to a database table. Again, the code is straightforward.

import pandas as pd
from sqlalchemy import create_engine

# Define the connection string and create engine
engine = create_engine('postgresql://your_username:your_password@localhost/your_database')
# Read the data from the file into a DataFrame
df = pd.read_csv('output.csv')
# Write the data to the database
df.to_sql('your_table', engine, if_exists='replace', index=False)

And here are the timings (in seconds). For the input files, I used the output files of the Postgres ->CSV file using Pandas operation.

CSV-> Postgres file using Pandas
=================================

Name     |   Rows        |  Columns     |   File size        |Time to write  |
+---------+---------------+-------------+--------------------+---------------+
| Table_1 |  22,500       |    10       |   485 KB           |2              |
| Table_2 |  2,880,000    |    10       |    62 MB           |215            |
| Table_3 |  11,500,000   |    10       |   495 MB           |2031           |
+---------+---------------+-------------+--------------------+---------------+

CSV -> Postgres using Psycopg2

import psycopg2

# Set up a connection to the PostgreSQL database
connection = psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
)
# Create a cursor object
cur = connection.cursor()
# Define the COPY command
copy_cmd = "COPY your_table FROM STDIN WITH (FORMAT CSV, HEADER)"
# Open the file to read from and execute the COPY command
with open('input.csv', 'r') as f:
    cur.copy_expert(copy_cmd, f)
# Commit the transaction
connection.commit()
# Close the connection
connection.close()

And here are the timings (in seconds). For the input files, I used the output files of the Postgres -> CSV file using Psycopg operation.

CSV-> Postgres file using Pandas
=================================

Name     |   Rows        |  Columns     |   File size        |Time to write  |
+---------+---------------+-------------+--------------------+---------------+
| Table_1 |  22,500       |    10       |   485 KB           |0.14           |
| Table_2 |  2,880,000    |    10       |    62 MB           |5              |
| Table_3 |  11,500,000   |    10       |   495 MB           |23             |
+---------+---------------+-------------+--------------------+---------------+

Once again pyscopg2 blows Pandas out of the water, being anywhere between 14 and 100 times faster, noting that the relative speed improvement increased substantially with increasing data volumes.

That’s all for me for now. Hopefully, you found this article useful. If you did, please check out my profile page at this link. From there, you can see my other published stories and subscribe to get notified when I post new content.

If you liked this content, you might find these articles interesting too.