Situation
We're making a dump of a database every day. below, the command used to make the dump :
pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" --exclude-table-data='ir_api_json_log' -f $FINAL_BACKUP_DIR"$DATABASE".custom
Since the 02.08.2023, the size of the dump is decreasing :
- 07.02.2023 : 1.2 G
- 08.02.2023 : 1.1 G
- 09.02.2023 : 993 M
- 10.02.2023 : 949 M
- 11.02.2023 : 926 M
- 12.02.2023 : 801 M
- 13.02.2023 : 801 M
- 14.02.2023 : 802 M
- 15.02.2023 : 721 M
I don't say it's impossible for those dumps to decrease (depending on what the customer is doing). The problem is that we have never seen such behaviour over several years in hundreds of client environments.
Anomalies
We took the time to check with the customer his working environment, there is no data loss noted. So we did some technical checks.
We have restored the dumps of the following dates: 08.02.2023, 09.02.2023, 12.02.2023 and 14.02.2023. We compared the size of the largest tables and their number of rows. Below are the results :
As you can see for the tables 1, 3, 4, 7 and 12, their size is drastically reduced but the number of lines increases.
Details
- There has been no postgresql update.
- This anomaly is found for only one customer out of more than a hundred.
- There are no cron, automated actions, vacuum, scripts or anything else on our side that would explain this behaviour.
- There are no visual defects or data losses in the customer's environment, the customer has not noticed anything.
- This behaviour is only observed on the database to which the client has access.
Our question
Maybe the behaviour is normal and we are worrying for nothing. However, is it normal for the size of a table to shrink drastically with no observed data loss, with an increase in the number of rows ?
If you have any ideas, leads, additional checks that we could do, please let us know.
Thank you in advance for your help.
