Мерење на перформанси

Извршена е анализа на перформансите на базата film_rental во PostgreSQL. Се споредуваат неоптимизирани табели и оптимизирани табели:

• rental_copy - непартиционирана табела, користена како почетна состојба за споредба;
• rental - партиционирана табела според rental_date, со дополнителни индекси;
• payment_copy - непартиционирана табела за плаќања;
• payment - партиционирана табела според payment_date, со индекс за датум и вработен.

За оваа анализа беше користено pgbench. Секој тест се извршува со повеќе паралелни клиенти, при што се мерат просечна латентност, standard deviation и TPS односно број на трансакции во секунда. Standard deviation покажува колку варира времето на извршување околу просечната латентност, па помала вредност значи постабилни и попредвидливи перформанси.

Пример за извршување на benchmark тест:

pgbench -n -U admin -d film_rental -c 10 -j 2 -T 60 -f /tmp/movie_recommendation_rental_copy.sql -l --log-prefix=/tmp/movie_recommendation_rental_copy_c10

Каде:

• -n означува дека pgbench не ја иницијализира стандардната pgbench шема;
• -U admin е корисникот со кој се поврзува кон базата;
• -d film_rental е базата врз која се извршува тестот;
• -c 10 означува 10 паралелни клиенти;
• -j 2 означува 2 работни нишки;
• -T 60 означува времетраење од 60 секунди;
• -f ја задава SQL датотеката што се извршува.
• --log-prefix ја задава патеката и префиксот на log датотеките во кои pgbench ги запишува резултатите.

Истата команда се повторуваше со различен број клиенти, на пример 10, 20, 30 и 50, и со SQL датотеки за неоптимизираната и оптимизираната верзија.

За пресметување на standard deviation од pgbench log датотеките се користеше следната awk команда:

awk '
$3 ~ /^[0-9]+$/ {
  n++;
  x=$3/1000;
  sum+=x;
  sumsq+=x*x
}
END {
  mean=sum/n;
  stddev=sqrt((sumsq - (sum*sum/n))/(n-1));
  printf "transactions=%d\nmean=%.3f ms\nstddev=%.3f ms\n", n, mean, stddev
}
' /tmp/movie_recommendation_rental_copy_c10*

Во log датотеките што ги генерира pgbench, секој ред претставува една извршена трансакција. Третата колона ($3) го содржи времето на извршување на таа трансакција, изразено во микросекунди. Бидејќи во табелите резултатите се прикажуваат во милисекунди, вредноста од третата колона се дели со 1000. Потоа од сите добиени вредности се пресметуваат просечната латентност и standard deviation.

Детални резултати

Q1 - Movie Recommendation

Прашалникот прави препорака на филмови за конкретен корисник. Се анализираат претходните изнајмувања, омилените категории, рејтинзите и популарноста на филмовите во 2024 година.

WITH customer_history AS (
    SELECT DISTINCT
        f.film_id,
        f.rating,
        fc.category_id
    FROM rental_copy r
    JOIN inventory i ON r.inventory_id = i.inventory_id
    JOIN film f ON i.film_id = f.film_id
    JOIN film_category fc ON f.film_id = fc.film_id
    WHERE r.customer_id = 101
),
preferred_categories AS (
    SELECT
        category_id,
        COUNT(*) AS category_score
    FROM customer_history
    GROUP BY category_id
),
candidate_films AS (
    SELECT
        f.film_id,
        f.title,
        f.rating,
        fc.category_id,
        pc.category_score
    FROM film f
    JOIN film_category fc ON f.film_id = fc.film_id
    JOIN preferred_categories pc ON fc.category_id = pc.category_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer_history ch
        WHERE ch.film_id = f.film_id
    )
),
film_popularity AS (
    SELECT
        i.film_id,
        COUNT(r.rental_id) AS rental_count
    FROM rental_copy r
    JOIN inventory i ON r.inventory_id = i.inventory_id
    WHERE r.rental_date >= DATE '2024-01-01'
      AND r.rental_date < DATE '2025-01-01'
    GROUP BY i.film_id
)
SELECT
    cf.film_id,
    cf.title,
    cf.rating,
    c.name AS category,
    cf.category_score,
    COALESCE(fp.rental_count, 0) AS yearly_rentals,
    (cf.category_score * 10 + COALESCE(fp.rental_count, 0)) AS recommendation_score
