Mastodon on YugabyteDB
Tags: mastodon, yugabytedb, postgres, distributed
Published: 2022-11-04 22:27:04

Twitter is changing and lot of users are moving to Mastodon. With this increase of load, had to scale up their database:

[![
@Gargron
From 8 cores to 36 cores processing power, let's see how the new database server holds up. Hopefully everything will be much faster from now on, but it should also give me a bit more room to scale horizontally.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vw87gtfksa54rb14ngdc.png)https://mastodon.social/@Gargron/109276158158046472](https://mastodon.social/@Gargron/109276158158046472)

Wouldn't it be easier to scale out? Mastodon uses **PostgreSQL**, so let's try it on **YugabyteDB**, which is the Open Source [Postgres-compatible](https://www.yugabyte.com/tech/distributed-sql/) Distributed SQL database.

My test is available here, ready to run on GitPod: https://github.com/FranckPachot/gitpod-mastodon-yb

## Changes for YugabyteDB

I forked [this](https://gitlab.com/acefed/gitpod-mastodon) and changed the base image from `gitpod/workspace-postgresql` to `gitpod/workspace-yugabytedb`.

As the default port for YSQL, the YugabyteDB PostgreSQL-compatible endpoint is `5433` I changed the DB parameters in `.env.production.example` to:
```sh
DB_HOST=127.0.0.1
DB_PORT=5433
DB_NAME=mastodon
DB_USER=yugabyte
DB_PASS=
```

The YSQL endpoint takes a few seconds to be available, then I wait that it is available and create a `mastodon` database:
```sh
      # wait until YSQL is available, create the database and utility function
      until ysqlsh <<<'
       create database mastodon;\c mastodon
       ' ; do sleep 1 ; done 2>/dev/null
      # create the database
      RAILS_ENV=production rails --trace db:setup
```

The creation of the database failed on two index creation:

```sql
CREATE UNIQUE INDEX index_ip_blocks_on_ip ON public.ip_blocks USING btree (ip);

NOTICE:  index method "btree" was replaced with "lsm" in YugabyteDB
ERROR:  INDEX on column of type 'INET' not yet supported
```
I'll not create this index. If it is required for performance reasons, there are some alternatives (use `text` instead of `inet` datatype), and a git issue can be opened to support it in Yugabyte.

```sql
CREATE UNIQUE INDEX index_unique_conversations ON public.account_conversations USING btree (account_id, conversation_id, participant_account_ids);

NOTICE:  index method "btree" was replaced with "lsm" in YugabyteDB
ERROR:  INDEX on column of type 'user_defined_type' not yet supported
```
The `participant_account_ids` is `bigint[]`. I'm not sure how this makes sense in a unique index because arrays with the same items in different order will be considered different. 
Then, I'll create a non-volatile function that sorts the items and returns them as `text`:
```sql
       create or replace function array_signature(a bigint[])
       returns text as $$
       select array_agg(unnest order by unnest)::text
       from unnest(a);
       $$ immutable language sql;
```
and create the index as:
```sql
 CREATE UNIQUE INDEX index_unique_conversations ON public.account_conversations 
       (account_id, conversation_id, (array_signature(participant_account_ids)));
```

Those are quick workaround. If you want to run Mastodon with YugabyteDB in production, please contact the Yugabyte community: https://www.yugabyte.com/community/

To be able to create the database, I've removed the indexes from the schema definition:
```sh
      # remove indexes that are not supported
      sed -e '/"index_unique_conversations"/d' -i db/schema.rb
      sed -e '/"index_ip_blocks_on_ip"/d' -i db/schema.rb
```
then run the creation:
```sh
      # create the database
      RAILS_ENV=production rails --trace db:setup
```
This works:
![
You are now connected to database "mastodon" as user "yugabyte".
** Invoke db:setup (first_time)
** Invoke db:create (first_time)
** Invoke db:load_config (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:load_config
** Execute db:create
Database 'mastodon' already exists
** Invoke environment 
** Invoke db:schema:load (first_time)
** Invoke db:load_config 
** Invoke db:check_protected_environments (first_time)
** Invoke db:load_config 
** Execute db:check_protected_environments
** Execute db:schema:load](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xv8nvtg4d9j3huq01ber.png)
If you are concerned by the performance of DDL, there are ways to improve it (like CREATE INDEX NONCONCURRENTLY).

Then, once created, I create the unique index with the function on the array:
```sql
      # add indexes with DDL (this may be done from schema.rb)
      ysqlsh -e -c "
       create or replace function array_signature(a bigint[])
       returns text as '
       select array_agg(unnest order by unnest)::text
       from unnest(a);
       ' immutable language sql;
      " -c '
       CREATE UNIQUE INDEX index_unique_conversations ON public.account_conversations 
       (account_id, conversation_id, (array_signature(participant_account_ids)));
       ' mastodon
      # end modifications for YugabyteDB
```

## Testing

The logs shows the users and passwords created:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5bhewmxv0alon2qiokqp.png)

I create some conversations:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ffpjl7dc5jx49li51nep.png)

And look at the `account_conversations;` table to check that all is ok:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3igv7tscdzgpal7eki7f.png)

## In conclusion

I love: Gitpod, Mastodon, PostgreSQL and YugabyteDB. If you have an application on PostgreSQL, you can get High Availability (resilience to failures and rolling upgrades) and Elasticity (scale out and down without application downtime) with YugabyteDB. If something is not supported, please open a git issue or ask for a workaround, there are always many possibilities. When moving from monolithic to distributed database, it is also important to check the indexes (hash and range sharding) and test performance.