Gematerealiseerde views in PostgreSQL

Als je veel met SQL werkt (bij voorkeur als PostgreSQL-gebruiker) of op gemiddeld niveau databases bevraagt, heb je vast al de nodige complexe queries geschreven om antwoorden op cruciale vragen te vinden. Het komt ook voor dat je de resultaten van die queries regelmatig nodig hebt in je werk. Het meerdere keren uitvoeren van een complexe query kan kostbaar zijn. Wat als je de resultaten van een complexe query zou kunnen cachen om de prestaties te verbeteren? Daar zijn gematerialiseerde views voor.

Misschien denk je: er zijn toch ook gewone views die het resultaat van een query kunnen opslaan? Maar een gewone view verschilt op meerdere punten van een gematerialiseerde view. Dit zegt de officiële documentatie van PostgreSQL over gewone views:

The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

Dit betekent dat gewone views niet fysiek in de database worden opgeslagen. Elke keer dat een view in een query wordt gebruikt, wordt de query die de view heeft gemaakt opnieuw uitgevoerd. Dat maakt gewone views wat minder efficiënt qua toegangstijd.

Gematerealiseerde views bieden daarentegen veel flexibiliteit doordat je een view fysiek in de database kunt bewaren. En je kunt met gematerialiseerde views werken zoals je met gewone views doet (maar met een kortere toegangstijd). In de rest van deze tutorial leer je over gematerialiseerde views in PostgreSQL. In een van zijn webinars liet DataCamps Chief Data Scientist David Robinson zien hoe hij gematerialiseerde views gebruikt om de resultaten van complexe queries efficiënt op te slaan.

Let op: om deze tutorial te kunnen volgen, is het handig als je bekend bent met de inhoud van de cursus Data samenvoegen in SQL.

Initiële database-instelling

Het is handig om een database in PostgreSQL op te zetten en daarin een basistabel aan te maken, zodat je die effectief kunt bevragen. Dit helpt je ook in de volgende secties van deze tutorial. Je kunt deze databasebackup terugzetten; die bevat de tabel die je in deze tutorial gaat gebruiken. Wil je leren hoe je een databasebackup in PostgreSQL terugzet? Volg dan het eerste gedeelte van deze tutorial.

Als het terugzetten is gelukt, zie je een tabel met de naam international_debt in de database (je moet eerst een database aanmaken als je die nog niet hebt). Laten we snel naar de eerste paar rijen van de tabel kijken (een simpele select-query helpt je daarbij) -

De tabel bevat informatie over de schuldcijfers van verschillende landen wereldwijd voor het huidige jaar in verschillende categorieën (zie de kolommen indicator_name, indicator_code). De kolom debt geeft het bedrag aan schuld (in USD) dat een bepaald land in een bepaalde categorie heeft. De data valt binnen het domein economie en wordt vaak gebruikt om de economische situatie van verschillende landen te analyseren. De data is verzameld door de Wereldbank.

Nu je de database succesvol hebt opgezet, kun je door naar de volgende secties van deze tutorial.

Gematerealiseerde views maken

Laten we een gematerialiseerde view maken van de volgende query:

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

De query geeft de landnamen terug met hun totale schulden (in alfabetische volgorde). De output van de query zou er zo uit moeten zien:

Nu de gematerialiseerde view:

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

Je zou een succesmelding moeten krijgen: Query returned successfully: 124 rows affected, 242 ms execution time.

Een gematerialiseerde view verversen

Je kunt PostgreSQL ook instrueren om geen data in de gematerialiseerde view te laden tijdens het aanmaken door de optie WITH NO DATA op te geven:

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

Je krijgt een melding: Query returned successfully with no result in 41 ms. Let op het verschil tussen de twee meldingen. Als je de optie WITH NO DATA toevoegt, wordt er een lege gematerialiseerde view aangemaakt. Je kunt deze gematerialiseerde view niet bevragen. Je kunt de view echter vullen door het volgende uit te voeren:

REFRESH MATERIALIZED VIEW country_total_debt_2;

Een gematerialiseerde view bevragen

Als het goed is, heb je nu twee gematerialiseerde views aangemaakt (country_total_debt, country_total_debt_2). Laten we een simpele select-query uitvoeren op een van de twee:

select * from country_total_debt_2;

De query zou iets als het volgende moeten retourneren:

De output is precies hetzelfde als de query die we in een gematerialiseerde view hebben verpakt. Je kunt ook queries met aggregatiefuncties op gematerialiseerde views uitvoeren:

select max(total_debt) from country_total_debt_2;

Je zou dit moeten krijgen:

Een gematerialiseerde view verwijderen

Gematerealiseerde views kun je verwijderen zoals je in PostgreSQL ook tabellen verwijdert. Je hoeft alleen Materialized View te specificeren na Drop:

Drop Materialized View country_total_debt_2;

Wil je MySQL vergelijken met zijn op een na populairste concurrent? Bekijk dan onze uitgebreide vergelijking PostgreSQL vs. MySQL.

Conclusie

Dat was het voor deze tutorial. Bedankt dat je tot het einde hebt gelezen. In deze tutorial heb je geleerd wat gematerialiseerde views in PostgreSQL zijn en hoe je ermee kunt werken. Door hun effectiviteit worden gematerialiseerde views veel gebruikt in datawarehousing. Als je geïnteresseerd bent in complexere queries, bekijk dan de volgende DataCamp-cursussen:

• Intermediate SQL
• Reporting in SQL