egorius

https://postgr.es/p/7jm

https://postgr.es/p/7jm

https://postgr.es/p/7jk

Barcelona PostgreSQL User Group met on Tuesday, Oct 7, organized by Dave Pitts & Lauro Ojeda

Speakers - Dave Pitts - Lauro Ojeda

Ellyne Phneah released two books: * The Social Code: Building a PostgreSQL Community Wired for Belonging * Decode PostgreSQL: Understanding the World's Most Powerful Open-Source Database Without Writing Code

https://postgr.es/p/7jk

https://postgr.es/p/7ji

© Tobi Albe 2025

PostgreSQL can scan B-tree indexes in both directions. That means that there is little need to create a index in descending order (one created with the DESC clause). However, there are some cases where you need a descending index. There are also some corner cases where a descending index performs better than an ascending one. Follow me to explore use cases for the DESC clause!

Mixed ORDER BY clauses

This is the obvious use case for a descending index. There is a reason why CREATE INDEX offers the same clauses as ORDER BY: if you want a B-tree index to support an ORDER BY clause, its sorting order must match the ORDER BY clause precisely. So, to support a query like

SELECT col2 FROM tab
ORDER BY col1 DESC NULLS LAST;

you need to create an index like

CREATE INDEX tab_col1_desc_idx
ON tab (col1 DESC NULLS LAST);

But since PostgreSQL can scan indexes in both directions, the following index will also work:

CREATE INDEX tab_col1_idx
ON tab (col1 NULLS FIRST);

However, you cannot avoid the DESC clause if you want to support an ORDER BY clause with both ascending and descending columns:

SELECT col2 FROM tab
ORDER BY col1 DESC, col2 NULLS LAST;

In a previous article, I wrote some more about mixed ORDER BY clauses in the context of keyset pagination. There, you can find some more creative ideas for using descending indexes.

Descending indexes for space efficiency

This is a somewhat more exotic use case. I'll demonstrate it with the following table:

CREATE UNLOGGED TABLE large(
   id bigint NOT NULL
);

CREATE INDEX large_id_idx ON large(id);
CREATE INDEX large_id_desc_idx ON large (id DESC);

One of these two indexes is clearly redundant. But look what happens if I INSERT rows in descending order:

INSERT INTO large (id)
SELECT * FROM generate_series(10000000, 1, -1);

SELECT ind AS index_name,
       pg_size_pretty(pg_relation_size(ind)) AS index_size
FROM (VALUES ('large_id_idx'::regclass),
             ('large_id_desc_idx'::regclass)) AS i(ind);

    index_na

https://postgr.es/p/7ji

https://postgr.es/p/7jg

In this series, we talk about the many different ways you can speed up PostGIS. Today let’s talk about looking across the queries with pg_stat_statements and some basic tuning.

Showing Postgres query times with pg_stat_statements

A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?”

Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries.

CREATE EXTENSION pg_stat_statements;

Now you will have to leave your database running for a while, so the extension can gather up data about the kind of queries that are run on your database.

Once it has been running for a while, you have a whole table – pg_stat_statements – that collects your query statistics. You can query it directly with SELECT * or you can write individual queries to find the slowest queries, the longest running ones, and so on.

Here is an example of the longest running 10 queries ranked by duration.

SELECT
  total_exec_time,
  mean_exec_time,
  calls,
  rows,
  query
FROM pg_stat_statements
WHERE calls > 0
ORDER BY mean_exec_time DESC
LIMIT 10;

While “pg_stat_statements” is good at finding individual queries to tune, and the most frequent cause of slow queries is just inefficient SQL or a need for indexing - see the first post in the series.

Occasionally performance issues do crop up at the system level. The most frequent culprit is memory pressure. PostgreSQL ships with conservative default settings for memory usage, and some workloads benefit from more memory.

Shared buffers

A database server looks like an infinite, accessible, reliable bucket of data. In fact, the server orchestrates data between the disk – which is permanent and slow – and the random access memory – which is volatile and fast – in order to provide the illusion of such a system.

When the balance between slow storage and fast memory is out of whack, syste

https://postgr.es/p/7jg

https://postgr.es/p/7jc

In August of this year, I published a blog entitled PostgreSQL’s incredible trip to the top with developers which shows how Postgres has become the most used, most loved and most desired database according to the Stack Overflow annual developer survey. In that blog I said, I want to do the series in two parts. After some thought, I have decided to make it a 3 part series. It will break down as follows:

Part 1: The aforementioned blog was just the facts regarding Postgres becoming the most loved, most desired and most used database by developers according to the stack overflow survey. 

Part 2

https://postgr.es/p/7jc