Напредни теми

Во рамки на проектот се имплементирани две напредни теми:

1. Геопросторно пребарување на најблиски продавници
2. Партиционирање на историјата на нарачки според година

1. Геопросторно пребарување

1.1 Цел

Целта на оваа функционалност е за дадена адреса на корисник да се пронајдат продавниците што се наоѓаат во зададен радиус. Продавниците се подредуваат според растојанието од корисникот.

Бидејќи PostGIS екстензијата не е достапна на серверот, решението е имплементирано со вградените можности на PostgreSQL:

• географска ширина и должина;
• вградениот тип point;
• GiST просторен индекс;
• ограничувачки правоаголник, односно bounding box;
• Haversine формула за пресметување растојание.

1.2 Географски координати

Во табелата public.address се додадени колоните latitude и longitude.

ALTER TABLE public.address
ADD COLUMN latitude DOUBLE PRECISION
    CHECK (latitude BETWEEN -90 AND 90),
ADD COLUMN longitude DOUBLE PRECISION
    CHECK (longitude BETWEEN -180 AND 180);

Ограничувањата CHECK спречуваат внесување невалидни координати.

Бидејќи адресите во податочното множество се генерирани и не секогаш претставуваат реални адреси, за демонстрацијата се користат приближни координати на ниво на град. Изворот на координатите се означува како CITY_APPROXIMATION.

1.3 Поглед со локации на продавници

Погледот advanced_gis.store_locations ги поврзува продавниците со нивните адреси и координати.

CREATE OR REPLACE VIEW advanced_gis.store_locations AS
SELECT
    si.store_instance_id,
    s.store_id,
    s.store_name,
    si.address_id,
    a.street,
    a.street_number,
    a.latitude,
    a.longitude,
    point(a.longitude, a.latitude) AS map_point,
    'CITY_APPROXIMATION'::VARCHAR(30) AS coordinate_source
FROM public.storeinstance si
JOIN public.store s
    ON s.store_id = si.store_id
JOIN public.address a
    ON a.address_id = si.address_id
WHERE a.latitude IS NOT NULL
  AND a.longitude IS NOT NULL;

Колоната map_point ја претставува локацијата како PostgreSQL точка во формат:

(longitude, latitude)

1.4 Пресметување растојание со Haversine формула

Функцијата advanced_gis.distance_meters го пресметува воздушното растојание меѓу две географски точки.

Haversine формулата го зема предвид сферичниот облик на Земјата. Како радиус на Земјата се користи вредноста 6.371.000 метри.

Функцијата прима:

• географска ширина на првата точка;
• географска должина на првата точка;
• географска ширина на втората точка;
• географска должина на втората точка.

Како резултат враќа растојание во метри.

1.5 Пронаоѓање најблиски продавници

Функцијата advanced_gis.find_nearest_stores прима идентификатор на адреса на корисник и максимален радиус во метри.

SELECT *
FROM advanced_gis.find_nearest_stores(5745513, 5000);

Функцијата работи во две фази:

1. Со bounding box брзо ги избира можните продавници во околината.
2. За избраните кандидати го пресметува точното растојание со функцијата distance_meters.

На крај се задржуваат само продавниците чие растојание е помало или еднакво на зададениот радиус, а резултатите се подредуваат од најблиска кон најдалечна продавница.

1.6 GiST просторен индекс

За побрзо пребарување според координати е креиран парцијален GiST индекс.

CREATE INDEX IF NOT EXISTS address_coordinates_gist_idx
ON public.address
USING GIST (
    (point(longitude, latitude))
)
WHERE latitude IS NOT NULL
  AND longitude IS NOT NULL;

Индексот ги содржи само адресите што имаат координати. Тој се користи за побрзо просторно филтрирање на кандидатите пред пресметувањето на точното растојание.

1.7 Ограничување на решението

