PostgreSQL 18 Release Notes

 The PostgreSQL 18 Release Notes are like a great noir novel, full of surprises and intrigue. But we know who did it - the amazing PostgreSQL community.

If you have not perused the Notes, I advise you to do so now. They contain many of what I call 'Wow!' level items, such as the redesigned I/O subsystem, skip scan lookups, virtual generated columns, and more.  The new Uuidv7 function and temporal constraints would be a significant leap forward. The OLD and NEW support for RETURNING will help lessen the learning curve for many who are new to SQL.

But go down to the seemingly more mundane items. Removing redundant self-joins, merge-joins can now use incremental sorts, and internally reordering the keys of SELECT DISTINCT to avoid sorting are the types of improvements that are not as attention-getting as the items in the preceding paragraph, but will make life much nicer for us. 

Each release of PostgreSQL is faster than the previous one, and early reports report that this has happened again. This is an astounding delivery for those of us with backgrounds in other databases. All too often, new features came with a trade-off in performance that relegated upgrades to the back burner.

Please read the Acknowledgments. This is a list of the good folks who have worked very hard on our behalf to deliver an improved version of PostgreSQL. And they are already working on 19! If you run into any of them, please give them a big 'thank you'.  

Comments

Post a Comment

Popular posts from this blog

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

Saving The Output From psql

  Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script . In typical PostgreSQL fashion, psql  has two ways to save your output. The First Way You must invoke psql with the --log-file-<filename> or -L <filename> option. The entire session will be recorded. stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo Password for user stoker: psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2)) Type "help" for help. demo=# \d ledger                    Table "public.ledger"      Column     |  Type   | Collation | Nullable | Default ----------------+---------+-----------+----------+---------  id             | integer |           |          |  tx_id          | integer |        ...
Read more