Skip to main content

PostgreSQL in LibreApps Desktop

The reliable foundation for your application data.

Overview​

PostgreSQL is the primary database for LibreApps Desktop's microservices. It provides a robust, feature-rich, and highly performant relational storage solution that ensures data integrity and consistency.

Usage in Microservices​

Each microservice that requires relational storage has its own dedicated PostgreSQL database (or schema). For example:

  • user_db: Stores user profiles, roles, and permissions.
  • account_db: Stores organization details and subscription info.
  • notification_db: Stores notification history and templates.

Configuration​

Microservices connect to PostgreSQL using Spring Data JPA and Hibernate. Connection details are typically provided in the application.yml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/user_db
    username: user
    password: password
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true

Performance Tuning​

  • Indexing: Ensure that all columns used in WHERE clauses or JOIN operations are properly indexed.
  • Connection Pooling: Use a connection pool like HikariCP (default in Spring Boot) to manage database connections efficiently.
  • Query Optimization: Use tools like EXPLAIN ANALYZE to identify and optimize slow-running queries.

Backup and Recovery​

In a production environment, it is critical to have a robust backup and recovery strategy for your PostgreSQL databases. This typically involves:

  • Daily Backups: Automated full backups of all databases.
  • Point-in-Time Recovery (PITR): Using Write-Ahead Logging (WAL) to recover the database to a specific moment in time.

Best Practices​

  • ✅ Do this: Use meaningful names for your tables and columns.
  • ✅ Do this: Implement foreign key constraints to ensure data integrity.
  • ❌ Don't do this: Use SELECT * in your application code; always specify the columns you need.