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

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):
>
> - 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

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.

> - Don't make the code (that access PG database) to be much more complicated
> 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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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