DEV Community: Scott P

Scaling Postgres COPY

Scott P — Sun, 26 Sep 2021 19:30:01 +0000

This post will be fairly quick, and covers how you can significantly reduce the amount of time a Postgres COPY command takes to complete.

Not too long ago, I managed to finish building out the first revision of my UK transport data platform.

During the time I was building it, I came across numerous challenges but perhaps one of the most critical - How do I improve the performance of data import?

Allow me to provide some context. I provide transport data for buses, trains and London underground via a GraphQL API. I source my data from numerous official providers, including National Rail, Network Rail, and the UK government open data sources. They provide me with huge datasets such as bus stop locations, transit schedules, etc. Every day, I retrieve this data and import it into a Postgres database.

One of my these datasets has over 38 million rows, and so ensuring that I can import that as quickly as possible is important for maintaining the general uptime of the API.

Here's a few quick tips which can significantly improve performance when importing datasets using Postgres COPY command:

Delete indexes before importing

Indexes provide a way for Postgres to quickly locate data in tables. You can run a database without them, but they help improve read performance significantly on large datasets. The downside of them is that they drastically reduce the performance of inserts. Removing them before you begin an import, and recreating them after the import is finished, reduces the need for the database to update indexes at the same time as it's handling the data import.

Set tables to unlogged before importing

When a table is set to LOGGED, it will write changes made to that table in the WAL (Write-Ahead Log). This helps ensure data consistency, and reduces the chance that a table will be corrupted. Unfortunately, much like indexes, this also reduces performance significantly when writing to the table. Setting a table to UNLOGGED before you begin the import, and then setting it to LOGGED after the import completes can improve performance significantly.

Let's cover some numbers.

Importing the data with indexes in-place, and tables set to logged: Approximately 27 minutes to import data to 6 tables

Importing the data without indexes, and tables set to unlogged: Approximately 10 minutes to import data to 6 tables

The creation of several indexes on the largest table after data import has completed takes around 3 minutes to complete. If I opted not to recreate the indexes, the entire import process would be completed in 7 minutes.

Self-hosting with Supabase

Scott P — Thu, 22 Jul 2021 01:15:56 +0000

Please note that this article is a work in progress. Please leave any suggestions or questions in the comments.

Relevant materials:

https://supabase.io/docs/guides/self-hosting#configuring-each-service

What is Supabase?

Supabase is an open-source database solution based on Postgres. It includes all the standard features of Postgres with some killer additions such as realtime streams and a REST API. These are all backed up by incredibly powerful libraries.

Pros and cons of self-hosting

There are many reasons you might want to self-host Supabase:

More database space - by using your own VPS, you could have a database that is many times bigger than what is (currently) available via Supabase managed platform
Full infrastructure control - you're in control of everything so if you need to make a change, you have that option

There are also some reasons you may want to let Supabase host your database instead of self-hosting:

It's already setup - You don't have to have a significant level of technical knowledge to setup a Supabase instance on their platform
Costs - They offer a free plan which may be enough for your needs

Who is this article for?

This article was written for people who want to self-host Supabase but have perhaps gotten lost with the self-hosting guides out there. Please be aware that at this time, the self-hosted options don't include the dashboard. If that's a deal breaker for you, this guide isn't for you.

To get the most out of this article, it is strongly advised that you have some basic experience with the following:

Nginx - Configuring endpoints / proxies
Docker - Deploying, configuring Dockerfiles

Even if you're lacking those skills, I've done my best to explain the key parts, as well as the files you need to change.

We will be using Portainer to manage our Docker containers. We'll also be using our service behind Cloudflare. For this, we'll use db.example.com as our domain name. This will be mentioned later in the Nginx configuration section.

Getting setup

The first thing you should do is setup somewhere to host. I personally recommend DigitalOcean as almost everything can be done from the web dashboard. For the purpose of this article, if you see the word 'Droplet', this simply refers to a VPS which will host our Supabase instance.

Supabase provide some one-click deploys for several platforms, but we won't be using those as they don't contain the full Supabase experience.

Instead, we'll be installing Docker. Several VPS providers already have 'ready to deploy' Docker instances. I'd recommend using one as this can save you a significant amount of time compared to setting Docker up manually.

Assuming you have setup a Droplet / VPS with Docker running, the first thing to do is install Portainer. To do this, follow the guide at https://documentation.portainer.io/v2.0/deploy/ceinstalldocker/ . As of writing, there are only 3 steps needed to get Portainer installed:

