Skip to main content

30. PostgreSQL for Data Persistence

Status: Accepted Date: 2025-07-06

Context​

The Arcana application needs to store structured, relational data, including user accounts, tarot reading history, custom user personas, and deck configurations. A simple file-based or key-value storage solution would not provide the data integrity, querying capabilities, or scalability required for these tasks. A robust, relational database management system (RDBMS) is necessary.

Decision​

We will use PostgreSQL as the primary database for data persistence in the Arcana application and other backend services. PostgreSQL is a powerful, open-source object-relational database system with a strong reputation for reliability, feature robustness, and performance. It provides ACID compliance, strong support for complex queries, and a wide range of data types, making it suitable for our application's needs.

Consequences​

Positive:

  • Data Integrity: PostgreSQL's support for constraints, transactions, and foreign keys ensures a high degree of data integrity.
  • Powerful Querying: It offers a rich SQL dialect with advanced features like JSONB support and full-text search, enabling complex data retrieval and analysis.
  • Reliability & Scalability: It is a mature and highly stable database system that can scale to handle large volumes of data and concurrent users.
  • Ecosystem: There is a vast ecosystem of tools, libraries (like MikroORM), and community support for PostgreSQL.

Negative:

  • Operational Overhead: Managing a PostgreSQL server (including setup, backups, and maintenance) adds operational complexity compared to simpler storage solutions or managed NoSQL databases.
  • Schema Management: Changes to the database schema must be managed carefully through a migration process to avoid data loss or inconsistencies.

Mitigation:

  • Managed Database Services: In production environments, we will use a managed PostgreSQL service (e.g., Amazon RDS, DigitalOcean Managed Databases) to offload the operational burden of server management and backups.
  • Docker for Development: For local development, we will use Docker to provide a consistent, easy-to-manage PostgreSQL environment.
  • ORM for Schema Migrations: We will use MikroORM's built-in migration tools to manage all schema changes in a structured and version-controlled manner.