Immagine del profilo di Andrew Vogel

byAndrew Vogelon2 Aprile 2019

La scorsa settimana, sono stato chiamato a saltare su un progetto Rails che aveva bisogno di alcune ottimizzazioni delle prestazioni. Il nostro approccio scelto è stato quello di prendere un processo di ricerca e aggiornamento di massa da ActiveRecord land e spostare tutto in Postgres. Il miglioramento delle prestazioni è stato enorme per i grandi set di record. Nella fascia alta, abbiamo visto i tempi di richiesta passare da > 5 minuti a sub 2 secondi.

Avevo usato SQL e Postgres prima, ma non avevo familiarità con il suo set completo di funzionalità. All’inizio, ho fatto coppia con un collega che ha una conoscenza più profonda di Postgres. Dopo alcuni giorni di scrittura di query e refactoring della suite di test, tutto era verde ed ero pronto per la distribuzione in staging per il test.

Ecco alcune interessanti cose SQL e PostgreSQL che ho imparato lungo la strada.

Le CTE

Sebbene non esclusive di PostgreSQL, le CTE o le espressioni di tabella comuni, sono un modo per scrivere query riutilizzabili in un sistema di database.

Diciamo che abbiamo 2 tabelle – books e authors.

Libri Tabella
id titolo author_id genere
1 Digitale Fortezza 1 thriller
2 Il Codice Da Vinci 1 thriller
3 Harry Potter e La Camera dei Segreti 2 fantasia
Authors
id nome età
1 Dan Brown 54
2 J. K. Rowling 53

Siamo in grado di scrivere un semplice CTE per ottenere i libri con gli autori:

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

Quindi, scegliendo dal nostro CTE, si ottiene la seguente:

SELECT * FROM authors_and_books; 
book_id titolo author_name
1 Digital Fortress Dan Brown
2 Il Codice Da Vinci Dan Brown
3 Harry Potter e La Camera dei Segreti J. K. La Rowling

Questo esempio è semplice ma puoi vedere come questo può davvero tornare utile quando hai bisogno di riutilizzare query complesse.

Tabelle temporanee (PostgreSQL)

Un’altra funzionalità di database davvero utile è la possibilità di creare tabelle temporanee. Nel mio caso, questo è stato davvero utile per mettere in scena i dati “non elaborati” che sono stati pubblicati dal front-end. Per creare una tabella temporanea, è necessario definire il suo schema come la normale istruzione 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; 

La parte importante qui è ON COMMIT alla fine. Dovrai dire a Postgres come gestire la tabella temporanea alla fine del blocco della transazione. DROP dice a Postgres di eliminare la tabella temporanea alla fine del blocco della transazione. I documenti Postgres descrivono più opzioni di COMMIT.

COALESCE

La funzione COALESCE restituisce il primo valore non nullo passato. Questa funzione accetta un numero illimitato di argomenti e restituisce il primo argomento non nullo, valutato da sinistra a destra.

Ecco alcuni esempi di come potrebbe apparire:

Coalesce Esempio 1

select coalesce(null, 1); 

coalesce

Coalesce Esempio 2

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

coalesce

Coalesce Esempio 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, o l’aggiornamento e l’inserimento, è un superful funzione in Postgres. Consente di gestire gli inserti con risoluzione dei conflitti se esiste già un record nel database.

Ricordate il nostro tavolo libri?

Libri Tabella
id titolo author_id genere
1 Digitale Fortezza 1 thriller
2 Il Codice Da Vinci 1 thriller
3 Harry Potter e La Camera dei Segreti 2 fantasia

Upserts Esempio 1

inseriremo alcuni record in libri, ma se il libro esiste, potremo scegliere di non fare nulla:

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; 

Ora diamo un’occhiata al nostro tavolo:

SELECT * FROM books; 
id titolo author_id genere
1 Digitale Fortezza 1 thriller
2 Il Codice Da Vinci 1 thriller
3 Harry Potter e La Camera dei Segreti 2 fantasia
4 Harry Potter e Il Principe Mezzo Sangue 2 fantasia

Upserts Esempio 2

Altra mano, inserire record in libri e aggiornare il registra con l’ID corrispondente. Per i documenti Postgres, è necessario utilizzare la tabella EXCLUDED per fare riferimento ai valori proposti per l’inserimento.

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; 

Guardando il nostro tavolo un’ultima volta:

SELECT * FROM books; 
id titolo author_id genere
1 Digitale Fortezza 1 thriller
2 Il Codice Da Vinci 1 thriller
3 Harry Potter e Il Calice di Fuoco 2 fantasia
4 Harry Potter e Il Principe Mezzo Sangue 2 fantasia

Grazie per aver seguito insieme con questo post del blog. Se c’è una particolare funzione Postgres di cui ti piacerebbe sentire parlare, sentiti libero di contattare [email protected]

Questo post è stato utile? Condividilo con gli altri.

  • Facebook Condividi
  • Linkedin logo Pubblica

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.