Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uyxkc-00Exiz-9m for pgsql-admin@arkaria.postgresql.org; Wed, 17 Sep 2025 19:23:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uyxkY-00ALlw-K5 for pgsql-admin@arkaria.postgresql.org; Wed, 17 Sep 2025 19:23:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uyxkY-00ALlm-0c for pgsql-admin@lists.postgresql.org; Wed, 17 Sep 2025 19:23:31 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uyxkV-000xLS-2r for pgsql-admin@lists.postgresql.org; Wed, 17 Sep 2025 19:23:29 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-71d71bcab6fso2515607b3.0 for ; Wed, 17 Sep 2025 12:23:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758137007; x=1758741807; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=0bUX3zjB3wTphR6MY45t1tKJGUyIRoxKpSnSULvqkjI=; b=gVINUC1n0WDL95I4pt8WVkBsIGKXG0UXzef0YLgwXdTy5Cg1FOZyLdEw1HUyQ6oTS7 dIu/E0qae2W3l8cK0dm1eXGAD+4XycO81sYVjxHEuDfxMRWp2YbiB7AtYEuthrs2UJPF yEcH8SCAF5FUwlvhy0qA0/sMBIUUoSPDLK7yvpAQe+NMK6cCIj7fhFwWgN0Rt2ysmqOg /qZ2pkGoqjrC/OEbCcf1s45BpmN7u0CSfsVa9rOb9W9LsjudQzUgLXr+13ffmFWYhAmy 1zC04GDTeFGbdtWCq37mMZocMU8TukWoCxMWuSh10k8QTXQFu4Is7kwhM4ah3mW+pT/0 LiIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758137007; x=1758741807; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=0bUX3zjB3wTphR6MY45t1tKJGUyIRoxKpSnSULvqkjI=; b=DN4fGaylbd+9DnVG8eg3rGLtXSlZT1MPrq58ZZFzSeZpVzFojvHMIhxD00ndERNSMx zppUodLuIxbVPw5JM4ZwHr/dtfEugsxGrd7ENqIjph+X1yAvQhxotFezs+eAQf6EBruE sUWul/k58tqSUJyLcleYGWR8L50AvQ00sL2VMZseeDeTjCZ9ZzruAXimprDhLNsxvhh8 abhQTtHeAeH0gtbFgoC5L6Z6LEluTsX6d+JTJHe7c8eG/FvzOoBRqmsWgNaHhbQ8n+JJ mFpr3KzuOOFIx5Cmuw0nu89B6F9pwp2tYq+94TQLn1Xarc51MHuRjfpecX7/7A4e6Iw8 YLRw== X-Gm-Message-State: AOJu0YybplBWXTPnKrRrXkaYCYF2NgWWEYp1+qzRDxPcThOfi9o/Ofsc 0ENQiGgHL5k4vJgyvijLEjUxVeeefz49EXR+U//Xrh/mlTgdqK+UMJ9WBHocmslmR+cr0pVbqbx euoSvZ4klu5Xb9cHsxQxfIXgimsT3ExI= X-Gm-Gg: ASbGncuNFUgqrtKJtT3GEbkB8W16G9iwxVnFFCMz2NOWNotdWkEr/MMHy8sHaOOMCNv UxTgcx6Vz1L4qVmm6MFLJtzinFLThJnJGLg846acYvpBLo3UO9KWVwS5Z87QbVQAolrSaAzTBiN Vih2dBdsw1bEEOrXCmxtBri0HBF+xu8IsIm6HPRUgFOW3evDhLi+/wHRAeHMcKZNJSE0j07fZkT BORabMc6AsjYDxXz8nWxbq6kzfnZqw/xZCrMURkQw== X-Google-Smtp-Source: AGHT+IEoG8lXcBttMkvJUPqM0aLPwWQiik8QDp7M1UY9HgbvgEEdYbDxZffDLE1sEQIQtfX1nnGcvQdsQdJdzYWGTRY= X-Received: by 2002:a05:690c:82:b0:71f:ecde:87ac with SMTP id 00721157ae682-738904c5db5mr29943627b3.16.1758137007233; Wed, 17 Sep 2025 12:23:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jishnu Sygal Date: Wed, 17 Sep 2025 21:23:14 +0200 X-Gm-Features: AS18NWDbnB8g9dagL4OJqjNvFqcak1_RIIA5pX2rN2I301IhyrHd-ofEviYK7lA Message-ID: Subject: Re: [Architecture] Multi-Environment pg_cron for Automated Partition Management To: bertrand HARTWIG Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c860d2063f042b2c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c860d2063f042b2c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Bertrand, Thank you for your response and for sharing your insights. I appreciate the emphasis on simplicity, stability, and observability, especially given the low frequency of these jobs. Your suggestion to consider a thin wrapper in each database as an alternative to relying heavily on dblink across environments is very practical. I will definitely look into how that could work and if it can still provide the unified API we are aiming for without the potential pitfalls you have encountered. Thanks again for the valuable input. Regards, Jishnu On Wed, 17 Sept, 2025, 8:27=E2=80=AFpm bertrand HARTWIG, wrote: > Hello*,* > > Interesting proposal =E2=80=94 thanks for sharing so much context! > > From my perspective, *I=E2=80=99d encourage you to* *keep it simple.* pg_= partman > jobs are relatively low frequency, which makes monitoring and reliability > more important than architectural elegance. > > Relying on pg_cron + dblink across multiple environments adds complexity > and risks (connection leaks, security management, debugging difficulties)= . > > If you really want a universal SQL API, consider a *thin wrapper* in each > database that can be triggered externally, rather than a cross-db dblink > layer. > > My 2 cents: stability and observability usually win over architectural > purity in the long run. > > Best regards, > > Bertrand > > *PS: Full disclosure =E2=80=94 I=E2=80=99ve had way too many bad adventur= es with dblink, > so maybe I=E2=80=99m a bit biased!* > > > Le 17 sept. 2025 =C3=A0 17:04, Jishnu Sygal a = =C3=A9crit : > > Hello Postgres Community, > > I am writing to get your expert opinion on a proposed architecture for > managing pg_partman automated partitioning across a multi-environment > PostgreSQL setup. While this solution is primarily for cost savings and i= s > used in our non-production environments, *stability is a must*. Our core > goal is to create a universal scheduling solution that works identically > across cloud-managed databases (AWS RDS, Google Cloud SQL, Azure Database= ), > as well as on-premises shared and dedicated hosts. > Business Context & Core Requirements > > We need to consistently automate pg_partman maintenance tasks across our > PostgreSQL 17 environments. The primary challenge is maintaining a single= , > identical application interface so that the same SQL commands work withou= t > modification, regardless of the deployment model. A key requirement is th= at > our in-house database schema migration tools will be used to roll out SQL > scripts that directly handle job maintenance and alterations during > releases. Our jobs are low frequency (monthly/quarterly/annually) but are > critically important, as failures can cause serious operational issues. > > Our current constraints include: > > - > > PostgreSQL 17 (with a planned migration to 18). > - > > A mix of AWS RDS, Google Cloud SQL, Azure Database, and on-premises > deployments. > - > > Shared hosts with 50-60 databases and dedicated hosts with a single > database. > > Our Proposed Architecture > > As the pg_cron extension requires a dedicated database to schedule jobs > for other databases, we have designed an abstraction layer to address our > multi-environment challenges. Our architecture places pg_cron in a > designated database and uses dblink to execute partition maintenance jobs > in the target application databases. > > Key elements of this design are: > > - > > *Universal API Layer:* Every application database would have an > identical cron schema with wrapper functions. > - > > *Identical Application Interface:* Applications use the exact same SQL > statement, for example: SELECT > cron.schedule('monthly_partition_maintenance', '0 2 1 * *', 'SELECT > partman.run_maintenance_proc()'); > - > > *Environment-Adaptive Communication:* dblink is used for > multi-database environments, while direct calls are used for > single-database setups. > - > > *PostgreSQL 18 Future-Proofing:* We plan to leverage SCRAM > pass-through authentication for dblink to eliminate the need for > storing credentials. > > Technical Questions & Concerns > > We have detailed several technical questions below and would greatly > appreciate your insights and validation. > > *1. Architecture Validation* > > - > > Is this universal abstraction layer a sound architectural pattern for > managing pg_partman across diverse environments, especially given our > focus on using in-house tools for job management? > - > > How should the architecture specifically adapt for AWS RDS vs. Google > Cloud SQL vs. Azure Database, especially concerning their limitations = on > pg_cron or connection management? > - > > Does this design scale appropriately from a single dedicated database > to a shared host with 50-60 databases and 400-900 partition jobs? > > *2. Connection & Performance* > > - > > Is our proposed temporary-connection-with-guaranteed-cleanup pattern > robust enough for production? We are concerned about dblink connection > exhaustion risks and connection leaks, especially in shared environmen= ts > with many jobs. > - > > Given the low frequency of jobs, will the dblink overhead be > significant? Are there specific performance optimizations we should > consider for cloud-managed services vs. on-premises? > > *3. Alternative Approaches* > > - > > Should we abandon this PostgreSQL-native approach and instead consider > cloud-native job schedulers (e.g, AWS EventBridge, Google Cloud > Scheduler, Azure Logic Apps) to trigger maintenance jobs? > - > > Are there existing enterprise scheduling solutions that are > purpose-built for this kind of multi-cloud, on-premises PostgreSQL > automation? > > We are most concerned about connection leaks, unnoticed maintenance > failures, and ensuring a single, identical SQL interface is truly > achievable. We believe this solution could significantly simplify our > operations, but we want to validate its viability and get feedback on > potential pitfalls from the community. > > Thank you for your time and any insights you can provide. > Best Regards, > Jishnu Sygal > > > --000000000000c860d2063f042b2c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Bertrand,

