Postgres Best Practices for the AI Agent Era! Leverage "Agent Skills" Released by Supabase

This article is a commentary reconstructed based on Supabase's official blog and the public documentation for supabase/agent-skills. Command examples and SQL examples include samples reorganized for easier understanding.

In an era where AI coding agents write code for us, it is truly grateful that AI helps with database queries and schema design, such as Cursor, Claude Code, and GitHub Copilot.

However, there is one major challenge. While AI agents are good at writing code, they don't necessarily write "correct" code based on a deep understanding of the target system. Especially with databases like Postgres, I've heard that working but poorly performing queries and designs with security holes are often born.

That is why Supabase released the "Postgres Best Practices for AI Agents" initiative. In this article, we will introduce its content and look at specific methods to use Postgres more safely and faster.

What is Supabase's "Agent Skills"?

Agent Skills released by Supabase is a knowledge base for teaching AI coding agents Postgres best practices.

As for the mechanism, it provides a set of documents optimized in a format that AI agents can reference. 31 reference files are classified into 8 categories, allowing agents to refer to these rules and generate correct code when writing queries or designing schemas (the number of references may change with future updates).

Installation is simple and can be introduced with a single command below.

npx skills add https://github.com/supabase/agent-skills --skill supabase-postgres-best-practices

Now, let's look at the items with high practical impact from the published guidelines in order of priority.

8 Categories and Priority

In Agent Skills, rules are classified into the following 8 categories. They are listed in order of impact.

The top three are classified as "CRITICAL", and leaving them unattended can lead to significant performance degradation or security risks. Let's delve into the specific details of each.

CRITICAL: Query Performance Optimization

Missing Indexes are the Biggest Enemy

Note: The SQL in this article are samples simplified and reorganized to explain the concepts of the original material.

The most common performance issue in Postgres is missing indexes. If there is no index on columns used in WHERE clauses or JOINs, a "full table scan" occurs, scanning the entire table. As the data volume increases, the impact becomes severe, potentially leading to 100-1000x speed reductions.

First, let's check with a simplified example of a typical anti-pattern.

-- Anti-pattern: Filtering without an index
select * from orders where customer_id = 123;
-- -> Scans the entire table

The solution is simple: create an index on the column used for the filter condition.

-- Best practice: Create an index on columns used in the WHERE clause
create index orders_customer_id_idx on orders (customer_id);

Similarly for JOINs, create an index on the foreign key side.

-- Create an index on columns used in JOINs as well
create index orders_customer_id_idx on orders (customer_id);

select c.name, o.total
from customers c
join orders o on o.customer_id = c.id;

Efficiency Up with Partial Indexes

An index that targets only rows matching specific conditions, rather than all rows in a table, is called a "Partial Index". The index size becomes 1/5 to 1/20, speeding up both writes and queries.

-- Anti-pattern: Index targeting all rows
create index users_email_idx on users (email);

-- Best practice: Partial index targeting only active users
create index users_email_active_idx on users (email)
  where deleted_at is null;

Partial indexes are particularly effective for tables with soft-deleted records or large amounts of data with specific statuses. Don't forget to match the WHERE clause on the query side with the index condition.

CRITICAL: Connection Management

Postgres Connections are Expensive

A Postgres connection consumes 1-3MB of memory per connection. If there are 500 concurrent users, 500 connections are needed just for that, which could cause the database to go down.

A common solution to this problem is connection pooling. A pooler (like PgBouncer) is placed between the application and the database, sharing a small number of actual connections among many clients.

Pool Size Calculation Formula

The optimal pool size can be calculated with the following formula:

Pool Size = (CPU Cores * 2) + Disk Spindle Count

For example, in a 4-core system, the pool size is about 10 connections, which can handle over 500 concurrent users.

Transaction Mode and Session Mode

Connection poolers have two modes.

• Transaction Mode: Returns the connection to the pool after the transaction completes. Ideal for temporary clients like serverless functions or Edge Functions.
• Session Mode: Retains the connection throughout the session. Required when using Prepared Statements or temporary tables.

When using Supabase, for projects heavily using the PostgREST API, it is recommended to limit the pool size to about 40% of the total, and allocate up to 80% otherwise.