Имплементацијата не користи PostGIS. Таа претставува геопросторно решение изработено со основните типови и индекси на PostgreSQL.

PostGIS не беше достапен на серверот, а корисникот на базата нема администраторски права за негово инсталирање. Поради тоа се користат point, GiST индекс и Haversine формула.

2. Партиционирање на нарачките

2.1 Цел

Табелата public."Order" содржи 10.000.013 нарачки со датуми од 2021-01-01 до 2026-05-10.

Целта на партиционирањето е големата логичка табела да се подели на помали физички табели според годината на нарачката.

Оригиналната табела public."Order" не се менува. За демонстрацијата е креирана посебна шема advanced_partitioning.

2.2 Родителска партиционирана табела

CREATE SCHEMA IF NOT EXISTS advanced_partitioning;

CREATE TABLE advanced_partitioning.order_history (
    LIKE public."Order"
)
PARTITION BY RANGE (order_date);

PARTITION BY RANGE (order_date) означува дека редовите се распределуваат во партиции според опсегот на order_date.

2.3 Годишни партиции

За секоја година е креирана посебна партиција.

CREATE TABLE advanced_partitioning.order_history_2021
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE advanced_partitioning.order_history_2022
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE advanced_partitioning.order_history_2023
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE advanced_partitioning.order_history_2024
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE advanced_partitioning.order_history_2025
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE advanced_partitioning.order_history_2026
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Долната граница е вклучена, додека горната граница не е вклучена. На пример, партицијата за 2025 година ги чува датумите:

2025-01-01 <= order_date < 2026-01-01

Креирана е и DEFAULT партиција за датуми што не припаѓаат во дефинираните годишни опсези.

CREATE TABLE advanced_partitioning.order_history_default
PARTITION OF advanced_partitioning.order_history
DEFAULT;

2.4 Внесување примерок од реалните податоци

За тестирање е земен приближно 1% примерок од оригиналната табела.

INSERT INTO advanced_partitioning.order_history
SELECT *
FROM public."Order"
TABLESAMPLE SYSTEM (1)
REPEATABLE (2026);

Вметнати се вкупно 102.606 реални нарачки. PostgreSQL автоматски го насочува секој ред во соодветната партиција според order_date.

Добиена е следната распределба:

Партиција	Број на нарачки
2021	19.122
2022	19.251
2023	19.015
2024	19.330
2025	19.273
2026	6.615

2.5 Индекс на датумот

На родителската табела е дефиниран индекс врз order_date.

CREATE INDEX IF NOT EXISTS order_history_order_date_idx
ON advanced_partitioning.order_history (order_date);

ANALYZE advanced_partitioning.order_history;

PostgreSQL автоматски креира соодветни индекси на поединечните партиции.

2.6 Partition pruning

Ефикасноста на партиционирањето е проверена со следниот план за извршување:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM advanced_partitioning.order_history
WHERE order_date >= DATE '2025-01-01'
  AND order_date < DATE '2026-01-01';

Во планот за извршување се добива:

Index Only Scan using order_history_2025_order_date_idx
on order_history_2025
Heap Fetches: 0
Execution Time: 3.486 ms

Иако барањето се извршува над родителската табела order_history, PostgreSQL пристапува само до order_history_2025.

Оваа оптимизација се нарекува partition pruning. Од условот за датум PostgreSQL определува дека потребните податоци можат да се наоѓаат само во партицијата за 2025 година и ги исклучува останатите партиции од пребарувањето.

Потоа се применува Index Only Scan, со што податоците се читаат директно од индексот, без дополнително читање од табелата.

Заклучок

Со геопросторното пребарување се овозможува пронаоѓање продавници во зададен радиус преку координати, просторен индекс и Haversine формула.

Со партиционирањето на нарачките по година се намалува бројот на податоци што треба да се пребаруваат. Комбинацијата од partition pruning и индексирање овозможува поефикасно извршување на временски ограничени барања над голема историја на нарачки.