Posts

Showing posts from April, 2018

Let’s speed things up.

Image
Parallelism settings and how to accelerate your database performance.Few days ago, when performing a routine audit for our client, we noticed that parallel queries in the database were disabled. It wouldn’t be surprising but it’s important to note that our client has powerful servers that run not only lightweight OLTP queries. When we brought it to our client’s attention his first question was "How can we enable it?" and after that he reasonably added "Will there be any negative consequences if we do?"

This kind of question pops up more often than one thinks, hence this post where we look into parallel queries in detail.




pg_wal is too big… what's going on?

What’s common between DBA and detective? They both solve murder and mystery while trying to make sense of the nonsense. Recently, one of our clients had Postgres crash due to the lack of free space on the database server. Client's reaction was instantaneous - a hot standby was immediately promoted to master and the application continued working. Some details about the crash were saved and since it’s the age of clouds, devops and microservices, the failed host was recycled into a new hot standby.

Once the dust has settled, the client gave us saved information about the failure asking: What was it? How can I avoid it in the future?
The first thing that we’ve done is what any good detective would do - started looking for clues. Client provided us with crash logs and said that pg_xlog directory was suspiciously big while there were only around 2 thousand files with 16MB size each. We looked into postgresql.log to see what were the last lines before crash:

PANIC: could not write to fi…

Waiting for PostgreSQL 11: Covering + unique indexes.

Indexes with INCLUDE columns and their support in B-treeLast weekend, a very useful patch was committed, that enhances an already extensive Postgres indexing functionality.

commit 8224de4f42ccf98e08db07b43d52fed72f962ebb
Author: Teodor Sigaev <teodor@sigaev.ru>
Date: Sat Apr 7 23:00:39 2018 +0300

Indexes with INCLUDE columns and their support in B-tree

This patch introduces INCLUDE clause to index definition. This clause
specifies a list of columns which will be included as a non-key part in
the index. The INCLUDE columns exist solely to allow more queries to
benefit from index-only scans. Also, such columns don't need to have
appropriate operator classes. Expressions are not supported as INCLUDE
columns since they cannot be used in index-only scans.

Index access methods supporting INCLUDE are indicated by amcaninclude flag
in IndexAmRoutine. For now, only B-tree indexes support INCLUDE clause.

In B-tree indexes INCLUDE columns are truncated from pivot index tuples
(tuples located …

Waiting for PostgreSQL 11: Online enabling of data checksums.

Enable data page checksums on the fly without the database interruption.New interesting patch was committed on Thursday, 5 Apr.

Upd.Unfortunately this patch was reverted, and we can only dream about it and hope this feature will be added in a future.

Author: Magnus Hagander <magnus@hagander.net>
Date: Thu Apr 5 21:57:26 2018 +0200
Allow on-line enabling and disabling of data checksums This makes it possible to turn checksums on in a live cluster, without
the previous need for dump/reload or logical replication (and to turn it
off).

Enabling checksums starts a background process in the form of a
launcher/worker combination that goes through the entire database and
recalculates checksums on each and every page. Only when all pages have
been checksummed are they fully enabled in the cluster. Any failure of
the process will revert to checksums off and the process has to be
started.

This adds a new WAL record that indicates the state of checksums, so
the process works across replicated clusters.

Waiting for PostgreSQL 11: Amcheck enhancement.

Check table’s indexed columns with amcheck.This weekend, an interesting patch related to amcheck was committed. Note, amcheck is a contrib module which allow to verify the logical consistency of indexes' structure.

commit 7f563c09f8901f6acd72cb8fba7b1bd3cf3aca8e
Author: Andres Freund <andres@anarazel.de>
Date: Sat Mar 31 19:52:01 2018 -0700

Add amcheck verification of heap relations belonging to btree indexes.

Add a new, optional, capability to bt_index_check() and
bt_index_parent_check(): check that each heap tuple that should have an
index entry does in fact have one. The extra checking is performed at
the end of the existing nbtree checks.

This is implemented by using a Bloom filter data structure. The
implementation performs set membership tests within a callback (the same
type of callback that each index AM registers for CREATE INDEX). The
Bloom filter is populated during the initial index verification scan.

Reusing the CREATE INDEX infrastructure allows the new verification
option …