--- title: "When a two column index is better than one" publication: "Postgres Weekly" issue: 483 published_at: "2022-11-30" canonical_url: "https://postgresweekly.com/issues/483" --- Plus AWS's new Oracle/SQL Server to Postgres schema migrator, using Postgres to model a task queue, and how Fly.io's Postgres platform works. | #​483 — November 30, 2022 [Web Version](https://postgresweekly.com/issues/483) Together with [![pgAnalyze\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_350,h_100/e_make_transparent/co_white,e_outline:7/lhwcvgc1asd4fzaahty4.png)](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary11302022) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/s0dbes1unoq14oyza6l1.jpg)](https://sirupsen.com/index-merges) [Index Merges vs Composite Indexes](https://sirupsen.com/index-merges) — Is it faster to use a composite index (a.k.a. a [multicolumn index](https://www.postgresql.org/docs/current/indexes-multicolumn.html)) 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 [![\](https://copm.s3.amazonaws.com/fa43cd51.png)](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary11302022) [eBook: How to Create the Best Postgres Index for Your Queries](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary11302022) — 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](https://www.crunchydata.com/blog/percentage-calculations-using-postgres-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](https://www.crunchydata.com/developers/playground/percentage-calculations) where you can play for yourself. Paul Ramsey [How Fly.io Runs Its New Postgres Platform](https://fly.io/blog/how-we-built-fly-postgres/) — 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:** - Microsoft's M. Ozan Saka gives a look behind the scenes at [how they got Postgres 15 on Azure Cosmos DB](https://devblogs.microsoft.com/cosmosdb/enabling-postgresql-15-in-azure-cosmos-db-for-postgresql-how-we-did-it/) within a week of 15's release. - [Postgres 16 is adding roles](https://www.cybertec-postgresql.com/en/grant-vacuum-analyze-postgresql-16/) to allow the ability to grant table vaccumming and analysis to users other than superusers or those tables' owners. - The _Postgres.FM_ podcast is back with [▶️ an episode about materialized views](https://postgres.fm/episodes/materialized-views). [AWS Database Migration Service Adds Managed Schema Migration](https://aws.amazon.com/blogs/aws/new-a-fully-managed-schema-conversion-in-aws-database-migration-service/) — 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](https://buildkite.com/unblock?utm_campaign=CPress&utm_source=ppc&utm_medium=Q4-2022&utm_term=Postgres-Weekly&utm_content=Unblock-Sign-Up) Buildkite sponsor [Using Postgres as a Task Queue for Rowdy Gophers](https://ente.io/blog/tech/postgres-queue/) — 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](https://www.timescale.com/blog/how-to-use-wide-table-model-to-reduce-query-cost-in-timescaledb/) Florian Herrengt 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/dsynghh5lgrdjtvsdhyg.jpg)](https://github.com/zekenie/d2-erd-from-postgres) [Create D2 ERD Diagrams from Postgres Schemas](https://github.com/zekenie/d2-erd-from-postgres) — A simple Node script that can turn the schema of a Postgres database into an elegant diagram using the [D2](https://d2lang.com/tour/intro/) declarative diagramming DSL. _(Also of interest is the [mega SQL query](https://github.com/zekenie/d2-erd-from-postgres/blob/main/query.sql) this script relies on for fetching a schema in one go.)_ Zeke Nierenberg [pgvector: Vector Similarity Search Extension](https://github.com/pgvector/pgvector) — Supports L2 distance, inner product, and cosine distance. There’s a whole suite of [client libraries](https://github.com/pgvector/pgvector#libraries) for Node, Go, Ruby, PHP, Rust, and more, too. pgvector [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/fngrr8gtvorypufqvbhs.jpg)](https://github.com/okbob/pspg) - [pspg 5.6](https://github.com/okbob/pspg) _(above)_ ↳ Unix pager designed for work with tables. - [postgres-meta 0.53](https://github.com/supabase/postgres-meta) ↳ RESTful API for managing Postgres. - [go-sqlbuilder 1.17](https://github.com/huandu/go-sqlbuilder) ↳ Flexible SQL query string builder for Go. - [pg-promise 10.15.4](https://github.com/vitaly-t/pg-promise) ↳ Alternative Node.js Postgres library. - [plpgsql_check 2.2.5](https://github.com/okbob/plpgsql_check) ↳ Static analysis and linting for PL/pgSQL. - [Rails PG Extras 4.10](https://github.com/pawurb/rails-pg-extras) ↳ Postgres stats within Ruby on Rails apps. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)