poza de profil a lui Andrew Vogel

byAndrew VogelonApril 2, 2019

săptămâna trecută, am fost chemat să sar pe un proiect Rails care avea nevoie de unele optimizări de performanță. Abordarea noastră aleasă a fost să luăm un proces de căutare și actualizare în vrac din terenul ActiveRecord și să mutăm totul în Postgres. Îmbunătățirea performanței a fost uriașă pentru seturi mari de recorduri. La capătul înalt, am văzut timpii de solicitare trecând de la > 5 minute la sub 2 secunde.

am folosit SQL și Postgres înainte, dar nu eram complet familiarizat cu setul său complet de caracteristici. La început, m-am asociat cu un coleg care are o cunoaștere mai profundă a Postgres. După câteva zile de scris interogări și refactorizarea suita de testare, totul a fost verde și am fost gata pentru a implementa la așteptare pentru testare.

iată câteva lucruri interesante SQL și PostgreSQL pe care le-am învățat pe parcurs.

CTE ‘s

deși nu este exclusiv pentru PostgreSQL, CTE’ s sau expresii comune de tabel, sunt o modalitate de a scrie interogări reutilizabile într-un sistem de baze de date.

să presupunem că avem 2 tabele – booksși authors.

tabel de cărți
id titlu autor_id gen literar
1 Cetatea digitală 1 thriller
2 Codul lui Da Vinci 1 thriller
3 Harry Potter și Camera Secretelor 2 fantezie
tabelul autorilor
id nume vârstă
1 Dan Brown 54
2 J. K. Rowling 53

putem scrie un CTE foarte simplu pentru a obține cărțile cu autorii:

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

apoi, selectând din CTE, obținem următoarele:

SELECT * FROM authors_and_books; 
book_id titlu nume autor
1 Cetatea digitală Dan Brown
2 Codul lui Da Vinci Dan Brown
3 Harry Potter și Camera Secretelor J. K. Rowling

acest exemplu este simplu, dar puteți vedea cum acest lucru poate fi cu adevărat util atunci când trebuie să reutilizați interogări complexe.

tabele Temp (PostgreSQL)

o altă caracteristică de bază de date cu adevărat utilă este capacitatea de a crea tabele temporare. În cazul meu, acest lucru a fost foarte util pentru punerea în scenă a datelor „neprelucrate” care au fost postate de la capătul frontal. Pentru a crea un tabel temp, va trebui să definiți schema acestuia ca instrucțiunea normală 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; 

partea importantă aici este ON COMMIT la sfârșit. Va trebui să-i spuneți lui Postgres cum să gestioneze tabelul temporar de la sfârșitul blocului de tranzacții. DROP îi spune lui Postgres să renunțe la tabelul temporar de la sfârșitul blocului de tranzacții. Documentele Postgres descriu mai multe dintre opțiunile de comitere.

COALESCE

funcția COALESCE returnează prima valoare non-nulă care i-a fost transmisă. Această funcție acceptă un număr nelimitat de argumente și returnează primul argument non-nul, evaluat de la stânga la dreapta.

iată câteva exemple despre cum ar putea arăta:

Coalesce exemplu 1

select coalesce(null, 1); 

coalesce

Coalesce exemplu 2

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

coalesce

Coalesce exemplu 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, sau actualizarea și inserarea, este o caracteristică superful în Postgres. Vă permite să gestionați inserții cu rezolvarea conflictelor dacă există deja o înregistrare în baza de date.

îți amintești masa noastră de cărți?

tabel de cărți
id titlu autor_id gen literar
1 Cetatea digitală 1 thriller
2 Codul lui Da Vinci 1 thriller
3 Harry Potter și Camera Secretelor 2 fantezie

Upserts Exemplul 1

vom insera unele înregistrări în cărți, dar dacă cartea există, vom alege să nu facem nimic:

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; 

acum să ne uităm la masa noastră:

SELECT * FROM books; 
id titlu autor_id gen literar
1 Cetatea digitală 1 thriller
2 Codul lui Da Vinci 1 thriller
3 Harry Potter și Camera Secretelor 2 fantezie
4 Harry Potter și Prințul Semipur 2 fantezie

Upserts Exemplul 2

alte altă parte, să inserați înregistrări în cărți și să actualizeze înregistrați cu ID-ul corespunzător. Conform documentelor Postgres, va trebui să utilizați tabelul EXCLUDED pentru a face referire la valorile propuse pentru inserare.

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; 

Privind la masa noastră pentru ultima oară:

SELECT * FROM books; 
id titlu autor_id gen literar
1 Cetatea digitală 1 thriller
2 Codul lui Da Vinci 1 thriller
3 Harry Potter și Pocalul de foc 2 fantezie
4 Harry Potter și Prințul Semipur 2 fantezie

Vă mulțumim pentru următoarele, împreună cu acest post pe blog. Dacă există o anumită funcție Postgres despre care doriți să auziți, nu ezitați să contactați [email protected]

a fost utilă această postare? Împărtășește-l cu alții.

  • Twitter logoTweet
  •  Facebook logo Share
  •  Linkedin logo Post

Lasă un răspuns

Adresa ta de email nu va fi publicată.