PGConf.Online 2021

JSONB is a popular data type in Postgres, and there is demand from users to improve its performance. In particular, we want to optimize a typical pattern of using jsonb as a storage for relatively short metadata and big blobs, which is currently highly inefficient. We will discuss several approaches to jsonb improvement and present the results of our experiments.

Multicorn technology allows you to develop FDWs in Python, which is much easier and faster than creating FDWs in C. However, there is a downside, Multicorn FDWs work well with primitive WHERE conditions, but more complex cases cause difficulties, which I will talk about. Cases will be considered on the example of my Multicorn FDW for getting OpenStreetMap data. I will also show examples of using the same code in Multicorn FDW and plpython functions, including performance comparison. In conclusion, I will share my findings on when it is better to use plpython, and when Multicorn FDW is more preferable.

Python is today one of the most used programming languages in the world: simple to learn and to use and ready to interface to any known service and protocol. psycopg2 is the most used PostgreSQL driver for Python: it offers good performance and makes the communication between the language and the database as smooth as possible.

Python has evolved enormously in the past years and its first-class support for async programming is changing the way new programs are written. PostgreSQL has evolved too: a new generation of the driver is needed to make the most of all the features it has to offer.

psycopg3 is the new generation of the most used Python-PostgreSQL adapter: it offers a familiar interface and smooth upgrade path, but behind the scenes it is engineered to obtain the best performance from the database and the language: async programming, prepared statements, binary parameters.

psycopg3 is also experimenting with innovative JSONB support and query pipelining! Come and discover the forefront of the research between your most loved language and database!

pspg is unix pager specially developed for usage in psql Postgres client. Today it allows more than usual browsing data. It can work in application mode or it work as csv or tsv viewer too. I will try to show the main possibilities of this application.