Building a PostgreSQL Data Platform: Implementing the Private Tables per Service Pattern
Introduction
Building a data platform on PostgreSQL involves more than choosing a database engine. It requires establishing architectural principles that support service autonomy, data ownership, scalability, and independent evolution.
One important architectural decision is how to organize data ownership in a microservices environment.
The Database per Service pattern, described by microservices.io, provides different approaches to ensure that each service maintains control over its own data.
This article explores the simplest of these approaches: Private Tables per Service, and how PostgreSQL can support its implementation through automated database provisioning.
1. Database per Service: Establishing Data Ownership
The Database per Service pattern establishes an important principle: each microservice must own its data and control how that data is accessed and modified.
Other services must not directly access its internal database structures. Instead, they must interact with the service responsible for the data through its published interfaces.
For example:
The Orders service owns order-related data.
The Customers service owns customer-related data.
The Payments service owns payment-related data.
If the Orders service needs customer information, it must request that information from the Customers service rather than directly accessing its tables.
This separation reduces coupling and allows services to evolve independently.
2. Private Tables per Service in PostgreSQL
Private Tables per Service is the simplest implementation of Database per Service.
In this approach, multiple services share the same database and schema, but each service maintains exclusive ownership of its own tables.
Consider the following example:
ServiceTablesOrdersorders_orders, orders_itemsCustomerscustomers_customers, customers_addressesPaymentspayments_transactions, payments_methods
All tables can reside in the same PostgreSQL schema.
The service prefix establishes a consistent naming convention and prevents naming collisions.
For example:
CREATE TABLE orders_orders ( id UUID PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL ); CREATE TABLE orders_items ( id UUID PRIMARY KEY, order_id UUID NOT NULL, quantity INTEGER NOT NULL ); CREATE TABLE customers_customers ( id UUID PRIMARY KEY, name VARCHAR(255) NOT NULL );
This approach provides logical ownership without requiring a separate database or schema for every service.
3. Why PostgreSQL?
PostgreSQL provides the native mechanisms required to implement this architecture.
Some particularly relevant capabilities include:
Object ownership and permissions
PostgreSQL roles, ownership, GRANT, and REVOKE allow database administrators to control which services can access or modify specific tables.
Transactional DDL
Many database structure operations can be executed within transactions, providing consistency during schema changes.
Constraints and indexes
Primary keys, unique constraints, foreign keys within a service, and indexes can be provisioned automatically.
System catalogs
PostgreSQL exposes metadata about database objects, allowing provisioning tools to inspect the existing database structure.
Concurrency control
PostgreSQL manages concurrent database operations through its locking and transaction mechanisms.
The architecture also eliminates cross-service table dependencies. Each service manages its own tables and their structural evolution.
4. Database Provisioning as a Platform Capability
The first essential capability of a PostgreSQL-based data platform should be automated database provisioning.
Rather than manually creating tables, developers should declare the structures required by their services.
Get Leandro Silveira’s stories in your inbox
Join Medium for free to get updates from this writer.
For example, a service definition could be expressed as YAML:
service: orders tables: - name: orders columns: - name: id type: uuid primary_key: true - name: created_at type: timestamptz nullable: false - name: items columns: - name: id type: uuid primary_key: true - name: order_id type: uuid nullable: false - name: quantity type: integer nullable: false
The provisioning engine would translate this definition into PostgreSQL DDL statements.
The process should support:
Service registration.
Table creation.
Primary and foreign key configuration.
Index creation.
Role and permission configuration.
Schema version management.
Migration execution and auditing.
This turns database provisioning into a repeatable and automated platform service.
5. Independent Schema Evolution
One of the main advantages of Private Tables per Service is the ability to evolve database structures independently.
For example, the Orders service can introduce a new column or table without requiring changes to the Customers service.
Each service maintains its own migration history.
This enables independent development and deployment cycles while PostgreSQL handles concurrent operations and transactional consistency.
The provisioning engine must nevertheless ensure that migrations are idempotent, properly versioned, and compatible with the service deployment strategy.
6. Security and Operational Considerations
Although services share the same PostgreSQL database and schema, their database permissions should remain independent.
The provisioning engine should establish dedicated database roles for each service and grant only the permissions required for its operations.
Application credentials should not have administrative privileges.
The platform should also maintain an audit trail containing:
Service identification.
Provisioning requests.
Database object definitions.
Migration versions.
Execution results.
Permission changes.
This provides the operational foundation for managing an increasing number of services.
7. From Database Provisioning to a Data Platform
Private Tables per Service is a practical starting point for building a PostgreSQL-based data platform.
It provides a relatively simple architecture while establishing fundamental principles:
Explicit data ownership.
Independent service evolution.
Automated database provisioning.
Controlled access to database objects.
Reduced coupling between microservices.
The next architectural step is to evaluate Schema per Service, which introduces a separate PostgreSQL schema for each service and changes the isolation and organization model.
The important point is that a data platform should not begin by introducing unnecessary infrastructure complexity.
It should begin by automating the fundamental capabilities required by its architectural model.
PostgreSQL already provides much of the underlying functionality. The platform's responsibility is to orchestrate these capabilities and make them available as a consistent, automated developer experience.