FROM candidate_films cf
JOIN category c ON cf.category_id = c.category_id
LEFT JOIN film_popularity fp ON cf.film_id = fp.film_id
WHERE cf.rating IN (
    SELECT rating
    FROM customer_history
)
ORDER BY recommendation_score DESC, cf.title
LIMIT 10;

За оптимизираната верзија се користи истата логика, но наместо rental_copy се користи партиционираната табела rental.

Clients	Threads	rental_copy latency	rental_copy stddev	rental_copy TPS	rental latency	rental stddev	rental TPS	Промена
10	2	2345.556 ms	945.171 ms	4.263	1653.931 ms	549.645 ms	6.046	~29.5% побрзо
20	4	4820.854 ms	1526.728 ms	4.149	3020.244 ms	889.134 ms	6.622	~37.4% побрзо
50	4	9811.989 ms	2272.239 ms	5.096	7444.747 ms	1702.896 ms	6.716	~24.1% побрзо

Q1 покажува подобрување кај оптимизираната табела кај сите нивоа на конкурентност. Кај 10 клиенти латентноста се намалува од 2345.556 ms на 1653.931 ms, а кај 50 клиенти од 9811.989 ms на 7444.747 ms.

Standard deviation исто така е помала кај оптимизираната верзија. Кај 10 клиенти се намалува од 945.171 ms на 549.645 ms, а кај 50 клиенти од 2272.239 ms на 1702.896 ms. Ова покажува дека партиционирањето и индексите не само што го намалуваат просечното време на извршување, туку го прават извршувањето постабилно.

Q2 - Popular Films 2024

Прашалникот ги пресметува најизнајмуваните филмови во 2024 година. Се користи филтер по датум, спојување со inventory и film, групирање по филм и сортирање според бројот на изнајмувања.

SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(r.rental_id) AS rental_count
FROM rental_copy r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
WHERE r.rental_date >= DATE '2024-01-01'
  AND r.rental_date < DATE '2025-01-01'
GROUP BY f.film_id, f.title, f.rating
ORDER BY rental_count DESC
LIMIT 20;

За оптимизираната верзија се користи истата логика, но наместо rental_copy се користи партиционираната табела rental.

Clients	Threads	rental_copy latency	rental_copy stddev	rental_copy TPS	rental latency	rental stddev	rental TPS	Промена
10	4	2183.371 ms	944.412 ms	4.580	1545.473 ms	584.405 ms	6.471	~29.2% побрзо
20	4	3769.550 ms	1473.425 ms	5.306	3215.116 ms	1297.115 ms	6.221	~14.7% побрзо
30	4	5653.717 ms	1793.595 ms	5.306	4893.620 ms	1679.520 ms	6.130	~13.4% побрзо
50	4	10163.130 ms	3034.697 ms	4.920	7924.103 ms	2330.346 ms	6.310	~22.0% побрзо

Q2 покажува подобри резултати кај партиционираната табела. Латентноста е помала кај сите тестирани нивоа на конкурентност, а TPS е повисок. Standard deviation исто така е помала кај оптимизираната верзија, што покажува постабилно време на извршување. Иако прашалникот содржи GROUP BY и ORDER BY, партиционирањето по rental_date и индексот врз rental_date, inventory_id помагаат затоа што филтерот е ограничен на 2024 година.

Q3 - Monthly Revenue

Прашалникот пресметува месечен приход по продавница за 2024 година. Се пресметуваат број на плаќања, вкупен приход, просечна, минимална и максимална уплата.

SELECT
    date_trunc('month', p.payment_date) AS month,
    s.store_id,
    COUNT(p.payment_id) AS payments_count,
    SUM(p.amount) AS total_revenue,
    AVG(p.amount) AS avg_payment,
    MIN(p.amount) AS min_payment,
    MAX(p.amount) AS max_payment
FROM payment_copy p
JOIN staff s ON p.staff_id = s.staff_id
WHERE p.payment_date >= DATE '2024-01-01'
  AND p.payment_date < DATE '2025-01-01'
GROUP BY date_trunc('month', p.payment_date), s.store_id
ORDER BY month, s.store_id;

За оптимизираната верзија се користи истата логика, но наместо payment_copy се користи партиционираната табела payment.

