Serwer baz danych - PostgreSQL

PostgreSQL to relacyjny system zarządzania bazą danych (RDBMS – Relational Database Management System), należący do najbardziej zaawansowanych i niezawodnych rozwiązań typu open source. Umożliwia on tworzenie, przechowywanie i przetwarzanie danych w sposób zgodny z zasadami modelu relacyjnego, zapewniając jednocześnie obsługę transakcji, integralność danych, bezpieczeństwo oraz wysoką wydajność.

Do pracy z PostgreSQL często wykorzystuje się narzędzie pgAdmin – graficzny interfejs użytkownika (GUI), który pozwala w wygodny sposób zarządzać serwerem i bazami danych, wykonywać zapytania SQL, projektować struktury tabel oraz analizować dane. Dzięki pgAdmin użytkownik może łączyć się z serwerem PostgreSQL, przeglądać obiekty bazy (schematy, tabele, widoki, funkcje), a także monitorować jej działanie.

PostgreSQL działa w architekturze klient–serwer. Oznacza to, że serwer baz danych (program postgres) działa w tle i odpowiada za przechowywanie oraz zarządzanie danymi, natomiast klienci (np. pgAdmin, aplikacje webowe, skrypty w Pythonie) łączą się z nim za pomocą sieciowego protokołu i wysyłają polecenia SQL. Serwer przetwarza te polecenia, wykonuje operacje na danych i zwraca wyniki do klienta. Takie rozwiązanie pozwala wielu użytkownikom lub aplikacjom jednocześnie korzystać z tej samej bazy danych w sposób bezpieczny i kontrolowany.

W trakcie laboratorium będziemy korzystać z PostgreSQL i pgAdmin, aby poznać zasady działania relacyjnych systemów baz danych – od projektowania schematów danych, poprzez tworzenie tabel i relacji, aż po wykonywanie zapytań SQL i analizę wyników.

📊 Analiza Bazy Danych i Ćwiczenia SQL (PostgreSQL)

Poniższy materiał omawia podstawowe operacje na bazie danych, wykorzystując przykładową tabelę teachers. Zawiera również propozycje bardziej zaawansowanych ćwiczeń, w tym tworzenie indeksów i wykorzystanie zagnieżdżonych zapytań (Subqueries), oraz odniesienia do funkcji systemowych PostgreSQL.

CREATE TABLE teachers,Tworzy tabelę przechowującą dane o nauczycielach.

INSERT INTO teachers,Dodaje rekordy (wiersze) do tabeli.

SELECT * FROM teachers,Wyświetla wszystkie dane ze wszystkich kolumn w tabeli.

CREATE TABLE teachers (
    id bigserial PRIMARY KEY,
    first_name varchar(25),
    last_name varchar(50),
    school varchar(50),
    hire_date date,
    salary numeric
);