CRITICAL: Security and Row Level Security (RLS)

Filtering Only at the Application Layer is Dangerous

In a multi-tenant environment, a design that "controls only with conditional branching on the app side" is dangerous. Just one bug or bypass route could allow access to other tenants' data.

Note: Here, "multi-tenant" refers to a configuration where one app is shared by multiple customers (companies/teams). Also, "other tenants" refers to data other than your own customer's.

Row Level Security (RLS) is a mechanism that enforces access control at the database level. Since access rights can be set on a row-by-row basis, data is protected even if there is a bug on the application side.

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

-- Enforce RLS for table owners as well
alter table orders force row level security;

-- Create a policy where users can only access their own data
create policy "Users can only see their own orders"
  on orders for select
  using (user_id = (select auth.uid()));

Optimizing RLS Performance

RLS is powerful, but if written incorrectly, it can have a major impact on performance. Particular attention should be paid to the cost of calling authentication functions.

-- Anti-pattern: auth.uid() is called for every row
create policy "bad_policy" on orders
  for select using (auth.uid() = user_id);

-- Best practice: Wrap in select to cache it
create policy "good_policy" on orders
  for select using ((select auth.uid()) = user_id);

This small difference can create a speed difference of over 100x on large tables. Just by wrapping auth.uid() with (select auth.uid()), the function is executed only once and cached.

Furthermore, it is important to add indexes to columns referenced in RLS policies (such as user_id).

-- Add index to column used in RLS
create index orders_user_id_idx on orders (user_id);

Prevent RLS Recursion with Security Definer Functions

For complex authorization checks (like team membership verification), extracting the logic into a security definer function stabilizes the implementation. If the table referenced within an RLS policy also has RLS applied, policy evaluations can chain during policy evaluation, causing slow queries or unintended behavior. By consolidating the judgment into a security definer function, you can avoid that recursive evaluation while keeping the policy body simple.

-- Function to check team membership
create or replace function is_team_member(team_id bigint)
returns boolean
language sql
security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.team_members
    where team_members.team_id = $1
    and team_members.user_id = (select auth.uid())
  );
$$;

However, there are caveats. security definer functions execute with the "creator's privileges", so they bypass some normal RLS checks. Therefore, placing them in a public schema (Exposed schemas) accessible directly from the API risks exposing unintentionally strong privileges to the outside. Basically, this type of function should be placed in a schema that is not publicly exposed, configured so that only necessary processing can be called safely.

HIGH: Schema Design Best Practices

Choose Appropriate Data Types

The choice of data types is directly linked to storage efficiency and query performance. Here is a summary of common mistakes and recommended types.

Especially if you use int for ID columns, it will overflow at about 2.1 billion records. Using bigint supports up to about 9 quintillion records, so it is safe for future expansion.

MEDIUM-HIGH: Concurrency and Locking

Keep Transactions Short

Long-running transactions hold locks and block other queries. A common mistake is calling external APIs within a transaction.

-- Anti-pattern: Calling API inside transaction
begin;
  select * from orders where id = 1 for update;  -- Lock acquisition
  -- External API call here (takes 2-5 seconds)
  update orders set status = 'paid' where id = 1;
commit;  -- Holds lock this whole time

-- Best practice: Perform API call outside transaction
-- 1. Call external API first (outside transaction)
-- 2. Start transaction after receiving result
begin;
  update orders set status = 'paid' where id = 1;
commit;  -- Lock retention is in milliseconds

Just by moving non-database processing out of the transaction like this, a 3-5x throughput improvement can be expected.

It is also recommended to set statement_timeout as a safety measure.

-- Global setting
set statement_timeout = '30s';

-- Session level setting
set local statement_timeout = '5s';

MEDIUM: Data Access Patterns

Resolve N+1 Problems

The N+1 problem is a pattern where N additional queries are executed in a loop for results obtained in a single query. Round trips to the database increase by 10-100 times.

-- Anti-pattern: Query one by one in a loop
-- 1st time: select * from users limit 100;
-- 2nd to 101st time: select * from orders where user_id = ?; (Repeat 100 times)
-- -> Total 101 queries

