Pfthb

I'm retrieving data from an AWS database using PgAdmin. This works well. The problem is that I have one column that I set to True after I retrieve the corresponding row, where originally it is set to Null. Doing so adds an enormous amount of data to my database.

I have checked that this is not due to other processes: it only happens when my program is running.
I am certain no rows are being added, I have checked the number of rows before and after and they're the same.

Furthermore, it only does this when changing specific tables, when I update other tables in the same database with the same process, the database size stays the same. It also does not always increase the database size, only once every couple changes does the total size increase.

How can changing a single boolean from Null to True add 0.1 MB to my database?

I'm using the following commands to check my database makeup:

To get table sizes

SELECT
 relname as Table,
 pg_total_relation_size(relid) As Size,
 pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as External Size
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

To get number of rows:

SELECT schemaname,relname,n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

To get database size:

SELECT pg_database_size('mydatabasename')

What do you mean adds data? to all the data files? specifically to some files?

to get a precise answer you should supply more details, but generally speaking, any DB operation will add data to the transaction logs, and possibly other files.

The problem is that As described here

"In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table"

Furthermore, we did not always close the cursor which also increased database size while running.

The current solution is to do a full vacuum every once in a while.

The problem still persists less strongly