DEV Community: Mark Burggraf

How to Get PostgreSQL Explain / Analyze Statistics from the Supabase Javascript Client

Mark Burggraf — Fri, 03 Feb 2023 18:09:27 +0000

Please Explain

PostgreSQL has this great tool for analyzing complex queries. You can use it to see what parts of your query are "costing" you CPU time, and use that information to build the right indexes to make your queries run lightning fast:

Postgres EXPLAIN — show the execution plan of a statement

https://www.postgresql.org/docs/current/sql-explain.html

This is great when you're writing SQL, but what if you're using the Supabase Javascript client?

const { data, error } = await supabase
  .from('users')
  .select(`
    name,
    teams (
      name
    )
  `)

Maybe you tried using select query from pg_stat_statements to find the query that your client code is sending to PostgreSQL. But then you see all sorts of parameters in the query such as $1, $2, $3. Now how do you analyze this?

There's an easier way -- you can get this data right from your client in debug mode.

Step One: Turn on `db_plan_enabled`

To be able to debug from the client side, you'll need to enable it first by running these commands:

alter role authenticator 
set pgrst.db_plan_enabled to true;
notify pgrst, 'reload config';

Step Two: Debug your client queries

Now that you have db_plan_enabled turned on you can do this in your client code:

const { data, error } = await supabase
  .from('users')
  .select(`
    name,
    teams (
      name
    )
  `)
  .explain({
      analyze: true, 
      verbose: true, 
      format: 'json'})

This will return valuable information about the query execution plan that you can use to tune your database for better performance.

Step Three: Turn it off

When you're done debugging, just turn db_plan_enabled back off with:

alter role authenticator 
set pgrst.db_plan_enabled to false;
notify pgrst, 'reload config';

Conclusion

This makes it easier to see exactly what PostgREST (the PostgreSQL REST interface used by the Supabase Javascript library) is doing behind the scenes. Once you know where the big costs are in your query, you can build the necessary indexes and you should see better performance on those operations.

Note

You'll need to be on PostgREST 10.1.1 or higher to get this capability.

To check your version, you can run this:

curl '<SUPABASE_URL>/rest/v1/' \
-H "apikey: <SUPABASE_ANON_KEY>" \
-H "Authorization: Bearer <SUPABASE_ANON_KEY>" | more

You'll see the PostgREST version near the very beginning of the output.

SQL or NoSQL? Why not use both (with PostgreSQL)?

Mark Burggraf — Mon, 21 Nov 2022 23:11:59 +0000

It's a tough decision for any developer starting a new project. Should you store your data in a standard, time-tested SQL database, or go with one of the newer NoSQL document-based databases? This seemingly simple decision can literally make or break your project down the line. Choose correctly and structure your data well, and you may sail smoothly into production and watch your app take off. Make the wrong choice and you could be headed for nightmares (and maybe even some major re-writes) before your app ever makes it out the door.

Simplicity vs Power

There are tradeoffs with both SQL and NoSQL solutions. Typically it's easier to get started with NoSQL data structures, especially when the data is complex or hierarchical. You can just take a JSON data object from your front-end code and throw it in the database and be done with it. But later when you need to access that data to answer some basic business questions, it's much more difficult. A SQL solution makes it easier to gather data and draw conclusions down the line. Let's look at an example:

Each day I track the food I eat, along with the number of calories in each item:

Day	Food Item	Calories	Meal
01 Jan	Apple	72	Breakfast
01 Jan	Oatmeal	146	Breakfast
01 Jan	Sandwich	445	Lunch
01 Jan	Chips	280	Lunch
01 Jan	Cookie	108	Lunch
01 Jan	Mixed Nuts	175	Snack
01 Jan	Pasta/Sauce	380	Dinner
01 Jan	Garlic Bread	200	Dinner
01 Jan	Broccoli	32	Dinner

I also track the number of cups of water I drink and when I drink them:

Day	Time	Cups
Jan 01	08:15	1
Jan 01	09:31	1
Jan 01	10:42	2
Jan 01	12:07	2
Jan 01	14:58	1
Jan 01	17:15	1
Jan 01	18:40	1
Jan 01	19:05	1

And finally, I track my exercise:

Day	Time	Duration	Exercise
Jan 01	11:02	0.5	Walking
Jan 02	09:44	0.75	Bicycling
Jan 02	17:00	0.25	Walking

For each day I also track my current weight along with any notes for the day:

Day	Weight	Notes
Jan 01	172.6	This new diet is awesome!
Jan 14	170.2	Not sure all this is worth it.
Jan 22	169.8	Jogged past a McDonald's today. It was hard.
Feb 01	168.0	I feel better, but sure miss all that greasy food.

Gathering All That Data

That's a lot of different data that needs to be gathered, stored, retrieved, and later analyzed. It's organized simply and easily, but the number of records varies from day to day. On any given day I may have zero or more entries for food, water, and exercise, and I may have zero or one entry for weight & notes.

In my app, I gather all the data for a single day on one page, to make it easier for my users. So, I get a JSON object for each day that looks like this:

{ "date": "2022-01-01", 
  "weight": 172.6, 
  "notes": "This new diet is awesome!", 
  "food": [
      { "title": "Apple", "calories": 72, "meal": "Breakfast"},
      { "title": "Oatmeal", "calories": 146, "meal": "Breakfast"},
      { "title": "Sandwich", "calories": 445, "meal": "Lunch"},
      { "title": "Chips", "calories": 280, "meal": "Lunch"},
      { "title": "Cookie", "calories": 108, "meal": "Lunch"},
      { "title": "Mixed Nuts", "calories": 175, "meal": "Snack"},
      { "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner"},
      { "title": "Garlic Bread", "calories": 200, "meal": "Dinner"},
      { "title": "Broccoli", "calories": 32, "meal": "Dinner"}
  ],
  "water": [
      {"time": "08:15", "qty": 1},
      {"time": "09:31", "qty": 1},
      {"time": "10:42", "qty": 2},
      {"time": "10:42", "qty": 2},
      {"time": "12:07", "qty": 1},
      {"time": "14:58", "qty": 1},
      {"time": "17:15", "qty": 1},
      {"time": "18:40", "qty": 1},
      {"time": "19:05", "qty": 1}
  ],
  "exercise": [
      {"time": "11:02", "duration": 0.5, "type": "Walking"}
   ]
}

