Improvements in Expanded Objects in PostgreSQL 18

 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 arrays in Oracle are typically not persisted, using expanded objects for pgcollection was a perfect match.