[destination-postgres] exponential disk usage with V2

[BertrandMiannay]

Topic

Since V2 migration, the disk usage on the destination db is massively increasing

Relevant information

We used airbyte to sync two postgres db.

However, since we updated the destination connector to 2.0.4, we noticed a big increase in destination database disk usage. In the example bellow, we synchronise a set of 500Mb and observed an increase of 2.5Gb. We can clean the internal tables after the sync, however, we still need to have a db big enough to handle the normalization process. This increase seems very important and not usable for scaling.

[marcosmarxm]

I added this to the destination team backlog to investigate. @evantahler fyi

[evantahler]

Can you please confirm the sync modes you are using, and the size of the data you are moving (e.g. how big do you expect the tables to be).

Previously, were you using normalization? If not, this is expected - the destination stores the data both in a raw table and a final (typed and deduped)table.

[BertrandMiannay]

Hi

Thank you for answering so quickly

We're using a mix of full-refresh/overwrite and deduped + history mod. I don't have exact size of data moving, but the 500Mb was the size of data after reset. And yes, we are using normalization and never used the raw data.

Hoping this will help.

[evantahler]

A few thoughts:

• Postgres isn't a great datawarehouse (for many reasons), one of which is that it doesn't actually free up disk space until a vacuum. You can try vacuuming after each sync - that might help
• If you are using the final tables now, but in the past, were only using the raw tables (not using normalization), that will likely increase your storage by 2x. You have gone from one table to 2 in the destination (raw to raw + final).