When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It's not guaranteed to be the same when your function is not immutable.

Check TRAKTOR 2 - less complicated, more stable, cloud ready! Compatible with PostgreSQL 16 and 17.

Stacey Haysler created a document which lists all the available volunteer options for the PostgreSQL project.

Luigi Nardi organized the Malmö PostgreSQL Meetup. Tomas Vondra presented.

April 23 the Postgres Extensions Ecosystem Mini-Summit #4 hosted a panel of users - Celeste Horgan, Sonia Valeja, Alexey Palazhchenko - to gain their POV (recording).

At the April 22 Barcelona PostgreSQL User Group meetup run by Andrea Cucciniello, Dave Pitts, and Nacho Alonso Portillo, Gabriele Bartolini presented "CloudNativePG: The Past, The Present, The Future".

On April 24th Gerard Zuidweg hosted the PostgreSQL User Group NL Spring 2025 Meetup. The following talks were held: Frank Sidi about “Analytics for PostgreSQL: From OLTP to Lakehouse to Petabyte-Scale AI” Hans-Jürgen Schönig about “Running PostgreSQL in Kubernetes made easy” Lightning Talk held by Ellert van Koperen about “Defaults and logical replication do not get along”

On April 23rd Karren Jex gave a lightning talk at DjangoCon Europe about the Diversity Task Force and encouraging folks to get involved in the PostgreSQL community and come along to PostgreSQL events.

Tom Kincaid organized the Boston PostgreSQL Meetup, Adam Wright spoke at the event.

Alastair Turner and Chris Ellis organized the PostgreSQL London Meetup. Tushar Ghotikar, Mauro Parada Crespo and Alastair Turner presented at the event.

