Spring Boot with PostgreSQL Multi-Schema

🌟 Introduction

When you're building a backend system where all users share the same application instance, but you still want a clean, modular architecture without microservices, multi-schema support is a powerful yet underutilized option.

In this post, we’ll walk through:

  • Why and when to use PostgreSQL multi-schema with Spring Boot

  • How to structure a modular monolith using JPA

  • How cross-schema entity relationships, joins, and transactions work

  • Production-grade considerations and anti-patterns

🧠 Use Case

“I have a single Spring Boot application, one shared PostgreSQL database, and a common user base. I want to logically separate domains like billing, authentication, user management, and auditing—without using microservices or tenant-based sharding.”

This is a perfect fit for:

  • Modular monolith architecture

  • Single-tenant but domain-isolated applications

  • Clean layering of domain logic and future scalability

🗂️ Schema Design

Here’s the schema layout we’ll follow:

Schema
Domain
Tables

users

User Management

profile, settings

auth

Authentication

user_credentials, sessions

billing

Payments & Invoicing

invoice, payment

audit

Activity Logging

activity_logs

This gives us:

  • Logical separation

  • Easier domain-based access control

  • Organized migrations

  • Future service modularity

❔ Why Multi-Schema?

  • 🔒 Security: Sensitive authentication data isolated in auth schema

  • 📊 Data Organization: Business domains clearly separated

  • 🔧 Maintainability: Schema-specific changes don't affect other domains

  • ⚡ Performance: Targeted indexing and optimization per schema

  • 👥 Team Collaboration: Different teams can work on different schemas

  • 🛡️ Access Control: Fine-grained database permissions per schema

🔧 Project Setup

We use standard Spring Boot with:

  • JPA (Hibernate)

  • PostgreSQL (single database)

  • HikariCP (connection pool)

  • Flyway (for schema-based migrations)

# application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=myuser
spring.datasource.password=mypass
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=false
spring.flyway.enabled=true
spring.flyway.schemas=public,users,auth,billing,audit

🧱 Entity Definitions with Schema Mapping

1. UserProfile in users schema

@Entity
@Table(name = "profile", schema = "users")
public class UserProfile {
    @Id @GeneratedValue
    private Long id;
    private String name;

    @OneToMany(mappedBy = "user")
    private List<Invoice> invoices;
}

2. Invoice in billing schema — Cross-Schema Join

@Entity
@Table(name = "invoice", schema = "billing")
public class Invoice {
    @Id @GeneratedValue
    private Long id;
    private Double amount;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private UserProfile user;
}

✅ This is an example of explicit ORM cross-schema mapping.

  1. UserCredentials in auth schema

@Entity
@Table(name = "user_credentials", schema = "auth")
public class UserCredentials {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String username;
    private String passwordHash;

}

  1. AuditLog in auth Schema

@Entity
@Table(name = "activity_logs", schema = "audit")
public class AuditLog {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String activity;
    private String performedBy;
    private LocalDateTime timestamp;
}

🪽 Flyway Schema Migration

--V1__Create_schemas.sql

-- Create the required schemas
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS users;
CREATE SCHEMA IF NOT EXISTS billing;
CREATE SCHEMA IF NOT EXISTS audit;

V2__Create_users_table.sql

-- Create users schema tables
CREATE TABLE users.profile (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on name for faster searches
CREATE INDEX idx_profile_name ON users.profile(name);

V3__Create_billing_tables.sql

-- Create billing schema tables
CREATE TABLE billing.invoice (
    id BIGSERIAL PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign key constraint to users.profile
    CONSTRAINT fk_invoice_user FOREIGN KEY (user_id) REFERENCES users.profile(id) ON DELETE CASCADE
);

-- Create index on user_id for faster lookups
CREATE INDEX idx_invoice_user_id ON billing.invoice(user_id);

-- Create index on amount for financial queries
CREATE INDEX idx_invoice_amount ON billing.invoice(amount);

🔁 Joining Entities Across Schemas

Hibernate supports joins across schemas as long as you’ve mapped the entities correctly.

Query: Get all invoices for users with name 'Alice'

@Query("SELECT i FROM Invoice i JOIN i.user u WHERE u.name = :name")
List<Invoice> findInvoicesByUserName(@Param(\"name\") String name);

➡️ This generates SQL:

SELECT * FROM billing.invoice i
JOIN users.profile u ON i.user_id = u.id
WHERE u.name = ?

💡 Use DTO projection when returning large datasets:

@Query("SELECT new com.example.dto.InvoiceSummaryDTO(u.name, i.amount) FROM Invoice i JOIN i.user u ")
List<InvoiceSummaryDTO> fetchInvoiceSummaries();

🚫 Avoiding Over-Normalization

A common mistake: mapping every domain with deep foreign keys.

For example:

@Entity
@Table(name = "user_credentials", schema = "auth")
public class UserCredentials {
    @Id @GeneratedValue
    private Long id;

    private String username;
    private String passwordHash;

    // ❌ Do not directly link this to UserProfile unless truly needed
}

Instead:

  • Use service-level joins if needed

  • Keep domain boundaries loose

  • Avoid deep nested fetches (especially auth ↔ billing ↔ audit)

🔄 Transactions Across Schemas

PostgreSQL handles transactions natively across schemas (same DB connection). So this just works:

@Transactional
public void createUserAndInvoice(UserProfile profile, Invoice invoice) {
    userRepository.save(profile);
    invoice.setUser(profile);
    invoiceRepository.save(invoice);
}

No need for XA transactions or multiple EntityManagers.

📐 Architecture Benefits

Feature
Value

Logical separation

✅ Strong domain boundaries

Team modularity

✅ Teams can own schema + module

Cross-schema joins

✅ Supported natively

Performance impact

❗ Slight schema overhead in large joins

Migration control

✅ Schema-based versioning with Flyway

Security

✅ Grant schema-level privileges easily

⚙️ Production Recommendations

  • Use @EntityGraph or DTOs to reduce unnecessary joins

  • Log SQL in non-prod with spring.jpa.show-sql=true

  • Monitor with pg_stat_statements for expensive queries

  • Avoid circular dependencies in entity mappings

📌 Summary

Using PostgreSQL multi-schema with Spring Boot JPA provides a powerful middle ground between monoliths and microservices. You get:

  • Logical modularity

  • Clean database boundaries

  • Transaction safety

  • Simpler codebase management

This approach is ideal for teams building well-structured, single-tenant applications that are future-proof and domain-oriented.

PreviousSpring Boot Multi-TenancyNextSpring Boot with PostgreSQL Multi-Tenant

Last updated