#​478 — October 26, 2022

Web Version

Together with  pgAnalyze logo

Postgres Weekly

'PostgresML Is 8-40x Faster Than Python HTTP Microservices' — Postgres gets compared to MySQL, SQL Server or Oracle all the time.. but Python? PostgresML is a Postgres extension for training and deploying machine learning models. It’s a task more commonly associated with Python, but Postgres offers a compelling, if quite different, interface and Lev has the numbers to prove it.

Lev Kokotov (PostgresML)

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x.

pganalyze sponsor

Azure Cosmos DB Brings in Distributed PostgresAzure Cosmos DB is a long-standing ‘multi-model’ managed database from Azure that has typically focused on providing NoSQL services with multi-region, global availability, so it’s curious to see Postgres support being introduced. In short, Azure Database for PostgreSQL's Hyperscale (Citus) offering is now.. this.

Erdogan and Cubukcu (Microsoft)

▶  Postgres FM: A Weekly Postgres Podcast — We’ve mentioned this show a few times, but it deserves a little extra support, especially as it has kept going every week without dropping off (quite an achievement for a new podcast!) Recent shows cover topics including stored procedures, Postgres 15 and query optimization.

Postgres FM podcast

IN BRIEF:

👶  Picking a Baby Name with Postgres and FuzzyStrMatch — Admittedly Postgres wasn’t quite on my mind when picking the names of my children, but.. it’s up to the task! Postgres’s fuzzystrmatch module is used to find names that are similar to others you like. Of course, this could be useful for a lot more than names.

Francesco Tisiot (Aiven)

📍  Routing with Lines through Polygons — I need to spend some time with PostGIS because it always looks a bit like magic to me, but Ryan does a great job of showing how coupled with pgRouting you can pull off some interesting things like routing across ‘polygons’ such as may represent features like lakes or pedestrianized zones.

Ryan Lambert

An ORDER BY Improved Our Query Time by 100x — It always pays to look at your slow queries and crack out EXPLAIN and pg_stat_statements to dig in and find a solution.

Colin Chartier

What Is This 1=1 Doing Here? — An interesting bit of syntax used by a developer to make working with SQL queries easier.

Waltton Morais

SSH Best Practices using Certificates, 2FA and Bastions

Teleport sponsor

Logging with JSON in Postgres 15 — One of 15’s new features is the jsonlog format for logging – Luca shows the basics of turning it on.

Luca Ferrari

Migrating IDs from serial to Identity After Upgrading to Django 4.1
Adam Johnson

🛠 Code and Tools

  • pgx 0.5.6
    ↳ Build Postgres extensions with Rust.

  • SeaORM 0.10
    ↳ Async and dynamic ORM for Rust.

  • Piccolo 0.94
    ↳ Fast, asyncio-compatible ORM and query builder for Python.

  • OctoSQL 0.12
    ↳ Cross-database querying and joining CLI tool.

  • Slonik 32.0
    ↳ Node.js Postgres client focused on type safety.

  • tds_fdw 2.0.3
    ↳ Foreign data wrapper to TDS databases (Sybase / SQL Server).

  • pgsodium 3.0.6
    ↳ libsodium-powered cryptography for Postgres.

  • pg-boss 8.1.1
    ↳ Postgres-based job queuing system for Node.js.