Tobias Bussman, Andreas Geppert and Daniel Westermann have been part of the organization team for Swiss Database Synergy Day 2025. [Dirk Krautschick] (https://www.linkedin.com/in/dirk-krautschick), Laurenz Albe, Stephan Keller, Maurizio De Giorgi and Daniel Westermann presented talks.

Working on TRAKTOR again. The good news is, it still works with PostgreSQL 17.x and I have completely replaced psycopg2 with pg8000 to remove all native dependencies. The bad news is, if you don't want to run the arbiter as superuser it's a complicated dance of permissions, some of which can only be GRANTed by a superuser, and I have to find a way to automate this as far as possible.

Today, I would like to discuss additional techniques to speed up query execution. Specifically, I will focus on rearranging conditions in filter expressions, JOINs, HAVING clauses, and similar constructs. The main idea is that if you encounter a negative result in one condition within a series of expressions connected by the AND operator, or a positive result in one of the conditions linked by the OR operator, you can avoid evaluating the remaining conditions. This can save computing resources. Below, I will explain how much execution efforts this approach saves and how to implement it effectively.

Occasionally, you may come across queries featuring complex filters similar to the following:

SELECT * FROM table
WHERE
  date > min_date AND
  date < now() - interval '1 day' AND
  value IN Subplan AND
  id = 42';

And in practice, it happens that a simple rearrangement of the order of conditions in such an expression allows for speeding up (sometimes quite notably) the query execution time. Why? Each individual operation costs little. However, if it is performed repeatedly on each of millions of the table's rows, then the price of the operation becomes palpable. Especially if other problems, like the table blocks getting into shared buffers, are successfully solved.

This effect is particularly evident on wide tables that contain many variable-length columns. For instance, I often encounter slow IndexScans that become slow when the field used for additional filtering is located somewhere around the 20th (!) position in the table, containing many variable-width columns. Accessing this field requires calculating its offset from the beginning of the row, which takes up processor time and slows down the execution.

The PostgreSQL community has already addressed this issue, as observed in the code. In 2002, commit 3779f7f, which was added by T. Lane, reorganised the clauses by positioning all clauses containing subplans at the end of the clause list (see order_qual_clauses). This change was logical because

When disaster strikes, whether a natural disaster or a technical event, its impact on your network, database, and end-users can cause both costly downtime and data corruption. Data corruption, whether sparked by hardware failures like dying disks or faulty RAM, software glitches such as operating system bugs, or human missteps like accidental overwrites, is a terrifying prospect for any administrator. Yet, it’s not a death sentence.Your PostgreSQL database is typically a dependable cornerstone of your operations. Still, when disaster strikes, it can swiftly morph into an inaccessible liability, bringing applications to a grinding halt and leaving critical data at risk. PostgreSQL 17 arms you with an enhanced arsenal to tackle this challenge head-on, offering built-in tools like for pinpointing corruption, improved failover slot synchronization to keep replication intact during recovery, and finer-grained Write-Ahead Logging (WAL) control for precise restoration. In this blog, we'll dive deep into the disaster management process, equipping you with real-world commands and expected outputs to diagnose corruption accurately and recover effectively, whether you’re restoring from a robust backup or salvaging scraps from a crippled cluster with no safety net. With the right approach, you can turn panic into a plan and restore order to your database.

Step 1: Detecting Corruption in PostgreSQL

© Laurenz Albe 2025

Saving storage space should not be your first objective in a PostgreSQL database. Very often, the desire to conserve space aggressively comes at the expense of performance. But there is also no reason to needlessly waste space. Therefore, it is a good idea to be familiar with the concepts of data type alignment and padding bytes in PostgreSQL.

What is data type alignment?

When the CPU reads or writes a value from memory, the performance is best if the address of the value is a multiple of the size of the value. For example, a 4-byte integer should start at an address that is a multiple of four. PostgreSQL tries to optimize for performance. Therefore, it makes sure that all values are correctly aligned in memory. Note that alignment is only relevant for data types with a fixed length: PostgreSQL stores variable length data types like text, varchar and numeric without respecting the type alignment.

Whenever PostgreSQL persists data on disk, it organizes these data in pages (also known as buffers or, when on disk, as blocks) of 8kB. To keep things simple and efficient, the layout of a block on disk is exactly the same as the page in memory. As a consequence, PostgreSQL respects the type alignment on disk as well.

PostgreSQL data types can have an alignment of 1, 2, 4 or 8 bytes. You can see the alignment of a data type in the typalign column of the system catalog pg_type:

SELECT typalign,
       string_agg(typname, ', ' ORDER BY length(typname)) AS types
FROM pg_type
WHERE typtype = 'b'            -- base type
  AND typelem = 0              -- no array
  AND typlen <> -1             -- fixed length
  AND typnamespace = 'pg_catalog'::regnamespace  -- system type
  AND typname NOT LIKE 'reg%'  -- no object identifier type
GROUP BY typalign;

 typalign │                                               types
══════════╪════════════════════════════════════════════════════════════════════════════════════════════════════
 c        │ bool, char, uuid
 d        │ xid8, time, int8,

With all five CommitFests over, we're now into the testing and bugfix phase, and no new features are likely to be added. Having said that, there have been a few tweaks to SQL function performance and handling this week.

more...

April 9 Christoph Berg spoke at the Postgres Extensions Ecosystem Mini-Summit #3 (slides, recording, transcript), a series of virtual events leading up to the gathering at PGConf.Dev.

Andrew Atkinson joined the Talking Postgres Podcast to talk about “Helping Rails developers learn Postgres”, back in November. He wrote a blog about it.

April 2nd the Talking Postgres podcast airs live, with Claire Giordano as the host, Bruce Momjian as the guest, and Aaron Wislang as its producer.

Henrietta Dombrovskaya kicked off Prairie Postgres, with help from Pat Wright, Dian Fay and Anna Bailliekova.

Gülçin Yıldırım Jelínek organized the [Prague PostgreSQL Meetup, March Edition on March 31st]((https://www.meetup.com/prague-postgresql-meetup/events/306440481). Mayuresh B and Tomáš Vondra presented sessions.

David Wheeler, Floor Drees, Keith Fiske and Yurii Rashkovskii organize a series of virtual gatherings to explore the current state and future of Postgres extension registration, packaging, and distribution, ahead of the Postgres Extensions Ecosystem Summit at PGConf.Dev. March 26, Peter Eisentraut (EDB) talked about implementing an extension search path (recording, slides, blog).

Nordic PGDay (March 18) was organized by Daniel Gustafsson, Magnus Hagander, Georgios Kokolatos.

The following speakers contributed with a talk:

• Chris Ellis - PostgreSQL Tips & Tricks For App Devs
• Alexander Kukushkin - pg_upgrade like a boss!
• Dr. Luigi Nardi - Unlock PostgreSQL performance: AI-powered tuning with DBtune
• Ants Aasma - Taming write latencies with Patroni quorum mode
• Kaarel Moppel - Postgres test data generation 101
• Dennis Rilorin - Multi-postgres: Enterprise ready PostgreSQL
• Nikolai Utzon-Meiland - Postgres is evolving from a Database to a platform for AI workloads —are you ready?
• Dmytro Hnatiuk - From 60 Million to 25 Billion: 8 Years of Scaling a Core System with PostgreSQL in Fintech
• Priyanka Chatterjee - Your PostgreSQL Database performance radar: pgBadger
• Derk van Veen - Leveraging table partitioning for query performance and data archiving
• Vik Fearing - Window Functions Are Easier and More Powerful Than You Think
• Andrzej Nowicki - From Queries to Pints: Building a Beer Recommendation System with pgvector

Lightning Talks:

• Christophe Pettus - First Responders
• Pavlo Golub - Play Wordle with PostgreSQL
• Devrim Gündüz - The state of PostgreSQL RPMs

Program Committee:

• Georgios Kokolatos
• Stefanie Janine Stölting
• Dennis Rilorin
• Jan Birk

PGDay Paris (March 20) was organized by Eliza Bennett, Julien Riou (OVHcloud), Stéphanie Baltus-Bergamo (Kanop), Valeria Kaplan (Data Egret), Vik Fearing (EDB.)

The speakers:

• Ellyne Phneah - Tuning community parameters: Hosting PostgreSQL events that perform
• Pavlo Golub - Professional PostgreSQL monitoring made easy
• Matt Cornillon - Full-Text-Search Explained from A to Z with French Food
• Dirk Krautschick - Why PostgreSQL people should really care about Kafka and Debezium?
• Viktoriia Hrechukha - PostgreSQL and NodeJs as a Core Feature of Backend for Frontend
• Hana Litavská - Explore the role of PostgreSQL in GitLab ecosys

PostgreSQL Person of the week: Emma Saroyan

... Thanks Andreas Scherbaum, for publishing these interviews since - checks notes - February 2020!

I wrote an article about PostgreSQL 18.  It is in Czech language, but translators from Czech to English, German, ... works relatively well today.

 https://www.root.cz/clanky/postgresql-18-tricet-let-otevreneho-vyvoje-databaze/

I wrote User, User, Who Is the User? in January of 2024, but didn't publish the slides at that time. I was planning to publish them once I delivered the presentation, but it has been a year and I have still not deivered the talk, so I am now publishing the slides.

Performance optimization in a production database is important, but trying to over-optimize can make things more complicated without real improvements.

In this post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these queries. I've changed the table and column names for privacy. We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning.

Plan A: Identifying and Resolving a Bottleneck

Execution Plan A (Before Optimization)
Nested Loop (cost=1000.42..25607.38 rows=1 width=129) (actual time=78.521..90.445 rows=0 loops=1)
-> Gather (cost=1000.00..25598.95 rows=1 width=65) (actual time=78.520..90.443 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on e (cost=0.00..24598.85 rows=1 width=65) (actual time=75.351..75.351 rows=0 loops=3)
Filter: (() = ''::date) AND (()::text = ''::text)
Rows Removed by Filter:
-> Index Scan using on a (cost=0.42..8.43 rows=1 width=41) (never executed)
Index Cond: (()::text = ()::text)
Filter: (()::text = ''::text)
Planning Time: 0.466 ms
Execution Time: 90.580 ms

The post Finding Bottlenecks and Avoiding Over-Optimization via Explain Plans appeared first on Stormatics.

In the previous post we have imported a fairly large data set containing Wikipedia data, which we downloaded using pgai. However, importing all this data is not enough because we also need to keep an eye on efficiency. Therefore, it is important to understand that indexing is the key to success.

In general, pgvector provides us with two types of indexes that we can use:

- hnsw
- ivfflat

The core questions: When do we need which type of index and how can we create those indexes in the first place?

In general, HNSW (a multilayer graph) is faster during execution time ("SELECT") but is a LOT slower during index creation, as we will see a little later. If index creation time is not an issue, HNSW is definitely a good choice - if index creation time does matter? Well, not so much.

Indexing a table containing vectors

To show how indexing can be done, we are using the following table, which has been created by the vectorization process described in the previous post:

cybertec=# \d demo_wiki_emb_store
                   Table "public.demo_wiki_emb_store"
     Column     |    Type     | Collation | Nullable |      Default      
----------------+-------------+-----------+----------+-------------------
 embedding_uuid | uuid        |           | not null | gen_random_uuid()
 id             | text        |           | not null | 
 chunk_seq      | integer     |           | not null | 
 chunk          | text        |           | not null | 
 embedding      | vector(384) |           | not null | 
Indexes:
    "demo_wiki_emb_store_pkey" PRIMARY KEY, btree (embedding_uuid)
    "demo_wiki_emb_store_id_chunk_seq_key" UNIQUE CONSTRAINT, btree (id, chunk_seq)
Foreign-key constraints:
    "demo_wiki_emb_store_id_fkey" FOREIGN KEY (id) REFERENCES wiki(id) ON DELETE CASCADE

Let us recall the content of those two tables (= raw data and vector data). The relation containing Wikipedia data contains 6.4 million rows that are broken down into 41 million chunks, which are turned into vectors:

cyberte

 Way back in PostgreSQL 9.5, there was a simple entry in the release notes:

Improve PL/pgSQL array performance (Tom Lane)

What was behind that was a concept called expanded objects. This introduced the ability for datatypes to have a different in-memory representation from its on-disk representation. In most cases, having the same representation is ideal. For example a 4 byte integer is compact and efficient for long term storage on-disk and is simple to use in-memory. However, for more complex datatypes like arrays or JSON, how they are used in memory in say a PL/pgSQL function, is very different than the needs for long term storage. Expanded objects like arrays and JSON, transition from a flat representation used on-disk and an expanded representation in memory. 

While expanded objects have been in core for a long time, not many extensions that add new datatypes use the concept. We can guess this based on a recent commit that will be available in PostgreSQL 18. Before this commit, if a local variable that uses expanded objects in a PL/pgSQL function was assigned from another local variable, the values would be flattened, assigned and then expanded again. That switching can end up being pretty expensive. This commit greatly helps datatypes like pgcollection. There is a COPY function available in pgcollection to get around this context switching, but knowing to use it does not come naturally for developers when they use the normal assignment operators for any other variable.

Pgcollection is designed for in-memory usage to help people move from databases like Oracle which have concepts like associative arrays to PostgreSQL. For some people, associative arrays are used extensively in their stored procedures and migrating them to PostgreSQL can be difficult. For associative arrays indexed by integers, moving them to PostgreSQL arrays in PL/pgSQL works pretty well, but when they are indexed by strings, inefficient work arounds like using JSON or Hstore are typically the path. Since associative

We are proud of what we have done in Bolzano.

PgTraining OpenDay is over!

Last Friday was PgTraining OpenDay in Bolzano, a free of charge day entirely dedicated to PostgreSQL, that we at PgTraning organized.

We hold the event in the spectacular NOI TechPark in Bozen (Bolzano), north Italy, and the room we had was simply amazing: everything was arranged in a very professional and clean way.

Chris, our host, Enrico and yours truly, had several talks with regard to cool topics like (but not limited to):

• vector support in PostgreSQL via PgVector, and what you can do with such a tool to create RAG applications;
• connection pooling (with regard to pgagroal)
• logical replication and hot upgrade.

The afternoon was a more practical part, when we displayed a few live demos to the audience.

All the material, slides and code samples, including a few Docker images, are available on the PgTraining Gitlab repository, more material will be available in the next days.

As a joke, during the afternoon, Chris embedded the whole Raku documentation in his RAG application and we enjoyed asking the application about how to connect Raku to a PostgreSQL database, with a couple of very detailed and accurate answers.

The audience was very interested in all the topics, and we are glad of such a good day.

We hope to be able to host soon another event like this, and we would like to get some more feedback, even “bad”, in order to arrange an even better event!

Well, the code freeze has come and gone, so it's all over for PostgreSQL 18 development, bar the testing. RC1 is likely due at the start of September, so fire up those test environments sooner rather than later!

Any new features from here on out will be for the future PostgreSQL 19 , whose first CommitFest will start in July ( 2025-07 ).

more...

Shortly before the code freeze for PostgreSQL 18, Robert Haas added a feature that allows external modules to provide additional information to the EXPLAIN command.

This was a long-awaited feature for me. For an extension that influences the query planning process, providing users with notes on how the extension has affected the plan makes perfect sense. Instead of merely writing to a log file - access to which is often restricted by security policies - this information may be made available through the EXPLAIN command.

The feature introduced many entities that are not easy to figure out: an EXPLAIN option registration routine (RegisterExtensionExplainOption), an explain extension ID, per plan/node hooks, and an option handler.

The pg_overexplain extension, introduced with this feature to demonstrate how it works, seems a little messy and impractical for me, at least in its current state. So, I decided to find out how flexible this new technique is and demonstrate the opportunities opening up to developers with a more meaningful example. I have modified the freely available pg_index_stats extension and added information about the statistics used in the query planning process.

The STAT parameter was added to the list of EXPLAIN options, accepting Boolean ON/OFF values. If it is enabled, information about the statistics used is inserted at the end of the EXPLAIN: the presence of MCV, histogram, and the number of elements in them, as well as the values ​​of stadistinct, stanullfrac, and stawidth.

You might wonder why this is necessary. After all, doesn't the set of statistics directly stem from the list of expressions in the query? Isn't it possible to identify which statistics were utilised by examining the cost-model code for a particular type of expression?

While it is indeed possible, this approach is not always sufficient. We understand the algorithms, but we typically do not have access to the underlying data. As a result, we cannot accurately determine which specific statistics are

I was watching Heikki Linnakangas’s talk on the Postgres protocol. I wanted to follow along with and see the protocol in action for myself. I ran into a problem here: my ostgres server rejected all non secure connections. And with TLS enabled, I could not get wireshark to decode postgres protocol messages because of encryption.

Now, wireshark supports decoding TLS connections using a key log file. Looking through documentation I realized that there is no way for a client to log the TLS keys using psql. So the natural next step was to add that support and try to get that accepted upstream in postgres. Once I understood how this could be implemented, this became my side quest.

CommitFest 52 (2025-03) - the final CommitFest in the PostgreSQL 18 development cycle - is approaching its conclusion, running as always beyond the end of March right up until code freeze on April 8th .

There have been a lot of interesting changes during this end spurt, and combined with personal reasons mean it's been challenging keeping track of them. As always, if there's something missing, please let me know via the feedback form.

more...