Multitenancy Query Patterns in SQL: Schema-Per-Tenant, Shared Tables, and Efficient Cross-Tenant Reporting Strategies

Introduction

You're three months into building a B2B SaaS platform for project management. You've got a dozen pilot customers, everything's running on a single database, and then your biggest enterprise prospect asks the question you've been dreading: "How do you guarantee my data is completely isolated from other customers?" Meanwhile, your head of operations is asking you why the monthly cross-customer usage report takes eleven minutes to run.

These two problems — data isolation and cross-tenant analytics — sit at the heart of multitenant database design. Get the isolation model wrong and you're either over-engineering for scale you don't have yet, or you're one bug away from leaking Customer A's data to Customer B. Get the reporting wrong and your business operations team builds dashboards in spreadsheets because the SQL is too slow or too painful to maintain.

By the end of this lesson, you'll understand the three major architectural patterns for multitenant SQL databases, know how to write queries that are both secure and efficient within each model, and have practical strategies for cross-tenant reporting that won't collapse under real data volumes. We'll build real examples from a SaaS project management scenario throughout — the kind you'd actually encounter in production.

What you'll learn:

• The trade-offs between schema-per-tenant, shared-table, and hybrid multitenancy models
• How to write tenant-safe queries and prevent cross-tenant data leakage at the SQL layer
• Indexing and partitioning strategies specific to multitenant workloads
• Cross-tenant reporting patterns including conditional aggregation and union-based approaches
• How row-level security policies complement application-layer tenant filtering

---

Prerequisites

You should be comfortable with JOINs, subqueries, GROUP BY aggregation, and basic indexing concepts. Familiarity with CTEs will help — if you need a refresher, the Common Table Expressions (CTEs) for Cleaner SQL article is a solid foundation. You should also have a working understanding of how indexes affect query performance.

---

The Three Multitenant Models (and When Each Makes Sense)

Before writing a single query, you need to understand what database structure you're working with. The pattern you choose shapes every query you'll ever write against this system.

Model 1: Shared Database, Shared Tables

Every tenant's data lives in the same tables. A tenant_id column on every table acts as the partition boundary. This is the most common approach for early-stage and mid-scale SaaS.

-- The canonical shared-table schema
CREATE TABLE projects (
    project_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id       UUID NOT NULL,
    name            VARCHAR(255) NOT NULL,
    status          VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    owner_user_id   BIGINT NOT NULL
);