T= hank you for your response and for sharing your insights. I appreciate the = emphasis on simplicity, stability, and observability, especially given the = low frequency of these jobs.

Your suggestion to consider a thin wrap= per in each database as an alternative to relying heavily on dblink across = environments is very practical. I will definitely look into how that could = work and if it can still provide the unified API we are aiming for without = the potential pitfalls you have encountered.

Thanks again for the va= luable input.

Regards,
Jishnu

On Wed,= 17 Sept, 2025, 8:27=E2=80=AFpm bertrand HARTWIG, <hartwig.bertrand@gmail.com> wrote:

Hello,

Interesting proposal =E2=80=94 thanks for sh= aring so much context!=C2=A0

From my perspective, I=E2=80=99d encourage you to=C2=A0keep it simple. pg_partman jobs are relatively low frequenc= y, which makes monitoring and reliability more important than architectural= elegance.=C2=A0

Relying on=C2=A0pg_cron + dblink=C2=A0across multiple environments adds complexity a= nd risks (connection leaks, security management, debugging difficulties).

If you really want a universal= SQL API, consider a=C2=A0thin w= rapper=C2=A0in each database that can be triggered externally, ra= ther than a cross-db dblink layer.

My 2 cents: stability and o= bservability usually win over architectural purity in the long run.

