Psycopg

See every query your app sends to PostgreSQL through Psycopg (the statement, how long it took, and which ones failed) as a span (one unit of work with a name, a start, and a duration) in Logfire. Related spans link together into a trace (the full journey of one request), so a slow query shows up right next to the code that triggered it.

This works with both the psycopg (Psycopg 3) and psycopg2 packages.

What you’ll capture

• Each query as a span, with its duration and any errors
• The SQL statement that ran
• Which database the query went to

Before you start

You’ll need a Logfire project. Open Add data in your project (top navigation) and follow the setup for your language: it signs your machine in with logfire auth (a browser sign-in, no token to copy) and, for production or other languages, creates a write token (the credential your app uses to send data). New to Logfire? Start with Getting Started.

Installation

Install logfire with the psycopg extra:

pip

Terminal

pip install 'logfire[psycopg]'

uv

Terminal

uv add 'logfire[psycopg]'

Or, if you use psycopg2, install the psycopg2 extra instead:

pip

Terminal

pip install 'logfire[psycopg2]'

uv

Terminal

uv add 'logfire[psycopg2]'

Usage

Add two lines to your app: logfire.configure() to connect to your project, and logfire.instrument_psycopg() to record every query.

The example below connects to a local PostgreSQL database. If you don’t have one running, you can start one with Docker:

Terminal

docker run --rm --name postgres \
    -e POSTGRES_USER=user \
    -e POSTGRES_PASSWORD=secret \
    -e POSTGRES_DB=database \
    -p 5432:5432 \
    -d postgres

This gives you a database you can reach at postgres://user:secret@127.0.0.1:5432/database.

main.py

import psycopg

import logfire

logfire.configure()
logfire.instrument_psycopg()  # instrument whichever of psycopg/psycopg2 is installed

connection = psycopg.connect('dbname=database user=user password=secret host=127.0.0.1 port=5432')

with logfire.span('Create table and insert data'), connection.cursor() as cursor:
    cursor.execute('CREATE TABLE IF NOT EXISTS test (id serial PRIMARY KEY, num integer, data varchar);')

    # Insert some data
    cursor.execute('INSERT INTO test (num, data) VALUES (%s, %s)', (100, 'abc'))
    cursor.execute('INSERT INTO test (num, data) VALUES (%s, %s)', (200, 'def'))

    # Query the data
    cursor.execute('SELECT * FROM test')

Run it with python main.py.

Verify it worked

Run your program, then open your project in the Logfire web app and go to the Live view. Within a few seconds you should see a span for each query the script ran. Click one to see the SQL statement and how long it took.

Troubleshooting

Not seeing your queries in Logfire? Check these first:

• logfire.configure() runs before logfire.instrument_psycopg(). Configure the connection first, then instrument.
• You call instrument_psycopg() exactly once. With no argument it instruments the whole module; pass a connection to instrument just that one.
• Your write token is set. In local development, run logfire projects use <your-project>; in production, set the LOGFIRE_TOKEN environment variable. See Getting Started.
• You actually ran a query. Spans appear only after a statement executes.

Advanced

Choosing what to instrument

You can instrument the whole module, a single package by name, or one connection:

import psycopg

import logfire

logfire.configure()

# Instrument the whole module:
logfire.instrument_psycopg(psycopg)
# or by name:
logfire.instrument_psycopg('psycopg')
# or instrument whichever modules (psycopg and/or psycopg2) are installed:
logfire.instrument_psycopg()

connection = psycopg.connect('dbname=database user=user password=secret host=127.0.0.1 port=5432')

# Or instrument just one connection:
logfire.instrument_psycopg(connection)

Adding context with SQL Commenter

SQL Commenter appends a comment to the end of each query with extra context (for example, the driver name and version). This can help tools that read your database’s own query logs correlate them back to your app. Turn it on with enable_commenter=True:

import logfire

logfire.configure()
logfire.instrument_psycopg(enable_commenter=True)

This can only be used when instrumenting the whole module, not individual connections.

By default the SQL comments include values for these keys:

• db_driver
• dbapi_threadsafety
• dbapi_level
• libpq_version
• driver_paramstyle
• opentelemetry_values

You can exclude any of these by passing a dictionary of keys mapped to False in commenter_options:

import logfire

logfire.configure()
logfire.instrument_psycopg(
    enable_commenter=True,
    commenter_options={'db_driver': False, 'dbapi_threadsafety': False},
)

Passing options to the OpenTelemetry instrumentor

logfire.instrument_psycopg() accepts additional keyword arguments and passes them to the OpenTelemetry Psycopg instrumentation. See the OpenTelemetry Psycopg and OpenTelemetry Psycopg2 documentation for the full list.

Reference

• Underlying OpenTelemetry packages: Psycopg · Psycopg2

instrument_psycopg

def instrument_psycopg(
    conn_or_module: PsycopgConnection | Psycopg2Connection,
    **kwargs: Any,
) -> None
def instrument_psycopg(
    conn_or_module: None | Literal['psycopg', 'psycopg2'] | ModuleType = None,
    enable_commenter: bool = False,
    commenter_options: PsycopgCommenterOptions | None = None,
    **kwargs: Any,
) -> None

Instrument a psycopg connection or module so that spans are automatically created for each query.

Uses the OpenTelemetry instrumentation libraries for psycopg and psycopg2.

Returns

None

Parameters

conn_or_module : Any Default: None

Can be:

• The psycopg (version 3) or psycopg2 module.
• The string 'psycopg' or 'psycopg2' to instrument the module.
• None (the default) to instrument whichever module(s) are installed.
• A psycopg or psycopg2 connection.

enable_commenter : bool Default: False

Adds comments to SQL queries performed by Psycopg, so that database logs have additional context.

commenter_options : PsycopgCommenterOptions | None Default: None

Configure the tags to be added to the SQL comments.

**kwargs : Any Default: {}

Additional keyword arguments to pass to the OpenTelemetry instrument methods, for future compatibility.

CommenterOptions

Bases: TypedDict

The commenter_options parameter for instrument_psycopg.

Attributes

db_driver

Include the database driver name in the comment e.g. ‘psycopg2’.

Type: bool

dbapi_threadsafety

Include the DB-API threadsafety value in the comment.

Type: bool

dbapi_level

Include the DB-API level in the comment.

Type: bool

libpq_version

Include the libpq version in the comment.

Type: bool

driver_paramstyle

Include the driver paramstyle in the comment e.g. ‘driver_paramstyle=pyformat’

Type: bool

opentelemetry_values

Enabling this flag will add traceparent values to the comment.

Type: bool