Saving the Data

Once we've gathered all the data for a day, we need to store it in our database. In a NoSQL database, this can be a pretty easy process, as we can just create a record (document) for a specific user for a specific date and throw document into a collection and we're done. With SQL, we have some structure we have to work within, and in this case it looks like 4 separate tables: food, water, exercise, and notes. We'd want to do 4 separate inserts here, one for each table. If we don't have data for a specific table (say no exercise was recorded today) then we can skip that table.

If you're using SQL to store this data, you might want to save each table's data as it's entered in your data entry form (and not wait until all the data is entered.) Or you might want to create a database function that takes all the JSON data, parses it, and writes it to all the related tables in a single transaction. There's a lot of ways to handle this, but suffice it to say this: it's a bit more complicated than saving the data in a NoSQL database.

Retrieving the Data

If we want to display all the data for a single day, it's pretty much the same. With NoSQL you can grab the data for the user's day and then use it in your application. Nice! With SQL we need to query 4 tables to get all the data (or we could use a function to get it all in a single call.) Of course, when displaying the data, we'd need to first break up our JSON data into pieces that are needed by each section of our dashboard screen, and you could argue that it's simpler to map each SQL table with the dashboard section on the screen, but that's a trivial point.

Analyzing the Data

Now that we've saved the data and we can retrieve it and display it, let's use it for some analysis. Let's display a graph of how many total calories I've eaten over the past month. With SQL, this is a simple task:

select 
   date, sum(calories) as total_calories 
from food_log 
group by date 
where user_id = 'xyz' 
and day between '2022-01-01' and '2022-01-31' 
order by date;

Bam! Done! Now we can send those results to our graphing library and make a nice pretty picture of my eating habits.

But if we've stored this data in NoSQL, it gets a little more complicated. We'll need to:

grab all the data for the user for the month
parse each day's data to get the food log information
loop through each day and total the calories
send the aggregate data to our graphing module

If this is something we're going to do regularly, it makes sense to calculate the total calories for each day and store it in the day's document so we can get at that data faster. But that requires more work up front, and we still need to pull the data for each day and parse out that calorie total first. And if we update the data we still need to recalculate things and update that total. Eventually we'll want to do that with the water and exercise totals as well. The code will eventually start to get longer and more complex.

SQL and NoSQL Together - FTW

Let's see how we can use the power of SQL together with the ease-of-use of NoSQL in the same database to make this all a bit easier. We'll create a table for each day of data (for each user) and store the basic fields such as weight and notes first. Then we'll just throw the food_log, water_log, and exercise_log fields in a JSONB field.

CREATE TABLE calendar (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    date date,
    user_id uuid NOT NULL,
    weight numeric,
    notes text,
    food_log jsonb,
    water_log jsonb,
    exercise_log jsonb
);
-- (Optional) - create a foreign key relationship for the user_id field 
ALTER TABLE ONLY calendar
    ADD CONSTRAINT calendar_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);

Now let's insert some data into the table. PostgreSQL offers both JSON and JSONB fields, and since the latter are more optimized by the database and much faster for query processing, we’ll almost always want to use JSONB. We’ll use JSONB fields for food_log, water_log, and exercise_log and just dump the data we got from our app right into those fields as a string:

insert into calendar (date, user_id, weight, notes, food_log, water_log, exercise_log)
values (
   '2022-01-01', 
   'xyz', 
   172.6, 
   'This new diet is awesome!',
   '[
      { "title": "Apple", "calories": 72, "meal": "Breakfast"},
      { "title": "Oatmeal", "calories": 146, "meal": "Breakfast"},
      { "title": "Sandwich", "calories": 445, "meal": "Lunch"},
      { "title": "Chips", "calories": 280, "meal": "Lunch"},
      { "title": "Cookie", "calories": 108, "meal": "Lunch"},
      { "title": "Mixed Nuts", "calories": 175, "meal": "Snack"},
      { "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner"},
      { "title": "Garlic Bread", "calories": 200, "meal": "Dinner"},
      { "title": "Broccoli", "calories": 32, "meal": "Dinner"}
     ]',
   '[
      {"time": "08:15", "qty": 1},
      {"time": "09:31", "qty": 1},
      {"time": "10:42", "qty": 2},
      {"time": "10:42", "qty": 2},
      {"time": "12:07", "qty": 1},
      {"time": "14:58", "qty": 1},
      {"time": "17:15", "qty": 1},
      {"time": "18:40", "qty": 1},
      {"time": "19:05", "qty": 1}
    ]',
   '[
      {"time": "11:02", "duration": 0.5, "type": "Walking"}
    ]'
);

While that's a big insert statement, it sure beats doing inserts on 4 separate tables. With all those food entries and water log entries, we would have had to made 1 entry in the main table, then 9 food_log entries, 9 water_log entries, and one exercise_log entry for a total of 20 database records. We've wrapped that into a single record.

But How Do We Query This Data?

Great, we're collecting the data now, and it's easy to insert the data into the database. Editing the data isn't too bad either because we're just downloading the data to the client, updating the JSON field(s) as needed, and throwing them back into the database. Not too hard. But how can I query this data? What about that task from before? Let's display a graph of how many total calories I've eaten over the past month.

In this case, that data is stored inside the food_log field inside the calendar table. If only PostgreSQL had a way of converting JSONB arrays into individual database records (recordsets). Well, it does! The jsonb_array_elements function will do this for us, allowing to create a simple table we can use to calculate our caloric intake.

Here's some SQL to turn that food_log array into individual output records:

select 
  user_id,
  date,
  jsonb_array_elements(food_log)->>'title' as title,
  jsonb_array_elements(food_log)->'calories' as calories,
  jsonb_array_elements(food_log)->'meal' as meal
