Wird die Verwendung von UUID als Primärschlüssel in PostgreSQL zu einer schlechten Indexleistung führen?

Question

Ich habe eine App in Rails auf Heroku mit einer PostgreSQL-Datenbank erstellt.

Es gibt ein paar Tabellen, die so konzipiert sind, dass sie mit mobilen Geräten synchronisiert werden können, wo Daten an verschiedenen Orten erstellt werden können. Dafür habe ich ein uuid-Feld, das eine Zeichenfolge speichert, die einer GUID entspricht, zusätzlich zu einem automatisch inkrementierten Primärschlüssel. Das uuid ist das, was zwischen dem Server und den Clients kommuniziert wird.

Nach der Implementierung des Synchronisations-Motors auf der Serverseite habe ich festgestellt, dass dies zu Leistungsproblemen führt, wenn ich ständig zwischen uuid<->id zuordnen muss (beim Schreiben von Objekten muss ich nach der uuid abfragen, um die id zu erhalten, bevor ich speichere, und umgekehrt beim Zurücksenden von Daten).

Ich überlege jetzt, nur noch UUID als Primärschlüssel zu verwenden, um das Schreiben und Lesen einfacher und schneller zu machen.

Ich habe gelesen, dass UUID als Primärschlüssel manchmal eine schlechte Indexleistung (Indexfragmentierung) geben kann, wenn ein geclusteter Primärschlüsselindex verwendet wird. Leidet PostgreSQL unter diesem Problem oder ist es in Ordnung, UUID als Primärschlüssel zu verwenden?

Ich habe bereits eine UUID-Spalte heute, also wird es speichermäßig besser sein, weil ich die reguläre id-Spalte entferne.

Answer 1

(Ich arbeite mit Heroku Postgres)

Wir verwenden UUIDs als Primärschlüssel in einigen Systemen und es funktioniert großartig.

Ich empfehle Ihnen, die uuid-ossp-Erweiterung zu verwenden, und lassen Sie Postgres sogar UUIDs für Sie generieren:

heroku pg:psql
psql (9.1.4, Server 9.1.6)
SSL-Verbindung (Verschlüsselung: DHE-RSA-AES256-SHA, Bits: 256)
Geben Sie "help" für Hilfe ein.

dcvgo3fvfmbl44=> CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION  
dcvgo3fvfmbl44=> CREATE TABLE test (id uuid primary key default uuid_generate_v4(), name text);  
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt impliziten Index "test_pkey" für Tabelle "test"
CREATE TABLE  
dcvgo3fvfmbl44=> \d test
                 Tabelle "public.test"  
Spalte | Typ |              Modifikatoren              
--------+------+-------------------------------------  
id     | uuid | not null default uuid_generate_v4()  name   | text |  
Indizes:
    "test_pkey" PRIMARY KEY, btree (id)

dcvgo3fvfmbl44=> insert into test (name) values ('hgmnz'); 
INSERT 0 1 
dcvgo3fvfmbl44=> select * from test;
                  id                  | name  
--------------------------------------+-------   
 e535d271-91be-4291-832f-f7883a2d374f | hgmnz  
(1 Zeile)

EDIT Leistungsimplikationen

Es wird immer von Ihrer Arbeitslast abhängen.

Der Integer-Primärschlüssel hat den Vorteil der Lokalität, wo ähnliche Daten näher beieinander liegen. Dies kann zum Beispiel bei Bereichstypabfragen wie WHERE id zwischen 1 und 10000 hilfreich sein, obwohl die Sperrkonflikte schlimmer sind.

Wenn Ihre Lese-Arbeitslast völlig zufällig ist, das heißt, Sie immer Primärschlüsselabfragen durchführen, sollte es keine messbaren Leistungseinbußen geben: Sie zahlen nur für den größeren Datentyp.

Schreiben Sie viel in diese Tabelle und ist diese Tabelle sehr groß? Es ist möglich, obwohl ich das nicht gemessen habe, dass es Auswirkungen auf die Aufrechterhaltung dieses Index gibt. Für viele Datensätze sind UUIDs jedoch völlig in Ordnung, und die Verwendung von UUIDs als Bezeichner hat einige gute Eigenschaften.

Zuletzt bin ich vielleicht nicht die qualifizierteste Person, um dies zu diskutieren oder zu empfehlen, da ich noch nie eine große Tabelle mit einem UUID PK ausgeführt habe, wo es zu einem Problem geworden ist. Die Ergebnisse können variieren. (Abgesehen davon würde ich gerne von Personen hören, die auf Probleme mit diesem Ansatz gestoßen sind!)

Answer 2

Wie die akzeptierte Antwort besagt, können Bereichsanfragen in diesem Fall langsam sein, aber nicht nur bei id.

Autoincrement wird natürlich nach Datum sortiert, so dass bei Verwendung von Autoincrement die Daten chronologisch auf der Festplatte gespeichert sind (siehe B-Tree), was Lesevorgänge beschleunigt (keine Suche bei HDDs). Wenn z. B. alle Benutzer aufgelistet werden, erfolgt die natürliche Reihenfolge nach Erstellungsdatum, was dasselbe ist wie Autoincrement, und somit werden Bereichsanfragen bei HDDs schneller ausgeführt, während bei SSDs, schätze ich, der Unterschied nicht vorhanden wäre, da SSDs grundsätzlich immer zufälligen Zugriff bieten (kein Kopfsuchen, keine mechanischen Teile beteiligt, nur reine Elektrizität)