Purging of old syncs from Airbyte databases

[stevenmurphy12]

Is it possible to purge the Airbyte Postgres databases of older syncs? Would there be negative side-effects of doing so?

We don't appear to be having any issues at the moment, though I'm mindful that the size of our DB is creeping up over time, and I'd like to understand what house keeping I can do.

Here are the top 5 tables by size in the main airbyte DB:

Table Name	Total Size	Table Size	Index Size
public.attempts	2756 MB	1412 MB	75 MB
public.jobs	2441 MB	1374 MB	181 MB
public.workload	1139 MB	974 MB	103 MB
public.workload_label	1058 MB	367 MB	691 MB
public.stream_stats	269 MB	151 MB	118 MB

The top 5 tables by size in the temporal DB:

Table Name	Total Size	Table Size	Index Size
public.history_node	16 GB	12 GB	3156 MB
public.executions	851 MB	714 MB	137 MB
public.history_tree	344 MB	290 MB	53 MB
public.timer_tasks	288 MB	228 MB	60 MB
public.current_executions	179 MB	91 MB	88 MB

And all tables in temporal_visibility DB:

Table Name	Total Size	Table Size	Index Size
public.executions_visibility	2663 MB	214 MB	2449 MB
public.schema_version	56 kB	8192 bytes	16 kB
public.schema_update_history	56 kB	8192 bytes	16 kB

Thank you

[marcosmarxm]

Good question @stevenmurphy12 I totally agree this topic need more documentation. For now, you can change the variable TEMPORAL_HISTORY_RETENTION_IN_DAYS from default 30 to a lower value to clean temporal logs and tables, the one is the biggest right now.
I couldn't find any clean routines for attempts and jobs in the platform code, which could be a useful feature to prevent full disk issues.

Reference:

• Documentation Page

[stevenmurphy12]

I'll try setting the temporal namespace retention. Do you expect this would result in a drop-off in the number of rows in the history_node table? Looking at it I don't see a date column, though I imagine there's a foreign key relationship somewhere.

I couldn't find any clean routines for attempts and jobs in the platform code, which could be a useful feature to prevent full disk issues.

Good to know. In lieu of a clean approach I could write a process to periodically cull the attempts/jobs, or would you recommend against that?

Thank you

[SkinnyPigeon]

We're in the same boat and would love an answer. Last time we tried to clear the pg_toast table, we experienced a large data loss. Currently, our public.attempts table is wild and we are worried about doing anything to it. Any help would be really useful @marcosmarxm

table_name                               |table_size|index_size|total_size|
-----------------------------------------+----------+----------+----------+
public.attempts                          |357 MB    |23 MB     |277 GB    |

[andrea-zanetti]

Hi, I'm also in the same situation with the attempts table consuming most of the db disk space. Anyone found a solution?

[fabriziospini]

Hi, I'm currently in the same situation with attempts taking 37GB of disk space. Could anyone watch into it?

[SkinnyPigeon]

We have deleted old data from the attempts table which does not reclaim the space. However, the space can be used by Postgres. The only way to reclaim the space is to use the VACUUM FULL command after deleting the data. However, since we had a catastrophic data loss last time we did this, we are just going to sit with this phantom space usage until Airbyte provides a secure way to do this