#372 — September 9, 2020

Web Version

Postgres Weekly

A Look at B-Tree Index Deduplication in Postgres 13 — 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 — 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

[Whitepaper] Business Case for Professional Support — 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 — 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 — Postgres’s tablefunc 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 — 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 — 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 — “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 — Deno 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 — One of those things you’re unlikely to need to do, but it’s nice to know how such problems are approached. 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

💡 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. Get more tips, database migration stories, and real-world distributed SQL database journeys at the (free) Distributed SQL Virtual Summit, Sept 15-17.