Find out 📣 who is speaking 📣 at POSETTE: An Event for Postgres 2024.
Update in October 2022: The Citus managed database service is now available in Azure Cosmos DB for PostgreSQL. If you’re looking to distribute PostgreSQL in the cloud (for multi-tenant SaaS apps, or real-time analytics, or time series workloads), take a look at Azure Cosmos DB for PostgreSQL. And as always, the Citus database is also available as open source: you can find the Citus repo on GitHub or download Citus here.
One of the primary challenges with scaling SaaS applications is the database. While you can easily scale your application by adding more servers, scaling your database is a way harder problem. This is particularly true if your application benefits from relational database features, such as transactions, table joins, and database constraints.
At Citus, we make scaling your database easy. Over the past year, we added support for distributed transactions, made Rails and Django integration seamless, and expanded on our SQL support. We also documented approaches to scaling your SaaS database to thousands of customers.
Today, we’re excited to announce the latest release of our distributed database—Citus 7.5. With this release, we’re adding key features that make scaling your SaaS / multi-tenant database easier. If you’re into bulleted lists, these features include the following.
What’s New in Citus 7.5
- Foreign key constraints from distributed to reference tables
- SELECT .. FOR UPDATE
- Per-customer (per-tenant) query statistics (aka Landlord)
- Advanced Security: Row and column level access controls
- Native integration with PostgreSQL extensions: HLL and TopN
To try these new features, you can download Citus packages on your local machine or create a Citus distributed database cluster on Citus Cloud. Or, keep reading to learn more about 7.5.
Example SaaS Application
Let’s write an ad analytics app which companies can use to view, change, and analyze their ads. Such an application has the characteristics of a typical SaaS / multi-tenant application. Data from different tenants is stored in a central database, and each tenant has an isolated view of their data.
First, you create two distributed tables using the standard PostgreSQL syntax.
CREATE TABLE companies (
id bigint NOT NULL,
name text NOT NULL,
image_url text,
CONSTRAINT pk_company_id PRIMARY KEY (id)
);
CREATE TABLE campaigns (
id bigint NOT NULL,
company_id bigint NOT NULL,
country_id int NOT NULL,
name text NOT NULL,
cost_model text,
monthly_budget bigint,
created_at timestamp without time zone NOT NULL,
CONSTRAINT pk_campaign_company_id PRIMARY KEY (id, company_id),
CONSTRAINT fk_campaign_company_id FOREIGN KEY (company_id) REFERENCES companies (id)
);
SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');
Foreign keys to reference tables
Now, your application needs to capture information about different countries, where you’re running ads and campaigns. For that, you create a small table. This table isn’t associated with different tenants, so we make it a reference table.
CREATE TABLE countries (
id bigint NOT NULL,
country_name text NOT NULL,
tax_rate numeric NOT NULL,
CONSTRAINT pk_country_id PRIMARY KEY (id)
);
SELECT create_reference_table('countries');
The campaigns table is related to the countries table through the country_id. To enforce that relationship, you need to create a foreign key constraint from the distributed table to the reference table. With Citus 7.5, that constraint is automatically enforced for your application.
-- create foreign key from distributed table to reference table
-- for different syntax to create foreign keys, please see https://stackoverflow.com/questions/28558920/postgresql-foreign-key-syntax
ALTER TABLE campaigns
ADD CONSTRAINT fk_campaign_country_id
FOREIGN KEY (country_id) REFERENCES countries (id);
Now that we have our relationships defined, our application can start inserting data into the Citus database cluster.
SELECT … FOR UPDATE
After about six months, your application logic starts getting complex. At some point, you start giving promotional credits to companies. This way, companies can start by running free experiments on your platform. To do this, you add two new columns to your table definition.
ALTER TABLE companies ADD COLUMN credit_amount bigint;
ALTER TABLE campaigns ADD COLUMN credits_used boolean;
Then, you need to ensure when you start a new ad campaign, there’s no condition that allows someone to spend the company credits multiple times.
In Ruby on Rails, you can use with_lock and lock methods to guard against such race conditions. (Django has get_or_create and update_or_create that require similar semantics from the database.)
So in your Rails application, you change the function that creates a new campaign. You add to this function with_lock and lock! calls to guard the critical section that applies promotional credits. The locking calls then translate to SELECT … FOR UPDATE in SQL.
Citus 7.5 now comes with support for SELECT … FOR UPDATE statements that operate on a tenant’s data. The following code block shows an example in SQL.
BEGIN;
-- lock rows related to a company’s balance before running app logic
SELECT * FROM companies WHERE id = 123 FOR UPDATE;
SELECT * FROM campaigns WHERE company_id = 123 AND id = 456 FOR UPDATE;
-- client application now verifies that sufficient credit is available
UPDATE companies SET credit_amount = credit_amount-60 WHERE id = 123;
UPDATE campaigns SET credits_used WHERE company_id = 123 AND id = 456;
COMMIT;
Landlord: Per-customer (per-tenant) query statistics
After another year, your SaaS business takes off. You now serve many customers and each customer is represented as a tenant in your database. With all these tenants, you now need a way to monitor each tenant. This way, you can find your noisy tenants and better serve your important ones. This is where Landlord comes in.
Citus 7.5 introduces Landlord, a feature that automatically provides statistics about each tenant. This feature is available in Citus Cloud and Citus Enterprise editions.
For example, your sales team would like to sell enterprise contracts to your most valuable customers. These are customers who actively use your SaaS features, and as a result, your database.
To see your most active customers and their queries, you enable citus_stat_statements. You then query this table.
SELECT * FROM citus_stat_statements ORDER BY calls LIMIT 3;
queryid | userid | dbid | query | executor | partition_key | calls
-----------+--------+-------+----------------------------------------+----------+---------------+-------
462021987 | 19064 | 16385 | INSERT INTO campaigns ($1, $2, $3, $4) | router | 970 | 9875
660713276 | 19064 | 16385 | INSERT INTO campaigns ($1, $2, $3, $4) | router | 56 | 66
183740289 | 19064 | 16385 | INSERT INTO campaigns ($1, $2, $3, $4) | router | 14 | 41
You see that your tenant 970 is way more active than other ones. Your sales team reaches out to this customer to sell an enterprise contract. As part of this contract, this customer wants to have quality of service guarantees. To provide those guarantees, you can further use Citus’ tenant isolation feature. With tenant isolation, you can give to this customer their dedicated hardware resources.
Row Level Security
Over time, your enterprise customers want their dedicated support person. For example, they would like this support person to be able to see ad campaign history and also delete inactive campaigns.
For this, you hire a new support person named Alice. You use Citus Cloud or Citus Enterprise to create a new role for her. Then, you set up Row Level Security (RLS) to grant Alice access to this enterprise customer’s campaigns.
-- create a policy with a distribution column filter
-- this allows Alice to only see campaigns for customer 970
ALTER TABLE campaigns ENABLE ROW LEVEL SECURITY;
CREATE POLICY support_policy ON campaigns TO alice
USING (company_id = 970) WITH CHECK (company_id = 970);
-- allow Alice to read and delete from the table according to the policy
GRANT SELECT, DELETE ON TABLE campaigns TO alice;
Now for a bit of magic. When Alice connects to Citus, all her queries get magically scoped to that customer. This is because the RLS policy automatically adds the distribution column to the query, even if Alice doesn’t.
-- when Alice runs a query, she only sees orders for tenant = 970
SELECT id, company_id, name, cost_model, monthly_budget, created_at
FROM campaigns WHERE monthly_budget > 100000;
id | company_id | name | cost_model | monthly_budget | created_at
----+------------+--------------+---------------------+-----------------+----------------------------
53 | 970 | Starfire | cost_per_impression | 103871 | 2017-02-17 00:09:51.792667
84 | 970 | Cy-Gor | cost_per_impression | 105443 | 2017-02-17 00:10:04.59091
96 | 970 | Toad Ivy | cost_per_impression | 107429 | 2017-02-17 00:10:15.098363
-- Alice can also delete old campaigns from the database
-- She can’t add new campaigns or change existing ones
DELETE FROM campaigns WHERE created_at <= '2015-01-01';
DELETE 112
Masking Sensitive Columns for Reporting
As you have more enterprise customers, you build a customer insights team. This team runs analytics queries on your database to better understand customer behavior. However, your customers don’t want your analysts to see sensitive data—for example, each campaign’s monthly budget.
This type of requirement also comes up when SaaS apps want to restrict access to Personally Identifiable Information (PII). To mask access to certain columns, you can use heavyweight ETL processes that mask PII bits. Fortunately, Postgres provides a few primitives to quickly hide PII columns within your database.
Citus 7.5 provides these same primitives for your scalable SaaS database. These features are available as part of Citus Cloud and Citus Enterprise. Now, let’s see how we’d hide the monthly budget column from our internal analysts.
CREATE SCHEMA reporting;
CREATE USER analysts;
REVOKE ALL privileges ON SCHEMA public FROM analysts;
Now, we’re going to create a view on the campaigns data. This view hides the monthly budget column. We then grant read access to our analysts on this view.
CREATE VIEW reporting.campaigns
AS SELECT id, company_id, name, cost_model, created_at FROM campaigns;
GRANT USAGE ON SCHEMA reporting TO analysts;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analysts;
Native Integration With Postgres Extensions: HLL and TopN
Citus 7.5 adds many exciting features that makes scaling your SaaS / multi-tenant database easy. At the same time, many of you are using Citus as a real-time analytics database. In those workloads, you want to ingest large volumes of data and get immediate insights out of that data.
Postgres has two extensions that allow you to get instant approximate results from the underlying data. HyperLogLog (HLL) provides fast count(distinct) approximations. TopN returns the top values in the database according to some criteria. When you wanted to parallelize computations with these extensions, you previously had to define an extra aggregate function in Citus.
Now, you can directly use the aggregate functions in HLL and TopN. Citus 7.5 will automatically parallelize these computations across the machines in the cluster for you.
Let us Know What You Think About Citus
We’re super excited to release Citus 7.5. We also love the amazing applications our customers are building on top of Citus. Citus is available as open source, as enterprise software, and in the cloud as a managed database service.
If you give the Citus extension to Postgres a try, we’d love to hear your feedback. Feel free to join the conversation in the Citus Slack channel and let us know what you think.
