Subject: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?

From: Jean Baro <[email-masked]>
Date: Fri, 11 Feb 2022 15:56:03 -0300

Hi there,

Just out of curiosity, is there anything that we (as developers) can do on
our code to (all together):

- Have maximum performance while running on a single PG 14.1 database (HA)
(Google Cloud SQL);
- Be prepared (if needed in the future) to migrate the database to a
sharding solution (FDW) once the microservice exceeds the capability of a
single machine
- Don't make the code (that access PG database) to be much more complicated
compared to one running on a SINGLE Database (ho sharding)
- No need to change the application's code when (and if) the database needs
to be sharded in the future (FDW built-in approach).

Our use case is to use PG today and be prepared to scale beyond a single
Google Cloud SQL machine max capability (maybe self-hosted Postgres when
sharding is necessary)
We are a financial institution/Fintech with over 1MM customers (and growing
50% YoY). The new platform will probably be built on top of PG (as the
standard database).
One PG (Cloud SQL) for every microservice.

Any help or suggestion would be appreciated.

Thanks

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 11 Feb 2022 15:19:13 -0500

On Fri, Feb 11, 2022 at 03:56:03PM -0300, Jean Baro wrote:
> Hi there,
> 
> Just out of curiosity, is there anything that we (as developers) can do on our
> code to (all together):
> 
> - Be prepared (if needed in the future) to migrate the database to a sharding
> solution (FDW) once the microservice exceeds the capability of a single machine

Good question.  I think it is generally agreed that sharding would
involve using partitions that point to foreign tables on other servers
using foreign data wrappers.   I have a talk here about it:

	https://momjian.us/main/writings/pgsql/sharding.pdf

So, if you are using partitioning, you are probably ready.  In fact,
with PG 14, you can do read-only sharding in parallel for large data
volumes.

> compared to one running on a SINGLE Database (ho sharding)

The clients don't know now they are using partitioning, so my guess is
that they wouldn't know they are using sharding either.

> - No need to change the application's code when (and if) the database needs to
> be sharded in the future (FDW built-in approach).

Right, we never required changes for partitioning either.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

---

From: Jean Baro <[email-masked]>
Date: Fri, 11 Feb 2022 18:34:41 -0300

Thanks Bruce.

I like this approach.

PG is great!!

Em sex., 11 de fev. de 2022 17:19, Bruce Momjian <[email-masked]>
escreveu:

> sharding

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 12 Feb 2022 07:58:58 -0500

> PG is great!!

FYI, you mentioned microservices, and I am working on a microservices
talk now --- here are the draft slides:

	https://momjian.us/main/writings/pgsql/microservices.pdf

I will post the final slides to my blog once I present them somewhere.

---

From: Jean Baro <[email-masked]>
Date: Sat, 12 Feb 2022 15:01:00 -0300

Thanks, I'll take a look. 🙏