Wichtige Updates für PostgreSQL^® 9.5

Heute sind aktualisierte Pakete der stabilen Versionen von PostgreSQL^® erschienen. Vor allem für Benutzer mit anderen Locale als C oder SQL_ASCII (im besonderen de_DE.UTF-8) ist die neue Version 9.5.2 wichtig. Verwendet man PostgreSQL^® 9.5.0 oder 9.5.1 und Indexe auf Spalten mit den Typen character, varchar oder text, so ist generell ein REINDEX nach dem Update auf 9.5.2 angeraten. Binärpakete stehen für gängige RPM-basierte Systeme auf http://yum.postgresql.org bzw. für Debian und Ubuntu auf https://wiki.postgresql.org/wiki/Apt zur Verfügung.

Hintergrund

Mit dem Erscheinen von PostgreSQL^® 9.5 wurde eine Optimierung für Sortieroperationen auf Zeichentypen eingeführt. Diese Optimierung nennt sich Abbreviated Keys, die es erlaubt, eine verkürzte Binärform einer Zeichenkette als Sortierschlüssel heranzuziehen. Hierzu wird die Systemroutine strxfrm() verwendet, die einen normalisierten Binärstring der ursprünglichen Zeichenkette zurückliefert. PostgreSQL^® verwendet nun die ersten 8 Bytes für den Sortierschlüssel, was diesen Vorgang insbesondere beim Indizieren bzw. Sortieren von sehr großen Datenmengen massiv beschleunigt. Die ersten 8 Bytes stellten sich bei umfangreichen Tests als ausreichend für Stringvergleiche heraus. Tiefergehende Details finden sich im Blogartikel von Peter Geoghegan, den Autor dieser Optimierung. Ein Voraussetzung für die Verwendung und Eignung dieser Methode in PostgreSQL^® wie beschrieben, war die in der C-Library ausgewiesene Kompatiblität der Sortierreihenfolge zwischen strcoll() und strxfrm(). Mit anderen Worten, wird das Ergebnis zweier Zeichenketten von strxfrm() mittels strcmp() verglichen, so ist das Ergebnis dasselbe, als würden die Zeichenketten direkt mit strcoll() verglichen. Diese Reihenfolge ist besonders für die Ermittlung der Indexposition für einen BTree-Index relevant.

Leider hat sich herausgestellt, dass Plattformen mit der GNU Libc unter Umständen eine fehlerhafte Implementierung aufweisen, die Inkompatibilitäten zwischen dem Ergebnis von strcoll() und der Kombination von strxfrm()/strcmp() verursachen. Dies führt in aktuellen PostgreSQL^® 9.5.0 und 9.5.1 Releases zu gegebenenfalls korrupten Indexen, je nachdem ob das verwendete Locale diese Unterschiede aufweist, oder nicht. Leider kann man nicht sagen, welche der Locales generell davon Probleme verursachen, es gibt jedoch deutliche Hinweise, dass insbesondere das deutsche Locale de_DE.UTF-8 massiv von dieser Problematik betroffen ist.

Daher ist es auf jeden Fall angeraten, entsprechende Indexe in Datenbanken mit anderen Locale als C bzw. SQL_ASCII
neu anzulegen. Hierzu lässt sich das Kommando REINDEX verwenden. Wer wissen möchte, ob er bestimmte BTree-Indexe auf char, varchar oder text Typen verwendet, kann folgende Query anwenden und den REINDEX auf diese beschränken (also das Reindizieren der kompletten Datenbank vermeiden):

SELECT
        DISTINCT r.relname, indisprimary AS primary_key, indisunique AS is_unique
FROM
        pg_class r
        JOIN pg_index i ON i.indexrelid = r.oid
        JOIN pg_am am ON am.oid = r.relam
        JOIN pg_namespace n ON n.oid = r.relnamespace
        JOIN pg_attribute attr ON attr.attnum = ANY(i.indkey)
        JOIN pg_type t ON t.oid = attr.atttypid
WHERE r.relkind = 'i'
      AND am.amname = 'btree'
      AND n.nspname NOT LIKE E'pg\\_%'
      AND t.typname IN ('text', 'varchar', 'bpchar');

Die aktuelle Version 9.5.2 deaktiviert zunächst die Optimierung vollständig, bis die genaue Ursache für das Problem identifziert werden konnte. Natürlich stehen wir auch bei der Problemlösung mit dem Supportteam der credativ gerne zur Verfügung.