#​473 — September 28, 2022

Web Version

Postgres Weekly

pgsqlite: A Pure Python Module to Import SQLite Databases into Postgres — Postgres is popular, but SQLite is almost universal. bit.io, a new-ish Postgres platform, recognized this and has created pgsqlite as a way to rapidly import SQLite databases into Postgres. This post focuses on some of the difficulties involved in such a process.

Adam Fletcher (bit)

ICU Features in Postgres 15 — ICU (International Components for Unicode) is a suite of libraries for reliably handling various Unicode related text operations. Postgres has supported ICU since version 10 for selective collation/sorting of columns, but Postgres 15 will let you specify an ICU collation for a whole database.

Peter Eisentraut

Accelerate Development with Fully Managed Postgres DBaaS — The world’s leading Postgres provider is now fully managed in the cloud. Free your team to innovate while we manage and support mission-critical Postgres. No one does it better than EDB. Learn more.

EDB BigAnimal sponsor

LISTEN and NOTIFY: Postgres's Client Notification System — Rather than having many clients polling the database for the same information over and over, what about having them just wait and you then send the information when it’s ready? LISTEN, NOTIFY and triggers can get you quite a long way for certain use cases.

Hans-Jürgen Schönig

How TimescaleDB Can 'Supercharge' Postgres — It’s important to note this post is from the developers of TimescaleDB and they throw out some very bold numbers, but the gist is to show how merely bringing TimescaleDB into a standard Postgres setup can deliver performance gains even if you’re not doing time-series work.

Ryan Booz (Timescale)

IN BRIEF:

▶  Optimizing Query Throughput in Python with Psycopg 3.1Psycopg 3 is a modern, feature-rich Postgres adapter for Python and with v3.1 (release post here) it supports pipeline mode so numerous queries can be sent to Postgres simultaneously without waiting for a response each time.

Lukas Fittl

Query Optimization with pg_stat_statements — Every week we read the latest PostgreSQL Person of the Week interview and it feels like most times the person being interviewed names pg_stat_statements as their favorite extension, so if you’re not using it on the regular, it’s worth becoming familiar.. (This post dates from 2021, but we missed it at the time.)

Kat Batuigas

'Preferred Types' — An interesting bit of spelunking where a weird query result leads to Laetitia discovering a quirk in Postgres’s behavior when it comes to determining data types and its preference of certain types over others.

Laetitia Avrot

Handling Heroku's New heroku_ext Schema for Postgres Extensions — The Heroku cloud platform made a change that requires all extensions to be created inside a special heroku_ext schema. Heroku has just posted an FAQ about the issue (though logging in is required to see it.)

Justin Searls

Seeing TOASTing in ActionTOAST (The Oversized-Attribute Storage Technique) is an approach Postgres uses to move (and even compress) large columns to a separate place from the rest of the table for efficiency reasons.

Daniel Westermann

PDF Download: Understanding Kubernetes Concepts and Implementation

Linode sponsor

Creating Continuous Aggregates with Ruby and Timescale — Continuous aggregates use materialized views to continuously and incrementally refresh a query so that only data that has changed needs to be computed, not the entire dataset.

Jônatas Davi Paganini

🛠 Code and Tools