from calendar 
where user_id = 'xyz' 
   and date between '2022-01-01' and '2022-01-31';

This returns a table that looks like this:

date	title	calories	meal
2022-01-01	Apple	72	Breakfast
2022-01-01	Oatmeal	146	Breakfast
2022-01-01	Sandwich	445	Lunch
2022-01-01	Chips	280	Lunch
2022-01-01	Cookie	108	Lunch
2022-01-01	Mixed Nuts	175	Snack
2022-01-01	Pasta/Sauce	380	Dinner
2022-01-01	Garlic Bread	200	Dinner
2022-01-01	Broccoli	32	Dinner

A couple things to note:

jsonb_array_elements(food_log)->>'title' as title this returns a text field, since the ->> operator returns TEXT
jsonb_array_elements(food_log)->'calories' as calories this returns a JSON object, since the -> operator return JSON

If we want to sum the calories to get some totals, we can't have a JSON object, so we need to cast that to something more useful, like an INTEGER:

(jsonb_array_elements(food_log)->'calories')::INTEGER as calories this returns an INTEGER

Now we can't just throw the sum operator on this to get the total calories by day. If we try this:

select 
  date,
  sum((jsonb_array_elements(food_log)->'calories')::integer) as total_calories
from calendar where user_id = 'xyz' 
      and date between '2022-01-01' and '2022-01-31'
group by date;

we get an error back from PostgreSQL: Failed to run sql query: aggregate function calls cannot contain set-returning function calls.

Instead, we need to think of this as a set of building blocks, where our first SQL statement returns a table:

select 
  date,
  (jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar where user_id = 'xyz' 
      and date between '2022-01-01' and '2022-01-31';

Now we can take that "table" statement, throw some (parenthesis) around it, and query it:

with data as
   (
      select 
        date,
        (jsonb_array_elements(food_log)->'calories')::integer as calories
      from calendar 
      where user_id = 'xyz' 
         and date between '2022-01-01' 
and '2022-01-31'
   ) 
select date, sum(calories) from 
   data group by date;

This gives us exactly what we want:

date	sum
2022-01-01	1838

If we add more data for the rest of the days of the month, we'll have all the data we need for a beautiful graph.

Searching the Data

What if we want to answer the question: How many calories were in the garlic bread I ate last month? This data is stored inside the food_log field in the calendar table. We can use the same type of query we used before to "flatten" the food_log data so we can search it.

To get every item I ate during the month of January, we can use:

select 
  date,
  jsonb_array_elements(food_log)->>'title' as title,
  (jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar 
where user_id = 'xyz' 
   and date between '2022-01-01' 
and '2022-01-31'

Now to search for the garlic bread we can just put (parenthesis) around this to make a "table" and then search for the item we want:

with my_food as 
(
   select 
     date,
     jsonb_array_elements(food_log)->>'title' as title,
     (jsonb_array_elements(food_log)->'calories')::integer as calories
   from calendar 
   where user_id = 'xyz' 
   and date between '2022-01-01' and '2022-01-31'
) 
select title, calories 
from my_food 
where title = 'Garlic Bread';

which gives us:

title	calories
Garlic Bread	200

Conclusion

If we take a little time to study the JSON Functions and Operators that PostgreSQL offers, we can turn Postgres into an easy-to-use NoSQL database that still retains all the power of SQL. This gives us a super easy way to store our complex JSON data coming from our application code in our database. Then we can use powerful SQL capabilities to analyze and present that data in our application. It's the best of both worlds!

Hacking the PostgREST Headers: Oh, the Things You Can Do!

Mark Burggraf — Mon, 17 Oct 2022 22:30:25 +0000

When using the Supabase Javascript Client, reading the PostgREST headers gives us all sorts of useful information for logging and security purposes.

Luke Bechtel from Revaly recently asked "is there any way to get things like Browser Type or Host Name from inside a PostgreSQL trigger or RLS (Row Level Security) Policy?" Well, since the Supabase client uses PostgREST, and PostgREST is a web tool, then it should be able to access to the server's request object. And indeed, it does.

Examples

See pg_headerkit: PostgREST Header Kit

Interesting Use Cases

Why is this useful or important? Imagine these use cases:

Allow-listing IPs: only allow users to select, insert, update, or delete if they're coming from a pre-defined list of IP addresses.
Origin Restrictions: allow a feature only during development (when the request is coming from localhost but not your production domain).
Platform Checking: only allow users from mobile platforms to use your application (no desktop browsers).
Logging: Log the user's IP address and browser User Agent in your database with their anonymous request data.
Version Requirements: only allow clients coming from the most recent version of the Supabase Javascript Client Library.

Of course, if the user is logged into our app, we can also get their id using auth.uid(), or their email using auth.jwt() ->> 'email'.

Getting Access to the Request Headers

How can we get access to all this useful information? By using the PostgreSQL current_setting function, we can access the request.headers liks this: current_setting('request.headers', true). So, to put that into a useful function, we get:

CREATE OR REPLACE FUNCTION get_headers() RETURNS jsonb
    LANGUAGE sql STABLE
    AS $$
    SELECT current_setting('request.headers', true)::json
$$;

This function returns a JSON object with all the header information from the request. We get things like:

accept-encoding: "gzip"
accept-language: "en-US,en;q=0.9"
host: "localhost:3000"
origin: "http://localhost:8100"
referer: "http://localhost:8100/"
user-agent: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36"
x-client-info: "supabase-js/1.35.7"
x-consumer-username: "anon-key"
x-forwarded-for: "142.251.46.206, 20.112.52.29"
x-forwarded-host: "xxxxxxxxxxxxxxxx.supabase.co"

If we want to get the data from a specific header, we can create this function:

CREATE OR REPLACE FUNCTION get_header(item text) RETURNS text
    LANGUAGE sql STABLE
    AS $$
    SELECT (current_setting('request.headers', true)::json)->>item
$$;

This allows us to get the text value for any specified header, such as: get_header('user-agent').

Using the Results in a RLS (Row Level Security) Policy

Let's say we want to only allow records to be inserted into our table beta_tests if the request is coming from a server running on localhost, port 3000.

CREATE POLICY "only allow inserts on public.beta_tests from localhost:3000" 
ON public.beta_tests 
FOR INSERT 
WITH CHECK (get_header('host')='localhost:3000');

For security purposes, we can restict the usage for a table based on a whitelisted set of IPs. First, we need to get the user's IP address, which is found in x-forwarded-for, but that has 2 IP addresses separated by commas, and we only want the first one. So we can use the PostgreSQL SPLIT_PART function, which is similar to the Javascript split function: SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1). Note how we concatenate a comma to the x-forwarded-for header (get_header('x-forwarded-for') || ','), just in case there's an empty string there?

Now that we have the user's IP address, we can test to see if it's in our whitelist set:

CREATE POLICY "only allow access to table_for_internal_use_only from a set of IPs" ON "public"."table_for_internal_use_only"
AS PERMISSIVE FOR ALL
TO public
USING (SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1) = ANY (ARRAY['123.44.152.151','203.44.11.22','11.4.102.33']))
WITH CHECK (SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1) = ANY (ARRAY['123.44.152.151','203.44.11.22','11.4.102.33']));

You could extend this by creating a table of IP addresses and check against that table ((SELECT count(*) from my_whitelist_table where ip = SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1)) > 0), but be careful, this adds an extra lookup to another table, and this slows down your RLS policy considerably and could lead to scaling problems down the road.

Using the Results in a PostgreSQL Trigger

Let's create a log table caled log_table, and then for every record inserted into our test_table, we'll log a record there with the user's user_agent, host, origin, referer, and ip:

CREATE TABLE IF NOT EXISTS log_table (id serial primary key, table_name text, key text, created_at timestamptz DEFAULT now(), user_agent text, host text, origin text, referer text, ip text);

CREATE OR REPLACE FUNCTION log_user_data()
  RETURNS trigger AS
$$
BEGIN
  INSERT INTO log_table(table_name, key, user_agent, host, origin, referer, ip)
  VALUES(TG_TABLE_NAME::regclass::text, NEW.id::text, get_header('user-agent'), get_header('host'), get_header('origin'), get_header('referer'), SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1));
  RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER test_trigger
  AFTER INSERT
  ON test_table
  FOR EACH ROW
  EXECUTE PROCEDURE log_user_data();

Things of note here:

TG_TABLE_NAME::regclass::text returns the current table name in our trigger (so we can re-use this trigger on other tables!)
NEW.id::text converts the id field of the current table to text (a string). I use a UUID as the id field for almost every table I create, so this should work just fine. If you use a different convention or primary key type, you may have to alter this.
SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1), as mentioned earlier, grabs the first ip found in the x-forwarded-for header.

Other Interesting Tidbits from the User-Agent

We can parse the user-agent header to get relevant information, such as:

Is the user running on a Windows platform:
get_header('user-agent') LIKE '%Windows%'

Or Mac:
get_header('user-agent') LIKE '%Mac OS X%'

Is the user on a Mobile device:
get_header('user-agent') LIKE 'Mobile/%'

Check iOS Major Version:
get_header('user-agent') LIKE '%iPhone OS 16%'

The user-agent isn't the most accurate way to get this information, though, and user-agents are always subject to change (and can be forged) so be careful with this.

Other Caveats and Warnings

You may find additional headers beyond the ones listed here available to you when testing this, but it's best not to rely on them, as they:

may not be available on every platform or device (some headers exist on desktop systems but not mobile systems, for instance)
may change or go away completely based on infrastructure changes or changes to PostgREST or the Supabase Client Libraries.

Conclusion

PostgREST exposes some really useful request headers that give PostgreSQL functions the power to do some things that previously required a separate middleware tier. Moving this functionality into the database eliminates the need for that extra tier and might also speed up your application by reducing extra network round-trips. It also allows you to add an extra security layer at the database level, so you can allow or restrict access based on IP address, host name, client type, Javascript client version, and more!

Postgres WASM by Snaplet and Supabase

Mark Burggraf — Mon, 10 Oct 2022 23:13:41 +0000

Today we're open sourcing postgres-wasm with our friends at Snaplet.

postgres-wasm is a PostgreSQL server that runs inside a browser. It provides a full suite of features, including persisting state to browser, restoring from pg_dump, and logical replication from a remote database.

We're not the first to run Postgres in the browser - that title belongs to the team at Crunchy Data who shared their version of this on HN a month ago. It's awesome, and we wanted an open source version, so we teamed up with Snaplet build it. Let's explore how it's built, and some extra features we've added.

🐈 Who's Snaplet?

Snaplet gives developers production-accurate data and preview databases that they can code against, so they can focus on shipping.

Checkout out how you can use Snaplet to clone Supabase environments.

Demo

Before we get into the technical details, try it out yourself (warning: it will download ~30MB):

wasm.supabase.com

To run it locally:

# From Snaplet Repo
git clone git@github.com:snaplet/postgres-wasm.git
cd postgres-browser/packages/pg-browser
npx serve

# From Supabase Fork
git clone git@github.com:supabase-community/postgres-wasm.git
cd postgres-wasm
git checkout web
cd packages/supabrowser
npx serve

And open a browser at localhost:3000.

Features

Our demo version has a few neat features!

Postgres 14.5, psql, pg_dump, etc.
Save & restore state to/from a file.
Save & restore Postgres state to/from the browser storage (IndexedDB).
Quick start from a state file or fully reboot the emulator.
Memory configuration options from 128MB to 1024MB.
Adjust the font size for the terminal.
Upload files to the emulator (including database dumps and CSVs).
Download files from the emulator.
Outgoing network connectivity from the emulator to the internet.
Incoming network tunnel to postgres port 5432 inside the emulator.

Why?

That's a good question. postgres-wasm is currently about 30mb. So at this stage, running Postgres in the browser isn't great for general use-cases. It has a lot of potential though. Some ideas we'll be playing with over the next few months:

Documentation: for tutorials and demos.
Offline data: running it in the browser for an offline cache, similar to sql.js or absurd-sql.
Offline data analysis: using it in a dashboard for offline data analysis and charts.
Testing: testing PostgresSQL functions, triggers, data modeling, logical replication, etc.
Dev environments: use it as a development environment — pull data from production or push new data, functions, triggers, views up to production.
Snapshots: create a test version of your database with sample data, then take a snapshot to send to other developers.
Support: send snapshots of your database to support personnel to demonstrate an issue you're having.

Repository Overview

We've divided in our repository into three folders: a virtual machine, a web application, and a network proxy.

Virtual Machine (VM)

We create an embeddable Virtual Machine (VM) using Buildroot. Our VM is a stripped-down Linux build with Postgres installed.

See source code.

Web application

Next, we need to run the the VM inside our browser. How? WASM. We use v86 to run our VM inside the browser. Our demo application is very simple - plain HTML and some basic styling.

See source code.

Network proxy

Running Postgres in a browser is great, but connecting to it with PgAdmin is even better. Unfortunately browsers block TCP network access to the VM. To circumvent this, we proxy the traffic through websockets. We run a fork of Websockproxy which allows the emulator to talk to the internet by converting data sent over a websocket port into TCP packets. Our fork of Websockproxy adds the ability to tunnel into the Postgres server.

See source code.

Technical deep-dive

There were a lot of hurdles that we discovered throughout the development of postgres-wasm.

WASM

The first thing to point out is that our implementation isn't pure WASM. We attempted to compile Postgres for WASM directly from source, but it was more complicated that we anticipated.

Crunchy's HN post provided some hints about the approach they took, which was to virtualize a machine in the browser. We pursued this strategy too, settling on v86 which emulates an x86-compatible CPU and hardware in the browser.

PostgreSQL 14 segfault errors

We quickly had PostgreSQL 13.3 running using an outdated version of Buildroot. But version PG14+ wouldn't start, giving a segfault during initialization. We tried:

manually copying build files for PG14 into the older version(s) of Buildroot
building with (many) newer copies of Buildroot
adjusting kernel parameters and environment settings such as the amount of memory allocated to the emulator, etc.

Eventually, Fabian, the creator of v86 suggested we turn off JIT compilation for v86 and that solved the issue. He narrowed it down to a bug in v86 and is pushing an update that will fix it. Switching Postgres from posix to sysv memory management also solved the issue for the current release of V86.

Optimizing startup time and image size

With PG14 running in the emulator, we shifted our focus to performance. The image size for the emulator was too big for a browser-based tool. Even with our best efforts, a compressed snapshot was over 30mb - a fairly large payload to download before you can see any interaction.

We solved this by booting only a minimal Linux image and then dynamically loading the rest of the VM over HTTPS after initialization.

We achieve this by mounting a compressed 9P filesystem in the VM. 9P provides a Python script which takes a filesystem folder,renames every file an 8-character name and produces a filesystem.json file representing a nested structure with files, original file names, sizes, etc.

We then copy this compressed output to the VM.
We modified the kernel command line and v86 boot parameters to boot directly from the 9P filesystem, and even put our kernel file into the p9 filesystem. All non-essential files are loaded asynchronously over HTTPS in the browser as needed.

The initial state was smaller, but still 15-20mb in size. We discussed this with Fabian who pointed us towards the page_poison=on kernel parameter. This parameter allowed us to clear caches before creating the snapshots, by forcing Linux to write arbitrary bytes on freed memory instead of random bytes, so unused memory is compressed much more efficiently.

The end result of all these changes? The compressed initial state file is about 12mb - including a running network state and Postgres 14.4 running with psql loaded.

We experimented without Postgres started too - this produced an even smaller initial state but the time to initialize Postgres was usually longer than the time to download a few extra megabytes. We opted for a faster startup time.

Additionally, without a running state you'd hit a performance penalty every time you refreshed the page, since Postgres would need to be initialized each time. In our current version, after the initial state is downloaded and cached by the browser the VM feels almost instant on refresh.

Networking

Networking was particularly difficult to solve. For security reasons, browsers only support access to a nominal set of ports. In short, browsers speak “80/443” and Postgres speaks “5432” so there was no way for our Postgres to communicate with the outside world.

WASM isn't much help here - a WebAssembly module running in the browser is generally not able to do anything outside itself, except for calling functions exposed from JavaScript.

Browsers provide another connection option for us however: Websockets. The nice thing about websocket connections is that they are persistent, so all we needed was to figure out how to proxy the virtual machine traffic through a websocket connection.

The VM includes an emulated network card (ne2k-pci) and a proxy_url startup parameter which points to an external server running a websocket proxy (i.e. on the internet). The websocket proxy establishes connections via a websocket port, then accepts raw ethernet packets. It turns those raw packets into TCP/IP packets and routes them between the Internet and the VM.

With our tunnel established, we could now send network traffic to the outside world. For example, try “starting” the network on the VM, "exiting" out of psql (cmd+D), and then run ping 1.1.1.1.

Most v86 VM's use an open proxy at wss://relay.widgetry.org/ based on websockproxy. This proxy allows the VM to communicate with the outside world. It can handle all the basic use-cases like loading data from other PostgreSQL databases using pg_dump and psql, or operating as read-only replica of another database.