Przykładowe dane

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200), 
('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

Filtrowanie i sortowanie danych

Filtrowanie kolumn (SELECT)

Wybranie tylko konkretnych kolumn jest pierwszą zasadą optymalizacji zapytań – wybieraj tylko to, co jest potrzebne.

SELECT last_name, first_name, salary FROM teachers;

Sortowanie (ORDER BY)

Pozwala uporządkować wyniki. DESC (Descending) oznacza malejąco, ASC (Ascending) oznacza rosnąco (domyślne). Sortowanie można wykonać po nazwie kolumny, lub po jej numerze porządkowym (licząc od 1).

Sortowanie malejąco po pensji.

SELECT first_name, last_name, salary FROM teachers ORDER BY salary DESC;

Sortowanie malejąco po trzeciej kolumnie (salary).

SELECT first_name, last_name, salary FROM teachers ORDER BY 3 DESC;

Sortowanie po wielu kolumnach: najpierw rosnąco wg szkoły, a w ramach szkoły malejąco wg daty zatrudnienia.

SELECT last_name, school, hire_date FROM teachers ORDER BY school ASC, hire_date DESC;

Eliminacja duplikatów (DISTINCT)

Używane do wyświetlenia unikalnych wartości.

-- Wyświetl unikalne nazwy szkół
SELECT DISTINCT school FROM teachers ORDER BY school;

-- Wyświetl unikalne kombinacje szkoły i pensji
SELECT DISTINCT school, salary FROM teachers ORDER BY school, salary;

Filtrowanie wierszy (WHERE)

Klucz do precyzyjnego pobierania danych.

Równość (=),

WHERE school = 'Myers Middle School';

wybiera nauczycieli z konkretnej szkoły.

Różność (<> lub !=),

WHERE school <> 'F.D. Roosevelt HS';

wybiera nauczycieli ze wszystkich szkół oprócz podanej.

Porównanie (<, >, <=, >=)

WHERE hire_date < '2000-01-01';

wybiera zatrudnionych przed 2000 rokiem.

Zakres (BETWEEN)

WHERE salary BETWEEN 40000 AND 65000

wybiera pensje włącznie z krańcami zakresu (równoważne: salary >= 40000 AND salary <= 65000).

Wzorce (LIKE / ILIKE)

WHERE first_name LIKE 'sam%'

lub

WHERE first_name ILIKE 'sam%'

LIKE jest czułe na wielkość liter;

ILIKE jest nieczułe na wielkość liter (preferowane w PostgreSQL).

% oznacza dowolną liczbę znaków.

Operatory Logiczne (AND, OR)

-- AND: Oba warunki muszą być spełnione
SELECT * FROM teachers
WHERE school = 'Myers Middle School'
AND salary < 40000;

-- OR: Wystarczy, że jeden warunek jest spełniony
SELECT * FROM teachers
WHERE last_name = 'Cole'
OR last_name = 'Bush';

-- Łączenie AND i OR (nawiasy są kluczowe!)
SELECT * FROM teachers
WHERE school = 'F.D. Roosevelt HS'
AND (salary < 38000 OR salary > 40000);

Wyszukiwanie z Wzorem (LIKE z %)

SELECT first_name, last_name, school, hire_date, salary
FROM teachers
WHERE school LIKE '%Roos%' -- Znajduje szkoły, w których nazwie występuje 'Roos'
ORDER BY hire_date DESC;

🚀 Zaawansowane (Subqueries, JOINs, Indexing)

Proponowane nowe tabele

Aby umożliwić ćwiczenie relacji i zapytań zagnieżdżonych, dodajmy tabelę students (studenci) i tabelę classes (przedmioty/klasy).

-- Tabela Przedmiotów (Classes)
CREATE TABLE classes (
    class_id serial PRIMARY KEY,
    class_name varchar(100) NOT NULL,
    teacher_id bigint REFERENCES teachers(id), -- Klucz obcy do tabeli teachers
    grade_level varchar(10)
);

-- Przykładowe dane do classes
INSERT INTO classes (class_name, teacher_id, grade_level) VALUES
('Mathematics 101', 2, '10th'),
('English Literature', 1, '9th'),
('World History', 3, '11th');

-- Tabela Studentów (Students)
CREATE TABLE students (
    student_id bigserial PRIMARY KEY,
    first_name varchar(25),
    last_name varchar(50),
    enrollment_date date,
    class_id bigint REFERENCES classes(class_id) -- Klucz obcy do tabeli classes
);

-- Przykładowe dane do students
INSERT INTO students (first_name, last_name, enrollment_date, class_id) VALUES
('Anna', 'Kowalska', '2023-09-01', 1),
('Piotr', 'Nowak', '2023-09-01', 1),
('Ewa', 'Wiśniewska', '2023-09-01', 2);

Zapytania w zapytaniach (Subqueries)

Zapytanie wewnętrzne (subquery) jest wykonywane jako pierwsze i zwraca wynik, który jest używany przez zapytanie zewnętrzne.

Znalezienie nauczycieli z najwyższą pensją:

SELECT first_name, last_name FROM teachers WHERE salary = (SELECT MAX(salary) FROM teachers);

Znalezienie nauczycieli, którzy prowadzą klasy dla ‘10th’ grade:,

SELECT first_name, last_name FROM teachers WHERE id IN (SELECT teacher_id FROM classes WHERE grade_level = '10th');

Wyświetlenie studentów, których nauczyciel ma pensję powyżej średniej:

SELECT s.first_name, s.last_name FROM students s JOIN classes c ON s.class_id = c.class_id JOIN teachers t ON c.teacher_id = t.id WHERE t.salary > (SELECT AVG(salary) FROM teachers);

💡 Indeksowanie w postgreSQL

Indeksy służą do szybszego wyszukiwania danych, działając podobnie do spisu treści w książce. Są kluczowe dla optymalizacji wydajności, zwłaszcza w dużych tabelach.

Tworzenie indeksu

Zaleca się tworzenie indeksów na kolumnach często używanych w klauzulach WHERE, JOIN i ORDER BY.

-- Tworzenie indeksu na kolumnie school w tabeli teachers
CREATE INDEX idx_teachers_school ON teachers (school);

Weryfikacja działania indeksu (EXPLAIN ANALYZE)

Aby sprawdzić, czy PostgreSQL używa indeksu i jak wpływa to na czas wykonania zapytania, użyj polecenia EXPLAIN ANALYZE.

Weryfikacja przyspieszenia po indeksowaniu:

EXPLAIN ANALYZE SELECT * FROM teachers WHERE school = 'Myers Middle School';

Analiza zapytania bez użycia indeksu:,

“Po wykonaniu EXPLAIN ANALYZE, poszukaj w wynikach fraz takich jak”“Index Scan”” (indeks został użyty) lub ““Seq Scan”” (skan sekwencyjny/całej tabeli - indeks nie został użyty).”

Wskazówka: W małej tabeli (jak teachers) zysk z indeksu może być niezauważalny, a nawet może nie zostać użyty przez optymalizator. Indeksy są najbardziej efektywne w tabelach liczących tysiące/miliony wierszy.

TEST A

DROP INDEX IF EXISTS idx_teachers_school;
DROP INDEX IF EXISTS idx_teachers_last_name;

usun indeksy na tabeli teachers - uwaga nazwy mogą się różnić.

Uruchom test wyszukiwania dla jednej z kolumn używając EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM teachers
WHERE school = 'Lincoln High School';

W wynikach EXPLAIN ANALYZE zobaczysz Seq Scan (skan sekwencyjny). Zanotuj czas Execution Time.

Test B: Z Indeksem (Test Optymalizacji)

Załaduj 500 tyś wierszy z generatora.

Utwórz indeks na kolumnie, którą testowałeś (school):

CREATE INDEX idx_teachers_school ON teachers (school);

Powtórz dokładnie to samo zapytanie z EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM teachers
WHERE school = 'Lincoln High School';

W wynikach EXPLAIN ANALYZE powinieneś zobaczyć Index Scan lub Bitmap Heap Scan. Zanotuj nowy czas Execution Time. Będzie on znacząco krótszy niż w Teście A, co udowodni efektywność indeksu.

⚙️ Systemowe Rzeczy w PostgreSQL

PostgreSQL oferuje wiele widoków i funkcji systemowych do monitorowania i zarządzania.

Przeglądanie Metadanych (Katalog Systemowy)

Lista wszystkich tabel w bieżącym schemacie:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
  • Szczegóły kolumn w tabeli teachers:
SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'teachers' AND table_schema = 'public';

Monitorowanie Wydajności

Aktualnie uruchomione zapytania (sesje) i ich stan:

SELECT pid, usename, datname, query, state FROM pg_stat_activity WHERE datname = current_database();

Statystyki użycia indeksów (czy indeksy są używane):

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'teachers';
  • idx_scan: Liczba skanowań indeksu (im wyższa, tym lepiej, bo znaczy, że indeks jest używany).

Zadanie 1: Podstawowe Filtrowanie i Łączenie

Cel: Użycie klauzuli WHERE, ORDER BY i operatora ILIKE.

Znajdź imiona, nazwiska i pensje wszystkich nauczycieli, których nazwisko zaczyna się na literę ‘S’ (niezależnie od wielkości liter).

Posortuj wyniki malejąco według daty zatrudnienia.

Zadanie 2: Zapytanie Zagnieżdżone (Subquery)

Cel: Użycie podzapytania do wyznaczenia wartości porównawczej.

Wyświetl imiona i nazwiska tych nauczycieli, których pensja jest wyższa niż średnia pensja wszystkich nauczycieli w bazie.

Zadanie 3: Łączenie Tabela (JOIN) i Agregacja

Cel: Łączenie wielu tabel (JOIN) i użycie funkcji agregującej (COUNT).

Dla każdego przedmiotu (class_name) wyświetl:

  • Nazwę przedmiotu.
  • Nazwisko i imię nauczyciela prowadzącego.
  • Liczbę studentów zapisanych do tego przedmiotu (COUNT).

Posortuj wyniki malejąco według liczby studentów.

Zadanie 4: Porównanie Dat i Wieloaspektowe Filtrowanie

Cel: Użycie funkcji na datach (EXTRACT), operatora AND / OR i zakresu.

Znajdź imiona i nazwiska wszystkich studentów, którzy:

  • Zostali zapisani w 2023 roku (EXTRACT(YEAR FROM enrollment_date)) LUB uczęszczają na zajęcia prowadzone przez nauczyciela z pensją w zakresie 30000 do 40000.

Zadanie 5: Weryfikacja Indeksu (Ćwiczenie w pgAdmin)

Cel: Zrozumienie, jak PostgreSQL wykonuje zapytania i testowanie efektywności indeksów.

  1. Stwórz indeks na kolumnie last_name w tabeli teachers.
  2. Uruchom poniższe zapytanie, używając funkcji systemowej EXPLAIN ANALYZE w pgAdmin.

W raporcie zwróć uwagę na:

Planning Time i Execution Time.

Czy pojawiła się fraza Index Scan lub Bitmap Heap Scan z odwołaniem do idx_teachers_last_name? Jeśli tak, indeks został użyty.

Gdybyś usunął indeks (DROP INDEX idx_teachers_last_name;) i powtórzył zapytanie, w raporcie prawdopodobnie pojawiłoby się Seq Scan (skan sekwencyjny). Porównaj czasy wykonania.