Clients	Threads	payment_copy latency	payment_copy stddev	payment_copy TPS	payment latency	payment stddev	payment TPS	Промена
10	4	2806.844 ms	891.434 ms	3.563	1121.703 ms	536.035 ms	8.915	~60.0% побрзо
30	4	10642.000 ms	5393.351 ms	2.819	3062.069 ms	1034.027 ms	9.797	~71.2% побрзо
50	4	24061.723 ms	9254.155 ms	2.078	5118.177 ms	1494.030 ms	9.769	~78.7% побрзо

Q3 има најдобри резултати бидејќи филтерот по payment_date директно го користи партиционирањето на табелата payment. Кај 50 клиенти латентноста се намалува од 24061.723 ms на 5118.177 ms, а TPS се зголемува од 2.078 на 9.769. Standard deviation е значително помала кај партиционираната табела. Кај 50 клиенти се намалува од 9254.155 ms на 1494.030 ms. Ова покажува дека партиционирањето според payment_date значително ја подобрува и брзината и стабилноста на извештајот.

Q4 - INSERT rental

Овој тест мери внесување нови редови во табелата за изнајмувања. За benchmark редовите се користи посебен датум 2026-03-07 12:00:00, за да се разликуваат од оригиналните податоци.

INSERT INTO rental_copy (rental_date, inventory_id, customer_id, staff_id)
VALUES (
    TIMESTAMP '2026-03-07 12:00:00',
    floor(random() * 50000 + 1)::int,
    floor(random() * 1000000 + 1)::int,
    floor(random() * 50 + 1)::int
);

За оптимизираната верзија се користи истата логика, но внесувањето се прави во партиционираната табела rental.

Clients	Threads	rental_copy latency	rental_copy stddev	rental_copy TPS	rental latency	rental stddev	rental TPS	Промена
10	4	2.750 ms	0.818 ms	3636.964	2.875 ms	0.937 ms	3478.295	~4.5% побавно
30	4	3.000 ms	1.177 ms	9999.631	3.340 ms	1.557 ms	8981.431	~11.3% побавно
50	4	7.099 ms	2.297 ms	7043.696	3.501 ms	2.220 ms	14281.463	~50.7% побрзо

Кај INSERT операциите резултатите се мешани. Кај 10 и 30 клиенти rental_copy има малку подобри резултати, бидејќи кај партиционираната табела PostgreSQL дополнително одредува во која партиција треба да се внесе редот. Кај 50 клиенти партиционираната табела покажува подобар резултат: латентноста се намалува од 7.099 ms на 3.501 ms, а TPS се зголемува од 7043.696 на 14281.463.

Q5 - UPDATE rental

Овој тест мери ажурирање на изнајмувања. Се избира еден ред со benchmark датумот и со return_date IS NULL, па потоа се поставува return_date = now().

UPDATE rental_copy
SET return_date = now()
WHERE rental_id = (
    SELECT rental_id
    FROM rental_copy
    WHERE rental_date = TIMESTAMP '2026-03-07 12:00:00'
      AND return_date IS NULL
    LIMIT 1
);

За оптимизираната верзија се користи истата логика, но ажурирањето се прави врз партиционираната табела rental.

Clients	Threads	rental_copy latency	rental_copy stddev	rental_copy TPS	rental latency	rental stddev	rental TPS	Промена
10	4	704.821 ms	61.336 ms	14.188	7.093 ms	3.997 ms	1409.861	~99.0% побрзо
30	4	1923.308 ms	457.842 ms	15.598	26.970 ms	16.112 ms	1112.346	~98.6% побрзо
50	4	3124.384 ms	700.922 ms	16.003	17.475 ms	13.475 ms	2861.279	~99.4% побрзо

UPDATE операцијата покажува најголемо подобрување. Кај 10 клиенти латентноста се намалува од 704.821 ms на 7.093 ms, а кај 50 клиенти од 3124.384 ms на 17.475 ms. Standard deviation исто така значително се намалува. Кај 50 клиенти се намалува од 700.922 ms на 13.475 ms. Причината е што UPDATE условот користи конкретен rental_date, па кај партиционираната табела PostgreSQL ја ограничува операцијата на релевантната партиција.

Вкупно подобрување

Партиционираните табели и индексите покажуваат најголемо подобрување кај прашалници кои користат временски филтер врз колоната по која е направено партиционирањето. Освен просечната латентност, во оваа анализа се зема предвид и standard deviation, која покажува колку е стабилно времето на извршување.

