PostgreSQL

PostgreSQL, often referred to as Postgres, is an open-source relational database management system. In Mastodon, it is used as the primary database to store and manage various types of data required for the functioning of the platform PostgreSQL plays a crucial role in Mastodon's architecture, providing persistence, data integrity, and efficient querying capabilities.

Here are some ways PostgreSQL is used in Mastodon:

  • User data storage: PostgreSQL stores user account information, such as usernames, email addresses, profile details, and encrypted passwords. This data is essential for user authentication, authorization, and managing user profiles.
  • Content storage: Mastodon stores user-generated content, such as statuses (toots), replies, favorites, and media attachments, in PostgreSQL. It also keeps track of relationships between these entities, such as which user authored a particular toot or which toots are part of a conversation thread.
  • Metadata storage: Mastodon stores metadata related to the platform's functioning, including server information, blocked servers, and domain-level configurations, in PostgreSQL. This information is used for managing the federated nature of the network.
  • Social graph management: PostgreSQL is used to store and manage the social graph, which consists of relationships between users, such as followers and followings, mute and block lists, and group memberships.

We use the DigitalOcean managed PostgresSQL database service, this delivers a highly available database backend. Updates and maintenance are performed by DigitalOcean, independent of our administration efforts.

There is one active PostgreSQL service, (Majel), with 4 vCPU and 16GB of memory, with dedicated vCPU cores allocated. We use PostgreSQL 15.x.

DigitalOcean Droplet "T-Shirt" sizes for databases are done by vCPU, memory, disk size, and connections to the database. The connection count limits are based on sizing best practices for PostgreSQL, with a few held in reserve for their use to manage the service.

DigitalOcean has an integrated "Connection Pool" feature of their platform which, in practice, puts the PgBouncer utility in front of the database. This acts as a reverse proxy / load balancer for the database, to make sure that connections to the database by Mastodon cannot stay open and consume resources longer than needed.

There are a few options for pooling modes with DigitalOcean, but the default Transaction Mode is the required option for Mastodon.

Example of .env.production configuration settings relevant to PostgreSQL:

# PostgreSQL
DB_HOST=path-to-postgresql-database.ondigitalocean.com
DB_PORT=25061
DB_NAME=the_mastodon_connection_pool
DB_USER=the_mastodon_user
DB_PASS=Ourpassw0rd!sNoneofyourbu$iness
PREPARED_STATEMENTS=false

The PREPARED_STATEMENTS=false is required of Mastodon to use PgBouncer. When performing upgrades of Mastodon that require changes to the database schema, you must temporarily modify the configuration on the system running the schema change to bypass PgBouncer and go directly to the database. You will need to remove the line with the prepared statement configuration or set it to true, then change the DB port and DB name values.