Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

09/16/26 09:45:43 (2 weeks ago)

Author:

231081

Comment:

--

AdvancedTopics

@@ +1 @@
+= Напредни теми =
+
+Во рамки на проектот се имплементирани две напредни теми:
+
+1. Геопросторно пребарување на најблиски продавници
+2. Партиционирање на историјата на нарачки според година
+
+= 1. Геопросторно пребарување =
+
+== 1.1 Цел ==
+
+Целта на оваа функционалност е за дадена адреса на корисник да се пронајдат продавниците што се наоѓаат во зададен радиус. Продавниците се подредуваат според растојанието од корисникот.
+
+Бидејќи PostGIS екстензијата не е достапна на серверот, решението е имплементирано со вградените можности на PostgreSQL:
+
+* географска ширина и должина;
+* вградениот тип `point`;
+* GiST просторен индекс;
+* ограничувачки правоаголник, односно bounding box;
+* Haversine формула за пресметување растојание.
+
+== 1.2 Географски координати ==
+
+Во табелата `public.address` се додадени колоните `latitude` и `longitude`.
+
+{{{#!sql
+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` ги поврзува продавниците со нивните адреси и координати.
+
+{{{#!sql
+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` прима идентификатор на адреса на корисник и максимален радиус во метри.
+
+{{{#!sql
+SELECT *
+FROM advanced_gis.find_nearest_stores(5745513, 5000);
+}}}
+
+Функцијата работи во две фази:
+
+1. Со bounding box брзо ги избира можните продавници во околината.
+2. За избраните кандидати го пресметува точното растојание со функцијата `distance_meters`.
+
+На крај се задржуваат само продавниците чие растојание е помало или еднакво на зададениот радиус, а резултатите се подредуваат од најблиска кон најдалечна продавница.
+
+== 1.6 GiST просторен индекс ==
+
+За побрзо пребарување според координати е креиран парцијален GiST индекс.
+
+{{{#!sql
+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 Родителска партиционирана табела ==
+
+{{{#!sql
+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 Годишни партиции ==
+
+За секоја година е креирана посебна партиција.
+
+{{{#!sql
+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` партиција за датуми што не припаѓаат во дефинираните годишни опсези.
+
+{{{#!sql
+CREATE TABLE advanced_partitioning.order_history_default
+PARTITION OF advanced_partitioning.order_history
+DEFAULT;
+}}}
+
+== 2.4 Внесување примерок од реалните податоци ==
+
+За тестирање е земен приближно 1% примерок од оригиналната табела.
+
+{{{#!sql
+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`.
+
+{{{#!sql
+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 ==
+
+Ефикасноста на партиционирањето е проверена со следниот план за извршување:
+
+{{{#!sql
+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 и индексирање овозможува поефикасно извршување на временски ограничени барања над голема историја на нарачки.