Hi Alex,

Sadie from the Product Management team here. This is a valuable suggestion regarding managing Fivetran-related metadata, especially around efficient data retention. We appreciate your clear feedback and suggestions.

The request has been added to our feature improvements backlog. To help us better prioritize and scope this, could you share more about your specific challenges? What's your current workaround and how often are you managing data retention related to these metadata tables?

We will keep this thread updated with any progress or changes regarding this request.

Thanks,
Sadie

ALTER TABLE fivetran_metadata.log RENAME TO log_old;
ALTER INDEX fivetran_metadata.log_pkey RENAME TO log_old_pkey;

CREATE TABLE fivetran_metadata.log (
LIKE fivetran_metadata.log_old INCLUDING DEFAULTS INCLUDING COMMENTS
) PARTITION BY RANGE (time_stamp);

ALTER TABLE fivetran_metadata.log ADD PRIMARY KEY (id, time_stamp);

SELECT partman.create_parent(
	p_parent_table => 'fivetran_metadata.log',
	p_control => 'time_stamp',
	p_type => 'range',
	p_interval => '1 month',
	p_premake => 1,
	p_default_table => true,
	p_start_partition => '2026-03-01'
);

UPDATE partman.part_config
SET retention = '3 months',
retention_keep_table = false
WHERE parent_table = 'fivetran_metadata.log';

SELECT partman.run_maintenance();

pg_partman's mainteance script, run_maintenance, will add new partitions, detach and drop old one according to the configuration settings per managed table. These actions are really DDL in nature, not DML, so they should be nearly instantaneous in comparison to expensive delete statements.

<<salesforce>>.fivetran_query on the other hand will not currently support this option. The candidate column, start, is scoped as NULLABLE and is not part of the existing primary key constraint. It is not possible to alter the primary key constraint because the DDL discrepancy will be detected on the next sync job. When this happens, the job will drop the new PK and attempt to recreate the old PK. If you did attempt to manage this with pg_partman and created a partitioned table, PostgreSQL will not allow the old primary key to be created.

So for the moment, the only option is to use a DELETE statement BUT the other problem is that while the "start" column is a candidate to use in this action, there is NO index on the column. Thus the deletion is an expensive operation. Oddly enough, even though you cannot alter a primary key constraint, you can add an index and the sync job doesn't seem to care.

Initially getting this table under control is painful. Since it can be large if unmanaged, it is generally easier to backfill a new table with applicable data from the original table.

• Rename the table, constraints, and add an index on the renamed table:

ALTER TABLE sf_onepinnacol.fivetran_query RENAME TO fivetran_query_old;
ALTER TABLE sf_onepinnacol.fivetran_query_old RENAME CONSTRAINT fivetran_query_pkey TO fivetran_query_old_pkey;

CREATE INDEX ON sf_onepinnacol.fivetran_query_old ("start");

• Re-create the table and any associated grants

CREATE TABLE sf_onepinnacol.fivetran_query (
 id varchar(36) NOT NULL,
 "start" timestamptz NULL,
 done timestamptz NULL,
 source_object varchar(256) NULL,
 source_api varchar(256) NULL,
 modified_field varchar(256) NULL,
 modified_since_inclusive timestamptz NULL,
 query varchar(16384) NULL,
 merge_mode varchar(256) NULL,
 rows_updated_or_inserted int8 NULL,
 _fivetran_synced timestamptz NULL,
 CONSTRAINT fivetran_query_pkey PRIMARY KEY (id)
);

ALTER TABLE sf_onepinnacol.fivetran_query OWNER TO ...; --if necessary
GRANT SELECT ON TABLE sf_onepinnacol.fivetran_query TO ...;
...

• Backfill the new table with the old table. This might be doable with a simple INSERT INTO if the amount of data is small enough, else other methods like pg_dump, batched insertion, etc. are necessary.

-- Insert data from the start of the month three months ago to current
INSERT INTO sf_onepinnacol.fivetran_query
SELECT
 *
FROM
 sf_onepinnacol.fivetran_query_old
WHERE
 "start"  >= date_trunc('MONTH', now()) - INTERVAL '3 month';

• Create an index on the new table to assist in the future (it is generally easier to insert first, then index)

CREATE INDEX fivetran_query_start_idx ON sf_onepinnacol.fivetran_query USING btree (start);

• Drop the old table

DROP TABLE IF EXISTS sf_onepinnacol.fivetran_query_old;

After I have all of the affected clusters in an initial good state, then the frequency of the maintenance will be monthly going forward. However, it will require two different mechanisms at present:

• use pg_partman's partition maintenance function to easily manage 2 of the 3 tables
• use a DELETE statement to manage the third table. this could be:
• a simple explicit DELETE statement
• a parametrized DELETE statement
• a more flexible, but complex function or stored procedure (depending on the version of PostgreSQL)

I would much rather have a single and simple mechanism moving forward, in other words pg_partman.

Even if I can drop the fivetran_metadata connection from our connections assuming there is no dependency upon them, it still leaves me with the two tables created for Salesforce Connections.

The fivetran_metadata Connector is optional, but requires an opt-out as far as I know. In other words, you create a Connector and the fivetran_metadata Connector is created automatically, then you have to delete it afterwards. An opt-in feature would preferable in my opinion.

Ideally, I would think that the Salesforce additional metadata functionality would be an opt-in feature, not an enabled by default feature that cannot be disabled.

If that is not doable, then any logging or similar append-only tables should be constructed with a reasonable attempt at data management. Ideally when PostgreSQL is the destination, it is configured such that PostgreSQL partitioning can be used - pg_partman is not required for this, but it very easy and flexible to use. At the very least the table have the necessary indexes such that DELETE statements are easy. 

In addition, if the management of these tables falls upon the customer then it should be well documented on how to do it. If this is documented, then it was not easily found in the documents.

Managing the partitions with pg_partman's maintenance script drops the oldest partition and successfully creates the newest future partition based on the table settings in pg_partman's part_config table in 1 to 2 seconds. Being able to manage large volumes of data easily like this is a huge win. Much easier than expensive and slow DELETE statements.