šŸŖ“ Zero's Garden

Supabase on Pigsty modifications

5 min read

Caveats

  1. PostgREST performance worsen with more than 16 CPU cores. To be safe, you should use 8-16 threads with GHCRTS="-N<x>" 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 e46ba4a..f393d22 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 673aa6d..67637d2 100644
--- a/app/supabase/volumes/api/kong.yml
+++ b/app/supabase/volumes/api/kong.yml
@@ -30,6 +30,24 @@ basicauth_credentials:
     username: $DASHBOARD_USERNAME
     password: $DASHBOARD_PASSWORD
 
+###
+### Upstreams for load balancing
+###
+upstreams:
+  - name: rest-upstream
+    algorithm: least-connections
+    healthchecks:
+      active:
+        healthy:
+          interval: 5
+          successes: 2
+        unhealthy:
+          interval: 5
+          http_failures: 3
+    targets:
+      - target: rest:3000
+        weight: 100
+
 ###
 ### API Routes
 ###
@@ -87,7 +105,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 +126,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
  1. 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.
  2. supabase/storage is written in Node.js and can only saturate 1 CPU core. You need multiple if it becomes a bottleneck.
  3. 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/.
  4. 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. 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. 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.
diff -Nuar pigsty-template/app/supabase/docker-compose.yml pigsty/app/supabase/docker-compose.yml
--- pigsty-template/app/supabase/docker-compose.yml	2025-08-15 05:04:31.000000000 +0000
+++ pigsty/app/supabase/docker-compose.yml	2025-10-20 04:05:31.934577659 +0000
@@ -361,21 +361,3 @@
       - 4000:4000
     extra_hosts:
       - "${POSTGRES_DOMAIN}:${POSTGRES_HOST}"
-
-  #--------------------------------------------------------------#
-  # Vector: collecting logs
-  #--------------------------------------------------------------#
-  vector:
-    container_name: supabase-vector
-    image: timberio/vector:0.28.1-alpine
-    healthcheck:
-      test: [ "CMD", "wget", "--no-verbose", "--tries=1", "--spider","http://vector:9001/health" ]
-      timeout: 5s
-      interval: 5s
-      retries: 3
-    environment:
-      LOGFLARE_PUBLIC_ACCESS_TOKEN: ${LOGFLARE_PUBLIC_ACCESS_TOKEN}
-    volumes:
-      - ./volumes/logs/vector.yml:/etc/vector/vector.yml:ro
-      - ${DOCKER_SOCKET_LOCATION}:/var/run/docker.sock:ro
-    command: [ "--config", "/etc/vector/vector.yml" ]
\ No newline at end of file
diff -Nuar pigsty-template/files/postgres/supa-kick pigsty/files/postgres/supa-kick
--- pigsty-template/files/postgres/supa-kick	2025-08-15 05:04:31.000000000 +0000
+++ pigsty/files/postgres/supa-kick	2025-10-20 04:06:12.868136800 +0000
@@ -13,4 +13,4 @@
 #==============================================================#
 
 # perform regular dummy update on supabase _analytics table to kick the stupid lag feedback
-psql -Atqwc 'update _analytics.users SET id = 1 where id = 1;'
\ No newline at end of file
+psql -Atqwc 'update _analytics.users SET id = 1 where id = 1;' supabase
diff -Nuar pigsty-template/pigsty.yml pigsty/pigsty.yml
--- pigsty-template/pigsty.yml	2025-10-20 04:07:45.934530281 +0000
+++ pigsty/pigsty.yml	2025-10-20 04:11:06.698695723 +0000
@@ -30,6 +30,7 @@
         192.168.64.4: { infra_seq: 1 }
       vars:
         repo_enabled: false    # disable local repo
+        loki_enabled: false
 
     #----------------------------------------------#
     # ETCD : https://doc.pgsty.com/etcd
@@ -90,7 +91,6 @@
               - { name: wrappers         ,schema: extensions } # wrappers: FDW collections
               - { name: http             ,schema: extensions } # http: allows web page retrieval inside the database.
               - { name: pg_cron          ,schema: extensions } # pg_cron: Job scheduler for PostgreSQL
-              - { name: timescaledb      ,schema: extensions } # timescaledb: Enables scalable inserts and complex queries for time-series data
               - { name: pg_tle           ,schema: extensions } # pg_tle: Trusted Language Extensions for PostgreSQL
               - { name: vector           ,schema: extensions } # pgvector: the vector similarity search
               - { name: pgmq             ,schema: extensions } # pgmq: A lightweight message queue like AWS SQS and RSMQ
@@ -192,7 +192,7 @@
       prometheus   : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
       alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
       blackbox     : { endpoint: "${admin_ip}:9115" }
-      loki         : { endpoint: "${admin_ip}:3100" }
+      #loki         : { endpoint: "${admin_ip}:3100" }
       pgadmin      : { domain: adm.pigsty ,endpoint: "${admin_ip}:8885" }
       bytebase     : { domain: ddl.pigsty ,endpoint: "${admin_ip}:8887" }
       minio        : { domain: m.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
@@ -218,10 +218,13 @@
     # NODE : https://doc.pgsty.com/node/param
     #----------------------------------------------#
     nodename_overwrite: false             # do not overwrite node hostname on single node mode
+    #node_write_etc_hosts: false
     node_tune: oltp                       # node tuning specs: oltp,olap,tiny,crit
     node_etc_hosts:                       # add static domains to all nodes /etc/hosts
       - 192.168.64.4 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty supa.pigsty
     node_repo_modules: node,pgsql,infra   # use pre-made local repo rather than install from upstream
+    node_dns_method: none
+    promtail_enabled: false
     node_repo_remove: true                # remove existing node repo for node managed by pigsty
     #node_packages: [openssh-server]      # packages to be installed current nodes with latest version
     #node_timezone: Asia/Hong_Kong        # overwrite node timezone
@@ -301,4 +304,4 @@
         cipher_pass: pgBackRest           # AES encryption password, default is 'pgBackRest'
         retention_full_type: time         # retention full backup by time on minio repo
         retention_full: 14                # keep full backup for the last 14 days
-...
\ No newline at end of file
+...

Graph View