#​509 — June 7, 2023

Web Version
Together with  Crunchydata

Postgres Weekly

High-Compression Metrics Storage with Hyperloglog — postgresql-hll is an extension introducing the HyperLogLog algorithm to Postgres. HyperLogLog is a probabilistic algorithm to efficiently estimate the number of distinct values in large datasets, trading a little precision for noteworthy time and memory savings.

Christopher Winslett

What is a Postgres 'Schema' — The term schema has multiple meanings in the database world, but in Postgres schemas are named containers that can contain other named objects like tables, data types, and functions, somewhat akin to a namespace.

Hans-Jürgen Schönig

❤️ Postgres — You need a database provider that loves Postgres as much as you do. We'll take care of all the hassle - monitoring, backups, HA, disaster recovery so you don't have to. Want amazing support? We'll be there when you have questions.

Crunchy Bridge sponsor

A Guide to PgBouncer/Postgres Compatibility — PgBouncer is a popular connection pooler but not every Postgres feature works with it. If you’re trying to debug PgBouncer issues, this is for you and this post tries to make it easier to navigate the more extensive writeup.

JP Camara

IN BRIEF:

  • Standards organization ISO has published the latest SQL spec: SQL:2023. With the core section alone clocking in at 1715 pages and ~$200, it's sure to be a sleeper hit. We refer to Peter Eisentraut's recent round up of what's new in SQL:2023 and the status of SQL:2023 features in Postgres if you want to dig deeper without nodding off.

  • Instaclustr's Sharan Foga is the PostgreSQL Person of the Week. Relatively new to Postgres, she's excited to engage with the community 👋

  • 📅 If you're reading this issue in time, there's a live ▶️ The Future of the Postgres Ecosystem roundtable discussion taking place today at 10am Pacific (1pm ET, 6pm BST, 7pm CEST), just a few hours after we send this issue. Folks from Yugabyte, NeonDB, CoreDB, Google, PGEdge, and others will be present.

An SQL Recipe: Comparing with Neighbors — If you need to calculate the difference between records and the previous or next ones, the LAG() window function is for you. Anton gives us some examples of its use. (If you missed our interview with him about window functions last week, it’s at the bottom of issue 508.)

Anton Zhiyanov

The Future of OpenAI for Postgres on Azure — ChatGPT supports plugins as a way to provide the popular chatbot / LLM with data to formulate replies. This post explains the broad concepts and shows how a combination of plugins and storing embeddings with pgvector could open up new querying opportunities.

Krishnakumar Ravi (Microsoft)

▶  Why Giving Talks at Postgres Conferences Matters — With Alvaro Herrera and Boriss Mejias.
Citus Data

Postgres Roles and Privileges Explained
Masaya Suzuki

Code and Tools

SchemaSpy: Database Documentation Built Easily — A long-standing tool that analyzes your database’s metadata, schema, and other information to help you visualize, check, and understand your data model via an HTML report.

SchemaSpy

[Open Source] Columnar, Vectorized Postgres

HYDRA sponsor

pgsql-ast-parser 11.1: A Simple Postgres SQL Parser — A TypeScript-based SQL syntax parser that can produce a typed AST for most queries (PL/pgSQL isn't supported). It’s used as part of the author’s pg-mem project, a mini, in-memory Postgres clone for Node and the browser.

Olivier Guimbal

Goxygen 0.4: Go Backend Generator for JavaScript Projects — A tool that sets up a new Go-based project with Angular, React, or Vue in the front-end, and Docker and Docker Compose files to make it all work. Supports Postgres, MySQL or MongoDB as the database.

Sasha Shpota

📧 Do we have a Go newsletter too? We do.