profiilikuva Andrew Vogelista

byAndrew VogelonApril 2, 2019

viime viikolla minut kutsuttiin hyppäämään Rails-projektiin, joka tarvitsi joitain suorituskyvyn optimointeja. Valitsemamme lähestymistapa oli ottaa bulk lookup-and-update prosessi pois ActiveRecord land ja siirtää kaikki Postgres. Tulosparannus oli huima isoille ennätyksille. Lopussa näimme pyyntöajat menevät > 5 minuutista alle 2 sekuntiin.

olin käyttänyt SQL: ää ja Postgresia aiemminkin, mutta en ollut täysin perehtynyt sen kaikkiin ominaisuuksiin. Aluksi Parini oli työkaveri, jolla on syvempää tietoa Postgresistä. Muutaman päivän kirjallisesti kyselyt ja refactoring testipaketti, kaikki oli vihreä ja olin valmis ottamaan käyttöön lavastus testausta varten.

Tässä muutamia mielenkiintoisia SQL-ja PostgreSQL-asioita, joita opin matkan varrella.

CTE: n

vaikka se ei ole yksinomaan PostgreSQL: lle, CTE: n eli yleisten taulujen lausekkeet ovat tapa kirjoittaa uusittavia kyselyjä tietokantajärjestelmään.

sanotaan, että meillä on 2 taulukkoa – books ja authors.

Kirjotaulukko
id otsikko tekijä_id genre
1 digitaalinen Linnoitus 1 trilleri
2 Da Vinci-koodi 1 trilleri
3 Harry Potter ja salaisuuksien kammio 2 Fantasia
Tekijätaulukko
henkilökortti nimi Ikä
1 Dan Brown 54
2 JK. Rowling 53

voimme kirjoittaa todella yksinkertaisen CTE: n, jotta saamme kirjat tekijöiden kanssa:

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

sitten, valitsemalla meidän CTE, saamme seuraavat:

SELECT * FROM authors_and_books; 
kirja_id otsikko tekijä_nimi
1 Digital Fortress Dan Brown
2 Da Vinci-koodi Dan Brown
3 Harry Potter ja salaisuuksien kammio J. K. Rowling

tämä esimerkki on yksinkertainen, mutta voit nähdä, miten tämä voi todella tulla kätevä, kun haluat käyttää monimutkaisia kyselyjä.

Temp-taulukot (PostgreSQL)

toinen todella hyödyllinen tietokantaominaisuus on kyky luoda väliaikaisia taulukoita. Minun tapauksessani tämä oli todella hyödyllinen lavastus ”käsittelemätön” tiedot, jotka oli lähetetty etupäässä. Jotta voit luoda temp-taulukon, sinun täytyy määritellä sen skeema kuten normaali create table .. lauseesi.

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

tärkeä osa tässä on ON COMMIT lopussa. Sinun on kerrottava Postgresille, miten transaktiolohkon lopussa olevaa väliaikaista pöytää käsitellään. DROP kertoo Postgresin pudottavan väliaikaisen taulukon tapahtumalohkon loppuun. Postgres docs kuvataan enemmän on COMMIT vaihtoehtoja.

koalitio

COALESCE funktio palauttaa ensimmäisen sille annetun ei-nollan arvon. Tämä funktio hyväksyy rajattoman määrän argumentteja ja palauttaa ensimmäisen Ei-null-argumentin, joka arvioidaan vasemmalta oikealle.

tässä muutama esimerkki siitä, miltä se voisi näyttää:

Koalitioesimerkki 1

select coalesce(null, 1); 

coalesce

Coalesce Esimerkki 2

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

coalesce

Coalesce esimerkki 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting eli updating-and-inserting on Postgresin superful-ominaisuus. Sen avulla voit käsitellä inserttejä konfliktinratkaisulla, jos tietue on jo olemassa tietokannassa.

Muistatko kirjapöytämme?

Kirjotaulukko
id otsikko tekijä_id genre
1 digitaalinen Linnoitus 1 trilleri
2 Da Vinci-koodi 1 trilleri
3 Harry Potter ja salaisuuksien kammio 2 Fantasia

Upserts Esimerkki 1

lisäämme joitakin tietueita kirjoihin, mutta jos kirja on olemassa, emme tee mitään:

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; 

katsotaan pöytäämme.:

SELECT * FROM books; 
id otsikko tekijä_id genre
1 digitaalinen Linnoitus 1 trilleri
2 Da Vinci-koodi 1 trilleri
3 Harry Potter ja salaisuuksien kammio 2 Fantasia
4 Harry Potter ja puoliverinen prinssi 2 Fantasia

Upserts Esimerkki 2

Other other hand, Let ’ s insert records into books and update the Tallenna vastaavalla ID: llä. Postgres-dokumenttien mukaan sinun on käytettävä EXCLUDED – taulukkoa viitataksesi asetettaviksi ehdotettuihin arvoihin.

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; 

katsotaan pöytäämme vielä kerran:

SELECT * FROM books; 
id otsikko tekijä_id genre
1 digitaalinen Linnoitus 1 trilleri
2 Da Vinci-koodi 1 trilleri
3 Harry Potter ja liekehtivä pikari 2 Fantasia
4 Harry Potter ja puoliverinen prinssi 2 Fantasia

Kiitos seuraavat yhdessä tämän blogikirjoitus. Jos on tietty Postgres ominaisuus haluat kuulla, ota rohkeasti yhteyttä [email protected]

oliko tämä viesti hyödyllinen? Jaa se muiden kanssa.

  • Twitter-logotwiitti
  • Facebook-logo Jaa
  •  Linkedin-logo Post

Vastaa

Sähköpostiosoitettasi ei julkaista.