Advanced functionality of Supabase

The Advanced Supabase Guide: Database Features, Multi-Tenancy, and Scalability

This guide delves into the advanced functionality of Supabase, exploring database features, security, and best practices for building robust, scalable applications.

---

Real-time: Trade-offs and How It Works

Supabase's real-time functionality provides a powerful way to build dynamic, responsive applications. However, it's crucial to understand how it works under the hood to appreciate its trade-offs.

How It Works

Supabase's real-time feature is built on PostgreSQL's logical replication [1]. Specifically, it uses a mechanism called wal2json, which reads the database's Write-Ahead Log (WAL) to capture all changes (INSERTs, UPDATEs, DELETEs) as they happen.

1. WAL Reading: The realtime server, which is an Elixir/Phoenix application, connects to the database as a logical replication client.
2. Change Capture: As a transaction commits in PostgreSQL, the changes are written to the WAL. The realtime server captures these changes and decodes them into JSON payloads using wal2json.
3. Broadcasting: The realtime server then broadcasts these JSON payloads to all connected clients that are subscribed to the relevant tables or channels. This is done via WebSockets [2].

Trade-offs

• Asynchronous Nature: Real-time updates are not instantaneous. There can be a slight delay (latency) between a change occurring in the database and a client receiving the update. This is typically in milliseconds but can be affected by network conditions and server load.
• Performance Impact: While logical replication is efficient, a large number of concurrent real-time subscriptions on a very busy database can add a minor load. For most applications, this is not an issue, but it's a consideration for high-scale, write-heavy use cases [1].
• Security: Real-time updates must respect Row-Level Security (RLS) policies. If a user does not have permission to read a specific row, they will not receive a real-time update for it.

In addition to table changes, Supabase's real-time features also include Broadcast, which allows any connected client to send messages to all others in a channel, and Presence, which tracks the online status of users in real-time [2].

---

Complex SQL Queries and CTEs: The RPC Approach

The Supabase client SDKs are designed to interact with tables via a RESTful API, which simplifies standard CRUD operations but doesn't natively support complex SQL like Common Table Expressions (CTEs) or JOINs across multiple tables.

The recommended best practice for this is to wrap your complex queries inside a PostgreSQL function, which you can then call from your client-side application as a Remote Procedure Call (RPC) [3].

Example: Using a CTE for a Leaderboard

Let's say you want to build a leaderboard by calculating the total score for each user.

Step 1: Write the PostgreSQL function

In the Supabase SQL Editor, create a function. This example uses a CTE to rank users based on their total score from a scores table.

CREATE OR REPLACE FUNCTION get_leaderboard()
RETURNS TABLE (
  user_id uuid,
  username text,
  total_score bigint,
  rank bigint
) AS $$
  WITH user_scores AS (
    SELECT
      user_id,
      SUM(score) AS total_score
    FROM
      scores
    GROUP BY
      user_id
  )
  SELECT
    us.user_id,
    u.username,
    us.total_score,
    RANK() OVER (ORDER BY us.total_score DESC) AS rank
  FROM
    user_scores us
  JOIN
    users u ON us.user_id = u.id
  ORDER BY
    rank ASC;
$$ LANGUAGE SQL IMMUTABLE;

Step 2: Call the function from the client

Using the Supabase JavaScript SDK, you can call this function using the .rpc() method.

const { data, error } = await supabase
  .rpc('get_leaderboard');

if (error) console.error(error);
else console.log(data);

This approach is secure, as you can also apply Row-Level Security to these functions, ensuring only authorized users can execute them.

---

Row-Level Security (RLS) and Public Data Access

It is a common requirement to allow unauthenticated users to view some data (e.g., blog posts) while restricting sensitive data and write access to authenticated users. This is achieved by creating different RLS policies for different roles.

Supabase defines two key roles:

• anon: The unauthenticated, public role.
• authenticated: The role for signed-in users.

Example: Public blog posts

Assume you have a posts table. To allow anyone to read the posts but only authenticated users to create them, you would set up the following policies:

Policy 1: Allow public read access

-- Enables RLS on the table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Allow anyone to read posts
CREATE POLICY "Allow public read access" ON posts
  FOR SELECT
  USING (true);

The USING (true) condition means the policy always evaluates to true, so anyone can select any row.

Policy 2: Allow authenticated users to create their own posts

CREATE POLICY "Allow authenticated users to create posts" ON posts
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

The auth.uid() function retrieves the ID of the currently authenticated user. This policy ensures that a user can only insert a row if the user_id column of that row matches their own ID.

---

Multi-Tenancy Patterns

Multi-tenancy is the practice of serving multiple tenants (customers) from a single application instance. In Supabase, the most common and secure pattern is using a tenant_id column and enforcing RLS [4].

1. Shared Database, Tenant ID Column: Every table that needs to be multi-tenant has a tenant_id column.
2. Enforce RLS: You must enable RLS on every multi-tenant table.
3. Create a Policy: Create a policy that restricts access to a tenant's data based on the current user's tenant_id.

Example: Multi-tenant projects table

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

-- Create a policy for multi-tenancy
CREATE POLICY "Access your own tenant's projects" ON projects
  FOR ALL
  USING (
    auth.uid() IN (SELECT user_id FROM user_tenant WHERE tenant_id = current_setting('app.tenant_id')::uuid)
  );

This policy assumes a user_tenant mapping table and uses a PostgreSQL current_setting to securely store and retrieve the tenant ID for the session [4]. This approach ensures complete data isolation.

---

JSON Fields, Full-Text Search, and Extensions

• JSON Fields: Supabase supports PostgreSQL's json and jsonb data types. The jsonb type is highly recommended as it stores data in a decomposed binary format, allowing for efficient indexing and advanced querying using native JSON operators [5]. You can query nested data directly in your API calls:

  const { data } = await supabase
    .from('users')
    .select('name')
    .eq('preferences->theme', 'dark');

• Full-Text Search: PostgreSQL has powerful built-in full-text search capabilities. The best way to use this is to create a dedicated tsvector column and a GIN index on it [6].

  -- Add a tsvector column
  ALTER TABLE articles ADD COLUMN fts_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || content)
  ) STORED;
  
  -- Create a GIN index for fast search
  CREATE INDEX articles_fts_idx ON articles USING GIN (fts_vector);

  You can then query this column from your application.

• Extensions: Supabase provides a wide range of pre-installed extensions, including pg_cron for scheduling tasks and pgvector for vector embeddings [7]. You can enable them directly from the Supabase UI or with a simple CREATE EXTENSION command in the SQL Editor. While you can't add custom extensions not on the Supabase list, the available ones cover a vast range of use cases [7].

---

Regional Replication and Read Replicas

Supabase offers Read Replicas on its Pro, Team, and Enterprise plans to enhance performance for global applications [8].

• How it Works: A Read Replica is an asynchronous copy of your primary database. Write operations (INSERTs, UPDATEs) go to the primary database, and the changes are then replicated to the read replicas.
• Use Case: Read replicas are perfect for read-heavy workloads and for reducing read latency for users in different geographic regions. You can set up a read replica in a region closer to a large user base to serve read requests from there, improving perceived performance.
• Trade-off: The key trade-off is asynchronous replication. This means there can be a small delay between a write on the primary and it being available on a replica. For this reason, all write operations should still be directed to the primary database [8].

---

Sources

1. Supabase. "Realtime | Supabase Docs". https://supabase.com/docs/guides/realtime
2. Supabase. "The Realtime Engine | Supabase Docs". https://supabase.com/docs/guides/realtime/concepts
3. Supabase. "Remote Procedure Calls (RPC) | Supabase Docs". https://supabase.com/docs/guides/database/functions
4. Supabase. "Multi-tenancy with RLS | Supabase Docs". https://supabase.com/docs/guides/database/multi-tenancy
5. Supabase. "PostgreSQL JSONB | Supabase Docs". https://supabase.com/docs/guides/database/postgres/jsonb
6. Supabase. "Full Text Search | Supabase Docs". https://supabase.com/docs/guides/database/full-text-search
7. Supabase. "PostgreSQL Extensions | Supabase Docs". https://supabase.com/docs/guides/database/extensions
8. Supabase. "Read Replicas | Supabase Docs". https://supabase.com/docs/guides/database/read-replicas