Following snippet of PostgreSQL documentation, https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW, explains the benefits of partitioning as fallows.
Sharding is not partitioning. As explained by the following MongoDB documentation - https://www.mongodb.com/features/database-sharding-explained#:~:text=Sharding%20is%20a%20method%20for,storage%20capacity%20of%20the%20system"Sharding is a method for distributing a single dataset across multiple databases, which can then be stored on multiple machines. This allows for larger datasets to be split into smaller chunks and stored in multiple data nodes, increasing the total storage capacity of the system."
However, PostgreSQL has implemented sharding on top of partitioning by allowing any given partition of a partitioned table to be hosted by a remote server. The basis for this is in PostgreSQL’s Foreign Data Wrapper (FDW) support, https://wiki.postgresql.org/wiki/Foreign_data_wrappers, which has been a part of the core of PostgreSQL for a long time.
This document will go into required set up, steps to enable sharding in PostgreSQL/RDS for a given table and using a simple Spring Boot application to connect database and tables.
Infrastructure Set UpComponents:
- ServiceA: Spring Boot application configured with a single DataSource configuration
- Controller, Repository
- HikariCP configuration
- AWS RDS PostgreSQL instance in eu-central-1.
- AWS RDS PostgreSQL instance in us-east-1.
Note: This article does not go into how to spin RDS instances in AWS or how to allow networking / security for them to communicate with each other which is needed. Both of these databases can be in the same Region. To make it transparent, in this set up, I used a different region all together.
Table Structure and Sharding Strategy
I used the following table set up to test the set up.
Table 1: TABLE_A → SHARDED TABLE
- id (pkey)
- organization_value (used in declarative sharding)
Configuration Set UpNo specific configuration is required at AWS RDS level as the most recent versions of PostgreSQL already come with postgres_fdw installed. We will just need to be enabling it.
- Local database → this is the database application is configured to connect. In our set up, this is the RDS database that was set up in USA.
- Remote database → this is the database set up in EU which is going to hold our remote table partitions.
Local Database Set Up
Application Set Up / Test Cases and Execution
Spring Boot Application can be used to easily configure a Datasource connection.
#Simple DataSource Setup Using Spring/Hikari - connection is only to Local database (i.e) - USA