Articles tagged psycopg3

Pipeline mode in Psycopg

Posted by Denis Laxalde on 2024-05-08
Tagged as psycopg3, development

Version 3.1 of Psycopg added support for libpq pipeline mode, bringing significant performance boost, especially when network latency is important. In this article, we’ll briefly describe how it works from users’ perspective and under the hood while also providing a few implementation details.


Building a Django driver for Psycopg 3

Posted by Daniele Varrazzo on 2021-08-02
Tagged as psycopg3, development, recipe

One of the goals of the Psycopg 3 project is to make easy to port code developed from Psycopg 2. For this reason the creation of a Django backend (the module you specify in the settings as your database ENGINE) was a project with a double goal:

  • A Django driver is a way to make Psycopg 3 useful from the start, with the possibility of dropping it in a project transparently and have available, when needed the new features offered (for instance the superior COPY support).
  • The difficulty of introducing Psycopg 3 in the Django codebase and the type of changes required are indicative of the type of problems that could be found porting other projects.

...and it's done! A few days ago, the new Psycopg 3 Django backend could pass the entire Django test suite!


Designing a connection pool for psycopg3

Posted by Daniele Varrazzo on 2021-01-17
Tagged as psycopg3, development

The psycopg2 pool is a pretty simple object, little more than... a pool of open connections, and I think it falls short in several ways:

  • the top usability problem is the fact that it cannot be used as context manager;
  • if a connection is broken it is not noticed it until it is used by a client;
  • if minconn connections are already taken, a new one is created and disposed of as soon as finished using, regardless of whether other clients may need it;
  • if more than maxconn connections are requested the client will receive an error.

For psycopg3 I would like something better. I have read around, looking into other pool implementations to figure out what a well designed connection pool ought to do (a very well thought one seems the Java HikariCP) and these are a few ideas I'd like to work on: they are here for a feedback, before I jump into enthusiastically implementing the wrong thing...


The psycopg3 adaptation system

Posted by Daniele Varrazzo on 2020-11-24
Tagged as psycopg3, development, news

The adaptation system between Python objects and PostgreSQL types is at the core of psycopg2 and psycopg3. The flexibility of the psycopg2 adaptation system provides good out-of-the-box object mapping and allows users to customise it to suit any need. Do you want your decimal numbers returned as float because you need speed over pennies? Do you want to map PostgreSQL Infinity dates to the 25th of December 3099? That's certainly doable.

The psycopg3 adaptation system needs some modification compared to psycopg2, because psycopg3 uses the "extended query protocol" to send query parameters separately from the query. Together, with the differences to accommodate, there is also a chance to improve a system that has been in use for several years and has shown its shortcomings together with its strengths.


The new COPY support in psycopg3

Posted by Daniele Varrazzo on 2020-11-15
Tagged as psycopg3, development, news

psycopg2 allows interaction with PostgreSQL COPY commands. However what is possible to do with them is relatively limited: the only possible interaction is with file-like objects:

  • there is no adaptation from Python objects to PostgreSQL, as there is for normal queries: data must be formatted "manually" by the user;
  • psycopg2 "pulls" data from the file: writing a system that produces data and pushes it into PostgreSQL is a very contrived operation, requiring to write a blocking file-like object;
  • there is no support for asynchronous copy.

psycopg3 addresses these shortcomings and makes it easy to write Python programs producing data and pushing it efficiently to the database using the COPY protocol.