Pipeline mode

The pipeline mode is by far the biggest feature introduced in Psycopg 3.1, largely the work of Denis Laxalde and supported by Dalibo. In pipeline mode, Psycopg will send batches of commands to the server without waiting for a response for every operation, resulting in a massive speed improvement.

The pipeline mode is exposed to Python as a context block. Within the block, Psycopg will manage the pipeline in a transparent way, even allowing the use of features which break the pipeline flow, for instance to fetch results, or to manage transactions:

with conn.pipeline():
    cur = conn.cursor()
    for op in operations:
        cur.execute(op)

    with conn.transaction():
        cur.execute(op1)
        rec = cur.fetchone()
        cur.execute(op2, [rec.id])
    ...

Quantifying the speedup is difficult, as it depends on the network conditions and on the pattern of statements executed. In particularly bad conditions (250 ms of ping time between client and server), we measured a loop of 100 inserts to take 25 s in normal mode and just 0.5 s in pipeline mode, for a 50x speedup. Testing the same operation on a localhost connection, we have measured a >20x speedup (a 5000 inserts batch taking 0.3 s instead of 6.6 s). If the program requires results from the server before sending further statement (for instance to insert in a table and then use the new record's primary key to insert related records in different tables) you can expect less dramatic speedups.

Client-binding cursors

Psycopg 3 uses server-side binding, passing the query and adapted arguments separately. This allows to use several features otherwise unavailable, such as prepared statements. However, many types of statements, especially data-definition, don't support server-side parameters. The 'sql' module allows to compose statements on the client, but using it might require many changes to programs making heavy use of data-definition statements.

Psycopg 3.1 introduces a ClientCursor object, which makes psycopg2 programs easier to port. This cursor reproduces psycopg2's way of composing queries, which is not the most efficient, but for certain programs it is exactly what is needed.

with psycopg.connect(..., cursor_factory=ClientCursor) as conn:
    cur = conn.cursor()  # this is a client-side binding cursor
    # This statement doesn't support server-side parameters
    cur.execute("ALTER TABLE x ALTER y SET DEFAULT %s", ["hello"])

As a bonus, the client cursor reintroduces the handy and often requested mogrify() method, which returns the query merged with the parameters the way it is passed to the server:

cur.mogrify("ALTER TABLE x ALTER y SET DEFAULT %s", ["hell'o"])
"ALTER TABLE x ALTER y SET DEFAULT 'hell''o'"

Enum adaptation

Python has enums, PostgreSQL has enums... Why not map them into each other? Well for a start because they are actually pretty different from each other (Python enums have a type and value, Postgres ones are just identities) and because often, in programs, differences between the enums in the db and the code creep in.

Psycopg 3.1 introduces a flexible adapter between Python and Postgres enums. It can be used in a simple way when there is a one-to-one mapping between the enums:

=# CREATE TYPE numbers AS ENUM ('ONE', 'TWO', 'THREE');

class Numbers(Enum):
    ONE = auto()
    TWO = auto()
    THREE = auto()

info = EnumInfo.fetch(conn, "numbers")
register_enum(info, conn, Numbers)

conn.execute("SELECT 'TWO'::numbers").fetchone()[0]
<Numbers.TWO: 2>

conn.execute("SELECT pg_typeof(%s)", [Numbers.ONE]).fetchone()[0]
'numbers'

The facility can also be customized in order to adapt enums when the mapping is not one-to-one:

class NumbersPlus(Enum):
    ONE = auto()
    TWO = auto()
    THREE = auto()
    THREE_PLUS = auto()  # has some meaning in the program, but it is not stored

register_enum(info, conn, NumbersPlus,
    # - Items not mentioned map naturally
    # - This order gives THREE priority over THREE_PLUS when loading from db.
    mapping=[(NumbersPlus.THREE_PLUS, "THREE"), (NumbersPlus.THREE, "THREE")])

conn.execute("SELECT %s::text",
    [[NumbersPlus.ONE, NumbersPlus.THREE_PLUS]]
).fetchone()[0]
'{ONE,THREE}'

conn.execute("select '{TWO,THREE}'::numbers[]").fetchone()[0]
[<NumbersPlus.TWO: 2>, <NumbersPlus.THREE: 3>]

In partnership with CockroachDB

CockroachDB is a distributed database presenting an SQL interface on top of a distributed key-value store. Although it is a completely independent implementation, it uses the same PostgreSQL client-server protocol.

In the past few months, we have collaborated to create an even smoother integration, so that every PostgreSQL feature, also supported by CockroachDB, can be used in a transparent way: server-side cursors, pipeline mode, CockroachDB data types are all supported out-of-the-box.

CockroachDB also implements CHANGEFEED, a streaming query, which Psycopg can consume using its Cursor.stream() feature. This immediately receives every change happening in a database table, enabling interesting new ways to write distributed applications.

executemany() improvements

The executemany() method now supports returning the executed statements' output. You can now for instance execute an INSERT ... RETURNING on a batch of records and retrieve the ids associated to the newly inserted records.

executemany() automatically uses the pipeline mode already described, making use of pipeline mode speedups without changing any code in the programs using this method.

And many more improvements

• The Two-phase commit protocol is now available as per DBAPI specification.
• Asynchronous connections don't block on DNS names resolution on connect.
• Cursor.copy() now takes parameters, like a normal query.
• It is also possible to replace the writer of a 'Copy' object, in order to use psycopg just to format data in COPY format and to do something else with the data produced, for instance save it to a file for later processing.
• ...And many more improvements you can find in our release notes.

Thank you very much!

We hope you will enjoy to use Psycopg 3.1 and will benefit from its new features. Psycopg 3 is developed and maintained thanks to the support of our sponsors.

If you are a Python and PostgreSQL user and would like to make sure that the interface between the two is well maintained and continuously improved, please consider sponsoring the project 💜.

Thank you very much, happy hacking!