profilový obrázek Andrewa Vogela

byAndrew VogelonApril 2, 2019

minulý týden jsem byl povolán Skočit na projekt Rails, který potřeboval nějaké optimalizace výkonu. Naším zvoleným přístupem bylo vzít hromadný proces vyhledávání a aktualizace ze země ActiveRecord a přesunout vše do Postgres. Zlepšení výkonu bylo obrovské pro velké rekordní sady. Na horním konci jsme viděli, že časy požadavků se pohybují od > 5 minut do sub 2 sekund.

použil jsem SQL a Postgres dříve, ale nebyl jsem úplně obeznámen s jeho úplnou sadou funkcí. Nejprve, spároval jsem se spolupracovníkem, který má hlubší znalosti Postgres. Po několika dnech psaní dotazů a refaktorování testovací sady bylo vše zelené a byl jsem připraven nasadit na staging pro testování.

zde je několik zajímavých věcí SQL a PostgreSQL, které jsem se naučil.

CTE

ačkoli to není výlučné pro PostgreSQL, CTE nebo běžné tabulkové výrazy jsou způsob, jak psát znovu použitelné dotazy v databázovém systému.

řekněme, že máme 2 tabulky – books a authors.

tabulka knih
id název autor_id žánr
1 Digitální pevnost 1 thriller
2 Da Vinciho kód 1 thriller
3 Harry Potter a Tajemná komnata 2 Fantazie
tabulka autorů
id jméno věk
1 Dana Browna 54
2 J. K. Rowlingová 53

můžeme napsat opravdu jednoduchý CTE, abychom získali knihy s autory:

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

poté výběrem z našeho CTE získáme následující:

SELECT * FROM authors_and_books; 
book_id název autor_name
1 Digitální pevnost Dan Brown
2 Da Vinciho kód Dan Brown
3 Harry Potter a Tajemná komnata J. K. Rowlingová

tento příklad je jednoduchý, ale můžete vidět, jak se to může opravdu hodit, když potřebujete znovu použít složité dotazy.

Temp Tables (PostgreSQL)

další opravdu užitečnou funkcí databáze je schopnost vytvářet dočasné tabulky. V mém případě to bylo opravdu užitečné pro inscenaci „nezpracovaných“ dat, která byla zveřejněna z předního konce. Chcete-li vytvořit tabulku temp, musíte definovat její schéma jako normální příkaz 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; 

důležitá část je zde ON COMMIT na konci. Budete muset říct Postgres, jak zacházet s dočasnou tabulkou na konci transakčního bloku. DROP řekne Postgres, aby upustil dočasnou tabulku na konci transakčního bloku. Dokumenty Postgres popisují více možností odevzdání.

COALESCE

funkce COALESCE vrací první nenulovou hodnotu, která jí byla předána. Tato funkce přijímá neomezený počet argumentů a vrací první nenulový argument, vyhodnocený zleva doprava.

zde je několik příkladů, jak by to mohlo vypadat:

Coalesce Příklad 1

select coalesce(null, 1); 

koalesce

příklad Koalesce 2

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

koalesce

příklad Koalesce 3

select coalesce(2, null, 1); 

koalesce

Upserts

Upserting, nebo aktualizace a vkládání, je superful funkce v Postgres. Umožňuje zpracovávat vložky s řešením konfliktů, pokud záznam již existuje v databázi.

Pamatujete si naši tabulku knih?

tabulka knih
id název autor_id žánr
1 Digitální pevnost 1 thriller
2 Da Vinciho kód 1 thriller
3 Harry Potter a Tajemná komnata 2 Fantazie

Upserts Příklad 1

vložíme některé záznamy do knih, ale pokud kniha existuje, rozhodneme se nedělat nic:

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; 

nyní se podívejme na náš stůl:

SELECT * FROM books; 
id název autor_id žánr
1 Digitální pevnost 1 thriller
2 Da Vinciho kód 1 thriller
3 Harry Potter a Tajemná komnata 2 Fantazie
4 Harry Potter a Princ dvojí krve 2 Fantazie

Upserts příklad 2

na druhou stranu, pojďme vložit záznamy do knih a aktualizovat záznam s odpovídajícím ID. V dokumentech Postgres budete muset použít tabulku EXCLUDED pro odkaz na hodnoty navržené pro vložení.

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; 

při pohledu na náš stůl Naposledy:

SELECT * FROM books; 
id název autor_id žánr
1 Digitální pevnost 1 thriller
2 Da Vinciho kód 1 thriller
3 Harry Potter a Ohnivý pohár 2 Fantazie
4 Harry Potter a Princ dvojí krve 2 Fantazie

Děkujeme, že jste sledovali tento blogový příspěvek. Pokud existuje konkrétní funkce Postgres, o které byste chtěli slyšet, neváhejte oslovit na [email protected]

byl tento příspěvek užitečný? Sdílejte to s ostatními.

  • Twitter logoTweet
  •  Facebook logo sdílet
  • Linkedin logo příspěvek

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.