Watt's Festival

byAndrew VogelonApril 2, 2019

I forrige uke ble Jeg kalt til å hoppe på Et Rails-prosjekt som trengte noen ytelsesoptimaliseringer. Vår valgte tilnærming var å ta en masseoppslag og oppdateringsprosess ut Av ActiveRecord land og flytte alt inn I Postgres. Ytelsesforbedringen var stor for store rekordsett. På den høye enden så vi forespørselstider gå fra > 5 minutter til sub 2 sekunder.

jeg hadde brukt SQL og Postgres før, men jeg var ikke helt kjent med sitt fulle sett med funksjoner. Først, jeg sammen med en kollega som har en dypere kunnskap Om Postgres. Etter noen dager med å skrive spørsmål og refactoring test suite, alt var grønt og jeg var klar til å distribuere til staging for testing.

her er noen interessante SQL og PostgreSQL ting jeg lærte underveis.

CTE

Selv om Det ikke er eksklusivt For PostgreSQL, ER CTE eller Vanlige Tabelluttrykk en måte å skrive gjenbrukbare spørringer i et databasesystem.

la oss si at vi har 2 tabeller – books og authors.

Bøker Tabell

id	tittel	forfatter_id	sjanger
1	Digital Fortress	1	thriller
2	Da Vinci-Koden	1	thriller
3	Harry Potter Og Mysteriekammeret	2	fantasy

Forfattere Tabell

id	navn	alder
1	Dan Brown	54
2	J. K. Rowling	53

Vi kan skrive en veldig enkel CTE for å få bøkene med forfatterne:

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

Deretter velger vi fra VÅR CTE, vi får følgende:

SELECT * FROM authors_and_books; 

book_id	tittel	forfatternavn
1	Digital Festning	Dan Brown
2	Da Vinci-Koden	Dan Brown
3	Harry Potter Og Mysteriekammeret	J. K. Rowling

dette eksemplet er enkelt, men du kan se hvordan dette virkelig kan komme til nytte når du trenger å gjenbruke komplekse spørringer.

Temp Tables (PostgreSQL)

En annen veldig nyttig databasefunksjon er muligheten til å lage midlertidige tabeller. I mitt tilfelle var dette veldig nyttig for å sette opp» ubehandlede » data som ble lagt ut fra fronten. For å opprette en temp-tabell må du definere skjemaet som din normale create table .. – setning.

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

den viktige delen her er ON COMMIT på slutten. Du må fortelle Postgres hvordan du håndterer det midlertidige bordet på slutten av transaksjonsblokken. DROP forteller Postgres å slippe den midlertidige tabellen på slutten av transaksjonsblokken. De Postgres docs beskrive mer AV PÅ FORPLIKTE alternativer.

COALESCE

funksjonen COALESCE returnerer den første ikke-null-verdien som sendes til den. Denne funksjonen godtar et ubegrenset antall argumenter, og returnerer det første argumentet som ikke er null, evaluert fra venstre mot høyre.

her er noen eksempler på hvordan det kan se ut:

Sameksempel 1

select coalesce(null, 1); 

coalesce

Coalesce Eksempel 2

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

coalesce

Coalesce Eksempel 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, eller oppdatering og innsetting, er en superfull funksjon I Postgres. Den lar deg håndtere innlegg med konfliktløsning hvis en post allerede finnes i databasen.

Husk våre bøker bord?

Bøker Tabell

id	tittel	forfatter_id	sjanger
1	Digital Fortress	1	thriller
2	Da Vinci-Koden	1	thriller
3	Harry Potter Og Mysteriekammeret	2	fantasy

Upserts Eksempel 1

vi setter inn noen poster i bøker, men hvis boken eksisterer, velger vi å ikke gjøre noe:

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; 

la Oss nå se på bordet vårt:

SELECT * FROM books; 

id	tittel	forfatter_id	sjanger
1	Digital Fortress	1	thriller
2	Da Vinci-Koden	1	thriller
3	Harry Potter Og Mysteriekammeret	2	fantasy
4	Harry Potter Og Halvblodsprinsen	2	fantasy

Upserts Eksempel 2

På Annen måte, la oss sette inn poster i bøker og oppdatere ta opp med tilhørende ID. Per Postgres docs må du bruke tabellen EXCLUDED for å referere til verdiene som er foreslått for innsetting.

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; 

Ser på bordet vårt en siste gang:

SELECT * FROM books; 

id	tittel	forfatter_id	sjanger
1	Digital Fortress	1	thriller
2	Da Vinci-Koden	1	thriller
3	Harry Potter Og Ildbegeret	2	fantasy
4	Harry Potter Og Halvblodsprinsen	2	fantasy

Takk for at du følger med dette blogginnlegget. Hvis det er en bestemt Postgres-funksjon du vil høre om, er du velkommen til å nå ut på [email protected]