~postgresweekly | Issue 573 - Optimizing queries before planning (9)

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.

▶  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!)

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.

▶  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.

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.

🤖 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.

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.