이 자습서에서는 탄력적 클러스터와 함께 Azure Database for PostgreSQL을 사용하여 수평 스케일 아웃의 이점을 활용하는 다중 테넌트 애플리케이션을 디자인하는 방법을 알아봅니다.
- 필수 구성 요소
- psql 유틸리티를 사용하여 스키마 만들기
- 노드 간 테이블 분할
- 샘플 데이터 수집
- 테넌트 데이터 쿼리
- 테넌트 간 데이터 공유
- 테넌트별 스키마 사용자 지정
필수 구성 요소
다음 방법 중 하나로 탄력적 클러스터를 만듭니다.
psql 유틸리티를 사용하여 스키마 만들기
psql을 사용하여 탄력적 클러스터에 연결한 후 탄력적 클러스터를 구성할 수 있습니다. 이 자습서에서는 회사에서 광고 캠페인을 추적할 수 있는 애플리케이션 플랫폼을 만드는 방법을 안내합니다.
참고
클러스터 간에 데이터를 배포할 때, 고유 데이터 제약 조건은 해당 분포 내의 "분할"에 범위가 지정됩니다. 다중 테넌트 예제에서 애플리케이션 데이터 고유성은 테넌트별로 적용됩니다(예: 회사 ID). 이러한 이유로 기본 및 외래 키 제약 조건에 대한 분산 테이블 정의에는 항상 회사 ID 열이 포함됩니다.
다중 테넌트 회사 정보를 보관할 테이블과 해당 캠페인에 대한 다른 테이블을 만듭니다. psql 콘솔에서 다음 명령을 실행합니다.
CREATE TABLE companies (
id bigserial PRIMARY KEY,
name text NOT NULL,
image_url text,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);
CREATE TABLE campaigns (
id bigserial,
company_id bigint REFERENCES companies (id),
name text NOT NULL,
cost_model text NOT NULL,
state text NOT NULL,
monthly_budget bigint,
blocked_site_urls text[],
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
PRIMARY KEY (company_id, id)
);
각 캠페인은 광고 실행에 대한 비용을 지불합니다. 다음 코드를 사용하여 psql에 광고 테이블을 추가합니다.
CREATE TABLE ads (
id bigserial,
company_id bigint,
campaign_id bigint,
name text NOT NULL,
image_url text,
target_url text,
impressions_count bigint DEFAULT 0,
clicks_count bigint DEFAULT 0,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
PRIMARY KEY (company_id, id),
FOREIGN KEY (company_id, campaign_id)
REFERENCES campaigns (company_id, id)
);
마지막으로 광고 선택 및 노출에 따라 통계를 추적하려고 합니다.
CREATE TABLE clicks (
id bigserial,
company_id bigint,
ad_id bigint,
clicked_at timestamp without time zone NOT NULL,
site_url text NOT NULL,
cost_per_click_usd numeric(20,10),
user_ip inet NOT NULL,
user_data jsonb NOT NULL,
PRIMARY KEY (company_id, id),
FOREIGN KEY (company_id, ad_id)
REFERENCES ads (company_id, id)
);
CREATE TABLE impressions (
id bigserial,
company_id bigint,
ad_id bigint,
seen_at timestamp without time zone NOT NULL,
site_url text NOT NULL,
cost_per_impression_usd numeric(20,10),
user_ip inet NOT NULL,
user_data jsonb NOT NULL,
PRIMARY KEY (company_id, id),
FOREIGN KEY (company_id, ad_id)
REFERENCES ads (company_id, id)
);
이제 다음을 실행하여 psql에서 새로 만든 테이블을 볼 수 있습니다.
\dt
노드 간 테이블 분할
이 시점까지 표준 Postgres 테이블을 만들었지만 궁극적으로 탄력적 클러스터에 분산 테이블을 만들어야 합니다. 탄력적 클러스터 내의 분산 테이블은 배포 열에서 정의한 값에 따라 다른 노드에 데이터를 저장합니다. 이 열은 기본 작업자 노드 전반에 걸쳐 행의 위치를 결정하는 데 사용됩니다.
다중 테넌트 식별자 역할을 하는 company_id 배포 열을 설정합니다. psql에서 다음 함수를 실행합니다.
SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');
SELECT create_distributed_table('ads', 'company_id');
SELECT create_distributed_table('clicks', 'company_id');
SELECT create_distributed_table('impressions', 'company_id');
참고
Azure Database for PostgreSQL 성능 기능을 사용하여 탄력적 클러스터를 활용하려면 테이블을 배포해야 합니다. 테이블 및 스키마를 배포하지 않는 한 클러스터 노드는 분산 쿼리 또는 작업에 참여하지 않습니다.
샘플 데이터 수집
psql 외부에서 일반 명령줄에서 샘플 데이터 집합을 다운로드합니다.
for dataset in companies campaigns ads clicks impressions geo_ips; do
curl -O https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-elastic-clusters/main/multi-tenant/${dataset}.csv
done
psql로 다시 돌아가서 데이터를 대량으로 로드합니다. psql은 데이터 파일을 다운로드한 것과 동일한 디렉터리에서 실행해야 합니다.
SET client_encoding TO 'UTF8';
\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv
이제 분산 테이블 내의 데이터가 탄력적 클러스터 작업자 노드에 분산됩니다.
테넌트 데이터 쿼리
애플리케이션이 특정 회사에 대한 데이터를 요청하는 경우 데이터베이스는 이제 적절한 작업자 노드에서 쿼리를 효율적으로 실행할 수 있습니다. 예를 들어 다음 쿼리(company_id = 5)는 광고 및 노출을 필터링합니다. 결과를 보려면 psql에서 실행해 보세요.
SELECT a.campaign_id,
RANK() OVER (
PARTITION BY a.campaign_id
ORDER BY a.campaign_id, count(*) DESC
), count(*) AS n_impressions, a.id
FROM ads AS a
JOIN impressions AS i
ON i.company_id = a.company_id
AND i.ad_id = a.id
WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions DESC;
테넌트 간 데이터 공유
지금까지 모든 테이블을 company_id에 의해 클러스터에 배포했습니다. 그러나 일부 유형의 데이터는 기본적으로 모든 테넌트에 "속"하며 모든 테넌트 배포와 함께 배치할 수 있습니다. 예를 들어 광고 플랫폼의 모든 회사는 IP 주소 세부 정보를 기반으로 대상 그룹에 대한 지리적 정보를 가져올 수 있습니다.
이 지리적 IP 정보를 저장할 참조 테이블을 만듭니다. psql에서 다음 명령을 실행합니다.
CREATE TABLE geo_ips (
addrs cidr NOT NULL PRIMARY KEY,
latlon point NOT NULL
CHECK (-90 <= latlon[0] AND latlon[0] <= 90 AND
-180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);
다음으로 geo_ips을 "참조 테이블"로 식별합니다. 클러스터는 모든 클러스터된 작업자 노드에 동기화된 테이블을 저장하여 이 테이블을 관리합니다.
SELECT create_reference_table('geo_ips');
이제 샘플 데이터를 사용하여 참조 테이블을 로드합니다. 데이터 세트 파일을 다운로드한 디렉터리에서 이 명령을 실행해야 합니다.
\copy geo_ips from 'geo_ips.csv' with csv
선택한 테이블을 geo_ips 조인하는 SQL 문은 이제 모든 노드에서 효율적입니다. 이 조인을 통해 광고 290에서 선택한 모든 IP의 위치를 찾을 수 있습니다. psql에서 쿼리를 실행해 보세요.
SELECT c.id, clicked_at, latlon
FROM geo_ips, clicks c
WHERE addrs >> c.user_ip
AND c.company_id = 5
AND c.ad_id = 290;
테넌트당 스키마 사용자 지정
경우에 따라 개별 테넌트는 다른 테넌트에 필요하지 않은 특수 정보를 저장해야 할 수 있습니다. 그러나 모든 테넌트는 동일한 데이터베이스 스키마와 공통 정의를 공유합니다. 추가 데이터는 어디에 배치할 수 있나요?
한 가지 솔루션은 PostgreSQL의 JSONB와 같은 유연한 열 형식을 사용하는 것입니다. 스키마의 clicks에는 user_data라는 JSONB 필드가 있습니다. 회사(예: 회사 5)는 이 열을 사용하여 사용자가 모바일 디바이스에 있는지 여부에 대한 정보를 추적할 수 있습니다.
다음은 회사 5가 모바일 또는 전통적인 방문자를 더 많이 선택하는 것을 찾기 위해 사용할 수 있는 쿼리입니다.
SELECT
user_data->>'is_mobile' AS is_mobile,
count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;
PostgreSQL에는 데이터의 특정 부분 또는 하위 집합을 인덱싱할 수 있는 강력한 기능이 포함되어 있습니다. 부분 인덱스 만들기를 통해 회사 5에 대한 쿼리를 추가로 최적화할 수 있습니다.
CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;
또한 JSONB 열을 포함하는 쿼리에서 성능을 향상시키는 또 다른 방법은 JSONB 열 내의 모든 기본 키 및 값에 대해 GIN 인덱 스를 만드는 것입니다.
CREATE INDEX click_user_data
ON clicks USING gin (user_data);
-- this speeds up queries like, "which clicks have the is_mobile key present in user_data?"
SELECT id
FROM clicks
WHERE user_data ? 'is_mobile'
AND company_id = 5;