구체화된 뷰에서 연속 집계까지: 실시간 분석으로 PostgreSQL 향상

사용자가 실시간 데이터 분석에 액세스할 수 있도록 하는 것은 많은 최신 애플리케이션의 핵심 기능입니다. 즐겨 사용하는 SaaS 플랫폼을 사용하는 모습을 상상해 보세요. 실시간 데이터와 기록 통찰력을 제공하는 직관적인 대시보드가 있을 것입니다. 플랫폼과 상호 작용하여 맞춤형 보고서를 생성하고, 세부 지표를 탐색하고, 몇 주 또는 몇 달에 걸친 추세를 시각화할 수 있습니다.

사용자로서 이 플랫폼이 느려지는 것을 원하지 않을 것입니다. 이는 이러한 제품을 지원하는 데이터베이스가 복잡한 분석 쿼리를 포함하여 대용량 데이터에 대한 쿼리를 빠르게 실행해야 함을 의미합니다.

하는 동안 PostgreSQL은 오늘날 개발자들 사이에서 가장 사랑받는 데이터베이스입니다. , 대용량 데이터 쿼리가 빠른 것으로 알려져 있지 않습니다. 하지만 걱정하지 마세요. Postgres 의 도구 상자에는 항상 도구가 있습니다. 가장 좋은 것 중 하나는 구체화된 뷰입니다.

PostgreSQL 구체화된 뷰란 무엇입니까?

구체화 기술을 기반으로 PostgreSQL 구체화된 뷰는 일반적으로 쿼리를 실행하고 결과를 테이블로 저장합니다. 뷰가 참조될 때마다 기본 쿼리를 실행하는 표준 PostgreSQL 뷰와 달리 구체화된 뷰는 소스 쿼리의 결과를 데이터베이스에 유지합니다. 이것의 가장 큰 장점은 쿼리를 실행할 때마다 데이터베이스가 쿼리를 실행할 필요가 없다는 것입니다. 결과는 이미 디스크에서 액세스할 수 있으므로 쿼리에 대한 응답을 훨씬 빠르게 얻을 수 있습니다.

이는 컴퓨팅에 리소스를 많이 사용하는 쿼리에 대한 쿼리 응답을 최적화하는 멋진 방법입니다. 예를 들어 대량의 데이터 처리, 집계 또는 다중 조인과 관련된 쿼리가 있습니다.

구체화된 뷰로 작업하는 것은 매우 간단합니다. 뷰를 생성하려면 CREATE MATERIALIZED VIEW 문과 선택한 쿼리를 사용합니다.

구체화된 뷰가 생성되면 이를 일반 PostgreSQL 테이블로 쿼리할 수 있습니다.

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

이 구체화된 뷰는 새로 고칠 때까지 빠르게 오래된 상태가 됩니다. 기본 테이블에 새 데이터를 추가하는 경우(또는 데이터를 업데이트하거나 삭제하는 경우)에도 구체화된 뷰에는 해당 변경 사항이 자동으로 포함되지 않습니다. 생성 당시의 스냅샷입니다. 구체화된 뷰를 업데이트하려면 REFRESH MATERIALIZED VIEW 실행해야 합니다.

 REFRESH MATERIALIZED VIEW customer_orders;

이 마지막 지점(새로 고침 처리 방법)은 구체화된 뷰의 아킬레스건입니다. 다음 섹션에서 설명하겠습니다.

실시간 분석은 어떻습니까? PostgreSQL 구체화된 뷰의 제한 사항

앞서 말했듯이 PostgreSQL 구체화된 뷰는 자주 실행되는 쿼리의 속도를 높이는 강력한 도구입니다. 특히 이러한 쿼리가 대량의 데이터를 처리하는 경우 더욱 그렇습니다. 그러나 구체화된 뷰에는 이상적이지 않은 한 가지 측면이 있습니다. 구체화된 뷰를 최신 상태로 유지하려면 새로 고쳐야 합니다.

이 단일 문제로 인해 세 가지 중요한 제한 사항이 발생합니다.