-- Best practice: Fetch in batch
select * from orders where user_id = any($1::bigint[]);
-- -> Complete in 1 query by specifying collectively with array

-- Or use JOIN
select u.id, u.name, o.*
from users u
left join orders o on o.user_id = u.id;

Choose either to collect IDs on the application side and fetch collectively with any(), or to fetch in one go with JOIN.

Monitoring: Identify Problems with EXPLAIN ANALYZE

When performance issues occur, it is important to judge based on actual data rather than guesswork. Using EXPLAIN ANALYZE allows you to check the query execution plan and actual processing time.

explain (analyze, buffers, format text)
select * from orders where customer_id = 123;

Points to note in the output results are as follows:

• Seq Scan: Possibility of missing indexes.
• Rows Removed by Filter: If many rows are excluded by the filter, consider adding an index.
• Buffer reads >> cache hits: Disk I/O is frequent, possibility of insufficient memory.
• Excessive repetition of Nested Loop: Need to review the JOIN method.

How to Use in Combination with AI Agents

How can we effectively incorporate the content so far into actual development? Here are some practical methods.

1. Introduce Agent Skills to the Project

Installing the skill with the command introduced earlier places reference files in the project directory.

npx skills add https://github.com/supabase/agent-skills --skill supabase-postgres-best-practices

AI agents can read these files and refer to best practices when generating or reviewing queries.

2. Can also be installed as a plugin in Claude Code

If you are using Claude Code, a method to install directly as a plugin is also provided.

/plugin marketplace add supabase/agent-skills
/plugin install postgres-best-practices@supabase-agent-skills

When installed as a plugin, Claude Code automatically references best practices when it detects database-related tasks. Just by instructing "Optimize this Postgres query" or "Add appropriate indexes to the table", you can get suggestions based on Agent Skills rules.

Of course, the aforementioned npx skills add command can also be used. If files are placed in the project directory, it is convenient when you want to share best practices with all team members.

3. Utilize as Checkpoints during Code Review

When requesting a code review from an AI agent, it is good to instruct it to include the following perspectives.

• Are there indexes on columns used in WHERE clauses or JOIN conditions?
• Is auth.uid() wrapped in (select auth.uid()) in RLS policies?
• Are external API calls included inside transactions?
• Is it an N+1 pattern?
• Are data types appropriate? (Especially, is the ID column bigint?)

That said, you don't need to write such detailed instructions every time. If the supabase-postgres-best-practices skill is introduced, simply requesting "Review this change from the perspective of Postgres best practices" will often result in a review following the skill content. On the other hand, if the review scope is wide or other rule sets are used together, explicitly stating "Review using the supabase-postgres-best-practices skill" conveys the intent better and stabilizes the result. The checklist above is an image of using it as an additional prompt when you want to further increase the comprehensiveness of the review.

4. Improve Gradually Based on Priority

Ideally, you should review all categories together after confirming the scope of impact. However, in reality, due to constraints on man-hours and risks, if it is difficult, it is effective to prioritize reviewing CRITICAL categories (Query Performance, Connection Management, Security/RLS).

Summary

We introduced the content of "Postgres Best Practices for AI Agents" released by Supabase. Let's recap the points.

• The challenge is that even if AI agents have best practices as knowledge, they don't always apply them.
• By introducing Agent Skills, you can teach agents the correct way to write Postgres.
• The three highest priority categories are Query Performance, Connection Management, and Security/RLS.
• Adding indexes, introducing connection pooling, and appropriate implementation of RLS are the most impactful improvement measures.
• Small tweaks like (select auth.uid()) can lead to over 100x speed improvements in some cases.

Opportunities to handle databases with AI agents will likely increase more and more. Incorporating these best practices into your project will raise the quality of code generated by AI. Please try using it.

Reference Links

• This article is written using the following primary sources as main references (content is summarized and reconstructed).
• Introducing: Postgres Best Practices - Supabase Blog
• supabase/agent-skills - GitHub
• Supabase Postgres Best Practices - Skills.sh
• Row Level Security - Supabase Docs
• Connection Management - Supabase Docs
• RAG with Permissions - Supabase Docs