Best regards,

Bertrand=C2=A0

= PS: Full disclosure =E2=80=94 I=E2=80=99ve had way too many bad adventures = with dblink, so maybe I=E2=80=99m a bit biased!



Le 17 sept. 2025 =C3=A0 17:04, Jis= hnu Sygal <jishnu.sygal@gmail.com> a =C3=A9crit :

Hello Postgres Community,

I am writing to get your e= xpert opinion on a proposed architecture for managing pg_partman automated partitioning across a multi-environment PostgreSQL setup. Whil= e this solution is primarily for cost savings and is used in our non-produc= tion environments, stability is a must. Our core goal is to create a= universal scheduling solution that works identically across cloud-managed = databases (AWS RDS, Google Cloud SQL, Azure Database), as well as on-premis= es shared and dedicated hosts.

Busin= ess Context & Core Requirements


We need to consistently automate pg_partman mai= ntenance tasks across our PostgreSQL 17 environments. The primary challenge= is maintaining a single, identical application interface so that the same = SQL commands work without modification, regardless of the deployment model.= A key requirement is that our in-house database schema migration tools wil= l be used to roll out SQL scripts that directly handle job maintenance and = alterations during releases. Our jobs are low frequency (monthly/= quarterly/annually) but are critically important, as failures can cause ser= ious operational issues.=C2=A0