새로 고침은 비효율적이며 계산 비용이 많이 듭니다.

구체화된 뷰를 새로 고치면 전체 데이터세트에 대해 쿼리가 다시 계산됩니다. 내부적으로 새로 고침을 실행하면 이전 구체화된 데이터가 삭제된 다음 다시 구체화된 새 데이터로 대체됩니다. 구현 증분 새로 고침 (변경된 데이터만 업데이트되는 경우) 집계 프로세스를 훨씬 더 효율적으로 만들 수 있지만 일관된 관계형 데이터베이스 내에서 올바르게 구현하기는 어렵습니다. 추가 스크립팅을 통해 해결 방법이 가능한 경우도 있지만, 특히 복잡한 쿼리의 경우나 데이터가 늦게 도착하는 경우에는 쉽지 않습니다.

새로 고침이 자동으로 실행되지 않습니다.

앞서 언급한 것처럼 구체화된 뷰는 최신 데이터를 자동으로 통합하지 않습니다. REFRESH MATERIALIZED VIEW 실행하여 새로 고쳐야 합니다. 프로덕션 설정에서 수동 새로 고침을 실행하는 것은 불가능합니다. 훨씬 더 현실적인 설정은 새로 고침을 자동화하는 것입니다.

안타깝게도 구체화된 뷰에는 자동 새로 고침 기능이 내장되어 있지 않으므로 PostgreSQL에서 구체화된 뷰에 대한 자동 새로 고침 일정을 만들려면 일종의 스케줄러가 필요합니다. 이는 확장 기능을 사용하여 데이터베이스 내에서 처리하거나 cron과 같은 스케줄러를 사용하여 데이터베이스 외부에서 처리할 수 있습니다. 그러나 새로 고침 비용이 많이 들고 시간이 오래 걸리기 때문에 관리됩니다. 뷰를 충분히 빠르게 새로 고칠 수 없는 상황에 빠지기 매우 쉽습니다.

구체화된 뷰에는 최신 결과가 표시되지 않습니다.

구체화된 뷰의 정적 특성으로 인해 쿼리 시 마지막 새로 고침 이후 추가되거나 변경된 데이터가 누락됩니다(새로 고침이 일정에 따라 발생하더라도). 예약 기간이 1시간으로 설정된 경우 집계된 값은 최대 1시간에 오래된 업데이트를 수행하는 데 걸리는 실제 시간이 더해집니다. 그러나 오늘날 많은 애플리케이션은 수집되는 데이터의 지속적인 스트림을 의미하며, 이러한 애플리케이션은 사용자가 뷰를 쿼리할 때 정확한 정보를 검색할 수 있도록 사용자에게 최신 결과를 제공해야 하는 경우가 많습니다.

이러한 한계로 인해 구체화된 뷰가 제약을 받는 것은 안타까운 일입니다. 새로운 데이터가 자주 들어오는 라이브 데이터 세트에서 SaaS 플랫폼을 구축하는 경우 구체화된 뷰를 완전히 폐기해야 합니까?

내 대답은 아니오 야. Timescale에서는 구체화된 뷰를 효과적으로 향상하여 최신 애플리케이션에 더욱 적합하게 만드는 솔루션인 연속 집계를 구축했습니다.

연속 집계 충족: 실시간 분석을 위한 자동 새로 고침을 통한 구체화된 뷰

구체화된 뷰가 정적 스냅샷이 아니라 동적으로 효율적으로 업데이트되는 세상을 상상해 보세요. 다른 것에 대해 걱정하지 않고 원하는 쿼리 성능 향상에 액세스할 수 있습니다. 글쎄요, Timescale의 연속 집계에 대해 설명한 것 같습니다.

연속 집계(TimescaleDB 확장을 통해 모든 PostgreSQL 데이터베이스에서 사용 가능하고 Timescale 플랫폼을 통해 AWS에서 사용 가능)는 효율적이고 자동화된 새로 고침 기능과 실시간 요소로 강화된 구체화된 보기입니다. 구체화된 뷰와 거의 동일하게 보이고 느껴지지만 다음을 허용합니다.

