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.