Supabase on Pigsty modifications

Caveats

1. PostgREST performance worsen with more than 16 CPU cores. To be safe, you should use 8-16 threads or less by setting GHCRTS="-N<x>" in environment variables and run multiple instances to sustain high throughput. (This may have improved lately.)

The numbers still don’t improve on 32, 48, 64 cores but now they maintain. Also not sure if there’s something else wrong with my benchmark setup in those. https://github.com/PostgREST/postgrest/issues/2294

-N ⟨x⟩. Use ⟨x⟩ simultaneous threads when running the program. https://ghc.gitlab.haskell.org/ghc/doc/users_guide/using-concurrent.html#rts-flag-N-x

https://github.com/Kong/kong/issues/3058

diff --git a/app/supabase/docker-compose.yml b/app/supabase/docker-compose.yml
index e46ba4a8..f393d22b 100644
--- a/app/supabase/docker-compose.yml
+++ b/app/supabase/docker-compose.yml
@@ -77,6 +77,8 @@ services:
     environment:
       KONG_DATABASE: "off"
       KONG_DECLARATIVE_CONFIG: /home/kong/kong.yml
+      KONG_DNS_VALID_TTL: 10
+      KONG_DNS_STALE_TTL: 3600
       # https://github.com/supabase/cli/issues/14
       KONG_DNS_ORDER: LAST,A,CNAME
       KONG_PLUGINS: request-transformer,cors,key-auth,acl,basic-auth,request-termination,ip-restriction
@@ -161,13 +163,15 @@ services:
   #--------------------------------------------------------------#
   # https://supabase.com/docs/guides/getting-started/architecture#postgrest-api
   rest:
-    container_name: supabase-rest
     image: postgrest/postgrest:v13.0.7
     restart: unless-stopped
+    deploy:
+      replicas: 3
     depends_on:
       analytics:
         condition: service_healthy
     environment:
+      GHCRTS: "-N8"
       PGRST_DB_URI: postgres://authenticator:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}
       PGRST_DB_SCHEMAS: ${PGRST_DB_SCHEMAS}
       PGRST_DB_ANON_ROLE: anon
diff --git a/app/supabase/volumes/api/kong.yml b/app/supabase/volumes/api/kong.yml
index 673aa6db..2bd28ddc 100644
--- a/app/supabase/volumes/api/kong.yml
+++ b/app/supabase/volumes/api/kong.yml
@@ -30,6 +30,16 @@ basicauth_credentials:
     username: $DASHBOARD_USERNAME
     password: $DASHBOARD_PASSWORD
 
+###
+### Upstreams for load balancing
+###
+upstreams:
+  - name: rest-upstream
+    algorithm: least-connections
+    targets:
+      - target: rest:3000
+        weight: 100
+
 ###
 ### API Routes
 ###
@@ -87,7 +97,7 @@ services:
   ## Secure REST routes
   - name: rest-v1
     _comment: 'PostgREST: /rest/v1/* -> http://rest:3000/*'
-    url: http://rest:3000/
+    url: http://rest-upstream/
     routes:
       - name: rest-v1-all
         strip_path: true
@@ -108,7 +118,7 @@ services:
   ## Secure GraphQL routes
   - name: graphql-v1
     _comment: 'PostgREST: /graphql/v1/* -> http://rest:3000/rpc/graphql'
-    url: http://rest:3000/rpc/graphql
+    url: http://rest-upstream/rpc/graphql
     routes:
       - name: graphql-v1-all
         strip_path: true
@@ -280,4 +290,4 @@ services:
       - name: cors
       - name: basic-auth
         config:
-          hide_credentials: true
\ No newline at end of file
+          hide_credentials: true
 

2. PostgREST’s db-pool (PGRST_DB_POOL) size defaults to 10, which should be increased as necessary per https://docs.postgrest.org/en/v12/references/connection_pool.html#acquisition-timeout.
3. supabase/storage is written in Node.js and can only saturate 1 CPU core. You need multiple if it becomes a bottleneck.
4. In high throughput scenarios with 10,000 or more RPS, you may need to increase connection reuse in Kong as documented in https://developer.konghq.com/gateway/performance/optimize/.
5. Publishable and secret keys are only available on the Supabase hosted platform. https://supabase.com/docs/guides/api/api-keys

Setup process

Based on v3.6.1:

[Download] ===========================================
[ OK ] version = v3.6.1 (from default)
curl -fSL https://repo.pigsty.io/src/pigsty-v3.6.1.tgz -o /tmp/pigsty-v3.6.1.tgz
################################################################################ 100.0%
[ OK ] md5sums = 083d8680fa48e9fec3c3fcf481d25d2f  /tmp/pigsty-v3.6.1.tgz
[Install] ===========================================
[ OK ] install = /home/ubuntu/pigsty, from /tmp/pigsty-v3.6.1.tgz

bash get
cd ~/pigsty
./configure -c supabase
patch -p1 < ...

1. Remove vector container in Supabase self-hosted docker compose stack. You should collect logs elsewhere.
   1. Preferably also remove the analytics container to save yourself from the supa-kick cron job.
2. Remove unused timescaledb extension.
3. Disable promtail and loki. (Now VictoriaLogs and Vector in v4.0)
4. Leave DNS alone with node_dns_method: none. Supabase has its hosts entries set in Docker specs.
   1. And /etc/hosts with node_write_etc_hosts: false if you don’t need to self-host MinIO.
5. Comment out PKI files in .gitignore to manage them via Git.