• 새로 고침 정책을 통한 자동 새로 고침
• 보다 효율적인 새로 고침 프로세스: 새로 고침이 실행되면 마지막 새로 고침 이후 변경된 데이터만 수정됩니다.
• 최신 결과, 구체화된 뷰를 활용할 수 있는 사용 사례 확장(예: 실시간 분석, 라이브 대시보드, 보고 등)

새로 고침을 자동으로 수행하고 리소스 효율성을 높입니다.

연속 집계 생성은 구체화된 뷰 생성과 매우 유사합니다(일반 PostgreSQL 테이블로 쿼리할 수도 있음).

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

그러나 구체화된 뷰와는 다르게 새로 고침 정책을 만드는 것은 간단합니다. 데이터베이스 내에서 새로 고침 간격을 쉽게 정의하여 지속적인 집계가 자동으로 주기적으로 업데이트되도록 할 수 있습니다.

아래 예에서는 30분마다 연속 집계를 업데이트하도록 새로 고침 정책을 설정합니다. end_offset 매개변수는 새로 고쳐질 데이터의 시간 범위를 정의하고, schedule_interval 연속 집계가 새로 고쳐지는 빈도를 설정합니다.

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

이 새로 고침 정책이 시작되면 일반 구체화된 뷰를 사용할 때보다 프로세스가 훨씬 더 효율적이 됩니다. REFRESH MATERIALIZED VIEW 실행과 달리 연속 집계가 새로 고쳐지면 Timescale은 이전 데이터를 모두 삭제하고 이에 대해 집계를 다시 계산하지 않습니다. 엔진은 가장 최근 새로 고침 기간(예: 30분)에 대해 쿼리를 실행하고 이를 추가합니다. 구체화에.

마찬가지로 이 마지막 기간 동안 수행된 UPDATE 및 DELETE 가 식별되어 관련 청크(파티션)를 다시 계산합니다. (Timescale을 기반으로 구축된 연속 집계 하이퍼테이블 , 자동으로 분할된 PostgreSQL 테이블입니다. 이는 데이터가 변경되었을 때 엔진이 전체 테이블이 아닌 특정 파티션만 다시 계산할 수 있다는 점에서 엄청난 이점입니다.)

실시간 분석을 위한 최신 결과 표시

그러나 연속 집계는 최신 결과를 보는 문제를 어떻게 해결합니까? 마지막 새로 고침 후 새 데이터가 추가되었으며 연속 집계를 쿼리하면 어떻게 되나요?

이 기능을 허용하기 위해 우리는 다음을 추가했습니다. 실시간 집계 기능 연속 집계로. 실시간 집계가 활성화된 경우 , 연속 집계를 쿼리하면 표시되는 결과는 두 부분을 결합한 것입니다.

• 마지막 새로 고침에서 업데이트된 기본 구체화된 뷰의 구체화된 데이터입니다.
• 아직 구체화되지 않은 가장 최근의 원시 데이터로, 여전히 기본 테이블(정확히 말하면 하이퍼테이블)에만 존재합니다.

이 기능은 구체화된 뷰를 정적 스냅샷에서 동적 엔터티로 변환하여 저장된 데이터가 단순한 기록 반영이 아니라 기본 데이터 세트의 최신 표현임을 보장합니다.

연속 집계 사용: 예

이 모든 것이 좋아 보이더라도 예를 들어보면 (희망적으로) 훨씬 더 잘 어울릴 것입니다.

운송 기관과 차량 공유 회사가 사용하는 플랫폼을 상상해 보세요. 이 플랫폼에는 주요 지표의 최신 상태가 포함된 테이블과 특정 요일 및 해당 주의 컨텍스트 내에서 지표가 어떻게 작동하는지 보여주는 두 개의 시각화를 포함하여 회사가 차량 상태에 대한 개요를 볼 수 있는 대시보드가 포함되어 있습니다.

