#​492 — February 8, 2023

Web Version

Together with  Polyscale

Postgres Weekly

What Performs Better: FILTER or CASE? — Lukas notes that SQL’s FILTER clause is syntactic sugar for an equivalent CASE expression in an aggregate function, but as we know, equivalent queries can have different performance profiles! Spoiler: This is also true for FILTER vs CASE.

Lukas Eder

💡 If FILTER is new to you, never fear, Craig Kerstiens has a 'Using Postgres FILTER' tutorial to bring you up to speed.

Coming to Postgres 16: A New Way to Reserve Connection Slots — You’ve long been able to use superuser_reserved_connections to reserve connection slots for superusers, but a new patch for Postgres 16 provides a way to reserve connection slots for non-superusers via a pg_use_reserved_connections role.

Pavlo Golub

Database Caching, Without the Complexity — PolyScale.ai provides sub-millisecond read queries, without the cost and complexities typical of cache development. Deploy in minutes with our serverless edge network or on‑premise.

PolyScale.ai sponsor

IN BRIEF:

Adventures in Embedding Postgres Schemas in GPT — Fun experimentation in trying to convince OpenAI’s GPT to return useful SQL queries based upon a prompt, but there’s still plenty of work to do.

William Pride

Simple SQL Tricks for More Effective CRUD — The Create, Read, Update, Delete (CRUD) paradigm is a fundamental element of many apps that use databases and you can leverage SQL to make CRUD tasks more atomic and, as Paul says, “do more things right inside the database.”

Paul Ramsey

How to Get a Row and All Of Its Dependencies? — Getting a single row of a table is easy, but what about if you want to also get all of the rows it references? Hubert got experimenting with a function to build such documents – it’s as much interesting for the process as the end result.

Hubert depesz Lubaczewski

Will Postgres Use My Index? Hypothetical Indexing for PostgresHypoPG can create hypothetical indexes (B-tree, BRIN, and hash indexes, but not GIN or GIST) and test if Postgres will use them. You can couple it with EXPLAIN to produce the right indexes for your queries.

Craig Kerstiens

Invoking (Your Own) Perl from PL/Perl
Luca Ferrari

🛠 Code and Tools

pgModeler 1.0 Released: A Postgres Database Modeler — An easy way to create and edit database models in a visual way. It’s packaged as a paid product but is also open source so you can build your own. Official homepage and GitHub repo.

Raphael Araújo e Silva

Free eBook: How to Create the Best Postgres Index for Your Queries

pganalyze sponsor

pg_ivm 1.5: Incremental View Maintenance Postgres Extension — IVM can update materialized views more efficiently than recomputation when only small parts of the view are changed. v1.5 adds (basic) CTE support.

SRA OSS LLC

pg_dumpbinary 2.9: Dump a Database in Binary Format — Using a binary format for a dump has some benefits in particular situations.

lzlabs

pgFormatter 5.5: SQL Syntax Beautifier — Mostly a bug-fix release. There’s an online demo here.

Gilles Darold