~postgresweekly | Issue 570 - Identity columns: a SERIAL killer? (8)

Optimizing Your Database for Analytics — Your database could be great for day to day operations, but when you need to run some complex analytics queries, some big decisions arise. You could replicate the data to do the analytics elsewhere in a variety of ways, or tweak Postgres to handle things better. A solid high-level look at the concepts involved. There’s ▶️ a video...

Why to Stop Using SERIAL in Postgres — SERIAL is a commonly used convenience for generating an automatically incrementing ID column, but it has a variety of quirks and is Postgres specific. The alternative? Identity columns, which are both a part of the SQL:2003 standard and supported by Postgres.

Hands on Postgres 17: What’s New & Impacting Performance — Join us for an exclusive webinar on the Postgres 17 release. Explore new enhancements—faster B-tree scans, adaptive vacuum strategies, and streaming I/O updates—and their impact on database performance & monitoring. Register today!

Postgres 17 RC1 vs sysbench on Small and Large Servers — If, like us, you’re anxiously awaiting next week’s Postgres 17 release, you might be curious at how it’ll stack up performance wise. Mark ran some benchmarks on 15.8, 16.4, 17 beta 3 and 17 RC1, and while there’s some detail to peck through, the summary is “17rc1 looks great - there are no...

Implementing Hybrid Search with Postgres and pgvector — Jonathan sets out to show us how to use pgvector in implementing hybrid search systems, but notes that he doesn’t yet want to answer whether you should – that’s a topic for a future post.

Window Functions for Data Analysis with Postgres — Window functions come in handy when you want to group or compare between rows you’ve already retrieved in a query – for instance, to calculate running totals, perform rankings, or work out rolling averages. They essentially provide higher order querying.

🐸  Rainfrog: A New Database Management TUI for Postgres — We love psql as much as anyone, but are always keen to see new takes, and this Rust-powered terminal UI is an elegant one. You get a query editor with highlighting, Vim-esque shortcuts, and can quickly switch between schemas.

📱  Selectable: An In-Development Android Postgres Client — A new freemium Android app for interacting with Postgres that’s admittedly still in its ‘early stages’ but available now on the Google Play Store to experiment with.