What is new in PostgreSQL 10.0?

PostgreSQL 10.0 has been released in late 2017 and is the first version that follows the new numbering scheme introduced by the PostgreSQL community. From now on, the way major releases are done will change and therefore, the next major version after PostgreSQL 10.0 will not be 10.1 but PostgreSQL 11. Versions 10.1 and 10.2 are merely service releases and will only contain bug fixes.

Understanding new database administration functions

PostgreSQL 10.0 has many new features that can help the administrator reduce work and make systems more robust.

One of these features that makes life easier for administrators is related to additional information in pg_stat_activity.

Using additional information in pg_stat_activity

Before PostgreSQL 10.0, pg_stat_activity only contained information about normal backend processes serving end users (connections). However, this has changed. Since PostgreSQL 10.0, a lot more information is exposed. It is possible to figure out what these other system processes are doing.

The following listing shows the content of  pg_stat_activity on an idle database instance:

test=# \x 
Expanded display is on. 

test=# SELECT pid, wait_event_type, wait_event, backend_type  
   FROM  pg_stat_activity ;
 
-[ RECORD 1 ]---+-------------------- 
pid             | 12159 
wait_event_type | Activity 
wait_event      | AutoVacuumMain 
backend_type    | autovacuum launcher 
-[ RECORD 2 ]---+-------------------- 
pid             | 12161 
wait_event_type | Activity 
wait_event      | LogicalLauncherMain 
backend_type    | background worker 
-[ RECORD 3 ]---+-------------------- 
pid             | 12628 
wait_event_type |  
wait_event      |  
backend_type    | client backend 
-[ RECORD 4 ]---+-------------------- 
pid             | 12156 
wait_event_type | Activity 
wait_event      | BgWriterMain 
backend_type    | background writer 
-[ RECORD 5 ]---+-------------------- 
pid             | 12155 
wait_event_type | Activity 
wait_event      | CheckpointerMain 
backend_type    | checkpointer 
-[ RECORD 6 ]---+-------------------- 
pid             | 12157 
wait_event_type | Activity 
wait_event      | WalWriterMain 
backend_type    | walwriter 

What you see here is that every server process is listed. It will allow you to gain some insights into what is happening the server.