profilbillede af Andrei Vogel

byogelonapril 2, 2019

i sidste uge blev jeg kaldet til at hoppe på et Rails-projekt, der havde brug for nogle præstationsoptimeringer. Vores valgte tilgang var at tage en bulk opslag og opdatering proces ud af ActiveRecord land og flytte alt ind Postgres. Præstationsforbedringen var enorm for store rekordsæt. I den høje ende så vi anmodningstider gå fra > 5 minutter til sub 2 sekunder.

jeg havde brugt Postgres før, men jeg var ikke helt bekendt med dets fulde sæt funktioner. Først, jeg parrede mig med en kollega, der har en dybere viden om Postgres. Efter et par dage med at skrive forespørgsler og refactoring test suite, alt var grønt, og jeg var klar til at implementere til iscenesættelse til test.

her er nogle interessante ting, jeg lærte undervejs.

CTE ‘er

selvom det ikke er eksklusivt for Postgraduate, CTE’ er eller almindelige Tabeludtryk, er det en måde at skrive genanvendelige forespørgsler i et databasesystem.

lad os sige, at vi har 2 tabeller – booksog authors.

bøger bord
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter og Hemmelighedernes Kammer 2 Fantasi
forfattere tabel
id navn alder
1 Dan brun 54
2 J. K. Rasmus 53

vi kan skrive en rigtig simpel CTE for at få bøgerne 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; ); 

derefter vælger vi fra vores CTE følgende:

SELECT * FROM authors_and_books; 
book_id Titel author_name
1 Digital fæstning Dan brun
2 Da Vinci-koden dan brun
3 Harry Potter og Hemmelighedernes Kammer J. K. Rasmus

dette eksempel er enkelt, men du kan se, hvordan dette virkelig kan være nyttigt, når du har brug for at genbruge komplekse forespørgsler.

Temp-tabeller

en anden virkelig nyttig databasefunktion er muligheden for at oprette midlertidige tabeller. I mit tilfælde var dette virkelig nyttigt til iscenesættelse af “uforarbejdede” data, der blev sendt fra frontenden. For at oprette en temp-tabel skal du definere dens skema som din normale create table .. – sætning.

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 vigtige del her er ON COMMIT i slutningen. Du skal fortælle Postgres, hvordan du håndterer den midlertidige tabel i slutningen af transaktionsblokken. DROP beder Postgres om at droppe den midlertidige tabel i slutningen af transaktionsblokken. Postgres-dokumenterne beskriver flere af mulighederne for forpligtelse.

COALESCE

funktionen COALESCE returnerer den første ikke-null-værdi, der sendes til den. Denne funktion accepterer et ubegrænset antal argumenter, og den returnerer det første ikke-null-argument, evalueret fra venstre mod højre.

her er et par eksempler på, hvordan det kan se ud:

Coalesce eksempel 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 opdatering og indsættelse, er en superful funktion i Postgres. Det giver dig mulighed for at håndtere indsatser med konfliktløsning, hvis der allerede findes en post i databasen.

husk vores bøger bord?

bøger bord
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter og Hemmelighedernes Kammer 2 Fantasi

Upserts eksempel 1

vi indsætter nogle poster i bøger, men hvis bogen findes, vælger vi ikke at gøre noget:

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; 

lad os nu se på vores bord:

SELECT * FROM books; 
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter og Hemmelighedernes Kammer 2 Fantasi
4 Harry Potter og Halvblodsprinsen 2 Fantasi

Upserts eksempel 2

anden anden side, lad os indsætte poster i bøger og opdatere Optag med det tilsvarende ID. I henhold til Postgres docs skal du bruge tabellen EXCLUDED til at henvise til de foreslåede værdier til indsættelse.

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å vores bord en sidste gang:

SELECT * FROM books; 
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter og Flammernes Pokal 2 Fantasi
4 Harry Potter og Halvblodsprinsen 2 Fantasi

tak for at følge sammen med dette blogindlæg. Hvis der er en bestemt Postgres-funktion, du gerne vil høre om, er du velkommen til at nå ud på [email protected]

var dette indlæg nyttigt? Del det med andre.

  • kvidre logokvidre
  • Facebook logo del
  •  Linkedin logoindlæg

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.