SSH into your VPS
Run docker volume create portainer_data
Run docker run -d -p 8000:8000 -p 9000:9000 --name=portainer --restart=always -v /var/run/docker.sock:/var/run/docker.sock -v portainer_data:/data portainer/portainer-ce

Next, you need to perform some basic configuration for Portainer. In your web browser, go to http://<ip-of-your-vps>:9000 and follow the on-screen instructions.

Next, we need to setup Nginx. Fortunately, this is very easy with Portainer.

Optional tip before continuing: Setup 2 volumes, nginx-config and nginx-data, and then use those from the volumes tab when setting up Nginx.

From the left-menu of the Portainer dashboard, select 'App Templates' and from the list, select 'Nginx', and then follow the instructions.

Deploying via Portainer

If you read this guide originally, I suggested creating a fork of the supabase repo. This is no longer what I recommend, as a better and more streamlined approach is available.

Going back to the Portainer web dashboard, select 'Stacks' from the left menu, and then click on 'Add Stack'.

Give your stack a name, and select 'Git repository'. For the repository URL, enter the full URL to the supabase repo (https://github.com/supabase/supabase).

In the 'Compose path' field, enter /docker/docker-compose.yml.

Copy the contents of the .env file (https://github.com/supabase/supabase/blob/master/docker/.env.example) to a blank file, and update the following values with valid details:

OPERATOR_TOKEN=your-super-secret-operator-token

JWT_SECRET=your-super-secret-jwt-token-with-at-least-32-characters-long

POSTGRES_PASSWORD=your-super-secret-and-long-postgres-password

# some SMTP server to send your auth-mails with
SMTP_HOST=mail.example.com
SMTP_PORT=
SMTP_USER=
SMTP_PASS=

Under 'Environment variables' click on 'Advanced mode', and then copy and paste the contents of your .env file.

Finally, click 'Deploy the stack' at the bottom. This process might take a few minutes to finish. Once it is completed, click 'Containers' from the Portainer menu. You may notice that supabase-auth isn't running initially. Simply select it, and then click 'Start'. It should then run without any issues.

The final step for this section is configuring the hostname for each Supabase service. From the containers menu item in the Portainers dashboard, select each Supabase container. Click on 'Duplicate/edit' near the top of the page. Then, scroll to the bottom of the page, select the 'Network' tab under 'Advanced container settings'. In the 'Hostname' field, enter the name of the service. For example, for the supabase-rest container, set the hostname to rest. You can set this to anything you like, but I recommend keeping it relevant to the container. Finally, click 'Deploy this container'. It will warn you that a container with the same name already exists - click 'OK'. After a short while, the container will be redeployed with the new hostname. Repeat these steps for each Supabase container.

By doing this, it will help Nginx connect to the container and make it easier to identify in other parts of the infrastructure.

One very important note regarding the supabase-auth container: By default, the DATABASE_URL will include sslmode=disable. The equals sign in this value may cause errors. For example, if you try to call the .signUp() method from the supabase-js library, you may get an error 500. The reason for this is that it will attempt to connect to disable instead of postgres://<db_user>:<password>@db:<db_port>/postgres?sslmode=disable

The solution to this is to ensure that you remove sslmode=disable and any other query parameters from the URL.

Configuring Nginx

In order to setup Nginx to support the various services needed for Supabase, we need to modify our Nginx config.

To do this, you need to SSH into your server, and then cd your way to the volume where your Nginx config is stored. If you set up a nginx-config volume as mentioned above, this will (usually) be located at /var/lib/docker/volumes/nginx-config/_data.

From here, we need to modify 2 files. The first is nginx.conf.

Inside the http section, add the following:

map $http_upgrade $connection_upgrade {
    default upgrade;
    '' close;
}

upstream websocket {
    server realtime:4000;
}

The other file we need to modify is conf.d/default.conf. There's many ways you can configure Nginx, but for the sake of simplicity, we're going to keep this to a single file.

In conf.d/default.conf, replace the entire contents with the following:

server {
    listen 443 ssl;
    server_name db.example.com;

    # REST
    location ~ ^/rest/v1/(.*)$ {
        proxy_set_header Host $host;
        proxy_pass http://kong:8000;
        proxy_redirect off;
    }

    # AUTH
    location ~ ^/auth/v1/(.*)$ {
        proxy_set_header Host $host;
        proxy_pass http://kong:8000;
        proxy_redirect off;
    }

    # REALTIME
    location ~ ^/realtime/v1/(.*)$ {
        proxy_redirect off;
        proxy_pass http://kong:8000;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection $connection_upgrade;
        proxy_set_header Host $host;
    }
}

Let me explain what's going on here.

Firstly, our REST handler is listening to all requests sent to /rest/v1 path, as well as anything added after that part of the path. It is then routing it to the Supabase Kong server, which by default will be accessible internally at http://kong:8000.

Likewise, our auth handler is listening for requests to /auth/v1, and routing them also to Kong.

Finally, the realtime subscriptions are handled by the /realtime/v1 route. Again, this routes to Kong.

If you read this guide when it was originally written, I told you to route requests to each individual service. After further investigation, it appears that routing to Kong is the better solution.

The reason we perform this proxying is that it allows us to ensure that requests from the Supabase libraries (e.g. @supabase/supabase-js) are able to correctly route to the different services in the stack.

Kong will handle routing each request to it's appropriate service based upon the URL, while Nginx is providing a way to expose these endpoints under a single URL.

After you've made the changes, save the file, and restart the Nginx container from the Portainer dashboard. To make sure everything is working as expected, select the Nginx container and then 'View logs'. It should indicate that everything is running as expected.

Finally, from the Nginx container (in the Portainer dashboard), connect it to the supabase_default network - the option to do this is found at the bottom of that page in the dashboard.

Necessary Kong configuration

From Portainer, create a new volume from the 'Volumes' menu option. Name the new volume kong-data.

Then, go back to 'containers', and select the 'supabase-kong' container. Click on 'Duplicate/Edit' at the top.

Scroll to the bottom of the page and click 'Volumes'. Click 'Map additional volume'. In the field marked 'Container', enter /var/lib/kong. Then click on the 'Volume' button to the right side of it.

From the dropdown field underneath, select the 'kong-data' volume you created a moment ago. Then click 'Deploy this container'.

GoTrue / auth environment variables

By default, the environment variables for each container should work without any issues, but the GoTrue / auth container may require some additional configuration.

For the sake of simplicity, here is an example environment config you can use in the supabase-auth container - edit it to fit your needs:

GOTRUE_OPERATOR_TOKEN=your-super-secret-operator-token
GOTRUE_JWT_DEFAULT_GROUP_NAME=authenticated

# Make sure this JWT secret matches what was configured during setup
GOTRUE_JWT_SECRET=your-super-secret-jwt-token-with-at-least-32-characters-long

# How long should JWT tokens be valid for?
GOTRUE_JWT_EXP=3600

# Since Supabase is based on Postgres, you shouldn't need to change this
GOTRUE_DB_DRIVER=postgres

# What schema should requests be routed to?
# There should be no reason to change this
DB_NAMESPACE=auth

# Where is our auth/GoTrue located
# You shouldn't need to change these unless the ports are mapped differently
GOTRUE_API_HOST=0.0.0.0
PORT=9999

# Email settings
# You must set these if you want to be able to send emails
GOTRUE_SMTP_HOST=smtp.your-email-host.com
GOTRUE_SMTP_PORT=465
GOTRUE_SMTP_USER=your-smtp-user
GOTRUE_SMTP_PASS=your-smtp-password

# Should users be required to confirm their email address before they can log in?
# If set to false, users won't have to confirm their registration
# If set to true, users will have to click the link in their email to confirm
GOTRUE_MAILER_AUTOCONFIRM=false

# What is the 'from' address that emails are sent from?
GOTRUE_SMTP_ADMIN_EMAIL=noreply@example.com

# Remove this if you don't want debug logs
GOTRUE_LOG_LEVEL=debug

# The connection string for your database
# `@db` says we're looking for the container called 'db' on our docker network
DATABASE_URL=postgres://postgres:your-super-secret-and-long-postgres-password@db:5432/postgres

# Email templates
# Invite user - provide a URL to a HTML or Text template
GOTRUE_MAILER_TEMPLATES_INVITE=https://example.com/path/to/your/invite/template.html

# Confirm registration - provide a URL to a HTML or Text template
GOTRUE_MAILER_TEMPLATES_CONFIRMATION=https://example.com/path/to/your/confirmation/template.html

# Password recovery - provide a URL to a HTML or Text template
GOTRUE_MAILER_TEMPLATES_RECOVERY=https://example.com/path/to/your/password_reset/template.HTML

# Magic link - provide a URL to a HTML or Text template
GOTRUE_MAILER_TEMPLATES_MAGIC_LINK=https://example.com/path/to/your/magic_link/template.html

# GoTrue URLs
# These are appended after the API_EXTERNAL_URL
# You shouldn't need to change these
GOTRUE_MAILER_URLPATHS_CONFIRMATION=/auth/v1/verify
GOTRUE_MAILER_URLPATHS_INVITE=/auth/v1/verify
GOTRUE_MAILER_URLPATHS_CONFIRMATION=/auth/v1/verify
GOTRUE_MAILER_URLPATHS_RECOVERY=/auth/v1/verify

# Site URLs
# This is where the user will be redirected to after clicking a link in an email and after oAuth
GOTRUE_SITE_URL=https://example.com/redirect_to_here
GOTRUE_URI_ALLOW_LIST=https://example.com/redirect_to_here

# This is the URL where your supabase stack is accessible
# i.e. this is the endpoint URL you would pass into a `createClient()` call in the supabase-js library
API_EXTERNAL_URL=https://database.example.com/

# Set this to true if you want to prevent signing up with email and password
GOTRUE_DISABLE_SIGNUP=true

# oAuth
# If you are not using oAuth to login (e.g. Login with Facebook), you can ignore the below
# If you want to disable oAuth for a specific provider, set the `GOTRUE_EXTERNAL_<provider>_ENABLED` to false
# Github oAuth
GOTRUE_EXTERNAL_GITHUB_CLIENT_ID=your_github_client_id
GOTRUE_EXTERNAL_GITHUB_SECRET=your_github_client_secret
GOTRUE_EXTERNAL_GITHUB_ENABLED=true

# Google oAuth
GOTRUE_EXTERNAL_GOOGLE_CLIENT_ID=your-google-client-id.apps.googleusercontent.com
GOTRUE_EXTERNAL_GOOGLE_SECRET=your-google-secret
GOTRUE_EXTERNAL_GOOGLE_ENABLED=true

# Facebook oAuth
GOTRUE_EXTERNAL_FACEBOOK_CLIENT_ID=your-facebook-client-id
GOTRUE_EXTERNAL_FACEBOOK_SECRET=your-facebook-app-secret
GOTRUE_EXTERNAL_FACEBOOK_ENABLED=true

# Add other oAuth provider details below

Final checks

At this point, everything should be running as expected.

You should be able to connect to your Postgres server using your VPS IP address, port 5432, and using the username postgres and the postgres password you setup inside the environmental variables.

Closing notes

As stated previously, this article is a work in progress. I am still working through some of the finer details of configuring the Supabase stack for self-hosting. There might be some things missing from the article - leave a comment to let me know, and I'll look into adding it.

Setting up Supabase for self-hosting is by no means a quick task, and there's still room for improvement. Hopefully, this article will help speed up the process for you and set you off on the journey.

Server-side MobX

Scott P — Sat, 19 Dec 2020 12:32:18 +0000

What?

I know what you're thinking - "State management on a server? Shouldn't servers be stateless?"

Today, I'm going to jump through a few use-cases for having server-side state.

Why?

We've been taught that servers, in general, should be stateless, and that anything your server needs should be stored in a database or a config file.

What happens if you want to dynamically control these? For example, let's say you have some scheduled tasks running in your app. You could potentially use Cron (or one of the Cron libraries for a code-based solution). If you want to change them, that's either updating a config, or doing a new deploy with new code.

Likewise, if you want to manage which GraphQL resolvers are enabled, you'd have to go through the same process.

With the rise of services like DigitalOcean's App Platform, neither of these are ideal solutions as they either require changing the config inside a docker container (requiring reconfiguring each time you redeploy), or they require you to redeploy, using some of those crucial build minutes and fixing up any issues that might crop up.

What if I told you there was an easier way? For this discussion, I'm going to be using FaunaDB, combined with MobX 6.0 to create dynamic config management in a Node server. I won't be covering FaunaDB integration in this article, as you could use any database solution, or even just have a remote file with your config stored.

How?

For this example, I'm going to use the scheduled tasks configuration. My use case is retrieving tweets from the National Rail enquiries Twitter account, which provides train delay information in the UK.

However, I only want to run that task if I've enabled it. Retrieving tweets is a typical use case for many applications, but consider it as just an example for the sake of this article.

The first thing to do is create a MobX store. This is just a class, with some properties which are marked as @observable, an @action to update the state, and some @computed getters to retrieve single fields from my state:


import logger from "@Modules/Logging/logging.module";
import { observable, computed, action, makeAutoObservable } from "mobx";
import { IFaunaDbEnvironmentScheduledTask } from "./interfaces";

export enum EScheduledTask {
    fetch_and_import_national_rail_delay_tweets = "fetch_and_import_national_rail_delay_tweets",
}

export type RScheduledTask = Record<EScheduledTask, IFaunaDbEnvironmentScheduledTask>;

export interface IFaunaDbEnvironmentConfig {
    myapi: {
        scheduled_tasks: RScheduledTask;
    };
}

class EnvironmentConfigStore {
    constructor() {
        makeAutoObservable(this);
    }

    @observable public EnvironmentConfig: IFaunaDbEnvironmentConfig = null;
    @action public setConfig = (config: IFaunaDbEnvironmentConfig) => {
        logger.log("debug", `Server config loaded to store successfully!`);
        this.EnvironmentConfig = config;
    };

    @computed public get scheduledTasks() {
        return this?.EnvironmentConfig?.myapi?.scheduled_tasks;
    }
}

const EnvironmentConfig = new EnvironmentConfigStore();

export default EnvironmentConfig;

As you can see, I've defined an interface for my state (which matches the structure of a document stored in FaunaDB), created a state store class, and decorated my properties. This is all fairly standard for MobX. I've also used makeAutoObservable in my constructor. I've also got a logger.log call in there - this is just a standard Winston logger class.

The next step is to use a MobX reaction to monitor my scheduled task. I do this in a separate file because writing modular code is something that you should try to do where possible:

import { reaction } from "mobx";
import EnvironmentConfigStore from "@Stores/store.environmentConfig";
import logger from "@Modules/Logging/logging.module";

let timer: NodeJS.Timeout = null;

const disableTimer = () => {
    clearInterval(timer);
};

// Check if the task is enabled
// Disables timer if not
reaction(
    () => EnvironmentConfigStore.scheduledTasks.fetch_and_import_national_rail_delay_tweets.enabled,
    enabled => {
        logger.log("debug", `fetch_and_import_national_rail_delay_tweets is now ${enabled ? "enabled" : "disabled"}!`);
        if (enabled === false) {
            disableTimer();
        } else {
            timer = setInterval(() => {
                console.log("Task would run now!");
            }, EnvironmentConfigStore.scheduledTasks.fetch_and_import_national_rail_delay_tweets.frequency_in_ms);
        }
    },
    {
        fireImmediately: true,
        onError: error => {
            console.log("Error in reaction: ", error);
        }
    }
);

What we're doing here is creating a reaction which will trigger each time the scheduledTasks.fetch_and_import_national_rail_delay_tweets.enabled property changes.

If the property changes to enabled: false, we stop our timer, otherwise, we start our timer. You can see that I currently only have a console.log("Task would run now!") as my function for the timer, but you can do whatever you wish to do in there.

Since the reaction only runs when the value changes, the timer will only be created when the value is set to true, and only cleared if the value changes to false - to clarify: You will not have multiple timers running if you use reaction in this way.

The final step is to get the config from FaunaDB, and update the store:

import EnvironmentConfigStore from "@Modules/Stores/store.environmentConfig";

doSomethingThatRetrievesConfig().then(myConfig => {
  EnvironmentConfigStore.setConfig(myConfig)
});

In this example, I retrieve the config from FaunaDB and then update the store. You could run this in a timer to retrieve it every so often, or you could subscribe to the document instead - the process is the same in either case.

That's all there is to it. Whenever I update the document which contains my server config on FaunaDB, this is propagated to the store, which then handles enabling or disabling the timer for the scheduled task.

You can integrate this in any way that feels right for your codebase.

Other use cases

There's potentially unlimited use cases for this. Here's just a few:

Dynamically enabling or disabling GraphQL resolvers
Marking a server as production, staging, local, etc
Enabling or disabling access to routes dynamically

Final notes

If you want to be able to configure your server at runtime, and serverless isn't a suitable use case for your project, then having some sort of state management becomes necessary. The beauty of this method is that it works with any database system. You could potentially just store the config in a file somewhere and periodically retrieve that file instead, but you've got to ensure you have the security you need around it.

To reiterate, my use case was based on DigitalOcean App Platform, and I wanted an easy way to manage scheduled tasks (amongst some other server config, which isn't covered here).