Најголеми придобивки:

• Q3 - Monthly Revenue: најголемо подобрување кај временски ограничените извештаи. Кај 50 клиенти латентноста се намалува од 24061.723 ms на 5118.177 ms, а standard deviation се намалува од 9254.155 ms на 1494.030 ms. Кај 50 клиенти латентноста се намалува од 24061.723 ms на 5118.177 ms, а standard deviation се намалува од 9254.155 ms на 1494.030 ms.
• Q5 - UPDATE rental: најголемо подобрување кај write операциите. Кај 50 клиенти латентноста се намалува од 3124.384 ms на 17.475 ms, а standard deviation се намалува од 700.922 ms на 13.475 ms.
• Q1 - Movie Recommendation: има подобрување кај сите нивоа на конкурентност, при што оптимизираната верзија има помала латентност и помала standard deviation.
• Q2 - Popular Films: по новите мерења со standard deviation, партиционираната табела има подобри резултати кај сите тестирани нивоа на конкурентност.

Мешани резултати:

• Q4 - INSERT rental: резултатите се мешани. Кај 10 и 30 клиенти rental_copy е малку побрза, затоа што INSERT во партиционирана табела има дополнителен overhead за избор на партиција. Кај 50 клиенти партиционираната табела е подобра, со помала латентност и поголем TPS.

Ова покажува дека оптимизацијата мора да се мери со повеќе метрики. Партиционирањето не е автоматско решение за сите операции, но е многу корисно кога прашалникот користи WHERE услов врз партициската колона, како rental_date или payment_date. Помалата standard deviation кај повеќето оптимизирани тестови покажува и постабилно, попредвидливо извршување.

Операции на читање

• Q1 - Movie Recommendation: оптимизираната верзија има помала латентност, поголем TPS и помала standard deviation кај сите нивоа на конкурентност.
• Q2 - Popular Films: по новите мерења, партиционираната табела има подобри резултати кај сите тестирани нивоа на конкурентност. Сепак, прашалникот содржи GROUP BY и ORDER BY, па агрегацијата и сортирањето остануваат значаен дел од трошокот.
• Q3 - Monthly Revenue: најдобар пример каде партиционирањето директно го намалува бројот на редови што треба да се читаат, бидејќи филтерот користи payment_date, а табелата payment е партиционирана според истата колона.

Кај операциите на читање, најдобри резултати се добиваат кога WHERE условот ја користи колоната по која е направено партиционирањето. Дополнително, помалата standard deviation кај оптимизираните верзии покажува дека извршувањето е постабилно и попредвидливо.

Операции на запишување

• INSERT: резултатите се мешани. Кај 10 и 30 клиенти rental_copy е малку побрза, но кај 50 клиенти партиционираната табела има подобра латентност и повисок TPS.
• UPDATE: има многу големо подобрување кај партиционираната табела, затоа што условот користи конкретна вредност за rental_date.

Кај INSERT, партиционирањето може да додаде overhead затоа што PostgreSQL мора да одреди во која партиција треба да се внесе новиот ред. Кај UPDATE, партиционирањето е многу корисно кога условот ја користи партициската колона, бидејќи пребарувањето се ограничува на релевантната партиција наместо на целата табела.

Заклучок

За ова специфично работно оптоварување:

• Партиционирањето е многу корисно за временски извештаи, особено кај payment и rental.
• Индексите значително помагаат кај прашалници со WHERE услови и JOIN операции врз често користени колони.
• Q3 и Q5 покажуваат најголемо подобрување, затоа што директно ја користат колоната по која е направено партиционирањето.
• Q1 и Q2 покажуваат подобрување кај SELECT прашалници кои користат временски филтри, но кај Q2 агрегацијата и сортирањето остануваат значаен дел од трошокот.
• INSERT операциите треба да се мерат внимателно, бидејќи партиционирањето може да додаде overhead при внесување, особено при помал број клиенти.
• Standard deviation е важна метрика затоа што покажува колку се стабилни перформансите. Кај повеќето оптимизирани тестови standard deviation е помала, што значи дека времето на извршување е попредвидливо.

Најсоодветен пристап за оваа база е да се задржи партиционирањето по датум за големите трансакциски табели rental и payment, да се користат индекси само за најчестите филтри и спојувања, и редовно да се проверуваат резултатите со EXPLAIN ANALYZE и pgbench.