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

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

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

За оваа анализа беше користено pgbench. Секој тест се извршува со повеќе паралелни клиенти, при што се мерат просечна латентност и TPS, односно број на трансакции во секунда. Во сите тестови бројот на неуспешни трансакции беше 0.

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

pgbench -n -U admin -d film_rental -c 10 -j 2 -T 60 -f /tmp/movie_recommendation_rental_copy.sql  

Каде:

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

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

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

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 TPS	rental latency	rental TPS
10	2	2235.603 ms	4.473	1083.428 ms	9.230
20	4	4036.198 ms	4.955	2208.459 ms	9.056
50	4	9487.494 ms	5.270	5247.761 ms	9.528

Q1 покажува значително подобрување кај оптимизираната табела. Кај 10 клиенти латентноста е намалена за околу 51.5%, а TPS е зголемен за околу 106.3%. Кај 50 клиенти латентноста е намалена за околу 44.7%, а TPS е зголемен за околу 80.8%. Ова се случува бидејќи прашалникот користи customer_id, inventory_id и rental_date. Индексите помагаат при пребарување на историјата на корисникот, а партиционирањето помага при делот од прашалникот кој ја пресметува популарноста за 2024 година.

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	rental_copy latency	rental_copy TPS	rental latency	rental TPS	Промена
10	2222.438 ms	4.500	1628.696 ms	6.140	~26.7% побрзо
20	4205.239 ms	4.756	3828.958 ms	5.223	~8.9% побрзо
30	6545.865 ms	4.583	6648.265 ms	4.512	~1.6% побавно
50	11077.453 ms	4.514	11290.484 ms	4.429	~1.9% побавно

Q2 има мешани резултати. Кај 10 и 20 клиенти партиционираната табела е побрза, но кај 30 и 50 клиенти резултатот е речиси ист или малку полош. Ова покажува дека партиционирањето не секогаш гарантира подобрување. Кај овој прашалник, освен читање од rental, има и GROUP BY, ORDER BY и LIMIT. При поголем број паралелни клиенти главен трошок може да станат агрегацијата и сортирањето, а не само читањето на редови од табелата.

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	payment_copy latency	payment_copy TPS	payment latency	payment TPS	Промена
10	2627.300 ms	3.806	1055.953 ms	9.470	~59.8% побрзо
30	8545.741 ms	3.511	3325.437 ms	9.021	~61.1% побрзо
50	16138.221 ms	3.098	5321.565 ms	9.396	~67.0% побрзо

Q3 покажува најстабилно подобрување кај аналитичките прашалници. Прашалникот пресметува месечен приход по продавница за 2024 година, а табелата payment е партиционирана според payment_date. Бидејќи условот користи временски интервал за 2024 година, PostgreSQL може да ја чита партицијата за 2024 година наместо целата табела. Затоа латентноста е намалена за околу 60-67%, а TPS е зголемен за околу 149-203%.

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	rental_copy latency	rental_copy TPS	rental latency	rental TPS	Промена
10	3.121 ms	3204.533	2.855 ms	3502.623	~8.5% побрзо
30	2.949 ms	10173.818	3.147 ms	9533.949	~6.7% побавно
50	6.756 ms	7400.777	3.088 ms	16190.222	~54.3% побрзо

INSERT операциите имаат мешани резултати. Кај 10 и 50 клиенти партиционираната табела е побрза, но кај 30 клиенти непартиционираната табела има малку подобар резултат. Причината е тоа што при внесување PostgreSQL мора да одреди во која партиција припаѓа новиот ред и мора да ги ажурира индексите. Тоа додава мал overhead, но при поголемо конкурентно оптоварување партиционираната структура може да помогне затоа што записите се насочуваат кон конкретна партиција.

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	rental_copy latency	rental_copy TPS	rental latency	rental TPS	Промена
10	590.026 ms	16.948	7.044 ms	1419.709	~98.8% побрзо
30	1483.097 ms	20.228	12.318 ms	2435.437	~99.2% побрзо
50	2646.029 ms	18.896	16.689 ms	2996.003	~99.4% побрзо

UPDATE операцијата покажува најголемо подобрување. Условот користи конкретен rental_date, па кај партиционираната табела PostgreSQL може да пребарува само во релевантната партиција. Кај rental_copy системот мора да пребарува низ голема непартиционирана табела. Затоа латентноста е многу поголема, а TPS е многу помал.

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

Партиционираните табели и индексите покажуваат значително подобрување кај прашалници кои се временски ограничени и кај операции кои можат да се насочат кон конкретна партиција. Најголеми придобивки:

• Q3 - Monthly Revenue: околу 64.5% просечно намалување на латентноста.
• Q5 - UPDATE rental: околу 99.2% просечно намалување на латентноста.
• Q1 - Movie Recommendation: околу 45.8% просечно намалување на латентноста.

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

• Q2 - Popular Films има подобрување кај 10 и 20 клиенти, но мало влошување кај 30 и 50 клиенти.
• Q4 - INSERT има подобрување кај 10 и 50 клиенти, но мало влошување кај 30 клиенти.

Ова покажува дека оптимизацијата мора да се мери. Партиционирањето не е автоматско решение за сите прашалници. Најмногу помага кога WHERE условот ја користи колоната по која е направено партиционирањето.

Операции на читање со мешани резултати

• Q1 - Movie Recommendation: јасно подобрување кај сите нивоа на конкурентност.
• Q2 - Popular Films: подобро кај помал број клиенти, но речиси исто или малку полошо кај поголем број клиенти.
• Q3 - Monthly Revenue: најдобар пример каде партиционирањето директно го намалува бројот на редови што треба да се читаат.

Кај читањата кои содржат големи агрегации и сортирања, како Q2, главниот проблем не е секогаш табелата од која се чита. Понекогаш главниот трошок е обработката по читањето, односно GROUP BY и ORDER BY.

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

• INSERT: просечно подобрување, но со варијации.
• UPDATE: многу големо подобрување поради филтрирање по rental_date.

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

Заклучок

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

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

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