~postgresweekly | Issue 579 - INSERT vs COPY for batch ingestion (9)

💎 pg_query 6.0: Ruby Library to Parse, Deparse and Normalize SQL Queries with Postgres' Own Parser — Uses the actual Postgres server source to parse SQL queries and return their internal Postgres parse tree. v6.0 upgrades to using the Postgres 17 parser and the new features it supports (e.g. new JSON functions).

pgspot: Spot Vulnerabilities in Postgres Extension Scripts — A security analysis tool for SQL scripts that checks for vulnerabilities and best practices. Created by a Timescale engineer to prevent the reoccurance of vulnerabilities found in TimescaleDB, it’s already resulted in finding an extension-related vulnerability which Postgres core has fixed.

Could GROUP BY Clause Reordering Improve Performance? — Could reordering the columns in a GROUP BY significantly enhance query performance? Small changes in query structure can indeed result in serious gains.

Benchmarking Batch Ingest with INSERT vs COPY — Two of the simplest approaches to loading data into Postgres en masse are by using INSERT and COPY. James explains each of their benefits, runs a benchmark of numerous configurations of both, and gives guidance on which approach to use in certain situations.

Why Postgres Major Version Upgrades Are Hard — Update the binaries and restart? No, no, no. Tweaks and updates to the underlying data format required a more structured approach – Peter explains why.

▶  The PGConf.EU 2024 Lightning Talks — A handy roundup of twelve brief talks given at the recent PGConf EU event, along with links to slides and recordings. Topics covered include BM25, an update on the Postgres Performance Farm, pg_duckdb, and WAL-G.

DELETEs Are Difficult — Conceptually, deleting data from a table is easy to understand. In practice, it involves a lot of work under the hood and performance issues can quickly rear their head.

Pigsty v3.1: A 'Batteries Included' Postgres Distribution — Pigsty is a packaging up of Postgres along with a variety of extensions and tools to provide a sort of RDS-like experience on your own machines. v3.1 introduces Postgres 17 as the default version and includes a way to quickly spin up a self-hosted Supabase instance.

Building AI Apps on Postgres? Start with pgai — pgai is a PostgreSQL extension that brings more AI workflows to PostgreSQL, like embedding creation and model completion. pgai empowers developers with AI superpowers, making it easier to build search and retrieval-augmented generation (RAG) applications.