~postgresweekly | Bookmarks (128)

▶  Building a Private RAG System with Llama 3, Ollama, and Postgres — Leaning on external providers like OpenAI or Anthropic might not satisfy your data protection policies, but luckily you can go a long way with local, open models.

How We Built a Content Recommendation System with Pgai and Pgvectorscale — pgvectorscale is an extension for pgvector that adds StreamingDiskANN index support and improved quantization.

IvorySQL 3.4: Open Source Oracle-Compatible Postgres — There are numerous approaches for migrating from Oracle to Postgres and IvorySQL’s approach is to add compatibility elements to Postgres, like support for Oracle’s PL/SQL syntax and Oracle XML functions.

▶  Hands on Postgres 17: What's New and How It Impacts Performance — An hour long webinar that takes a performance-oriented look at Postgres 17’s numerous enhancements (and there are a lot of them!)

🤖 dbx: A Forthcoming AI-Powered SQL Editor and Database Client — We rarely link to tools that aren’t openly available, but as one of our readers asked so nicely, their landing page is certainly worth a look and you can register for early access if you like what you see.

TimescaleDB 2.17.0: Time-Series Extension for Postgres — The popular extension implementing features and optimizations for working with time-series data at scale introduces full Postgres 17 support with this release. New, extra SIMD optimizations also increase performance for real-time analytical queries over compressed hypertables.

Optimizing Postgres Table Layout for Maximum Efficiency — The alignment of data within broader data structures has long been an important factor for optimization in low level programming, but it can affect your Postgres tables too. Renato looks at how table layout can impact both storage efficiency and query performance due to how data is arranged and padded on disk.

The Value of 'VALUES -> ANY' Transformations? — The authors ponder if an RDBMS should optimize a query before trying to make a plan for it, particularly in a landscape where we’re running fewer hand crafted queries than ever.

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.

pg_stat_kcache: Gather Statistics About Disk Access and CPU Consumption — Sits atop pg_stat_statements and creates views you can use to measure CPU time, page faults, swaps, bytes read and written, and more at both database and query level.

What's So Great About Postgres 17? — Finally, Postgres 17 is here and we’re starting to see some opinionated takes on its many enhancements. Laurenz covers the things that caught his eye here.

Are You Qualified to Use NULL in SQL? — A fun(?) quiz designed to test your intuitions about the behavior of NULL and how it interacts with other types, predicates, aggregations, and more. It’s a real brainscratcher. (Note: My Postgres, at least, behaves differently to the expected answer on the final question, showing just how confusing it all is.)

▶  Heroku's Glory Days and Postgres vs The World — Postgres legend Craig Kerstiens joined Aaron Francis (working on the soon to be launched Mastering Postgres course) to talk about his history with Postgres at Heroku, Citus Data, and now Crunchy Data. It’s not all about him, though; the interview is packed with insights about building ecosystems, scaling Postgres, and even DuckDB.

▶  SQL IN Clauses are Miles Faster in Postgres 17 — Hussein is so amazed by PG17’s enhancements to b-tree scanning performance when using IN that he’s recorded a screencast explaining how it works, compares it to SQL Server’s approach, and shows it off with a live demo.

Waiting for Postgres 18: Add Temporal PRIMARY KEY and UNIQUE Constraints — Postgres 17 may have just landed, but Hubert is looking ahead to Postgres 18, and a new way to enforce uniqueness across a time period, say, using WITHOUT OVERLAPS rather than just against static values.

Exploring the Limits of Postgres: When Does It Break? — Postgres’ limits are well understood on paper, but how far could you push a single node Postgres server in the real world and where are the bottlenecks?

pgsql-tweaks: A Grab Bag of Useful Postgres Functions — An interesting suite of functions that the author (a former PostgreSQL Person of the Week) uses in her day job as a Postgres user, covering areas like checking data types, gathering statistics, some aggregates, and conversion functions.

pg_qualstats: Extension for Collecting Statistics About Predicates — Analyze the most often used predicates in queries against your database, perhaps as a way to create more effective indexes. Part of the POWA (Postgres Workload Analyzer) project.

PostgreSQL 17 Released — The big one is here. It's the newest major version of Postgres, and it takes a bigger step forward than even v16. Some of what’s new:

libpg-query-node: Use Postgres' SQL Parser from Node.js — Use Postgres’s SQL parser at a low level from Node via libpg_query. If you want something higher level, this library is used by pgsql-parser which gives the ability to parse and serialize queries into/from ASTs.

When Postgres Indexing Went Wrong — “TLDR: Be careful when creating indexes — a lesson I learned the hard way when concurrent indexing failed silently.”

Hierarchical Data Types in Postgres — hierarchyid is a special type in SQL Server for representing positions within a hierarchy (such as a tree). You could do it with any integer column in Postgres, but if you want something more structured, ltree provides a more structured option.

Generating a Few Secure, Random Bytes Without pgcrypto — random() gives you pseudo random numbers, but pgcrypto makes it possible to get much better randomness. If your server (perhaps a local one) isn’t built against OpenSSL, though, it won’t be available. Luckily, Brandur found an interesting alternative option.

Building an Image Search Engine on Postgres — pgvector strikes again. The technique is simple: take images, create embeddings of them, then use the embeddings of other supplied images to find similar images based via a vector similarity search.