Skip to main content

0004 - Use PostgreSQL as a relational store

Status: Accepted

Date Accepted: 5/5/2022

Reviewers: @felipe-lee @ferlatte @JasonLin0991 @macrael @mikena-solution8 @reggieriser @sandy-wright @suzubara

Context​

It is typical for our projects to require the persistent storage of data. A relational database is often a good choice for this as it allows us to predefine a schema for and relationships between data. A database management system (DBMS) can interpret and enforce these rules while providing a means to efficiently and safely interact with the data. This ADR documents the reasoning behind our recommendation of a preferred DBMS.

Decision​

Use PostgreSQL when you need a relational store.

Why is this Applicable to the Practice as a Whole​

Since the requirement for a relational data store is common, there are advantages of us standardizing on one DBMS across the practice. Benefits include:

  • ability to transfer knowledge across projects
  • reduced ramp-up time when switching projects
  • support by cloud providers, allowing reuse of infrasec practices
  • norming on a common SQL/DDL dialect

Historically, most Solution8 projects deploy a database on Azure. Note that Azure provides two PostgreSQL options: Azure Database for PostgreSQL and Azure Cosmos DB for PostgreSQL. The typical Solution8 project will likely use Azure Database for PostgreSQL since Azure Cosmos DB for PostgreSQL targets very large or high-availability sites. That said, evaluate the features and pricing for each against your project's needs.

When to Not Implement This Decision​

  • If the project's client/sponsor has an established and/or inflexible existing DBMS requirement
  • If the nature of the data lends itself to a different type of data store (NoSQL database, graph database, etc.)
  • The project is so small, self-contained, or temporary that a lightweight solution like SQLite is more appropriate

Alternatives Considered​

PostgreSQL​

Pros​

  • + Mature and full-featured DBMS with a 25-year history
  • + Free and open-source
  • + Good documentation and user community
  • + Advanced JSON support, so flexible enough for some unstructured data
  • + Supported by most database tools and ORMs
  • + Most Solution8 projects have used PostgreSQL for a relational store, so we already have experience we can build upon

Cons​

  • - No guaranteed support (unless you pay for third-party support)
  • - Some clients may not be comfortable with an open-source DBMS

MySQL​

Pros​

  • + Mature DBMS with a 25-year history
  • + Community edition is free and open-source
  • + Good documentation and user community
  • + Supported by most database tools and ORMs

Cons​

  • - No guaranteed support (unless you pay for third-party support)
  • - Some clients may not be comfortable with an open-source DBMS
  • - Given that MySQL is owned by Oracle, some worry that it may be harder to contribute to or not always be free
  • - More advanced features are found in the paid Enterprise Edition
  • - ACID support is not as mature

Commercial Databases (Oracle, SQL Server, DB2, etc.)​

Pros​

  • + Mature DBMS with long history
  • + Traditional paid software backed by a company and a support team
  • + Supported by most database tools and ORMs

Cons​

  • - Licensing fees and support can be very expensive
  • - Not open source so you are reliant on company for fixes
  • - Support by ORMs can be spotty
  • - Differences in SQL syntax and error codes can make it difficult to get help compared to more popular open-source options