zdjęcie profilowe Andrew Vogela

byAndrew Vogelonkwiecień 2, 2019

w zeszłym tygodniu zostałem wezwany, aby przejść do projektu Rails, który wymagał optymalizacji wydajności. Nasze podejście polegało na zbiorczym procesie wyszukiwania i aktualizacji z terenu ActiveRecord i przeniesieniu wszystkiego do Postgres. Poprawa wydajności była ogromna dla dużych zestawów płyt. Na najwyższym poziomie widzieliśmy czasy żądań od > 5 minut do sub 2 sekund.

wcześniej korzystałem z SQL i Postgres, ale nie byłem całkowicie zaznajomiony z jego pełnym zestawem funkcji. Na początku połączyłem się ze współpracownikiem, który ma głębszą wiedzę na temat Postgres. Po kilku dniach pisania zapytań i refaktoryzacji pakietu testowego wszystko było zielone i byłem gotowy do wdrożenia do staging w celu przetestowania.

oto kilka ciekawych rzeczy SQL i PostgreSQL, których nauczyłem się po drodze.

wyrażenia CTE

chociaż nie są wyłączne dla PostgreSQL, wyrażenia CTE lub wspólne wyrażenia tabel, są sposobem na pisanie zapytań wielokrotnego użytku w systemie bazodanowym.

powiedzmy, że mamy 2 tabele – books i authors.

tabela książek
id title author_id genre
1 Cyfrowa Twierdza 1 thriller
2 Kod Da Vinci 1 thriller
3 Harry Potter i Komnata Tajemnic 2 fantasy
tabela autorów
id imię wiek
1 Dan Brown 54
2 J. K. Rowling 53

możemy napisać naprawdę proste CTE, aby uzyskać książki z autorami:

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; ); 

następnie, wybierając z naszego CTE, otrzymujemy następujące:

SELECT * FROM authors_and_books; 
book_id title author_name
1 Digital Fortress Dan Brown
2 Kod Da Vinci Dan Brown
3 Harry Potter i Komnata Tajemnic J. K. Rowling

ten przykład jest prosty, ale możesz zobaczyć, jak to może się przydać, gdy musisz ponownie użyć złożonych zapytań.

tabele Temp (PostgreSQL)

kolejną bardzo przydatną funkcją bazy danych jest możliwość tworzenia tabel tymczasowych. W moim przypadku było to naprawdę przydatne do inscenizacji „nieprzetworzonych” danych, które zostały wysłane z przodu. Aby utworzyć tabelę tymczasową, musisz zdefiniować jej schemat, jak zwykłe polecenie create table ...

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

ważną częścią jest tutaj ON COMMIT na końcu. Musisz powiedzieć Postgresowi, jak poradzić sobie z tymczasową tabelą na końcu bloku transakcji. DROP każe Postgresowi upuścić tymczasową tabelę na końcu bloku transakcji. Dokumenty Postgres opisują więcej opcji on COMMIT.

COALESCE

funkcja COALESCE zwraca pierwszą przekazaną jej wartość inną niż null. Funkcja ta przyjmuje nieograniczoną liczbę argumentów i zwraca pierwszy argument nieujemny, obliczany od lewej do prawej.

oto kilka przykładów jak to może wyglądać:

1

select coalesce(null, 1); 

coalesce

przykład Coalesce 2

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

coalesce

przykład Coalesce 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, czyli aktualizowanie i wstawianie, jest wspaniałą funkcją w Postgres. Pozwala na obsługę wstawek z rozwiązywaniem konfliktów, jeśli rekord już istnieje w bazie danych.

pamiętasz naszą tabelę z książkami?

tabela książek
id title author_id genre
1 Cyfrowa Twierdza 1 thriller
2 Kod Da Vinci 1 thriller
3 Harry Potter i Komnata Tajemnic 2 fantasy

Upserts przykład 1

wstawimy kilka rekordów do książek, ale jeśli książka istnieje, nie będziemy nic robić:

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; 

teraz spójrzmy na nasz stół:

SELECT * FROM books; 
id title author_id genre
1 Cyfrowa Twierdza 1 thriller
2 Kod Da Vinci 1 thriller
3 Harry Potter i Komnata Tajemnic 2 fantasy
4 Harry Potter i Książę Półkrwi 2 fantasy

Upserts Example 2

Other other hand, wstawmy rekordy do książek i zaktualizujmy rekord z odpowiednim identyfikatorem. W dokumentach Postgres musisz użyć tabeli EXCLUDED, aby odwołać się do wartości zaproponowanych do wstawienia.

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; 

patrząc na nasz stół po raz ostatni:

SELECT * FROM books; 
id title author_id genre
1 Cyfrowa Twierdza 1 thriller
2 Kod Da Vinci 1 thriller
3 Harry Potter i Czara Ognia 2 fantasy
4 Harry Potter i Książę Półkrwi 2 fantasy

Dziękujemy za śledzenie wraz z tym blogu. Jeśli chcesz usłyszeć o konkretnej funkcji Postgres, skontaktuj się z nami pod adresem [email protected]

czy ten post był pomocny? Podziel się nim z innymi.

  • logo TwitteraTweet
  • logo FacebookaUdostępnij
  • logo LinkedinPost

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.