When the Supabase dashboard peaks your app

I’m working for a client with a massive set of users (> 10million). We had sudden spikes ; database became unresponsive and queries waited.

I noticed that “someone” is doing COUNT(*) on auth.users when this happens. That led to a chain of problems but the general rule of thumb for Postgres is: Don’t do counts on such big tables. The “someone” however was: Supabase. More specifically, all you had to do to trigger it, was moving to Auth overview - yes, also in your project, not just ours. The difference is that for few projects the user count is so big that you actually notice performance drains.

I digged into the Supabase Studio source code, reported the problem and provided the solution; and it got fixed and deployed for everybody. It’s a good reminder though, that the dashboard can impact your production instance if you have large sets of data and filter on them in the table view.

If you want to estimate a row count on a huge table, you get pretty accurate information with

SELECT reltuples::bigint AS estimate
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'YOUR_TABLE_NAME';

FYI: My first supabase-related product

I just released selfhost-supabase.com , a safe-by-default, VPN-tunneled way to self-host Supabase on your own server, including newer images than the official Supabase git repo.

Let’s start with Quiz #4 😎 

Poll results of Quiz #3 → at the end.

Say you just imported data from a NoSQL database. Now, you have a table employees with a column metadata::jsonb . What is the best choice of index if you want to run fast queries like

SELECT * FROM employees WHERE metadata @> '{"is_ceo":false}' AND metadata ? 'last_payment_date'

(Fetches all employees that are not the ceo and have a last_payment_date property defined).

Resolution including explanation in #5.

Postgres Snacks 🐘 

Snack 1 / 5: Hyper-specific conditional table triggers

Too often I see something like this in update trigger functions:

CREATE OR REPLACE FUNCTION on_changed_state()
RETURNS TRIGGER AS $$
BEGIN
  IF (OLD.is_locked) THEN
    RAISE EXCEPTION 'Cannot edit a locked row';
  END IF;

  IF (NEW.state <> OLD.state) THEN
    -- state has changed, do something
    NEW.state_updated_at = now();
  END IF;
END;
$$ LANGUAGE plpgsql;

That works but gets dirty way too fast. There are cleaner solutions:

Call trigger function only if state column was updated:

CREATE TRIGGER trg_upd_change
BEFORE UPDATE 
OF state
ON my_table
FOR EACH ROW
EXECUTE FUNCTION on_changed_state();

The problem here: It doesn’t check the value from before, so this is also called when e.g. you do SET state='closed' even when the state column was already set to closed before; so you would still need the IF (NEW.state <> OLD.state) .

However, there’s an optimized solution:

CREATE TRIGGER trg_upd_change
BEFORE UPDATE 
OF state
ON my_table
FOR EACH ROW
WHEN (OLD.state <> NEW.state) 
EXECUTE FUNCTION on_changed_state();

Now, you can actually simplify your trigger function:

CREATE OR REPLACE FUNCTION on_changed_state()
RETURNS TRIGGER AS $$
BEGIN
  IF (OLD.is_locked) THEN
    RAISE EXCEPTION 'Cannot edit a locked row';
  END IF;

  NEW.state_updated_at = now();
END;
$$ LANGUAGE plpgsql;

If you really want to separate things, create a prevent_locked_row_update function that simply raises an exception, without an if, and uses WHEN (OLD.is_locked = true) .