Optimizing Database Schema to Reduce Supabase Storage by 80%

In serverless application design, database resources are easily one of the largest cost centers. When we began building the serverless tracking tools for Curious Kaizer Analytics, our database size grew dramatically inside the first 48 hours. Every scroll, mouse move, page load, and session parameters were logged directly as raw payload strings.

Within days, our tables bloated, storage quotas approached warning limits, and database insertion speed suffered. The issue lay in a classic architecture anti-pattern: storing redundant, unnormalized session details alongside unique event logs on a single table. In this article, I will share the exact database normalization strategy we used to shrink our Supabase disk space consumption by over 80% and resolve anonymous user security policy errors.

Identifying the Storage Bloat

In our initial database prototype, every tracking record stored variables like user_agent, screen_resolution, language, and geographic location inside a flat, wide table called analytics_events. A typical event insert contained about 1.5 KB of descriptive text:

"If a visitor generates 10 pageviews and clicks 5 CTA buttons during a single visit, we recorded their user-agent, IP address, screen resolution, and country 15 separate times. For a site with 10,000 monthly visits, this redundantly copies gigabytes of string text."

This wide structure has two severe downsides:

1. Storage waste: Text variables consume substantial physical disk sectors when written repeatedly.
2. Database constraint failures: Highly detailed user-agent strings (from specific bot scripts or devices) occasionally exceeded the DB checks, throwing unexpected database exceptions to client-side trackers.

The Solution: Normalizing Sessions vs Events

To fix this, we normalized our tables by separating the data into two distinct relations:

• analytics_sessions: Stores one row per visit. It contains the visitor's metadata (device specs, language, screen resolution, IP address, and geographic headers) linked to a unique session_id.
• analytics_events: Stores one row per event. It only records light variables: the event type (pageview, click), the page path, page title, event label, and the associated session_id foreign key.

Because the event row only holds short parameters and an index key, its size dropped from 1.5 KB to less than 100 bytes! All wide string descriptors are now stored exactly once per session, resulting in a massive 80%+ storage reduction.

Trigger Automation & Resolving the PostgreSQL RLS Policy Trap

A key requirement for our analytics was that the client-side tracker should insert data in a single API call to minimize network requests. We did this by directing inserts into a unified view called analytics_events, which triggers an automated PostgreSQL function to handle inserts behind the scenes.

However, we hit a roadblock: **Row-Level Security (RLS) policies**. Since anonymous tracking calls are made from visitors' browsers under the public anon API key, the database requires read permissions to check conflicts (e.g. ON CONFLICT DO NOTHING) during session insertion. Since anonymous users are restricted from reading sessions, our insertions kept failing with RLS errors.

We solved this by using the `SECURITY DEFINER` parameter on our database trigger function, allowing the database trigger to run under owner permissions without violating security policies:

-- Creating trigger function with SECURITY DEFINER
CREATE OR REPLACE FUNCTION public.insert_analytics_event_into_normalized()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    -- 1. Insert session metadata if it doesn't exist
    INSERT INTO public.analytics_sessions (
        session_id,
        user_agent,
        screen_resolution,
        language,
        referrer
    ) VALUES (
        NEW.session_id,
        NEW.user_agent,
        NEW.screen_resolution,
        NEW.language,
        NEW.referrer
    )
    ON CONFLICT (session_id) DO NOTHING;

    -- 2. Insert event details
    INSERT INTO public.analytics_events_normalized (
        session_id,
        event_type,
        event_label,
        page_path,
        page_title
    ) VALUES (
        NEW.session_id,
        NEW.event_type,
        NEW.event_label,
        NEW.page_path,
        NEW.page_title
    );

    RETURN NEW;
END;
$$;

Key Takeaways for Developers

If you are building database-driven applications on PostgreSQL or Supabase, keep these principles in mind:

1. Always isolate repeat meta-data early: Do not allow wide descriptive strings to sit in fast-growing tables. Moving user configurations, browser details, or hardware specifications to a parent lookup table pays massive dividends.
2. Leverage database triggers: Triggers keep your frontend light and keep transaction logic inside the database engine where it runs fastest.
3. Understand PostgreSQL SECURITY DEFINER: When implementing RLS write-only public pipelines, always use SECURITY DEFINER triggers to let the DB safely handle lookups and conflicts behind the scenes.