PostgreSQL Dba

Saturday, 20 December 2014

Brand new elephant

So finally PostgreSQL 9.4 has been released. This version introduces a new schema less type, the jsonb which joins the other fantastic features abused and misused by the developers all around the globe.

The DBA improvements in this release is much more interesting and can push seriously the adoption in the enterprises of this DBMS.

Alter system

Finally is possible to change the postgresql.conf settings using the ALTER SYSTEM SET command. The cool part is the values are validated when the command is issued. No more hosed clusters by typos. Kudos!

autovacuum_work_mem

Tuning the maintenance_work_mem, in particular on large instances, is a pain in the ass. If the value is too high multiple autovacuum processes can exaust the server's memory. If too low the vacuum of the indices can take ages. This parameter sets the maintenance work mem just for the autovacuum daemons. Kudos!

Replication slots

The streaming replication is a fantastic feature with a big problem. The master doesn't know which location standby is replaying. The WAL files are recycled on a regular basis, and if the lag becomes too much the SR will loose the connection with the WAL sequence. This requires the double implementation SR/Wal shipping to have a fallback strategy if the slave accumulates lag. The replications slots set an active communication between the slave and the master which does not recycle the wal files unless are no longer required by the slave. Kudos!

Planning time

Now EXPLAIN ANALYZE shows the time spent by the planner to build the execution plan, giving to the performance tuners a better understanding of the query efficiency.

pg_prewarm

This additional module loads the relation's data into the shared buffer after a shutdown. This allows the cluster reaching the efficiency quickly.

huge_pages

This parameter enables or disables the use of huge memory pages on Linux. Turning on this parameter can result in a reduced CPU usage for managing large amount of memory on Linux.