Posts

Showing posts from March, 2018

Waiting for PostgreSQL 11: Pain free ADD COLUMN with non-NULL defaults

Fast ALTER TABLE ADD COLUMN with a non-NULL default.
On 28th of March, Andrew Dunstan commited the following patch:

commit 16828d5c0273b4fe5f10f42588005f16b415b2d8
Author: Andrew Dunstan <andrew@dunslane.net>
Date: Wed Mar 28 10:43:52 2018 +1030

Fast ALTER TABLE ADD COLUMN with a non-NULL default

Currently adding a column to a table with a non-NULL default results in
a rewrite of the table. For large tables this can be both expensive and
disruptive. This patch removes the need for the rewrite as long as the
default value is not volatile. The default expression is evaluated at
the time of the ALTER TABLE and the result stored in a new column
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set
to true. Any existing row when fetched will be supplied with the
attmissingval. New rows will have the supplied value or the default and
so will never need the attmissingval.

Any time the table is rewritten all the atthasmissing and attmissingval
settings for the attribu…

PostgreSQL Bloatbusters.

Image
Or when the vacuum is not enough.Anyone who works with PostgreSQL, whether a DBA or a developer, at some point encountered vacuum. All DBAs have configured vacuum and most developers have experienced decreased database performance and slow query performance due to the vacuum.
Though vacuum may seems as a black box to developers, most DBAs know how important vacuum and its configurations are. To those who are unfamiliar with vacuum, it is basically a garbage collector which cleans the database. Someone might ask - what does it clean my database from? Here, I would like to provide a brief overview, whilst you can find a detailed answer in the official PostgreSQL documentation.