- 공간 정보를 다루기 위해 PostGIS를 활용하기
- postgreSQL의 function을 활용하여 데이터를 가져오기
- 성능 측정 (단순 비교 vs PostGIS)
배경
지도에 마커를 표시하기 위해 데이터를 불러와야 했는데, 모든 데이터를 가져온 후에 지도에 표시하는 것은 비효율적인 방법이라고 생각했다.
(클라이언트 지도 상에 보이지 않는 영역에 대한 데이터를 불러오기 때문)
클라이언트에서 보고 있는 영역에 대한 데이터만 불러오려면 어떻게 해야 할까?
- 클라이언트에서 보고 있는 영역에 대한 정보를 가져온다.
- 영역 정보를 기반으로 데이터를 요청한다.
1) 클라이언트에서 보고 있는 영역에 대한 정보를 가져온다.
보고 있는 영역의 좌표는 남서쪽 모서리(min)와 북동쪽 모서리(max) 좌표를 통해 표현할 수 있다.
2) 영역 정보를 기반으로 데이터를 요청
따릉이 정류소 데이터
| id | name | latitude | longitude |
|---|---|---|---|
| 1 | 경복궁역 7번출구 앞 | 37.57579422 | 126.9714508 |
| 2 | 종로구청 옆 | 37.57255936 | 126.9783325 |
영역에 해당하는 정류소 데이터를 가져오려면?
1. 단순 비교
지도의 경계 좌표가 남서(37.571, 126.976), 북동(37.573, 126.979) 라면,
아래 조건을 통해 데이터를 필터링하여 가져올 수 있다.
37.571 <= latitude <= 37.573
126.976 <= longitude <= 126.979
// supabase 예시 코드
const get_data_within_bounds = async (
lat_min,
lat_max,
lon_min,
lon_max
) => {
const { data, error } = await supabase
.from('my_marker_data')
.select('*')
.gte('latitude', lat_min)
.lte('latitude', lat_max)
.gte('longitude', lon_min)
.lte('longitude', lon_max);
// ...
}2. PostGIS 확장
PostgreSQL의 PostGIS 확장을 사용하면, 지리 공간 데이터를 더욱 효율적으로 관리하고 쿼리할 수 있다.
- PostGIS 확장 활성화
데이터베이스에서 PostGIS 확장을 활성화한다.
CREATE EXTENSION IF NOT EXISTS postgis;- 지리 공간 데이터 타입의 컬럼 추가
ALTER TABLE my_marker_data
ADD COLUMN geom geometry(Point, 4326);- 4326은 WGS 84 좌표계를 나타냅니다.
- 기존 데이터 업데이트
UPDATE my_marker_data
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);- ST_MakePoint(longitude, latitude): 포인트 생성
- ST_SetSRID: 좌표계(SRID) 설정
- 데이터베이스 함수 생성
CREATE
OR REPLACE FUNCTION public.stations_in_view (
min_latitude DOUBLE PRECISION,
max_latitude DOUBLE PRECISION,
min_longitude DOUBLE PRECISION,
max_longitude DOUBLE PRECISION
) RETURNS TABLE (
id BIGINT,
station_id TEXT,
station_name TEXT,
LOCATION TEXT,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION
) AS $$
BEGIN
RETURN QUERY
SELECT
s.id,
s.station_id,
s.station_name,
s.location,
s.latitude,
s.longitude
FROM
public.stations s
where
ST_Intersects(
s.geom,
ST_MakeEnvelope(
min_longitude, min_latitude,
max_longitude, max_latitude,
4326
)
);
END;
$$ LANGUAGE plpgsql;
- 클라이언트에서 쿼리 수행
const get_data_within_bounds = async (max_latitude, max_longitude, min_latitude, min_longitude) => {
let { data, error } = await supabase
.rpc('stations_in_view', {
max_latitude,
max_longitude,
min_latitude,
min_longitude
});
// ...
}추가
성능 비교
클라이언트에서 성능 측정
| index | rows [개] | 단순 비교 [ms] | 공간 정보(PostGIS) [ms] |
|---|---|---|---|
| 0 | 43 | 57.4 | 60.0 |
| 1 | 2763 | 267.8 | 309.3 |
| 2 | 2763 | 339.9 | 269.0 |
지리 정보를 활용하는 방식이 일반적인 필터에 비해 당연히 빠를 것이라고 생각했는데, 예상과 다른 결과.
쿼리 성능보다는 네트워크의 영향이 더 큰 건 아닐까?
쿼리 성능 측정
EXPLAIN ANALYZE 를 활용해서, 쿼리 성능을 측정했다.
ex)
Seq Scan on stations (cost=0.00..83.63 rows=2763 width=131) (actual time=0.013..0.277 rows=2763 loops=1)
Planning Time: 0.390 ms
Execution Time: 0.486 ms
cost: 예측한 쿼리 실행 비용
actual time: 실제 실행 시간 (첫 번째 행을 가져오는 데 걸린 시간, 마지막 행을 가져오는 데까지 걸린 총 시간)
planning time: 쿼리 실행 계획을 수립하는 데 걸린 시간
execution time: 쿼리를 실제로 실행하는 데 걸린 전체 시간 (모든 노드의 실행 시간 + 추가적인 오버헤드)
EXPLAIN ANALYZE
SELECT
*
FROM
public.stations
WHERE latitude >= 36
AND latitude <= 38
AND longitude >= 126
AND longitude <= 128;EXPLAIN ANALYZE
SELECT *
FROM public.stations
WHERE ST_Intersects(
geom,
ST_MakeEnvelope(126, 36, 128, 38, 4326)
);| * | scan type | rows | cost | actual time | planning time [ms] | execution time [ms] |
|---|---|---|---|---|---|---|
| 단순 비교 | Seq Scan | 2763 | 111.26 | 0.552 | 0.461 | 0.713 |
| PostGIS | Seq Scan | 2763 | 69158.63 | 2.264 | 27.269 | 2.483 |
성능: 단순 비교 > PostGIS
예상과 달리 공간 인덱스를 생성했음에도 단순 필터를 이용해 비교를 하는 것 보다 느리게 나왔다.
데이터 수가 적어서 순차적 스캔이 활성화될 수 있다고 해서, 순차 스캔을 비활성화도 해봤지만 속도가 빠른 결과가 나오지는 않았다.
성능 차이가 발생한 이유 (by gpt)
- 데이터량이 적음
- 공간 함수의 오버헤드
- 쿼리 계획 수립 시간 증가
위 성능 차이가 유의미한 차이일까?
실행 시간 비교
- 단순 비교 쿼리: 0.7ms
- 공간 쿼리: 1.2ms
절대 시간 차이는 약 0.5ms
이는, 실제 애플리케이션에서 사용자 체감 성능에 거의 영향을 미치지 않음.
성능 차이의 유의미성을 판단하는 기준 (사용자 경험에 미치는 영향) (by gpt)
- 응답 시간이 1초를 넘을 경우: 성능 개선 필요
- 응답 시간이 100ms 이내: 사용자들이 느리다고 느낄 수 있지만, 대부분의 어플리케이션에서 충분히 빠름
Next
- 정류소 데이터를 효과적으로 캐싱하려면 어떻게 해야 할까?
