PostgreSQL

PostgreSQL 16 is the primary relational database for every service in the MATIH Platform. It stores transactional data, metadata, configuration, and audit records. Multi-tenancy is implemented through Hibernate's schema-based multi-tenancy, where each tenant has its own PostgreSQL schema within a shared database.

Role in the Platform

Aspect	Details
Version	16
Deployment	Kubernetes StatefulSet via Helm
Services using it	All 24 services
Multi-tenancy	Schema-per-tenant (Hibernate `SCHEMA` strategy)
Connection pooling	HikariCP with per-service configuration

Schema-Per-Tenant Model

Each service maintains a single database with multiple schemas:

PostgreSQL Instance
  +-- Database: matih_ai
  |     +-- Schema: system       (platform metadata)
  |     +-- Schema: acme_corp    (Tenant: ACME Corp)
  |     +-- Schema: globex       (Tenant: Globex Inc)
  |     +-- Schema: initech      (Tenant: Initech LLC)
  |
  +-- Database: matih_bi
  |     +-- Schema: system
  |     +-- Schema: acme_corp
  |     +-- Schema: globex
  |
  +-- Database: matih_control_plane
        +-- Schema: public       (shared Control Plane data)

Hibernate Multi-Tenancy

The TenantIdentifierResolver directs Hibernate to the correct schema:

@Component
public class TenantIdentifierResolver
    implements CurrentTenantIdentifierResolver<String> {
 
    @Override
    public String resolveCurrentTenantIdentifier() {
        return TenantContext.getCurrentTenantIdOrDefault("system");
    }
}

Before each query, Hibernate sets the PostgreSQL search path:

SET search_path TO 'acme_corp';
SELECT * FROM dashboards WHERE created_by = 'user-123';

Connection Pool Configuration

Parameter	Development	Production
Maximum pool size	10	30
Minimum idle	2	5
Connection timeout	30s	10s
Idle timeout	10 minutes	5 minutes
Max lifetime	30 minutes	30 minutes

Connection pool metrics are exposed via Micrometer:

Metric	Description
`hikaricp_connections_active`	Active connections in the pool
`hikaricp_connections_idle`	Idle connections in the pool
`hikaricp_connections_pending`	Threads waiting for a connection

Backup and Recovery

Strategy	Method
Per-tenant backup	`pg_dump` with `--schema` filter
Full database backup	`pg_dump` of entire database
Point-in-time recovery	WAL archiving to MinIO / S3
Tenant migration	Export schema, import to target database

Redis -- Caching layer
Multi-Tenancy: Database Isolation -- Schema isolation details
Data Infrastructure -- Technology overview

Overview Redis