Mastodon on YugabyteDB – Dev Community

Twitter is changing and a lot of users are turning to Mastodon. With this increase of load, my database had to grow:

@Gargon
From 8 cores to 36 cores of processing power, let’s see how the new database server holds up. Hopefully from now on everything will be much faster, but this should give me a little more room to scale horizontally.
” loading=”lazy” width=”880″ height=”544″/>https://mastodon.social/@Gargron/109276158158046472

Wouldn’t it be easier to scale? Mastodon Uses PostgreSQLso let’s try it YugabyteDBWhich is an open source Postgres-compatible 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 it and replaced the base image gitpod/workspace-postgresql To gitpod/workspace-yugabytedb,

As the default port for YSQL, YugabyteDB is a PostgreSQL-compatible endpoint 5433 I changed the db parameter .env.production.example To:

DB_HOST=127.0.0.1
DB_PORT=5433
DB_NAME=mastodon
DB_USER=yugabyte
DB_PASS=
enter fullscreen mode

exit fullscreen mode

The YSQL endpoint takes a few seconds to become available, then I wait if it is available and create one mastodon Database:

      # 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
enter fullscreen mode

exit fullscreen mode

The creation of the database failed on two index creations:

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
enter fullscreen mode

exit fullscreen mode

I will not create this index. If it is needed for performance reasons, there are some alternatives (use .) text Instead inet datatype), and a Git issue may be opened to support it in Yugabyte.

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
enter fullscreen mode

exit fullscreen mode

participant_account_ids Is bigint[], I’m not sure how it makes sense to have a unique index as arrays containing the same items in different order will be treated as different.
Then, I would create a non-volatile function that sorts the items and returns them text,

       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;
enter fullscreen mode

exit fullscreen mode

and create the index as:

 CREATE UNIQUE INDEX index_unique_conversations ON public.account_conversations 
       (account_id, conversation_id, (array_signature(participant_account_ids)));
enter fullscreen mode

exit fullscreen mode

They are quick solutions. If you would like 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 have removed the index from the schema definition:

      # remove indexes that are not supported
      sed -e "https://dev.to/"index_unique_conversations"/d' -i db/schema.rb
      sed -e "https://dev.to/"index_ip_blocks_on_ip"/d' -i db/schema.rb
enter fullscreen mode

exit fullscreen mode

Then run creation:

      # create the database
      RAILS_ENV=production rails --trace db:setup
enter fullscreen mode

exit fullscreen mode

This work:
<br /> You are now connected to the database "mastodon" as user "ugabyte",<br /> ** Invoke db:setup (first time)<br /> ** Invoke db:create(first_time)<br /> ** Invoke db:load_config(first_time)<br /> ** Invoke Environment (first_time)<br /> ** execute environment<br /> **execute db:load_config<br /> ** execute db:create<br /> Database ‘mastodon’ already exists<br /> ** Invite Environment <br /> ** invoke db:schema:load(first_time)<br /> ** Invoke db:load_config <br /> ** Invoke db:check_protected_environments(first_time)<br /> ** Invoke db:load_config <br /> **execute db:check_protected_environments<br /> ** execute db:schema :weight” loading=”lazy” width=”880″ height=”484″/><br />If you are concerned with the performance of DDL, there are several ways to improve it (such as CREATE INDEX NONCONCURRENTLY).</p>
<p>Then, once created, I create the unique index on the array with the function:</p>
<div class=

      # 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
enter fullscreen mode

exit fullscreen mode

test

The log shows the created user and password:
picture description

I create some conversations:
picture description

see more account_conversations; Table to check that all is well:
picture description

in conclusion

I love: Gitpod, Mastodon, PostgreSQL and YugabyteDB. If you have an application on PostgreSQL, you can achieve high availability (resilience to failures and rolling upgrades) and elasticity (scaling 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 examine indexes (hash and range sharding) and test performance.

Leave a Comment