Quick Start Guide: Building a Multi‑Tenant Ad Analytics Database with Distributed PostgreSQL (Citus)

Overview

The guide demonstrates how to use Citus to support a multi‑tenant ad‑analytics application, showing both a batch‑oriented ad‑management scenario and a real‑time GitHub event analytics scenario.

Data Model and Sample Data

Three PostgreSQL tables ( companies, campaigns, ads) model the ad‑analytics domain. Sample CSV files are downloaded with curl and, if Docker is used, copied into the container via docker cp.

curl https://examples.citusdata.com/tutorial/companies.csv > companies.csv
curl https://examples.citusdata.com/tutorial/campaigns.csv > campaigns.csv
curl https://examples.citusdata.com/tutorial/ads.csv > ads.csv

For the real‑time example, two tables ( github_events, github_users) are defined, and CSVs are fetched similarly.

curl https://examples.citusdata.com/tutorial/users.csv > users.csv
curl https://examples.citusdata.com/tutorial/events.csv > events.csv

Create Tables

Connect to the Citus coordinator (native PostgreSQL or Docker) with psql and run CREATE TABLE statements. Primary‑key indexes are added to each table.

CREATE TABLE companies (
    id bigint NOT NULL,
    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 bigint NOT NULL,
    company_id bigint NOT NULL,
    name text NOT NULL,
    cost_model text NOT NULL,
    state text NOT NULL,
    monthly_budget bigint,
    blacklisted_site_urls text[],
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

CREATE TABLE ads (
    id bigint NOT NULL,
    company_id bigint NOT NULL,
    campaign_id bigint NOT NULL,
    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
);

ALTER TABLE companies ADD PRIMARY KEY (id);
ALTER TABLE campaigns ADD PRIMARY KEY (id, company_id);
ALTER TABLE ads ADD PRIMARY KEY (id, company_id);

For the GitHub schema, similar CREATE TABLE statements are used, followed by a GIN index on the payload JSONB column.

CREATE INDEX event_type_index ON github_events (event_type);
CREATE INDEX payload_index ON github_events USING GIN (payload jsonb_path_ops);

Distribute Tables and Load Data

Tables are sharded by the tenant identifier ( company_id for ads, user_id for GitHub data) using create_distributed_table. This colocates related tables on the same worker nodes, enabling efficient joins and foreign‑key enforcement.

SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');
SELECT create_distributed_table('ads', 'company_id');

SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');

Data is loaded with the PostgreSQL \COPY command (or docker cp followed by \COPY inside the container).

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv

\copy github_users from 'users.csv' with csv
\copy github_events from 'events.csv' with csv

Run Queries

Standard DML statements ( INSERT, UPDATE, DELETE) work across distributed tables. Examples include inserting a new company, doubling a company's campaign budget, and deleting a campaign with its ads inside a transaction.

INSERT INTO companies VALUES (5000, 'New Company', 'https://randomurl/image.png', now(), now());

UPDATE campaigns SET monthly_budget = monthly_budget * 2 WHERE company_id = 5;

BEGIN;
DELETE FROM campaigns WHERE id = 46 AND company_id = 5;
DELETE FROM ads WHERE campaign_id = 46 AND company_id = 5;
COMMIT;

A user‑defined function delete_campaign is created and distributed to workers with create_distributed_function, allowing the function to run directly on the appropriate shards.

CREATE OR REPLACE FUNCTION delete_campaign(company_id int, campaign_id int)
RETURNS void LANGUAGE plpgsql AS $fn$
BEGIN
  DELETE FROM campaigns WHERE id = $2 AND company_id = $1;
  DELETE FROM ads WHERE campaign_id = $2 AND company_id = $1;
END;
$fn$;

SELECT create_distributed_function('delete_campaign(int, int)', 'company_id', colocate_with := 'campaigns');
SELECT delete_campaign(5, 46);

Analytical queries demonstrate typical use cases: retrieving the top campaigns by budget, aggregating impressions and clicks, and, for the GitHub dataset, counting commits per minute and finding the most active repository creators.

SELECT name, cost_model, state, monthly_budget
FROM campaigns
WHERE company_id = 5
ORDER BY monthly_budget DESC
LIMIT 10;

SELECT campaigns.id, campaigns.name, campaigns.monthly_budget,
       sum(impressions_count) AS total_impressions,
       sum(clicks_count) AS total_clicks
FROM ads, campaigns
WHERE ads.company_id = campaigns.company_id
  AND campaigns.company_id = 5
  AND campaigns.state = 'running'
GROUP BY campaigns.id, campaigns.name, campaigns.monthly_budget
ORDER BY total_impressions, total_clicks;

SELECT date_trunc('minute', created_at) AS minute,
       sum((payload->>'distinct_size')::int) AS num_commits
FROM github_events
WHERE event_type = 'PushEvent'
GROUP BY minute
ORDER BY minute;

SELECT login, count(*)
FROM github_events ge
JOIN github_users gu ON ge.user_id = gu.user_id
WHERE event_type = 'CreateEvent' AND payload @> '{"ref_type": "repository"}'
GROUP BY login
ORDER BY count(*) DESC
LIMIT 10;

Real‑Time Application Analytics

The same workflow is applied to a real‑time dashboard using the public GitHub events stream. By distributing the github_events and github_users tables on user_id, the system can ingest high‑volume event data and answer sub‑second analytical queries.

Next Steps

After completing the tutorial, readers can explore further sections on multi‑tenant data modeling, migration of existing applications, time‑series data handling, and best practices for choosing distribution columns.