Database

Learn about Supabase Database - a full PostgreSQL database with real-time capabilities, row-level security, and powerful extensions.

Every Supabase project comes with a full PostgreSQL database, one of the world’s most stable and advanced relational databases. Supabase extends Postgres with real-time functionality, automatic APIs, and a suite of powerful extensions.

Architecture

Supabase Database is built on PostgreSQL with several key components:

• PostgreSQL Core: Your database runs on a dedicated Postgres instance with full postgres level access
• PostgREST: Automatically generates a RESTful API from your database schema
• pg_graphql: Provides a GraphQL API for your data
• Realtime Server: Broadcasts database changes via WebSockets
• postgres-meta: A RESTful API for database management operations

All database operations go through the Kong API gateway, which handles authentication, rate limiting, and routing to the appropriate service.

Core Concepts

Tables and Schemas

PostgreSQL organizes data into tables within schemas. Supabase creates several schemas by default:

• public: Your application tables (exposed via auto-generated APIs)
• auth: User authentication data (managed by Supabase Auth)
• storage: File metadata (managed by Supabase Storage)
• extensions: PostgreSQL extensions
• realtime: Real-time configuration

You can create tables using SQL or the Supabase Dashboard:

create table posts (
  id bigint generated by default as identity primary key,
  title text not null,
  content text,
  author_id uuid references auth.users(id),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Row Level Security (RLS)

RLS is PostgreSQL’s built-in authorization system that allows you to control access to individual rows in your tables. Supabase enables RLS by default on new tables.

-- Enable RLS
alter table posts enable row level security;

-- Create a policy that allows users to read all posts
create policy "Posts are viewable by everyone"
  on posts for select
  using (true);

-- Create a policy that allows users to insert their own posts
create policy "Users can create their own posts"
  on posts for insert
  with check (auth.uid() = author_id);

-- Create a policy that allows users to update their own posts
create policy "Users can update their own posts"
  on posts for update
  using (auth.uid() = author_id);

Without RLS policies, tables are not accessible via the auto-generated APIs. You must create policies to allow access.

Relationships

PostgreSQL supports foreign keys to define relationships between tables:

create table comments (
  id bigint generated by default as identity primary key,
  post_id bigint references posts(id) on delete cascade,
  content text not null,
  author_id uuid references auth.users(id),
  created_at timestamp with time zone default now()
);

The Supabase Dashboard provides a visual interface to explore relationships and navigate between related data.

Indexes

Indexes improve query performance by allowing the database to find data without scanning every row:

-- Index for faster lookups by author
create index posts_author_id_idx on posts(author_id);

-- Index for faster sorting by creation date
create index posts_created_at_idx on posts(created_at desc);

-- Composite index for complex queries
create index comments_post_author_idx on comments(post_id, author_id);

Auto-generated APIs

Supabase automatically generates RESTful and GraphQL APIs from your database schema:

REST API (PostgREST)

// Read all posts
const { data, error } = await supabase
  .from('posts')
  .select('*')

// Read posts with author and comments
const { data, error } = await supabase
  .from('posts')
  .select(`
    *,
    author:auth.users(*),
    comments(*)
  `)

// Insert a new post
const { data, error } = await supabase
  .from('posts')
  .insert({
    title: 'My First Post',
    content: 'Hello World!',
    author_id: user.id
  })

GraphQL API

query {
  postsCollection {
    edges {
      node {
        id
        title
        content
        commentsCollection {
          edges {
            node {
              content
            }
          }
        }
      }
    }
  }
}

Database Functions

You can create custom functions in PostgreSQL to encapsulate business logic:

create or replace function get_post_count(author uuid)
returns bigint
language sql
security definer
as $$
  select count(*)
  from posts
  where author_id = author;
$$;

Call functions from your client:

const { data, error } = await supabase
  .rpc('get_post_count', { author: user.id })

Triggers

Triggers automatically execute functions in response to database events:

-- Function to update a timestamp
create or replace function handle_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- Trigger that calls the function
create trigger set_updated_at
  before update on posts
  for each row
  execute function handle_updated_at();

Extensions

Supabase provides access to over 50 PostgreSQL extensions. Enable them with a single click in the Dashboard:

Popular Extensions

• pgvector: Store and query vector embeddings for AI applications
• PostGIS: Geographic information system capabilities
• pg_cron: Schedule periodic jobs
• uuid-ossp: Generate UUIDs
• pg_stat_statements: Track query performance

-- Enable the pgvector extension
create extension vector;

-- Create a table with vector embeddings
create table documents (
  id bigint primary key,
  content text,
  embedding vector(1536)
);

Database Management

Migrations

Use migrations to version control your database schema:

-- supabase/migrations/20240101000000_create_posts.sql
create table posts (
  id bigint generated by default as identity primary key,
  title text not null,
  created_at timestamptz default now()
);

alter table posts enable row level security;

Apply migrations using the Supabase CLI:

supabase db push

Backups

Supabase manages automated backups for your database:

• Free tier: Daily backups retained for 7 days
• Pro tier: Daily backups retained for 14 days
• Team tier: Daily backups retained for 28 days
• Enterprise: Custom retention periods and Point-in-Time Recovery (PITR)

Database backups do not include Storage objects - only their metadata. Back up Storage buckets separately.

Performance Optimization

Connection Pooling

Supabase uses PgBouncer for connection pooling. You can access your database through two connection modes:

• Session mode (Port 5432): Full PostgreSQL features, limited connections
• Transaction mode (Port 6543): Higher concurrency, some feature limitations

Query Performance

Use the SQL Editor to analyze query performance:

-- Explain query execution plan
explain analyze
select * from posts
where author_id = 'user-uuid'
order by created_at desc
limit 10;

Vacuum and Analyze

PostgreSQL automatically performs vacuum operations, but you can manually optimize:

-- Analyze table statistics
analyze posts;

-- Full vacuum (reclaims space)
vacuum full posts;

Best Practices

Use RLS

Always enable Row Level Security and create appropriate policies for your tables.

Index Strategically

Create indexes on columns used in WHERE clauses and JOIN conditions.

Normalize Data

Design tables following database normalization principles to reduce redundancy.

Use Transactions

Wrap related operations in transactions to ensure data consistency.

Next Steps

Database Functions

Learn how to create custom database functions

Triggers

Automate database operations with triggers

Extensions

Extend PostgreSQL with powerful extensions

Migrations

Version control your database schema