Thread: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
From
Jean Baro
Date:
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)
- 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.
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
Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
From
Bruce Momjian
Date:
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.
Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
From
Jean Baro
Date:
Thanks Bruce.
I like this approach.
PG is great!!
Em sex., 11 de fev. de 2022 17:19, Bruce Momjian <bruce@momjian.us> escreveu:
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.
Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
From
Bruce Momjian
Date:
On Fri, Feb 11, 2022 at 06:34:41PM -0300, Jean Baro wrote:
> Thanks Bruce.
>
> I like this approach.
>
> 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.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future?
From
Jean Baro
Date:
Thanks, I'll take a look. 🙏
On Sat, Feb 12, 2022 at 9:58 AM Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Feb 11, 2022 at 06:34:41PM -0300, Jean Baro wrote:
> Thanks Bruce.
>
> I like this approach.
>
> 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.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.