However, this proxy doesn't allow incoming traffic to be routed into a VM running locally on a laptop. For instance, you can't connect a local PgAdmin4 to the PostgreSQL instance running inside your browser. So we forked the [proxy(https://github.com/benjamincburns/websockproxy) and added nginx to provide a reverse proxy.

The reverse proxy listens on a range of ports, mapping each port to the VM at a specific private address you want to reach. The simple solution was to take the last 2 segments of the private IP and map it to a port number. So if your private IP is 10.5.6.123, you connect to the proxy on port 6123 and that points you to PostgreSQL (running on port 5432) on private IP 10.5.6.123. Shortened addresses are padded, so 10.5.6.2 maps to port 6002 and 10.5.6.44 to port 6044.

Sending commands to the emulator from the browser UI

Once the emulator is running, in the words of Fabian, “it runs as a black box process”, and the only way to communicate between the UI and the operating system running in the emulator is through serial0_send to send keystrokes over the emulated serial port, or through the create_file function which uploads a file to the running emulator.

To restart the network after restoring a state file, we need to run a short script to unbind and re-bind the emulated network card (so it gets a new mac address and thus a unique private IP address at the proxy), and then a standard network restart command.

We could run this script by sending keystrokes to the emulator, but that's clumsy. What if the user is at a psql prompt? Then we'd send \! script_command. But if they're at an OS prompt, that won't work.

We solved the issue by creating a folder called \inbox and added a listener to the folder. When files arrive there, if the file ends in .sh (a shell script), we chmod +x that file, execute it, then delete it. That allows us to run commands “in the background” without upsetting the UI.

Just for fun

If you want to do something very cool, you can connect to another user's Postgres instance that they are running in their browser.

Here are the steps:

Find a friend (sometimes the first step is the hardest)
Tell them to go to wasm.supabase.com
Tell them to “Start Network” and get their Network URL
Have them set a password on their database ALTER ROLE postgres WITH PASSWORD 'my_password';
On your own machine, open a terminal and run psql postgres://postgres:my_password@proxy.wasm.supabase.com:<PORT>
And you're in! You're now connecting to a remote Postgres instance, inside a VM, inside a browser, from a terminal on your own computer.

Database Replication

Even more mind-blowing - it's possible to replicate data from an online PostgreSQL database (for example, a Supabase project) to PostgreSQL running inside your browser,
and vice-versa using Postgres's logical replication. The steps are no different than any other Postgres database - just use the proxy URL as the target.

What's next?

For now, this is very experimental - but it has a lot of potential. If you want to get involved, please reach out to us or the team at [Snaplet(https://www.snaplet.dev/). The work they're doing over at Snaplet is incredible, and we've had a blast collaborating with them.

Snaplet repo: https://github.com/snaplet/postgres-wasm
Supabase fork: https://github.com/supabase-community/postgres-wasm

Speeding Up Bulk Loading in PostgreSQL

Mark Burggraf — Tue, 05 Jul 2022 15:06:47 +0000

Speeding Up Bulk Loading in PostgreSQL

Testing 4 ways to bulk load data into PostgreSQL

The Need For Speed

If you only need to load a few hundred records into your database, you probably aren't too concerned about efficiency. But what happens when try to insert thousands, or even millions of records? Now, data-loading efficiency can mean the difference between success and failure for your project, or at the very least the difference between a project that’s delivered timely and one that’s woefully overdue.

PostgreSQL has a great copy command that’s optimized for this task: https://www.postgresql.org/docs/current/sql-copy.html. But that’s only a good solution if your data is specifically in a CSV (or Binary) file. But what if you need to load data from pure SQL? Then what’s the fastest way?

Four Ways to Insert Data

Basic Insert Commands

Let’s look at the structure for some basic SQL insert commands:



create table users (id integer, firstname text, lastname text);
insert into users (id, firstname, lastname) values (1, 'George', 'Washington');
insert into users (id, firstname, lastname) values (2, 'John', 'Adams');
insert into users (id, firstname, lastname) values (3, 'Thomas', 'Jefferson');

Now we have some basic SQL for inserting records into our user table. This will get the data into our table, alright, but it's the slowest way to get data into our table. Let's look at some ways we can speed things up.

Transactions

A quick and easy way to speed things up is simply to put large batches of insert statements inside a transaction:



begin transaction;
insert into users (id, firstname, lastname) values (1, 'George', 'Washington');
insert into users (id, firstname, lastname) values (2, 'John', 'Adams');
insert into users (id, firstname, lastname) values (3, 'Thomas', 'Jefferson');
commit;

In my Windows test, this doubled the speed of the insert of 100k user records. Under MacOS, the speed tripled. While you can technically create batches with billions of records in them, you'll probably want to experiment with batch sizes of, say 1000, 10000, 100000, or something like that to see what works best based on your hardware, bandwidth, and record size.

Batched Inserts

Another way to speed things up is to use the SQL batch insert syntax when doing your insert. For example:



insert into users (id, firstname, lastname) values 
  (1, 'George', 'Washington'),
  (2, 'John', 'Adams'),
  (3, 'Thomas', 'Jefferson');

This method speeds things up considerably. In my tests, it was about 6 times faster. The same rules apply to batch sizes as with transactions -- you'll want to test different batch sizes to optimize things. I generally tend to start with a batch of around 10000 records for most applications and if that works well enough, I leave it there.

What About Both?

Can you combine transactions and batch insert statements for even more speed? Well, yes, and no. You certainly can combine them, but the speed increase is negligible (or in my Windows test case it even slowed things down just a bit.)



begin transaction;
insert into users (id, firstname, lastname) values 
  (1, 'George', 'Washington'),
  (2, 'John', 'Adams'),
  (3, 'Thomas', 'Jefferson');
commit;

So, while using both techniques here is perfectly valid, it may not be the fastest way to load data.

The Downsides

What are the potential downsides of using transactions or batched inserts? Error handling is the main one. If any one of the records in your batch fails, the entire batch will fail and no data will be inserted into your table from that batch. So you'll need to make sure your data is valid or else have some way to break up and fix failed batches.

If the failure is caused by a unique constraint, you can use the on conflict clause in your insert statement, but if your insert fails for any other reason, it'll throw out the whole batch.

Other Speed Considerations

There are many other factors that can affect your data insert speed and ways you can make things even faster. Removing indexes until after inserting data, creating non-logged tables, and avoiding unnecessary unique keys are just a few of these. These other optimizations will improve performance, but probably not nearly as dramatically as the basic techniques described here.

Conclusion

If you need to deal with large amounts of data, it pays to plan ahead when you're writing your SQL insert code. A few small changes can potentially save you hours (or sometimes even days) of processing time.

Appendix: Sample Test Results

See my GitHub Repo postgresql-bulk-load-tests for some code to test these methods. My test run results are listed below.



===========================
Windows VM (UTM Windows 11)
===========================
create 100k users with individual insert statements
30.0 seconds
create 100k users with individual insert statements in a transaction
14.0 seconds
create 100k users with batch insert statement
4.3 seconds
create 100k users with batch insert statement in a transaction
4.6 seconds

====================
MacBook Pro (M1 Max)
====================
create 100k users with individual insert statements

real    0m9.112s
user    0m0.509s
sys     0m0.337s

create 100k users with individual insert statements in a transaction

real    0m2.540s
user    0m0.457s
sys     0m0.325s

create 100k users with batch insert statement

real    0m1.360s
user    0m0.179s
sys     0m0.042s

create 100k users with batch insert statement in a transaction

real    0m1.154s
user    0m0.189s
sys     0m0.041s

Supabase Custom Claims

Mark Burggraf — Tue, 31 May 2022 13:45:57 +0000

I've put together a [GitHub Repository (https://github.com/supabase-community/supabase-custom-claims) that makes it easy to implement Custom Claims for your application based on Supabase. (Supabase is an open-source backend service that uses PostgreSQL as its database and GoTrue for authentication. It does plenty of other cool things, too, but those items are out of the scope of this article.)

This is just one way to implement custom claims for a Supabase project. The goal here is simply to add JSON data to the JWT access token that an authenticated user receives when logging into your application. That token (and thus the custom claims contained in that token) can be read and used by both your application and by your PostgreSQL database server. These custom claims are stored in the raw_app_meta_data field of the users table in the auth schema. (auth.users.raw_app_meta_data)

In order to make this technique as portable as possible, I've implemented it using a set of PostgreSQL functions, which can be called from any Supabase Client Language that can call a PostgreSQL function. (That covers a lot of programming languages!)

FAQ

What are custom claims?

Custom Claims are special attributes attached to a user that you can use to control access to portions of your application.

For example:

plan: "TRIAL"
user_level: 100
group_name: "Super Guild!"
joined_on: "2022-05-20T14:28:18.217Z"
group_manager: false
items: ["toothpick", "string", "ring"]

What type of data can I store in a custom claim?

Any valid JSON data can be stored in a claim. You can store a string, number, boolean, date (as a string), array, or even a complex, nested, complete JSON object.

Where are these custom claims stored?

Custom claims are stored in the auth.users table, in the raw_app_meta_data column for a user.

Are there any naming restrictions?

The Supabase Auth System (GoTrue) currently uses the following custom claims: provider and providers, so DO NOT use these. Any other valid string should be ok as the name for your custom claim(s), though.

Why use custom claims instead of just creating a table?

Performance, mostly. Custom claims are stored in the security token a user receives when logging in, and these claims are made available to the PostgreSQL database as a configuration parameter, i.e. current_setting('request.jwt.claims', true). So the database has access to these values immediately without needing to do any disk i/o.

This may sound trivial, but this could have a significant effect on scalability if you use claims in an RLS (Row Level Security) Policy, as it could potentially eliminate thousands (or even millions) of database calls.

What are the drawbacks to using custom claims?

One drawback is that claims don't get updated automatically, so if you assign a user a new custom claim, they may need to log out and log back in to have the new claim available to them. The same goes for deleting or changing a claim. So this is not a good tool for storing data that changes frequently.

You can force a refresh of the current session token by calling supabase.auth.update({}) on the client, but if a claim is changed by a server process or by a claims administrator manually, there's no easy way to notify the user that their claims have changed. You can provide a "refresh" button or a refresh function inside your app to update the claims at any time, though.

How can I write a query to find all the users who have a specific custom claim set?

examples

find all users who have `claims_admin` set to `true`

select * from auth.users where (auth.users.raw_app_meta_data->'claims_admin')::bool = true;

find all users who have a `userlevel` over 100

select * from auth.users where (auth.users.raw_app_meta_data->'userleval')::numeric > 100;

find all users whose `userrole` is set to `"MANAGER"`

(note for strings you need to add double-quotes becuase data is data is stored as JSONB)
select * from auth.users where (auth.users.raw_app_meta_data->'userrole')::text = '"MANAGER"';

What's the difference between `auth.users.raw_app_meta_data` and `auth.users.raw_user_meta_data`?

The auth.users table used by Supabase Auth (GoTrue) has both raw_app_meta_data and a raw_user_meta_data fields.

raw_user_meta_data is designed for profile data and can be created and modified by a user. For example, this data can be set when a user signs up: sign-up-with-additional-user-meta-data or this data can be modified by a user with auth-update

raw_app_meta_data is designed for use by the application layer and is used by GoTrue to handle authentication (For example, the provider and providers claims are used by GoTrue to track authentication providers.) raw_app_meta_data is not accessible to the user by default.

Security Considerations

If you want to tighten security so that custom claims can only be set or deleted from inside the query editor or inside your PostgreSQL functions or triggers, edit the function is_claims_admin() to disallow usage by app users (no usage through the API / Postgrest). Instructions are included in the function.

By default, usage is allowed through your API, but the ability to set or delete claims is restricted to only users who have the claims_admin custom claim set to true. This allows you to create an "admin" section of your app that allows designated users to modify custom claims for other users of your app.

Bootstrapping

If the only way to set or delete claims requires the claims_admin claim to be set to true and no users have that claim, how can I edit custom claims from within my app?

The answer is to "bootstrap" a user by running the following command inside your Supabase Query Editor window:

select set_claim('03acaa13-7989-45c1-8dfb-6eeb7cf0b92e', 'claims_admin', 'true');

where 03acaa13-7989-45c1-8dfb-6eeb7cf0b92e is the id of your admin user found in auth.users.

Using the functions

Inside the Query Editor

You can get, set, and delete claims for any user based on the user's id (uuid) with the following functions:

`get_claims(uid uuid)`

This returns a JSON object containing all the custom claims for a user.

`get_claim(uid uuid, claim text)`

This returns a JSON object for a single claim for a user.

`set_claim(uid uuid, claim text, value jsonb)`

This sets a specific claim for a user. You can send any valid JSON data here, including numbers, text, boolean, arrays, or complete complex nested JSON objects.

`delete_claim(uid uuid, claim text)`

This will delete a custom claim for a user.

Inside PostgreSQL Functions and Triggers

When using custom claims from inside a PostgreSQL function or trigger, you can use any of the functions shown in the section above: Inside the Query Editor.

In addition, you can use the following functions that are specific to the currently logged-in user:

`is_claims_admin()`

This returns true if the claims_admin claim is set to true. This means the user is allowed to execute the functions above (set_claim, etc.)

`get_my_claims()`

This returns a JSON object containing all the custom claims for the current user.

`get_my_claim(claim TEXT)`

This returns a JSON object for a single claim for the current user.

Inside an RLS (Row Level Security) Policy

To use custom claims in an RLS Policy, you'll normally use the get_my_claim to check a specific claim for the currently logged in user. For example, you could check to see if the user is a subscriber to your app before allowing access to a table. Something like this:
get_my_claim('userrole') = '"SUBSCRIBER"'

The syntax is slightly odd here because the get_my_claim function returns a JSON object, so if you're checking for a string you need to enclose it in quotes. You can also check for other types (numeric, boolean, etc.)

Getting Claims Data from Local Session Data

You can extract claims information from the session object you get when the user is logged in. For example:

supabase.auth.onAuthStateChange((_event, session) => {
  if (session?.user) {
    console.log(session?.user?.app_metadata) // show custom claims
  }
})

If any claims have changed since your last log in, you may need to log out and back in to see these changes.

Setting Claims Data From Your Application (using `.rpc()`)

The following functions can only be used by a "claims admin", that is, a user who has the claims_admin custom claim set to true:

(Note: these functions allow you to view, set, and delete claims for any user of your application, so these would be appropriate for an administrative branch of your application to be used only by high-level users with the proper security rights (i.e. claims_admin level users.))

TypeScript Examples:

public get_claims = async (uid: string) => {
    const { data, error } = await supabase
        .rpc('get_claims', { uid });
    return { data, error };
}
public get_claim = async (uid: string, claim: string) => {
    const { data, error } = await supabase
        .rpc('get_claim', { uid, claim });
    return { data, error };
}
public set_claim = async (uid: string, claim: string, value: object) => {
    const { data, error } = await supabase
        .rpc('set_claim', { uid, claim, value });
    return { data, error };
}
public delete_claim = async (uid: string, claim: string) => {
    const { data, error } = await supabase
        .rpc('delete_claim', { uid, claim });
    return { data, error };
}

Warning

Be sure to watch for reserved claims in your particular development environment. For example, the claims exp and role are reserved by the Supabase Realtime system and can cause problems if you try use these names. To avoid these potential problems, it's good practice to use a custom identifier in your custom claims, such as MY_COMPANY_item1, MY_COMPANY_item2, etc.

Conclusion

I hope you've found this article helpful as you develop your application with Supabase. This is just my personal approach to handling claims, so feel free to use this as a starting point for your custom approach. I'm looking forward to seeing what cool things you come up with in your applications!

DEV Community: Mark Burggraf

How to Get PostgreSQL Explain / Analyze Statistics from the Supabase Javascript Client

Please Explain

Step One: Turn on db_plan_enabled

Step Two: Debug your client queries

Step Three: Turn it off

Conclusion

Note

SQL or NoSQL? Why not use both (with PostgreSQL)?

Simplicity vs Power

Gathering All That Data

Saving the Data

Retrieving the Data

Analyzing the Data

SQL and NoSQL Together - FTW

But How Do We Query This Data?

Searching the Data

Conclusion

Hacking the PostgREST Headers: Oh, the Things You Can Do!

Examples

Interesting Use Cases

Getting Access to the Request Headers

Using the Results in a RLS (Row Level Security) Policy

Using the Results in a PostgreSQL Trigger

Other Interesting Tidbits from the User-Agent

Other Caveats and Warnings

Conclusion

Postgres WASM by Snaplet and Supabase

Demo

Features

Why?

Repository Overview

Virtual Machine (VM)

Web application

Network proxy

Technical deep-dive

WASM

PostgreSQL 14 segfault errors

Optimizing startup time and image size

Networking

Sending commands to the emulator from the browser UI

Just for fun

What's next?

Speeding Up Bulk Loading in PostgreSQL

Speeding Up Bulk Loading in PostgreSQL

The Need For Speed

Four Ways to Insert Data

Basic Insert Commands

Transactions

Batched Inserts

What About Both?

The Downsides

Other Speed Considerations

Conclusion

Appendix: Sample Test Results

Supabase Custom Claims

FAQ

What are custom claims?

What type of data can I store in a custom claim?

Where are these custom claims stored?

Are there any naming restrictions?

Why use custom claims instead of just creating a table?

What are the drawbacks to using custom claims?

How can I write a query to find all the users who have a specific custom claim set?

examples

find all users who have claims_admin set to true

find all users who have a userlevel over 100

find all users whose userrole is set to "MANAGER"

What's the difference between auth.users.raw_app_meta_data and auth.users.raw_user_meta_data?

Security Considerations

Bootstrapping

Using the functions

Inside the Query Editor

get_claims(uid uuid)

get_claim(uid uuid, claim text)

set_claim(uid uuid, claim text, value jsonb)

delete_claim(uid uuid, claim text)

Inside PostgreSQL Functions and Triggers

is_claims_admin()

get_my_claims()

Step One: Turn on `db_plan_enabled`

find all users who have `claims_admin` set to `true`

find all users who have a `userlevel` over 100

find all users whose `userrole` is set to `"MANAGER"`

What's the difference between `auth.users.raw_app_meta_data` and `auth.users.raw_user_meta_data`?

`get_claims(uid uuid)`

`get_claim(uid uuid, claim text)`

`set_claim(uid uuid, claim text, value jsonb)`

`delete_claim(uid uuid, claim text)`

`is_claims_admin()`

`get_my_claims()`

`get_my_claim(claim TEXT)`

Setting Claims Data From Your Application (using `.rpc()`)