Watt's Festival

byAndrew VogelonApril 2, 2019

a múlt héten felhívtak, hogy ugorjak egy Rails projektre, amely némi teljesítményoptimalizálást igényelt. A választott megközelítésünk az volt, hogy tömeges keresési és frissítési folyamatot hajtottunk végre az ActiveRecord land-ből, és mindent áthelyeztünk a Postgres-be. A teljesítmény javulása hatalmas volt a nagy rekordkészleteknél. A csúcs végén láttuk, hogy a kérési idők > 5 percről 2 másodpercre mennek.

korábban már használtam SQL-t és Postgres-t, de nem voltam teljesen tisztában a funkciók teljes készletével. Először, párosítottam egy munkatárssal, aki mélyebb ismeretekkel rendelkezik a Postgres-ről. Néhány napos lekérdezések írása és a tesztcsomag refaktorálása után minden zöld volt,és készen álltam a tesztelésre.

itt van néhány érdekes SQL és PostgreSQL dolog, amit megtanultam az út során.

a CTE

bár nem kizárólag a PostgreSQL, a CTE vagy a Common Table kifejezéseket használja, így újrafelhasználható lekérdezéseket írhat egy adatbázis-rendszerben.

tegyük fel, hogy 2 táblánk van – books és authors.

Könyvek táblázat

id	cím	szerző_azonosító	műfaj
1	digitális erőd	1	thriller
2	A Da Vinci-kód	1	thriller
3	Harry Potter és a Titkok Kamrája	2	Fantázia

szerzők táblázat

id	név	életkor
1	Dan Brown	54
2	J. K. Rowling	53

írhatunk egy nagyon egyszerű CTE – t, hogy megkapjuk a könyveket a szerzőkkel:

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

ezután a CTE-ből választva a következőket kapjuk:

SELECT * FROM authors_and_books; 

book_id	cím	szerző neve
1	digitális erőd	Dan Brown
2	A Da Vinci-kód	Dan Brown
3	Harry Potter és a Titkok Kamrája	J. K. Rowling

ez a példa egyszerű, de láthatja, hogy ez valóban hasznos lehet, ha összetett lekérdezéseket kell újrafelhasználnia.

Temp Tables (PostgreSQL)

egy másik nagyon hasznos adatbázis-funkció Az ideiglenes táblák létrehozásának képessége. Az én esetemben ez nagyon hasznos volt a “feldolgozatlan” adatok előkészítéséhez, amelyeket a kezelőfelületről tettek közzé. Ideiglenes tábla létrehozásához meg kell határoznia annak sémáját, mint a normál create table .. utasítás.

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

a fontos rész itt a ON COMMIT a végén. El kell mondania a Postgres-nek, hogyan kell kezelni az ideiglenes táblát a tranzakciós blokk végén. DROP azt mondja a Postgres-nek, hogy dobja el az ideiglenes táblát a tranzakciós blokk végén. A Postgres dokumentumok többet írnak le az on COMMIT opciókról.

COALESCE

a COALESCE függvény az első nem null értéket adja vissza. Ez a függvény korlátlan számú argumentumot fogad el, és az első nem null argumentumot adja vissza, balról jobbra kiértékelve.

Íme néhány példa arra, hogy ez hogyan nézhet ki:

Coalesce példa 1

select coalesce(null, 1); 

coalesce

Coalesce példa 2

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

coalesce

Coalesce példa 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, vagy updating-and-inserting, egy szuper funkció Postgres. Ez lehetővé teszi a Beszúrások konfliktusmegoldással történő kezelését, ha egy rekord már létezik az adatbázisban.

emlékszel a könyvasztalunkra?

Könyvek táblázat

id	cím	szerző_azonosító	műfaj
1	digitális erőd	1	thriller
2	A Da Vinci-kód	1	thriller
3	Harry Potter és a Titkok Kamrája	2	Fantázia

Upserts példa 1

néhány rekordot beillesztünk a könyvekbe, de ha a könyv létezik, úgy döntünk, hogy nem teszünk semmit:

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; 

most nézzük meg az asztalunkat:

SELECT * FROM books; 

id	cím	szerző_azonosító	műfaj
1	digitális erőd	1	thriller
2	A Da Vinci-kód	1	thriller
3	Harry Potter és a Titkok Kamrája	2	Fantázia
4	Harry Potter és a Félvér Herceg	2	Fantázia

Upserts Example 2

más viszont, írjunk be rekordokat a könyvekbe és frissítsük a rögzítse a megfelelő azonosítóval. A Postgres dokumentumok szerint a EXCLUDED táblázatot kell használnia a beillesztésre javasolt értékek hivatkozásához.

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; 

Utoljára nézzük az asztalunkat:

SELECT * FROM books; 

id	cím	szerző_azonosító	műfaj
1	digitális erőd	1	thriller
2	A Da Vinci-kód	1	thriller
3	Harry Potter és a Tűz Serlege	2	Fantázia
4	Harry Potter és a Félvér Herceg	2	Fantázia

Köszönjük, hogy követte ezt a blogbejegyzést. Ha van egy adott Postgres funkció, amelyről szeretne hallani, nyugodtan keresse meg a [email protected]