Watt's Festival

byAndrew VogelonApril 2, 2019

Letzte Woche wurde ich gerufen, um an einem Rails-Projekt teilzunehmen, das einige Leistungsoptimierungen benötigte. Unser gewählter Ansatz war es, einen Bulk-Lookup-and-Update-Prozess aus ActiveRecord Land zu nehmen und alles in Postgres zu verschieben. Die Leistungsverbesserung war für große Datensätze enorm. Am oberen Ende sahen wir, dass die Anforderungszeiten von > 5 Minuten auf unter 2 Sekunden stiegen.

Ich hatte zuvor SQL und Postgres verwendet, war aber mit den Funktionen nicht vollständig vertraut. Zuerst habe ich mich mit einem Kollegen zusammengetan, der tiefere Kenntnisse in Postgres hat. Nach ein paar Tagen des Schreibens von Abfragen und des Refactorings der Testsuite war alles grün und ich konnte es zum Testen im Staging bereitstellen.

Hier sind einige interessante SQL- und PostgreSQL-Dinge, die ich auf dem Weg gelernt habe.

CTEs

Obwohl sie nicht exklusiv für PostgreSQL sind, sind CTEs oder allgemeine Tabellenausdrücke eine Möglichkeit, wiederverwendbare Abfragen in ein Datenbanksystem zu schreiben.

Nehmen wir an, wir haben 2 Tabellen – books und authors.

Büchertisch

id	Titel	autor_id	Genre
1	Digitale Festung	1	thriller
2	Der Da Vinci Code	1	thriller
3	Harry Potter und die Kammer des Schreckens	2	fantasie

Autoren-Tabelle

id	name	Alter
1	Dan Brown	54
2	J. K. Rowling	53

Wir können einen wirklich einfachen CTE schreiben, um die Bücher mit den Autoren zu bekommen:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

Wenn wir dann aus unserem CTE auswählen, erhalten wir Folgendes:

SELECT * FROM authors_and_books; 

book_id	Titel	Name des Autors
1	Digitale Festung	Dan Brown
2	Der Da Vinci Code	Dan Brown
3	Harry Potter und die Kammer des Schreckens	J. K. Rowling

Dieses Beispiel ist einfach, aber Sie können sehen, wie dies wirklich nützlich sein kann, wenn Sie komplexe Abfragen wiederverwenden müssen.

Temporäre Tabellen (PostgreSQL)

Eine weitere wirklich nützliche Datenbankfunktion ist die Möglichkeit, temporäre Tabellen zu erstellen. In meinem Fall war dies wirklich nützlich, um „unverarbeitete“ Daten zu staging, die vom Frontend gepostet wurden. Um eine temporäre Tabelle zu erstellen, müssen Sie ihr Schema wie Ihre normale create table .. -Anweisung definieren.

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

Der wichtige Teil hier ist der ON COMMIT am Ende. Sie müssen Postgres mitteilen, wie die temporäre Tabelle am Ende des Transaktionsblocks behandelt werden soll. DROP weist Postgres an, die temporäre Tabelle am Ende des Transaktionsblocks zu löschen. Die Postgres-Dokumente beschreiben mehr der ON COMMIT-Optionen.

COALESCE

Die Funktion COALESCE gibt den ersten übergebenen Wert ungleich Null zurück. Diese Funktion akzeptiert eine unbegrenzte Anzahl von Argumenten und gibt das erste Argument ungleich Null zurück, das von links nach rechts ausgewertet wird.

Hier ein paar Beispiele, wie das aussehen könnte:

Coalesce Beispiel 1

select coalesce(null, 1); 

coalesce

Coalesce Beispiel 2

select coalesce(null, null, 1, null); 

coalesce

Coalesce Beispiel 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting oder Aktualisieren und Einfügen ist eine großartige Funktion in Postgres. Sie können Einfügungen mit Konfliktlösung verarbeiten, wenn bereits ein Datensatz in der Datenbank vorhanden ist.

Erinnerst du dich an unseren Büchertisch?

Büchertisch

id	Titel	autor_id	Genre
1	Digitale Festung	1	thriller
2	Der Da Vinci Code	1	thriller
3	Harry Potter und die Kammer des Schreckens	2	fantasie

Upserts Beispiel 1

Wir fügen einige Datensätze in Bücher ein, aber wenn das Buch existiert, werden wir nichts tun:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

Schauen wir uns nun unseren Tisch an:

SELECT * FROM books; 

id	Titel	autor_id	Genre
1	Digitale Festung	1	thriller
2	Der Da Vinci Code	1	thriller
3	Harry Potter und die Kammer des Schreckens	2	fantasie
4	Harry Potter und der Halbblutprinz	2	fantasie

Upserts Beispiel 2

Other Andererseits fügen wir Datensätze in Bücher ein und aktualisieren die datensatz mit der entsprechenden ID. In den Postgres-Dokumenten müssen Sie die Tabelle EXCLUDED verwenden, um auf die zum Einfügen vorgeschlagenen Werte zu verweisen.

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

Ein letztes Mal auf unseren Tisch schauen:

SELECT * FROM books; 

id	Titel	autor_id	Genre
1	Digitale Festung	1	thriller
2	Der Da Vinci Code	1	thriller
3	Harry Potter und der Gefangene von Askaban	2	fantasie
4	Harry Potter und der Halbblutprinz	2	fantasie

Danke, dass du diesem Blogpost gefolgt bist. Wenn es eine bestimmte Postgres-Funktion gibt, von der Sie hören möchten, wenden Sie sich bitte an [email protected]