CREATE TABLE tasks (
    task_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id       UUID NOT NULL,
    project_id      BIGINT NOT NULL,
    title           VARCHAR(500) NOT NULL,
    assignee_id     BIGINT,
    due_date        DATE,
    completed_at    TIMESTAMPTZ,
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

CREATE TABLE users (
    user_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id       UUID NOT NULL,
    email           VARCHAR(320) NOT NULL,
    display_name    VARCHAR(255) NOT NULL,
    role            VARCHAR(50) NOT NULL DEFAULT 'member',
    UNIQUE (tenant_id, email)
);

Pros: Simple to build, easy to query across tenants for analytics, cheap to operate at lower scale.

Cons: One missing WHERE tenant_id = ? clause exposes every customer's data. Schema migrations affect all tenants simultaneously.

Model 2: Schema-Per-Tenant

Each tenant gets their own PostgreSQL schema (or MySQL database). The table structures are identical, but they're completely isolated at the schema level.

-- Creating a new tenant schema
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Each schema gets the same tables
CREATE TABLE tenant_acme.projects (
    project_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    status          VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    owner_user_id   BIGINT NOT NULL
    -- No tenant_id needed — the schema IS the tenant
);

CREATE TABLE tenant_acme.tasks (
    task_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id      BIGINT NOT NULL,
    title           VARCHAR(500) NOT NULL,
    assignee_id     BIGINT,
    due_date        DATE,
    completed_at    TIMESTAMPTZ
);

Pros: Strong isolation, impossible to accidentally query across tenant boundaries in single-schema queries, per-tenant schema migrations are possible.

Cons: Cross-tenant reporting requires UNION ALL across every schema (painful), schema count grows with your customer count, connection pooling becomes complex.

Model 3: Database-Per-Tenant

Each tenant gets an entirely separate database instance. This is the highest isolation model and is common in regulated industries (healthcare, finance) or for enterprise customers paying for dedicated infrastructure.

We won't focus heavily on this model for query patterns — most SQL logic within a single tenant's database is straightforward. The real complexity here is in routing, provisioning, and the near-impossibility of efficient cross-tenant reporting.

Note: In practice, most SaaS companies use a hybrid: shared tables for the majority of customers and a dedicated schema or database for enterprise clients who require contractual data isolation. Design your query patterns to accommodate this.

---

Writing Tenant-Safe Queries in Shared Tables

The shared-table model is where most query bugs live. Let's get specific about how to write queries that are structurally safe.

Always Lead with `tenant_id`

The single most important habit: every query against a multitenant table should have tenant_id in the WHERE clause, and it should be the first filter. This isn't just about security — it's about index utilization.

-- BAD: Finds the project by ID, but doesn't scope to tenant
-- If project_id 4821 belongs to a different tenant, you just leaked data
SELECT p.project_id, p.name, p.status
FROM projects p
WHERE p.project_id = 4821;

-- GOOD: tenant_id scopes the query before anything else
SELECT p.project_id, p.name, p.status
FROM projects p
WHERE p.tenant_id = '3f7a1b2c-8e4d-4f91-b3a2-9c7d5e6f1234'
  AND p.project_id = 4821;

This sounds obvious, but in production code where project_id comes from a URL parameter and tenant_id comes from a session, it's easy to skip the tenant check when you're iterating fast.

Composite Indexes: The Right Column Order Matters

For shared-table multitenancy, your index strategy is fundamentally different from single-tenant databases. Every significant query will filter by tenant_id first, so your indexes should reflect that.

-- Instead of a single-column index on project_id:
CREATE INDEX idx_projects_project_id ON projects(project_id);
-- This helps a global lookup but does nothing for tenant-scoped queries

-- Use composite indexes with tenant_id first:
CREATE INDEX idx_projects_tenant_status 
    ON projects(tenant_id, status, created_at DESC);

CREATE INDEX idx_tasks_tenant_project 
    ON tasks(tenant_id, project_id);

CREATE INDEX idx_tasks_tenant_assignee_due 
    ON tasks(tenant_id, assignee_id, due_date)
    WHERE completed_at IS NULL;  -- Partial index for active tasks only

The partial index on completed_at IS NULL is particularly powerful in task-management scenarios — in most systems, the overwhelming majority of tasks are completed, so the active-task index stays small and fast.

Key insight: In a shared-table multitenant system with 500 tenants and 10 million total rows, a tenant-scoped query that uses (tenant_id, status) composite index might scan 20,000 rows. The same query without tenant_id leading the index might scan all 10 million rows before filtering. This is the difference between a 5ms query and a 3-second query. Refer to Indexing Fundamentals for Query Performance for the mechanics of why column order in composite indexes matters so much.

Joining Across Multitenant Tables Safely

When you join multiple tables, every table in the join needs its tenant_id checked. Here's a realistic example: getting a summary of open tasks per project for a specific tenant.

-- A safe multi-table join in a shared-table model
SELECT
    p.project_id,
    p.name                                          AS project_name,
    COUNT(t.task_id)                                AS total_tasks,
    COUNT(t.task_id) FILTER (WHERE t.completed_at IS NULL)  AS open_tasks,
    COUNT(DISTINCT t.assignee_id)                   AS assigned_members
FROM projects p
LEFT JOIN tasks t
    ON t.project_id = p.project_id
   AND t.tenant_id = p.tenant_id  -- Explicitly scope the join condition
WHERE p.tenant_id = '3f7a1b2c-8e4d-4f91-b3a2-9c7d5e6f1234'
  AND p.status = 'active'
GROUP BY p.project_id, p.name
ORDER BY open_tasks DESC;

Notice t.tenant_id = p.tenant_id in the JOIN condition itself, not just in the WHERE clause. This is belt-and-suspenders safety: even if foreign key relationships are properly set up, explicitly including the tenant_id in both sides of the join documents your intent and gives the query optimizer more information to work with.

---

Schema-Per-Tenant Query Patterns

When you're in a schema-per-tenant architecture, single-tenant queries are beautifully simple — no tenant_id anywhere. The complexity is in cross-tenant operations.

Setting the Search Path

In PostgreSQL, you route a connection to the correct tenant schema by setting search_path. This is typically done at the application layer when a session is established.

-- At session start, after authenticating the tenant:
SET search_path TO tenant_acme, public;

-- Now all unqualified table references resolve to tenant_acme
SELECT project_id, name, status
FROM projects  -- resolves to tenant_acme.projects
WHERE status = 'active';

Warning: SET search_path affects the entire session. In connection-pooled applications where connections are shared between requests (PgBouncer, for example), you must reset the search_path at the start of every request or use SET LOCAL inside a transaction. Failing to do this is one of the nastiest data leakage bugs you can introduce — silently serving one tenant's data to another.

Using SET LOCAL inside a transaction is safer:

BEGIN;
SET LOCAL search_path TO tenant_globex, public;

SELECT p.project_id, p.name, COUNT(t.task_id) AS task_count
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.project_id
WHERE p.status = 'active'
GROUP BY p.project_id, p.name;

COMMIT;
-- After COMMIT, search_path reverts to session default

Fully Qualified References for Admin Queries

When you're writing administrative queries that span schemas — schema migrations, health checks, provisioning scripts — always use fully qualified schema.table names:

-- Schema provisioning: copy a template schema structure
-- (run by an admin, not an end-user request)
SELECT
    s.schema_name,
    COUNT(t.table_name)    AS table_count
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t
    ON t.table_schema = s.schema_name
WHERE s.schema_name LIKE 'tenant\_%' ESCAPE '\'
GROUP BY s.schema_name
ORDER BY s.schema_name;

---

Cross-Tenant Reporting: The Hard Problem

Here's where things get genuinely interesting. Your operations team wants a dashboard showing: total active projects per tenant, average task completion time by tenant, and which tenants are churning based on activity decline. In a shared-table model, this is SQL you can write. In a schema-per-tenant model, it requires some creativity.

Cross-Tenant Reports in Shared Tables

This is the shared-table model's biggest advantage. Cross-tenant aggregation is just aggregation without the tenant_id filter.

-- Platform-wide tenant activity summary
-- This would power an internal admin dashboard
WITH tenant_activity AS (
    SELECT
        p.tenant_id,
        COUNT(DISTINCT p.project_id)                            AS total_projects,
        COUNT(DISTINCT CASE WHEN p.status = 'active' 
                            THEN p.project_id END)              AS active_projects,
        COUNT(t.task_id)                                        AS total_tasks,
        COUNT(t.task_id) FILTER (WHERE t.completed_at IS NULL)  AS open_tasks,
        MAX(t.completed_at)                                     AS last_task_completion,
        MAX(p.created_at)                                       AS last_project_created
    FROM projects p
    LEFT JOIN tasks t 
        ON t.project_id = p.project_id
       AND t.tenant_id = p.tenant_id
    GROUP BY p.tenant_id
),
tenant_health AS (
    SELECT
        ta.tenant_id,
        ta.total_projects,
        ta.active_projects,
        ta.total_tasks,
        ta.open_tasks,
        ROUND(
            (ta.open_tasks::NUMERIC / NULLIF(ta.total_tasks, 0)) * 100, 
            1
        )                                                       AS open_task_pct,
        GREATEST(ta.last_task_completion, ta.last_project_created) AS last_activity_at,
        CASE
            WHEN GREATEST(ta.last_task_completion, ta.last_project_created) 
                 < now() - INTERVAL '30 days' THEN 'at_risk'
            WHEN GREATEST(ta.last_task_completion, ta.last_project_created) 
                 < now() - INTERVAL '14 days' THEN 'declining'
            ELSE 'healthy'
        END                                                     AS health_status
    FROM tenant_activity ta
)
SELECT *
FROM tenant_health
ORDER BY health_status, last_activity_at;

This query cleanly separates the aggregation logic (in tenant_activity) from the business classification logic (in tenant_health), making both easier to read and test. See Common Table Expressions (CTEs) for Cleaner SQL for more on this structuring pattern.

Conditional Aggregation for Cross-Tenant Comparison

When you need to compare tenants side by side — for example, showing how each tenant's task completion rate compares to the platform average — conditional aggregation gives you a clean single-pass approach.

-- Compare each tenant's completion rate to platform average
-- in a single table scan
SELECT
    tenant_id,
    COUNT(task_id)                                          AS total_tasks,
    COUNT(task_id) FILTER (WHERE completed_at IS NOT NULL)  AS completed_tasks,
    ROUND(
        100.0 * COUNT(task_id) FILTER (WHERE completed_at IS NOT NULL)
        / NULLIF(COUNT(task_id), 0),
        1
    )                                                       AS completion_rate_pct,
    ROUND(
        100.0 * SUM(COUNT(task_id) FILTER (WHERE completed_at IS NOT NULL)) OVER ()
        / NULLIF(SUM(COUNT(task_id)) OVER (), 0),
        1
    )                                                       AS platform_avg_completion_pct
FROM tasks
GROUP BY tenant_id
ORDER BY completion_rate_pct DESC;

The window function SUM(...) OVER () with no partition clause computes the platform total across all tenants while preserving the per-tenant rows — a powerful pattern you can see explained in depth in Aggregating Across Groups with SQL Window Functions.

Cross-Tenant Reports in Schema-Per-Tenant (UNION ALL Pattern)

This is where schema-per-tenant makes you pay. To query across tenants, you need to UNION ALL across every tenant schema. In production, this is usually generated dynamically.

-- Statically written version (impractical at scale, but shows the pattern)
SELECT 'acme' AS tenant_slug, project_id, name, status, created_at
FROM tenant_acme.projects
WHERE status = 'active'

UNION ALL

SELECT 'globex' AS tenant_slug, project_id, name, status, created_at
FROM tenant_globex.projects
WHERE status = 'active'

UNION ALL

SELECT 'initech' AS tenant_slug, project_id, name, status, created_at
FROM tenant_initech.projects
WHERE status = 'active';

With 10 tenants this is manageable. With 500, it's not. The real solution is to generate this SQL dynamically, which we cover in the next section.

Dynamic Cross-Schema Reporting with Dynamic SQL

In PostgreSQL, you can use plpgsql to build and execute a UNION ALL query across all tenant schemas at runtime. This approach is covered extensively in Dynamic SQL: Writing and Executing Parameterized Queries at Runtime, but here's a concrete multitenant example:

-- Function to generate a cross-tenant active project count
CREATE OR REPLACE FUNCTION platform_active_project_summary()
RETURNS TABLE(tenant_slug TEXT, active_project_count BIGINT)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_sql        TEXT := '';
    v_separator  TEXT := '';
    v_schema     RECORD;
BEGIN
    -- Discover all tenant schemas dynamically
    FOR v_schema IN
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name LIKE 'tenant\_%' ESCAPE '\'
        ORDER BY schema_name
    LOOP
        v_sql := v_sql || v_separator ||
                 format(
                     'SELECT %L::TEXT AS tenant_slug, COUNT(*)::BIGINT AS active_project_count FROM %I.projects WHERE status = ''active''',
                     substring(v_schema.schema_name FROM 8),  -- strip 'tenant_' prefix
                     v_schema.schema_name
                 );
        v_separator := ' UNION ALL ';
    END LOOP;

    IF v_sql = '' THEN
        RETURN;
    END IF;

    RETURN QUERY EXECUTE v_sql;
END;
$$;

-- Usage:
SELECT * FROM platform_active_project_summary()
ORDER BY active_project_count DESC;

Warning: Dynamic SQL generation in schema-per-tenant systems can become a bottleneck as your tenant count grows. With 1,000 tenant schemas, a UNION ALL across all of them may take many seconds and put enormous pressure on the query planner. Consider maintaining a dedicated reporting database with a shared-table structure that's populated by CDC (change data capture) from your tenant schemas, so analytics queries don't touch production data at all.

---

Row-Level Security: Defense in Depth

Neither application-layer tenant filtering nor indexing strategies are substitutes for proper database-level row isolation. PostgreSQL Row-Level Security (RLS) lets you enforce tenant isolation directly in the database engine — so even if your application has a bug that omits a WHERE tenant_id = ? clause, the database itself enforces the filter.

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create a policy that uses the current app.tenant_id setting
CREATE POLICY tenant_isolation_policy ON projects
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Your application sets this at session start:
-- SET app.tenant_id = '3f7a1b2c-8e4d-4f91-b3a2-9c7d5e6f1234';

-- After that, any query against projects is automatically filtered:
SELECT * FROM projects;
-- Equivalent to: SELECT * FROM projects WHERE tenant_id = '3f7a...'

Admin roles that need to bypass RLS (for cross-tenant reporting) can do so explicitly:

-- Grant bypass to the analytics role
ALTER ROLE analytics_role BYPASSRLS;

-- Or use a separate policy for admins
CREATE POLICY admin_bypass_policy ON projects
    TO admin_role
    USING (true);  -- Admins see everything

For a comprehensive treatment of RLS, dynamic data masking, and permission-based filtering, see the dedicated lesson on Advanced SQL Security: Row-Level Security, Dynamic Data Masking, and Permission-Based Query Filtering.

---

Partitioning for High-Volume Multitenant Tables

When your shared tables grow into hundreds of millions of rows, composite indexes alone may not be enough. Table partitioning by tenant_id (or by a combination of tenant_id and time) can dramatically improve query performance by allowing the planner to skip entire partitions.

-- Create a partitioned tasks table (PostgreSQL declarative partitioning)
CREATE TABLE tasks (
    task_id         BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id       UUID NOT NULL,
    project_id      BIGINT NOT NULL,
    title           VARCHAR(500) NOT NULL,
    assignee_id     BIGINT,
    due_date        DATE,
    completed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY LIST (tenant_id);

-- Each high-volume tenant gets their own partition
CREATE TABLE tasks_tenant_acme
    PARTITION OF tasks
    FOR VALUES IN ('3f7a1b2c-8e4d-4f91-b3a2-9c7d5e6f1234');

CREATE TABLE tasks_tenant_globex
    PARTITION OF tasks
    FOR VALUES IN ('8a9b2c3d-1e5f-4g67-h8i9-0j1k2l3m4n5o');

-- Remaining tenants share a default partition
CREATE TABLE tasks_default
    PARTITION OF tasks DEFAULT;

With this setup, a query filtered to a single tenant_id will only scan that tenant's partition — giving you the physical isolation of schema-per-tenant with the query simplicity of shared tables.

Key insight: Partitioning is most effective when your tenant data is highly skewed — a handful of large enterprise tenants generating 80% of your row volume. Partition those tenants individually; let everyone else share a default partition. This pattern is covered in detail in Partitioning Strategies in SQL: Using Table Partitioning and Partition Pruning to Accelerate Queries on Large Datasets.

---

Real-World Project: Tenant Usage Analytics Report

Let's build something you'd actually deploy. Imagine your head of customer success wants a weekly email with: each tenant's activity summary for the past 30 days, their trend vs. the previous 30 days, and a churn risk flag.

We'll build this step by step against the shared-table model.

-- Step 1: Per-tenant activity for the current and previous 30-day window
WITH period_activity AS (
    SELECT
        t.tenant_id,
        COUNT(t.task_id) FILTER (
            WHERE t.created_at >= now() - INTERVAL '30 days'
        )                                                       AS tasks_current_period,
        COUNT(t.task_id) FILTER (
            WHERE t.created_at >= now() - INTERVAL '60 days'
            AND   t.created_at <  now() - INTERVAL '30 days'
        )                                                       AS tasks_previous_period,
        COUNT(t.task_id) FILTER (
            WHERE t.completed_at >= now() - INTERVAL '30 days'
        )                                                       AS completions_current,
        COUNT(DISTINCT t.assignee_id) FILTER (
            WHERE t.created_at >= now() - INTERVAL '30 days'
        )                                                       AS active_users_current
    FROM tasks t
    GROUP BY t.tenant_id
),

-- Step 2: Compute trend and classify
tenant_trends AS (
    SELECT
        pa.tenant_id,
        pa.tasks_current_period,
        pa.tasks_previous_period,
        pa.completions_current,
        pa.active_users_current,
        CASE
            WHEN pa.tasks_previous_period = 0 AND pa.tasks_current_period > 0
                THEN NULL  -- New tenant, no valid comparison
            WHEN pa.tasks_previous_period = 0
                THEN -100.0
            ELSE ROUND(
                100.0 * (pa.tasks_current_period - pa.tasks_previous_period)
                / pa.tasks_previous_period::NUMERIC,
                1
            )
        END                                                     AS task_volume_change_pct,
        CASE
            WHEN pa.tasks_current_period = 0                    THEN 'inactive'
            WHEN pa.tasks_current_period < pa.tasks_previous_period * 0.5
                                                                THEN 'high_risk'
            WHEN pa.tasks_current_period < pa.tasks_previous_period * 0.8
                                                                THEN 'declining'
            ELSE                                                     'stable'
        END                                                     AS churn_risk
    FROM period_activity pa
),

-- Step 3: Add platform percentile rank for each tenant
ranked_tenants AS (
    SELECT
        tt.*,
        PERCENT_RANK() OVER (
            ORDER BY tt.tasks_current_period
        )                                                       AS activity_percentile
    FROM tenant_trends tt
)

SELECT
    tenant_id,
    tasks_current_period,
    tasks_previous_period,
    task_volume_change_pct,
    completions_current,
    active_users_current,
    churn_risk,
    ROUND(activity_percentile * 100, 0)                         AS activity_percentile_rank
FROM ranked_tenants
ORDER BY
    CASE churn_risk
        WHEN 'inactive'  THEN 1
        WHEN 'high_risk' THEN 2
        WHEN 'declining' THEN 3
        ELSE                  4
    END,
    task_volume_change_pct ASC;

This query is a single pass over the tasks table. The conditional aggregation in period_activity computes both time windows simultaneously rather than joining two separate subqueries — avoiding a potentially expensive self-join. The window function in ranked_tenants adds platform-wide context without a second query. For more on combining these patterns for analytical work, see SQL for Data Analysis: Cohort Analysis, Funnels, and Retention.

---

Hands-On Exercise

You're building a multitenant invoicing module for the project management platform. The schema looks like this:

CREATE TABLE invoices (
    invoice_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id       UUID NOT NULL,
    client_name     VARCHAR(255) NOT NULL,
    amount_cents    BIGINT NOT NULL,
    currency        CHAR(3) NOT NULL DEFAULT 'USD',
    status          VARCHAR(50) NOT NULL,  -- 'draft', 'sent', 'paid', 'overdue'
    issued_date     DATE NOT NULL,
    due_date        DATE NOT NULL,
    paid_at         TIMESTAMPTZ
);

Exercise tasks:

1. Write a query for a single tenant (tenant_id = 'abc123...') that returns: total invoiced amount, total collected amount, outstanding amount, and count of overdue invoices (where due_date < today and status != 'paid').

2. Write a cross-tenant admin query that ranks all tenants by their overdue invoice total, showing only tenants with more than $1,000 USD overdue.

3. Add appropriate composite indexes for this table, justifying each one with the query pattern it supports.

4. Extend your query from task #1 to include a month-by-month breakdown of invoiced vs. collected amounts for the past 12 months. Use window functions to also show the running cumulative collected amount.

---

Common Mistakes & Troubleshooting

Mistake 1: Implicit Tenant from Application Context, Not SQL

Some teams rely entirely on their ORM or application framework to inject tenant_id, and then write "clean" SQL without it for ad-hoc analytics. This creates two code paths with different security properties — and the unsafe path is the one your DBA runs when debugging at 2am.

Fix: Always include tenant_id in SQL, even in contexts where you're confident of the application-level filter. Add RLS as a backstop.

Mistake 2: Cross-Tenant Foreign Keys in Shared Tables

-- This constraint doesn't scope to the same tenant:
ALTER TABLE tasks
    ADD CONSTRAINT fk_tasks_projects
    FOREIGN KEY (project_id) REFERENCES projects(project_id);

-- In theory, this allows task(tenant_id='acme', project_id=500)
-- to reference project(tenant_id='globex', project_id=500)

Fix: Use composite foreign keys that include tenant_id:

ALTER TABLE tasks
    ADD CONSTRAINT fk_tasks_projects
    FOREIGN KEY (tenant_id, project_id) 
    REFERENCES projects(tenant_id, project_id);

-- This requires (tenant_id, project_id) to be unique in projects:
CREATE UNIQUE INDEX uidx_projects_tenant_project 
    ON projects(tenant_id, project_id);

Mistake 3: Reporting Queries Hitting Production OLTP Tables

Cross-tenant analytical queries are inherently expensive — they scan large portions of shared tables without tenant partition pruning. Running these on your primary database during business hours will hurt application performance.

Fix: Use a read replica for analytics queries, or better, maintain a separate analytics schema populated by an incremental load pipeline. The patterns for this are covered in Incremental Query Design with Watermark Tables and Change Data Capture.

Mistake 4: Forgetting `tenant_id` in Aggregation with HAVING

-- This finds projects with more than 50 tasks across ALL tenants
-- A project_id could appear in multiple tenants' data
SELECT project_id, COUNT(*) AS task_count
FROM tasks
GROUP BY project_id
HAVING COUNT(*) > 50;

-- Correct: include tenant_id in GROUP BY
SELECT tenant_id, project_id, COUNT(*) AS task_count
FROM tasks
GROUP BY tenant_id, project_id
HAVING COUNT(*) > 50;

Warning: This mistake is particularly insidious because the query returns results that look plausible. If project_id values happen to be unique across tenants (because they're generated by a global sequence), you'll get the right numbers — until you migrate to UUIDs per-tenant or run a data import that creates ID collisions.

Mistake 5: Slow Search Path Switches in High-Throughput Schema-Per-Tenant Systems

If you're switching search_path on every request in a schema-per-tenant system, you're paying a small but non-trivial cost per connection handshake. Under high concurrency, this adds up.

Fix: Use SET LOCAL inside transactions (shown above), or use fully qualified table names with connection-specific roles that have a default search_path baked into the role definition:

CREATE ROLE tenant_acme_app LOGIN PASSWORD 'secret';
ALTER ROLE tenant_acme_app SET search_path TO tenant_acme, public;

This way the search_path is set at connection creation by the server, with no runtime overhead per query.

---

Summary & Next Steps

Multitenancy is fundamentally a design constraint that shapes every query you write. Here's what we covered:

• Shared tables give you query simplicity and cheap cross-tenant analytics, but require disciplined tenant_id filtering and composite indexes with tenant_id leading every relevant index.
• Schema-per-tenant provides strong physical isolation and clean single-tenant queries, but makes cross-tenant reporting complex and operationally expensive.
• Hybrid approaches — shared tables for most tenants, isolated schemas or partitions for enterprise clients — are the practical production reality at scale.
• Row-level security is defense in depth, not a replacement for application-level filtering, but it catches bugs that application code will inevitably miss.
• Cross-tenant reporting in shared tables is a SQL aggregation problem; in schema-per-tenant systems, it's a dynamic SQL generation problem with real performance limits.

Where to go next:

If you're building the analytics layer on top of a multitenant system, the patterns in Query Optimization with Materialized Views are essential — pre-aggregating cross-tenant summaries into materialized views and refreshing them on a schedule keeps your reporting fast without hammering production tables. If you're worried about query correctness and want to build automated verification, Writing Effective SQL Unit Tests will show you how to codify tenant-isolation checks into your CI pipeline so regressions get caught before they reach production.