Watt's Festival

byAndrew VogelonApril 2, 2019

vorige week werd ik opgeroepen om op een Rails-project te springen dat enkele prestatieoptimalisaties nodig had. Onze gekozen aanpak was om een bulk lookup-and-update proces te nemen uit ActiveRecord land en alles te verplaatsen naar Postgres. De prestatieverbetering was enorm voor grote recordsets. Op de high-end, zagen we verzoek tijden gaan van > 5 minuten naar sub 2 seconden.

ik had SQL en Postgres eerder gebruikt, maar ik was niet helemaal bekend met de volledige set van functies. In eerste instantie, ik gekoppeld met een collega die een diepere kennis van Postgres heeft. Na een paar dagen van het schrijven van queries en refactoring de test suite, alles was groen en ik was klaar om te implementeren voor de enscenering voor het testen.

hier zijn enkele interessante SQL-en PostgreSQL-dingen die ik onderweg geleerd heb.

CTE ‘s

hoewel niet exclusief voor PostgreSQL, zijn CTE’ s of veelvoorkomende Tabeluitdrukkingen een manier om herbruikbare queries in een databasesysteem te schrijven.

laten we zeggen dat we 2 tabellen hebben – books en authors.

Boeken Tafel

id	titel	author_id	genre
1	Digital Fortress	1	thriller
2	De Da Vinci Code	1	thriller
3	Harry Potter en De geheime Kamer	2	fantasie

Tabel Authors

id	naam	leeftijd
1	Dan Brown	54
2	J. K. Rowling	53

kunnen We schrijven een heel eenvoudige CTE om de boeken met de auteurs:

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

Dan, het selecteren van onze CTE, krijgen we het volgende:

SELECT * FROM authors_and_books; 

book_id	titel	author_name
1	Digital Fortress	Dan Brown
2	De Da Vinci Code	Dan Brown
3	Harry Potter en De geheime Kamer	J. K. Rowling

dit voorbeeld is eenvoudig, maar je kunt zien hoe dit echt van pas kan komen wanneer je complexe query ‘ s moet hergebruiken.

Temp Tables (PostgreSQL)

een andere zeer nuttige database-functie is de mogelijkheid om tijdelijke tabellen aan te maken. In mijn geval was dit erg handig voor het stagen van “onbewerkte” gegevens die vanaf de front-end werden gepost. Om een temp tabel te maken, moet u het schema definiëren zoals uw normale create table .. statement.

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

het belangrijkste deel hier is de ON COMMIT aan het einde. Je moet Postgres vertellen hoe de tijdelijke tabel aan het einde van het transactieblok moet worden afgehandeld. DROP vertelt Postgres om de tijdelijke tabel aan het einde van het transactieblok te laten vallen. De Postgres docs beschrijven meer van de op COMMIT opties.

COALESCE

de functie COALESCE geeft de eerste niet-nulwaarde terug die eraan is doorgegeven. Deze functie accepteert een onbeperkt aantal argumenten en het retourneert het eerste non-null argument, geëvalueerd van links naar rechts.

hier zijn een paar voorbeelden van hoe dat eruit zou kunnen zien:

Samenvallen Voorbeeld 1

select coalesce(null, 1); 

fuseren

Samenvallen Voorbeeld 2

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

fuseren

Samenvallen Voorbeeld 3

select coalesce(2, null, 1); 

fuseren

Upserts

Upserting, of het bijwerken van-en-plaatsen, is een superful functie in Postgres. Hiermee kunt u inserts met conflictoplossing verwerken als er al een record in de database bestaat.

herinner je je onze boekentabel?

Boeken Tafel

id	titel	author_id	genre
1	Digital Fortress	1	thriller
2	De Da Vinci Code	1	thriller
3	Harry Potter en De geheime Kamer	2	fantasie

Upserts Voorbeeld 1

Wij invoegen van records in de boeken, maar als het boek bestaat, zullen we ervoor kiezen niets te doen:

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; 

laten we Nu eens kijken aan onze tafel:

SELECT * FROM books; 

id	titel	author_id	genre
1	Digital Fortress	1	thriller
2	De Da Vinci Code	1	thriller
3	Harry Potter en De geheime Kamer	2	fantasie
4	Harry Potter en De halfbloed Prins	2	fantasie

Upserts Voorbeeld 2

Andere andere kant, laten we het invoegen van records in de boeken en de update opnemen met de bijbehorende ID. Per Postgres docs, moet u de EXCLUDED tabel gebruiken om te verwijzen naar de waarden voorgesteld voor het invoegen.

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; 

een laatste keer naar onze tabel kijken:

SELECT * FROM books; 

id	titel	author_id	genre
1	Digital Fortress	1	thriller
2	De Da Vinci Code	1	thriller
3	Harry Potter en De vuurbeker	2	fantasie
4	Harry Potter en De halfbloed Prins	2	fantasie

Bedankt voor het volgen samen met deze blog post. Als er een bepaalde Postgres functie die u wilt horen over, voel je vrij om uit te reiken op [email protected]