이 애플리케이션을 강화하기 위해 먼저 탑승에 대한 데이터가 지속적으로 삽입되는 하이퍼테이블이 있어야 합니다. 하이퍼테이블은 다음과 같이 보일 수 있습니다.

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

하이퍼테이블은 매우 빠르고 확장성이 뛰어납니다. 이 테이블은 수십억 개의 행이 있어도 성능을 유지합니다.

실시간 개요를 제공하여 테이블을 강화하기 위해 연속 집계를 사용하여 30분 단위로 데이터를 버킷화합니다. 이렇게 하면 프로세스가 빠르고 반응적으로 유지됩니다.

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

이전 코드에서 end_offset 매개변수는 집계가 최신 데이터 새로 고침을 즉시 시도하지 않도록 하여 데이터 도착 시 지연을 수용할 수 있는 버퍼 시간을 허용합니다. end_offset 10 minutes 으로 설정하면 집계가 최소 10분 이전의 데이터를 새로 고쳐 데이터 유입의 사소한 지연으로 인해 업데이트가 누락되지 않도록 보장합니다. 실제 사용 사례에서는 데이터 파이프라인에서 관찰되는 평균 지연을 기준으로 이 값을 조정합니다.

일별 보기를 제공하는 시각화를 강화하기 위해 두 번째 연속 집계를 생성합니다. 이 차트에서는 데이터가 시간 단위로 표시되므로 이전 차트처럼 분당 세부사항이 필요하지 않습니다.

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

마지막으로 주간 보기를 제공하는 차트를 강화하기 위해 연속 집계를 하나 더 생성합니다. 이번에는 데이터를 일별로 집계합니다.

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS 연속 집계 정의 경험을 더욱 효율적으로 만들기 위해, Timescale은 계층적 연속 집계를 도입했습니다. TimescaleDB 2.9에서. 연속 집계에 익숙해지면 다른 연속 집계 위에 이를 생성할 수 있습니다. 예를 들어 이전 예에서는 분별 집계 위에 시간별 집계를 정의할 수도 있습니다.

결론

PostgreSQL이 원래 대규모 라이브 데이터 세트를 처리해야 하는 애플리케이션용으로 구축되지 않았더라도 추측해 보세요. 이러한 유형의 워크로드는 이제 어디에나 있습니다. 그러나 PostgreSQL에는 이 작업에 도움이 되는 기능이 제공됩니다. 구체화된 뷰는 쿼리 결과를 미리 계산하고 빠른 검색을 위해 디스크에 저장할 수 있으므로 가장 강력한 뷰 중 하나입니다.

그러나 구체화된 뷰에는 세 가지 중요한 제한 사항이 있습니다. 첫째, 새로 고침을 트리거하는 것은 계산적으로 매우 비효율적입니다. 둘째, 이러한 자동 새로 고침을 설정하는 것조차 원활한 프로세스가 아닙니다. 셋째, 구체화된 뷰는 마지막 새로 고침 이후 추가되거나 수정된 데이터를 제외하므로 최신 결과를 표시하지 않습니다.

이러한 제한으로 인해 구체화된 뷰는 많은 최신 응용 프로그램에 대한 비실용적인 솔루션이 됩니다. 이를 해결하기 위해 우리는 연속 집계를 구축했습니다. 이는 새로 고침 정책을 쉽게 정의할 수 있는 PostgreSQL 구체화된 뷰이므로 새로 고침이 자동으로 수행됩니다. 이러한 새로 고침은 점진적이므로 훨씬 더 효율적입니다. 마지막으로, 연속 집계를 사용하면 구체화된 데이터를 마지막 새로 고침 이후 추가 및 수정된 원시 데이터와 결합하여 최신 결과만 얻을 수 있습니다.

하드웨어에서 PostgreSQL을 실행 중인 경우 다음을 통해 연속 집계에 액세스할 수 있습니다. TimescaleDB 확장 설치 . AWS에 있다면 Timescale 플랫폼을 확인해 보세요. . 처음 30일은 무료입니다.

Carlota Soto 와 Mat Arye가 각본을 맡았습니다.