Our cu= rrent constraints include:

  • Po= stgreSQL 17 (with a planned migration to 18).

  • A mix of AWS RDS, Google Cloud SQL, Azure Database, and o= n-premises deployments.

  • Shar= ed hosts with 50-60 databases and dedicated hosts with a single database.

Our Proposed Architecture


As the pg_cron extension requires a dedicated database to schedule jobs for other da= tabases, we have designed an abstraction layer to address our mul= ti-environment challenges. Our architecture places pg_cron in = a designated database and uses dblink to execute partition mai= ntenance jobs in the target application databases.

Key elements of this design are:

  • Universal API Layer: Every application database w= ould have an identical cron schema with wrapper functions.

  • Identical Application Interf= ace: Applications use the exact same SQL statement, for example: = SELECT cron.schedule('monthly_partition_maintenance', '0 2 1 * = *', 'SELECT partman.run_maintenance_proc()');

    =

  • Environment-Adaptive Communication:= dblink is used for multi-database environments, while dir= ect calls are used for single-database setups.

  • PostgreSQL 18 Future-Proofing: We plan to leverag= e SCRAM pass-through authentication for dblink to eliminate th= e need for storing credentials.

Technical Questions & Concerns


We have detailed several technical questions = below and would greatly appreciate your insights and validation.

=

1. Architecture Validation

  • Is this universal abstraction layer a soun= d architectural pattern for managing pg_partman across diverse= environments, especially given our focus on using in-house tools for job m= anagement?

  • How should the ar= chitecture specifically adapt for AWS RDS vs. Google Cloud SQL vs. Azure Da= tabase, especially concerning their limitations on pg_cron or = connection management?

  • Does = this design scale appropriately from a single dedicated database to a share= d host with 50-60 databases and 400-900 partition jobs?

    • 2. Connection & Performance<= /p>

    • Is our proposed temporary-connection= -with-guaranteed-cleanup pattern robust enough for production? We are concerned about dblink connection exhaustion risks and co= nnection leaks, especially in shared environments with many jobs.

    • Given the low frequency of jobs, will= the dblink overhead be significant? Are there specific perfor= mance optimizations we should consider for cloud-managed services vs. on-pr= emises?

    3. Alt= ernative Approaches

    • Shoul= d we abandon this PostgreSQL-native approach and instead consider cloud-nat= ive job schedulers (e.g,=C2=A0AWS EventBridge, Google Cloud Sched= uler, Azure Logic Apps) to trigger maintenance jobs?

    • = Are there existing enterprise scheduling solutions= that are purpose-built for this kind of multi-cloud, on-premises PostgreSQ= L automation?

    We are most concerned about connection leaks, unnoticed maintenance failures, = and ensuring a single, identical SQL interface is truly achievable. We beli= eve this solution could significantly simplify our operations, but we want = to validate its viability and get feedback on potential pitfalls from the c= ommunity.

    Thank you for your time and= any insights you can provide.

    Best Rega= rds,
    Jishnu Sygal

    --000000000000c860d2063f042b2c--