---
title: "The effect B-tree index deduplication has in Postgres 13"
publication: "Postgres Weekly"
issue: 372
published_at: "2020-09-09"
canonical_url: "https://postgresweekly.com/issues/372"
---

Plus more news, tutorials, and tips from the Postgres world this week.       

#372 — September 9, 2020 

[Web Version](https://postgresweekly.com/issues/372)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/y7yg2yhwsqhaaabsamhg.jpg)](https://blog.rustprooflabs.com/2020/09/postgres-beta3-btree-dedup)   

[A Look at B-Tree Index Deduplication in Postgres 13](https://blog.rustprooflabs.com/2020/09/postgres-beta3-btree-dedup) — B-tree indexes are the default type of index created in Postgres so any changes to their operation is likely to have a lot of knock-on effects. _Deduplicating_ these indexes, as possible in the forthcoming Postgres 13, will help keep these indexes smaller and has performance implications (most likely lower I/O usage at a cost of minor CPU increase, but with higher overall performance in most cases). 

Ryan Lambert   

[How to Get the Best Out of Postgres Logs](https://www.2ndquadrant.com/en/blog/how-to-get-the-best-out-of-postgresql-logs/) — Postgres’s logging system is very tunable and there are lots of parameters to fiddle with. This post covers some basic practices for getting the most out of a Postgres server’s logs and what you can tweak. 

Sadequl Hussain   [![\](https://copm.s3.amazonaws.com/ee8d8f3e.png)](https://resources.2ndquadrant.com/business-case-for-postgresql-support?utm_campaign=Whitepaper%20-%20Business%20Case%20for%20PostgreSQL%20Support&utm_source=CooperPress_SupportWhitepaper_Sept9) 

[[Whitepaper\] Business Case for Professional Support](https://resources.2ndquadrant.com/business-case-for-postgresql-support?utm_campaign=Whitepaper%20-%20Business%20Case%20for%20PostgreSQL%20Support&utm_source=CooperPress_SupportWhitepaper_Sept9) — Learn the importance of Professional Support for your mission-critical PostgreSQL systems & how it can benefit your company. Discover how it increases database performance, helps scale, distributes data, reduces costs, saves you from known pitfalls, and more. 

2ndQuadrant Services sponsor   

[What’s New in the Citus 9.4 Extension to Postgres](https://www.citusdata.com/blog/2020/09/05/citus-9-4-whats-new/) — Citus transforms Postgres into a distributed database, distributing your data and your SQL queries across multiple nodes. v9.4 improves `EXPLAIN ANALYZE`, has some performance and safety improvements, and can now calculate percentiles at scale using the `t-digest` extension. 

Marco Slot (Citus Data)   

[Generating a Normal Distribution in SQL](https://www.cybertec-postgresql.com/en/generating-a-normal-distribution-in-sql/) — Postgres’s [tablefunc](https://www.postgresql.org/docs/9.5/tablefunc.html) extension provides a variety of functions that return tables, including sets of normally distributed random values. 

Hans-Jürgen Schönig   

[PostGIS and the Geography Type](https://info.crunchydata.com/blog/postgis-and-the-geography-type) — The PostGIS `geography` type is a geospatial type that understands coordinates as spherical coordinates (in latitude and longitude) and here’s a basic introduction to them (and one of the reasons to use PostGIS too, really). 

Paul Ramsey   

[Best-Practices on How to Speed Up Your Postgres Queries. Free eBook](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresSecondary09092020) — Companies like Robinhood and Atlassian are able to speed up their queries by orders of magnitude. This eBook shares our best practices for optimizing Postgres performance. 

pganalyze sponsor   

[Tuning Postgres on ZFS](https://pg.uptrace.dev/zfs/) — _“The main reason to use ZFS instead of ext4/xfs is compression. With reasonable configuration you can achieve 3-5x compression ratio using LZ4. That means that LZ4 compresses 1 terabyte of data down to ~300 gigabytes.”_ 

Uptrace   

[Building Microservices with Deno, Reno, and Postgres](https://www.sitepoint.com/building-microservices-with-deno-reno-postgresql/) — [Deno](https://deno.land/) is a server-side JavaScript runtime built on top of V8 (a bit like Node, but not) and Reno is a routing library for Deno apps. 

James Wright   

[Mining for Logic Bugs in the Citus Extension to Postgres with SQLancer](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/mining-for-logic-bugs-in-the-citus-extension-to-postgres-with/ba-p/1634393) — One of those things you’re unlikely to need to do, but it’s nice to know how such problems are approached. [SQLancer](https://github.com/sqlancer/sqlancer) is a tool we’ve linked to before that helps you detect logic-related bugs in database systems. 

Nazli Ugur Koyluoglu      supported by [![YugabyteDB\](https://res.cloudinary.com/cpress/image/upload/v1593529453/obtljl96reuhv3jzrxqr.png)](https://distributedsql.org/)  

💡**Tip of the Week** 

Returning rows in the order specified in a list 

If you have a table of data (let's say a `books` table of books stored with their `name` and `publication_date`) and you want to return rows as specified by a list, you can use `IN` to do this, like so:  

SELECT * FROM books WHERE name IN ('The Adventures of Huckleberry Finn', 'Pride and Prejudice', 'The Great Gatsby');
 
 name | publication_date 
 ------------------------------------+---------------------
 Pride and Prejudice | 1813-01-28 00:00:00
 The Adventures of Huckleberry Finn | 1884-12-10 00:00:00
 The Great Gatsby | 1925-04-10 00:00:00
 (3 rows)   

However, the rows are _not_ guaranteed to be returned in the order in which they appear in the `IN` clause. 

If you want to retrieve rows and order them based on their order in the `IN` clause, you can make use of the `VALUES` clause and do a join as shown below:  

SELECT b.* FROM books b
 JOIN (
 VALUES ('The Adventures of Huckleberry Finn',1),
 ('Pride and Prejudice',2),
 ('The Great Gatsby',3)
 ) AS x (name, sortorder)
 ON b.name = x.name ORDER BY x.sortorder;   

Alternatively, you can also use `WITH ORDINALITY` for a different approach:  

SELECT b.* FROM books b 
 JOIN 
 unnest('{"The Adventures of Huckleberry Finn",
 "Pride and Prejudice",
 "The Great Gatsby"}'::text[])
 WITH ORDINALITY t(name, sortorder) USING (name)
 ORDER BY t.sortorder;   

This week’s tip is sponsored by [YugabyteDB](https://distributedsql.org/). Get more tips, database migration stories, and real-world distributed SQL database journeys at the (free) [Distributed SQL Virtual Summit](https://distributedsql.org/), Sept 15-17.          

Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom