PostgreSQL Features Used by Supabase

Supabase is an open source platform that offers ready-to-use backend functionality, based on PostgreSQL as the data management system. PostgreSQL is a powerful and feature-rich relational database, many of which are leveraged by Supabase to offer a modern and scalable platform. In this article, we examine the main PostgreSQL features used by Supabase.

1. PostgreSQL Extensions

Supabase enables numerous PostgreSQL extensions to enhance database functionality. Among the most relevant:

  • pgcrypto: for encryption and secure UUID generation.
  • postgis: for advanced geospatial support.
  • pgroonga: for multilingual full-text search.
  • http: to make HTTP requests directly from the database.
-- Enabling an extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. Row-Level Security (RLS)

One of the fundamental features of Supabase is row-level security. RLS allows defining granular policies on who can read or write specific rows.

-- Enable RLS on a table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Define a policy
CREATE POLICY "Users can view their messages"
  ON messages
  FOR SELECT
  USING (user_id = auth.uid());

3. SQL Functions and RPC

Supabase exposes PostgreSQL-defined functions as Remote Procedure Calls (RPC) via its RESTful API. This allows writing business logic directly in the database.

-- Create an SQL function
CREATE FUNCTION get_user_profile(uid uuid)
RETURNS TABLE(name text, email text)
AS $$
  SELECT name, email FROM profiles WHERE id = uid;
$$ LANGUAGE sql STABLE;

4. Triggers and PL/pgSQL Functions

Triggers allow automating actions in response to data events such as inserts, updates, or deletions.

-- Trigger to update the timestamp
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

5. Real-Time Replication with Logical Decoding

Supabase uses logical replication to implement realtime functionality. Thanks to this, every database change can be listened to by clients in real time.

PostgreSQL emits change events (INSERT, UPDATE, DELETE) through a publication channel that can be consumed by the Supabase Realtime Server.

6. JSON and JSONB Types

PostgreSQL supports JSON and JSONB data types, allowing storage and querying of semi-structured data. Supabase leverages these capabilities to offer greater flexibility in data schema.

-- Querying JSONB data
SELECT data->>'name'
FROM users
WHERE data->'settings'->>'theme' = 'dark';

7. Partitioning and Performance

PostgreSQL offers partitioning tools that Supabase can use to scale large databases by logically splitting tables into manageable subsections.

Conclusion

Thanks to PostgreSQL's powerful features, Supabase manages to offer a modern, secure, and highly customizable platform. Extensions, advanced security, JSON support, server-side functions, and realtime capabilities are just some of the features that make PostgreSQL the ideal choice for an open source backend like Supabase.

Back to top