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 Beispiel 2
select coalesce(null, null, 1, null);
Coalesce Beispiel 3
select coalesce(2, null, 1);
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]
War dieser Beitrag hilfreich? Teilen Sie es mit anderen.
-
Tweet
-
Teilen
-
Posten