Profilbild av Andrew Vogel

byAndrew VogelonApril 2, 2019

förra veckan kallades jag för att hoppa på ett Rails-projekt som behövde några prestandaoptimeringar. Vårt valda tillvägagångssätt var att ta en bulkuppslag-och-uppdateringsprocess ur ActiveRecord land och flytta allt till Postgres. Prestandaförbättringen var enorm för stora rekorduppsättningar. I high end såg vi förfrågningstiderna gå från > 5 minuter till sub 2 sekunder.

jag hade använt SQL och Postgres tidigare, men jag var inte helt bekant med sin fulla uppsättning funktioner. I början, jag parat med en medarbetare som har en djupare kunskap om Postgres. Efter några dagar med att skriva frågor och refactoring testsviten var allt grönt och jag var redo att distribuera till staging för testning.

här är några intressanta SQL och PostgreSQL saker jag lärde mig på vägen.

CTE: s

även om det inte är exklusivt för PostgreSQL, CTE: s eller vanliga Tabelluttryck, är ett sätt att skriva återanvändbara frågor i ett databassystem.

låt oss säga att vi har 2 tabeller – books och authors.

böcker tabell
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter och Hemligheternas kammare 2 fantasi
författare tabell
id namn ålder
1 Dan Brown 54
2 J. K. Rowling 53

vi kan skriva en riktigt enkel CTE för att få böckerna med författarna:

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

sedan väljer vi från vår CTE, vi får följande:

SELECT * FROM authors_and_books; 
book_id Titel författarnamn
1 Digital fästning Dan Brown
2 Da Vinci-koden Dan Brown
3 Harry Potter och Hemligheternas kammare J. K. Rowling

det här exemplet är enkelt men du kan se hur detta verkligen kan komma till nytta när du behöver återanvända komplexa frågor.

Temptabeller (PostgreSQL)

en annan riktigt användbar databasfunktion är möjligheten att skapa tillfälliga tabeller. I mitt fall var detta verkligen användbart för att iscensätta ”obearbetade” data som publicerades från fronten. För att skapa en temp-tabell måste du definiera dess schema som ditt vanliga create table .. – uttalande.

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 viktiga delen här är ON COMMIT i slutet. Du måste berätta för Postgres hur du hanterar den tillfälliga tabellen i slutet av transaktionsblocket. DROP berättar Postgres att släppa den tillfälliga tabellen i slutet av transaktionsblocket. Postgres docs beskriver mer av alternativen för on COMMIT.

COALESCE

funktionen COALESCE returnerar det första icke-null-värdet som skickas till det. Den här funktionen accepterar ett obegränsat antal argument och returnerar det första icke-null-argumentet, utvärderat från vänster till höger.

här är några exempel på hur det kan se ut:

Coalesce exempel 1

select coalesce(null, 1); 

coalesce

Coalesce exempel 2

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

coalesce

Coalesce exempel 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, eller uppdatera-och-infoga, är en superfull funktion i Postgres. Det låter dig hantera inlägg med konfliktlösning om en post redan finns i databasen.

kom ihåg våra böcker bord?

böcker tabell
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter och Hemligheternas kammare 2 fantasi

Upserts exempel 1

vi sätter in några poster i böcker men om boken finns väljer vi att inte göra någonting:

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; 

låt oss nu titta på vårt bord:

SELECT * FROM books; 
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter och Hemligheternas kammare 2 fantasi
4 Harry Potter och Halvblodsprinsen 2 fantasi

Upserts exempel 2

andra andra sidan, låt oss infoga poster i böcker och uppdatera spela in med motsvarande ID. Per Postgres docs måste du använda tabellen EXCLUDED för att referera till de värden som föreslås för infogning.

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; 

titta på vårt bord en sista gång:

SELECT * FROM books; 
id Titel author_id genre
1 Digital Fortress 1 thriller
2 Da Vinci-koden 1 thriller
3 Harry Potter och den flammande bägaren 2 fantasi
4 Harry Potter och Halvblodsprinsen 2 fantasi

Tack för att du följer med det här blogginlägget. Om det finns en viss Postgres-funktion som du vill höra Om, Kontakta gärna på [email protected]

var det här inlägget till hjälp? Dela det med andra.

  • Twitter logotypTweet
  • Facebook logotypdela
  • Linkedin logotyp inlägg

Lämna ett svar

Din e-postadress kommer inte publiceras.