#​483 — November 30, 2022

Web Version

Together with  pgAnalyze

Postgres Weekly

Index Merges vs Composite Indexes — Is it faster to use a composite index (a.k.a. a multicolumn index) than merge indexes? Yes and, at least in Postgres, by quite a lot, because Postgres doesn’t support index-only scans when queries involve index merges.

Simon Hørup Eskildsen

eBook: How to Create the Best Postgres Index for Your Queries — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x.

pganalyze sponsor

Percentage Calculations Using Window Functions — Rather than fetching your data and then performing percentage calculations upon it, why not do it all in a single query? A perfect use case for window functions, says Paul, who gives a practical example, plus a link to an interactive sandbox where you can play for yourself.

Paul Ramsey

How Fly.io Runs Its New Postgres Platform — We’re big fans of Fly (a sort of broadly distributed, modern Heroku-a-like) so it’s been fun seeing them build out their platform. Running Postgres is pretty easy, but the trick is in managing replicas, failover, etc. and Fly uses entirely open-source pieces to make it all work.

Nicoll and Davis (Fly)

IN BRIEF:

AWS Database Migration Service Adds Managed Schema Migration — AWS Database Migration Service (DMS) is a long standing service for migrating databases (particularly those running on Oracle or SQL Server) to AWS. At this week’s re:Invent, AWS unveiled a new automatic database schema converter as an integral part of the service. It supports migrations from SQL Server 2008+ and Oracle 10.2+, 11g-12.2, 18c, and 19c to MySQL or Postgres running on RDS.

Channy Yun (AWS)

📅  Register for Unblock - A CI/CD Conference by Buildkite

Buildkite sponsor

Using Postgres as a Task Queue for Rowdy Gophers — A developer walks through his experience of building a fast and efficient queuing system on top of Postgres for his Go routines to use for dividing up work.

Manav at Ente

How to Reduce Query Cost with a Wide Table Layout in TimescaleDB
Florian Herrengt

🛠 Code and Tools

Create D2 ERD Diagrams from Postgres Schemas — A simple Node script that can turn the schema of a Postgres database into an elegant diagram using the D2 declarative diagramming DSL. (Also of interest is the mega SQL query this script relies on for fetching a schema in one go.)

Zeke Nierenberg

pgvector: Vector Similarity Search Extension — Supports L2 distance, inner product, and cosine distance. There’s a whole suite of client libraries for Node, Go, Ruby, PHP, Rust, and more, too.

pgvector