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

Community

Connector Improvement: Allow UUID Data Type to replicate on Postgres to Postgres connectors

Matthew Belo User

In our source Postgres database, we use a large number of UUID data types for tables as their primary keys and for foreign key references.  A great deal of Python code has been written for APIs and processing that access the database expecting the data type to be UUID.  We then need to replicate these tables to other Postgres databases so that updates to the data is readily available for other applications.

When replicating the tables to another Postgres database, though, the connector automatically transforms the field to VARCHAR(256).  This causes all of the API code to fail because it is expecting a UUID data type.  I can provide a converter on the field through a view (e.g., field_name::UUID) which allows the code to work, but it adds massive amounts of overhead to the performance of using the tables and invalidates the indexes that we can put on the destination table causing sequential scans of the field.  This is abhorrent when the table contains over 300M records.

We need to be able to have the replication create the destination table with the UUID data type when the source has that type.

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Philip Fivetranner

    In addition to the above feature request, the customer has noticed an additional datatype that would be beneficial while adding the UUID datatype. Can Fivetran add an extended PostgreSQL datatype which is an array of UUIDs? It is denoted in the DDL as UUID[]. 

  • Egidio Terra Fivetranner

    Hi Matthew,

    Thanks for sharing your request. It has been added to our backlog and we investigate when/how we can implement it. In the meantime, could you please share more on the use case?

    Best,

  • Matthew Belo User

    The use case for this is really very simple.  We need to replicate several tables in one PostgreSQL database to another.  Many of the tables contain at least one UUID data types as we have used that data type as a primary key to the table.  When the table is replicated to the destination PG database, we need to have the data type remained unchanged (left as UUID).  We have large amounts of application code connected to the destination databases that are expecting the UUID data type for these tables instead of the varchar that Fivetran converts it to.

    Prior to using Fivetran for replication, we were able to accommodate this using DMS in AWS.  Since we could not use the SaaS product from Fivetran to keep the data type intact, we were forced to do the PG-to-PG replilcation using HVR.  This has worked well but it also locks us into a higher price on our license.  It also makes it so that we have to monitors multiple applications to determine how replication is doing instead of using a single interface.