Multi-tenant Applications

In this tutorial, we will use a sample ad analytics dataset to demonstrate how you can use Citus to power your multi-tenant application.

Note

This tutorial assumes that you already have Citus installed and running. If you don’t have Citus running, you can setup Citus locally using one of the options from Single-Node Citus.

Data model and sample data

We will demo building the database for an ad-analytics app which companies can use to view, change, analyze and manage their ads and campaigns (see an example app). Such an application has good characteristics of a typical multi-tenant system. Data from different tenants is stored in a central database, and each tenant has an isolated view of their own data.

We will use three Postgres tables to represent this data. To get started, you will need to download sample data for these tables:

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

If you are using Docker, you should use the docker cp command to copy the files into the Docker container.

  1. docker cp companies.csv citus:.
  2. docker cp campaigns.csv citus:.
  3. docker cp ads.csv citus:.

Creating tables

To start, you can first connect to the Citus coordinator using psql.

If you are using native Postgres, as installed in our Single-Node Citus guide, the coordinator node will be running on port 9700.

  1. psql -p 9700

If you are using Docker, you can connect by running psql with the docker exec command:

  1. docker exec -it citus_master psql -U postgres

Then, you can create the tables by using standard PostgreSQL CREATE TABLE commands.

  1. CREATE TABLE companies (
  2.     id bigint NOT NULL,
  3.     name text NOT NULL,
  4.     image_url text,
  5.     created_at timestamp without time zone NOT NULL,
  6.     updated_at timestamp without time zone NOT NULL
  7. );
  9. CREATE TABLE campaigns (
  10.     id bigint NOT NULL,
  11.     company_id bigint NOT NULL,
  12.     name text NOT NULL,
  13.     cost_model text NOT NULL,
  14.     state text NOT NULL,
  15.     monthly_budget bigint,
  16.     blacklisted_site_urls text[],
  17.     created_at timestamp without time zone NOT NULL,
  18.     updated_at timestamp without time zone NOT NULL
  19. );
  21. CREATE TABLE ads (
  22.     id bigint NOT NULL,
  23.     company_id bigint NOT NULL,
  24.     campaign_id bigint NOT NULL,
  25.     name text NOT NULL,
  26.     image_url text,
  27.     target_url text,
  28.     impressions_count bigint DEFAULT 0,
  29.     clicks_count bigint DEFAULT 0,
  30.     created_at timestamp without time zone NOT NULL,
  31.     updated_at timestamp without time zone NOT NULL
  32. );

Next, you can create primary key indexes on each of the tables just like you would do in PostgreSQL

  1. ALTER TABLE companies ADD PRIMARY KEY (id);
  2. ALTER TABLE campaigns ADD PRIMARY KEY (id, company_id);
  3. ALTER TABLE ads ADD PRIMARY KEY (id, company_id);

Distributing tables and loading data

We will now go ahead and tell Citus to distribute these tables across the different nodes we have in the cluster. To do so, you can run create_distributed_table and specify the table you want to shard and the column you want to shard on. In this case, we will shard all the tables on the company_id.

  1. SELECT create_distributed_table('companies', 'id');
  2. SELECT create_distributed_table('campaigns', 'company_id');
  3. SELECT create_distributed_table('ads', 'company_id');

Sharding all tables on the company identifier allows Citus to colocate the tables together and allow for features like primary keys, foreign keys and complex joins across your cluster. You can learn more about the benefits of this approach here.

Then, you can go ahead and load the data we downloaded into the tables using the standard PostgreSQL \COPY command. Please make sure that you specify the correct file path if you downloaded the file to some other location.

  1. \copy companies from 'companies.csv' with csv
  2. \copy campaigns from 'campaigns.csv' with csv
  3. \copy ads from 'ads.csv' with csv

Running queries

Now that we have loaded data into the tables, let’s go ahead and run some queries. Citus supports standard INSERT, UPDATE and DELETE commands for inserting and modifying rows in a distributed table which is the typical way of interaction for a user-facing application.

For example, you can insert a new company by running:

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

If you want to double the budget for all the campaigns of a company, you can run an UPDATE command:

  1. UPDATE campaigns
  2. SET monthly_budget = monthly_budget*2
  3. WHERE company_id = 5;

Another example of such an operation would be to run transactions which span multiple tables. Let’s say you want to delete a campaign and all its associated ads, you could do it atomically by running:

  1. BEGIN;
  2. DELETE FROM campaigns WHERE id = 46 AND company_id = 5;
  3. DELETE FROM ads WHERE campaign_id = 46 AND company_id = 5;
  4. COMMIT;

Each statement in a transactions causes roundtrips between the coordinator and workers in multi-node Citus. For multi-tenant workloads, it’s more efficient to run transactions in distributed functions. The efficiency gains become more apparent for larger transactions, but we can use the small transaction above as an example.

First create a function that does the deletions:

  1. CREATE OR REPLACE FUNCTION
  2.   delete_campaign(company_id int, campaign_id int)
  3. RETURNS void LANGUAGE plpgsql AS $fn$
  4. BEGIN
  5.   DELETE FROM campaigns
  6.    WHERE id = $2 AND campaigns.company_id = $1;
  7.   DELETE FROM ads
  8.    WHERE ads.campaign_id = $2 AND ads.company_id = $1;
  9. END;
  10. $fn$;

Next use create_distributed_function to instruct Citus to run the function directly on workers rather than on the coordinator (except on a single-node Citus installation, which runs everything on the coordinator). It will run the function on whatever worker holds the Shards for tables ads and campaigns corresponding to the value company_id.

  1. SELECT create_distributed_function(
  2.   'delete_campaign(int, int)', 'company_id',
  3.   colocate_with := 'campaigns'
  4. );
  6. -- you can run the function as usual
  7. SELECT delete_campaign(5, 46);

Besides transactional operations, you can also run analytics queries using standard SQL. One interesting query for a company to run would be to see details about its campaigns with maximum budget.

  1. SELECT name, cost_model, state, monthly_budget
  2. FROM campaigns
  3. WHERE company_id = 5
  4. ORDER BY monthly_budget DESC
  5. LIMIT 10;

We can also run a join query across multiple tables to see information about running campaigns which receive the most clicks and impressions.

  1. SELECT campaigns.id, campaigns.name, campaigns.monthly_budget,
  2.        sum(impressions_count) as total_impressions, sum(clicks_count) as total_clicks
  3. FROM ads, campaigns
  4. WHERE ads.company_id = campaigns.company_id
  5. AND campaigns.company_id = 5
  6. AND campaigns.state = 'running'
  7. GROUP BY campaigns.id, campaigns.name, campaigns.monthly_budget
  8. ORDER BY total_impressions, total_clicks;

With this, we come to the end of our tutorial on using Citus to power a simple multi-tenant application. As a next step, you can look at the Multi-Tenant Apps section to see how you can model your own data for multi-tenancy.