public inbox for [email protected]help / color / mirror / Atom feed
Best practices for preparing an application to (possibly) be sharded (FDW) in the future? 5+ messages / 2 participants [nested] [flat]
* Best practices for preparing an application to (possibly) be sharded (FDW) in the future? @ 2022-02-11 18:56 Jean Baro <[email protected]> 2022-02-11 20:19 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Jean Baro @ 2022-02-11 18:56 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? 2022-02-11 18:56 Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> @ 2022-02-11 20:19 ` Bruce Momjian <[email protected]> 2022-02-11 21:34 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Bruce Momjian @ 2022-02-11 20:19 UTC (permalink / raw) To: Jean Baro <[email protected]>; +Cc: [email protected] 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? 2022-02-11 18:56 Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 2022-02-11 20:19 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> @ 2022-02-11 21:34 ` Jean Baro <[email protected]> 2022-02-12 12:58 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Jean Baro @ 2022-02-11 21:34 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected] Thanks Bruce. I like this approach. PG is great!! Em sex., 11 de fev. de 2022 17:19, Bruce Momjian <[email protected]> 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 <[email protected]> https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? 2022-02-11 18:56 Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 2022-02-11 20:19 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> 2022-02-11 21:34 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> @ 2022-02-12 12:58 ` Bruce Momjian <[email protected]> 2022-02-12 18:01 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Bruce Momjian @ 2022-02-12 12:58 UTC (permalink / raw) To: Jean Baro <[email protected]>; +Cc: [email protected] 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? 2022-02-11 18:56 Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 2022-02-11 20:19 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> 2022-02-11 21:34 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 2022-02-12 12:58 ` Re: Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Bruce Momjian <[email protected]> @ 2022-02-12 18:01 ` Jean Baro <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Jean Baro @ 2022-02-12 18:01 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected] Thanks, I'll take a look. 🙏 On Sat, Feb 12, 2022 at 9:58 AM Bruce Momjian <[email protected]> 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 <[email protected]> https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-02-12 18:01 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-02-11 18:56 Best practices for preparing an application to (possibly) be sharded (FDW) in the future? Jean Baro <[email protected]> 2022-02-11 20:19 ` Bruce Momjian <[email protected]> 2022-02-11 21:34 ` Jean Baro <[email protected]> 2022-02-12 12:58 ` Bruce Momjian <[email protected]> 2022-02-12 18:01 ` Jean Baro <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox