Postgres Row-Level Security for Mobile Clients: High Velocity or Latency Bottleneck?

The pitch behind modern Backend-as-a-Service tools like Supabase and PostgREST is intoxicating:

“Skip writing an API layer. Just connect your mobile app directly to Postgres, set up Row-Level Security (RLS) policies, and let the database handle authorization.”

For early prototypes, it feels like cheating. You can ship a working iOS and Android MVP in two weekends.

Then your product starts growing. You hit 50,000 active users. Suddenly, simple queries that used to take 8ms are taking 1,800ms. Your database CPU hits 100%, and mobile users are staring at endless loading spinners.

Here is what is actually happening under the hood when your mobile app queries an RLS-enabled table.

The Hidden Sequential Scan

When you write a naive RLS policy like this:

-- NAIVE POLICY: Looks harmless, acts like a landmine
CREATE POLICY "Users can only view orders from their organization" 
ON orders
FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id 
    FROM organization_members 
    WHERE user_id = auth.uid()
  )
);

To the untrained eye, this looks fine. But run EXPLAIN ANALYZE on a query where a mobile client fetches their recent 20 orders:

EXPLAIN ANALYZE 
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 20;

Look at the query plan. Because the subquery SELECT organization_id FROM organization_members WHERE user_id = auth.uid() is evaluated for every candidate row in orders, Postgres is forced to run a Sequential Scan across the entire orders table.

If your orders table has 200,000 rows, Postgres evaluates that subquery 200,000 times!

Seq Scan on orders  (cost=0.00..84120.40 rows=10000 width=184) 
  Filter: (SubPlan 1)
  SubPlan 1
    ->  Seq Scan on organization_members  (cost=0.00..4.20 rows=1 width=16)
          Filter: (user_id = auth.uid())
Execution Time: 1842.140 ms

1.8 seconds of database compute time for a user looking at their order history.

The Fix: Subquery Flattening & Security Definer Functions

To fix this, we do two things:

1. Wrap auth lookups in STABLE functions: Tell the query planner that auth.uid() doesn’t change per row:

CREATE OR REPLACE FUNCTION get_user_org_ids()
RETURNS SETOF uuid
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT organization_id 
  FROM organization_members 
  WHERE user_id = auth.uid();
$$;

2. Index the foreign key and RLS predicates together:

CREATE INDEX idx_orders_org_created 
ON orders (organization_id, created_at DESC);

CREATE INDEX idx_org_members_user 
ON organization_members (user_id, organization_id);

Now rewrite the RLS policy to use the flattened function:

CREATE POLICY "Users view org orders optimized" 
ON orders
FOR SELECT
USING (
  organization_id = ANY(ARRAY(SELECT get_user_org_ids()))
);

Run EXPLAIN ANALYZE again. The sequential scan is replaced by a lightning-fast Bitmap Index Scan.

Execution time drops from 1,842ms down to 14ms.

Direct database access for mobile clients is a viable architecture, but only if you treat RLS policies with the same rigorous profiling you give complex join queries.