~postgresweekly | Bookmarks (148)

'Look Ma, I Wrote a New JIT Compiler for Postgres' — Before you get too excited, the author’s pg-copyjit project is experimental (and x86-64 only), but they do seek feedback on how it works out for you — so if you’re feeling brave and are seeking some potential performance gains...

Enforcing Join Orders in Postgres — With SQL being a declarative language, you'd reasonably expect Postgres to handle the complexities of joining tables in the way you require. It usually does a good job, but there are sometimes reasons to force Postgres to take a different approach..

How to Do Performance Testing with pgbench — A straightforward introduction to using pgbench, a benchmarking tool included with Postgres.

Speeding Up pgvector Queries with Indexes — If you’re using Postgres and pgvector to work with large language embeddings, as is the style at the time, this is a must read. Francesco goes into more detail than you might expect, and you’ll certainly pick up some techniques here.

Postgres Creator Introduces His New Database Startup — MIT professor Mike Stonebraker was a principal architect of the Postgres project and, aged 80, refuses to rest on his laurels, with a fresh project (DBOS) putting the database closer to the center of the software stack.

▶  The Path to Citus Con: A Podcast for Postgres — The Citus Con event itself is now called POSETTE but the podcast continues under the old name (for now) with monthly episodes digging deep into Postgres topics. The latest episode focuses on the latest around using Postgres in AI and ML use cases, starring Microsoft’s Arda Aytekin along with Claire Giordano and Pino de Candia.

Creating a Postgres Logical Replication System in Go — The folks behind the (Go-powered) Dolt database are working on a feature to replicate data from Postgres, and share what they’ve learnt so far in this particularly detailed post.

pgCluu 4.0: Postgres Performance Monitoring and Auditing Tool — A two part tool that collects statistics from a running Postgres cluster and then presents performance information for you to inspect. Version 4.0 is a major release with new features and report types.

🌐 Using Postgres for (Military) Geoanalytics Tasks — If you dislike military scenarios, skip this, but otherwise it’s a well walked through example of using publicly available data and PostGIS to analyze and plot information of strategic importance (though the powers that be almost certainly do this at 1000x the scale).

pg_jsonschema 0.3: JSON Schema Validation Extension — Postgres’s support for JSON / JSONB columns is second to none, but what if you want to validate the structure of JSON data? JSON Schema provides a way to define JSON-based formats and this extension lets you verify JSON documents using such schemas. v0.3 completes the extension’s Postgres 16 support.

pg_ivm 1.8: Incremental View Maintenance Extension — IVM can update materialized views incrementally, which is more efficient than a full recomputation as REFRESH MATERIALIZED VIEW does.

Pigsty: A 'Batteries-Included' Postgres Distribution — The name of this project always makes me grin: PIGSTY is actually an acronym, standing for Postgres In Great STYle! It’s a Postgres distribution that includes lots of components and tools out of the box in areas like availability, deployment, and observability. The latest release pushes everything up to Postgres 16.2 standards and introduces new ParadeDB and DuckDB...

Building a Q&A Bot Natively with Postgres Extensions — pgvector and embedding models have made it easier to create advanced natural language search systems in the past year or two. Here, Tembo shows off how their stack and extensions make it an even easier, direct process. pg_vectorize is a particular highlight as it’s an extension that automates a lot of the process of transforming...

Comparing Postgres Managed Services: AWS, Azure, GCP and Supabase — A reasonably simple, straightforward comparison that doesn’t aim to be exhaustive but instead provide pointers for developers in the market for a managed service.

pganalyze Index Advisor 3.0: Workload-Aware Index Suggestions — Launched last week, the new advisor finds missing indexes for each table, detects indexes that would block HOT Updates, lets you drill-down to see all considered indexes, and more. Powered by the constraint programming model introduced at PGCon 2023.

▶  Database Disasters and How to Find Them — You’re part of an engineering team and you get pinged because something’s gone down.. what happens next?

Tips for Tuning Postgres LIKE/ILIKE Queries via pg_trgm on Azure — Tips to improve query performance when using pattern search with LIKE or ILIKE. 

pg_activity 3.5: htop-Like Activity Monitoring Tool — Much as you might use top or htop to monitor processes and CPU usage, pg_activity gives you a similar look behind the scenes of Postgres with views over running queries, up to date performance stats, and more.

The azure_ai Extension to Azure Database for PostgreSQL — One benefit of Microsoft’s close relationship with OpenAI is that it’s available directly within Azure, and now you can directly work with Azure OpenAI from within Azure Database for PostgreSQL using the azure_ai extension, as well as pgvector to work with any embedding-based results of that work. Claire shows off how this all looks in...

11 Lessons to Learn When Using NULLs — NULL represents a missing, unknown or ‘non’-value in SQL and it has some interesting properties and behaviors to be aware of.

The Rest is History: Investigations of WAL — Get a deeper insight into just what the WAL files used by Postgres contain as Brian steps through reviewing the Write-Ahead Log history as part of a sample recovery scenario.

The State of the Database Landscape 2024 — Redgate’s latest survey results show that PostgreSQL continues to be in the top four most used databases as organizations continue to embrace multiple database platforms. Find out more about the latest trends in the database landscape.