> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/supabase/supabase/llms.txt
> Use this file to discover all available pages before exploring further.
# 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](https://www.postgresql.org/) 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:
```sql theme={null}
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.
```sql theme={null}
-- 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:
```sql theme={null}
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:
```sql theme={null}
-- 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)
```typescript theme={null}
// 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
```graphql theme={null}
query {
postsCollection {
edges {
node {
id
title
content
commentsCollection {
edges {
node {
content
}
}
}
}
}
}
}
```
## Database Functions
You can create custom functions in PostgreSQL to encapsulate business logic:
```sql theme={null}
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:
```typescript theme={null}
const { data, error } = await supabase
.rpc('get_post_count', { author: user.id })
```
## Triggers
Triggers automatically execute functions in response to database events:
```sql theme={null}
-- 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
```sql theme={null}
-- 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:
```sql theme={null}
-- 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:
```bash theme={null}
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:
```sql theme={null}
-- 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:
```sql theme={null}
-- Analyze table statistics
analyze posts;
-- Full vacuum (reclaims space)
vacuum full posts;
```
## Best Practices
Always enable Row Level Security and create appropriate policies for your tables.
Create indexes on columns used in WHERE clauses and JOIN conditions.
Design tables following database normalization principles to reduce redundancy.
Wrap related operations in transactions to ensure data consistency.
## Next Steps
Learn how to create custom database functions
Automate database operations with triggers
Extend PostgreSQL with powerful extensions
Version control your database schema