Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Other: Postgres Partitioned tables

Chris Dobbyn User
  • Edited

I see that you have a detailed document about how you support BigQuery partitioned tables.

Do you support Postgres partitioning of tables, are there any restrictions that we should be aware of? Is there any supplementary documentation or could that documentation be created?

Thank you for your time.

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi,

    Thanks for your post and comments here! Similar to BigQuery, while there should not be an impact to partitioning the Postgres destination, as the destination database is fully managed by your team. However, this is not a workflow we have tested and thus we don't have official documentation supporting this.

    This is a feature request I will make sure to discuss with the team for the backlog.

  • Mark Yassa User

    We also definitely need this feature as our target tables are getting bigger exponentially and we need to partition them 

  • Alex Thornbury User

    This is especially true for the tables that Fivetran adds for replication from Salesforce. Tables like fivetran_api_call and fivetran_query get large. Deletion is a hassle while partition management with pg_partman is simple.

    For example, I want to use pg_partman to manage a three month rolling partition setup, with one future partition created just in case, and a catch all default partition. I can do this with some of the tables because the appropriate column like "start" or "time_stamp" is already part of the primary key constraint.

    However, this fails on fivetran_query bc "start" is not part of the primary key. if I create a partitioned version of this table and manually set "start" to not null, and create a PK on id, start then partitioning is fine. However, the fivetran sync job starts and then appears to notice the discrepancy in the target table DDL and the expected DDL. At the very least, it drops the new PK constraint and attempts to replace it with the original PK constraint of just id. that fails bc the table is partition by range on "start" and "start" must be part of the primary key constraint.