I am using PgAdmin to retrieve data from an AWS database. This works well. The problem is that I set a column to True after retrieving the corresponding row and initially set it to Null. Doing so will add a lot of data to my database.
I have checked that this is not due to other processes: it only happens while my program is running.
I’m sure I didn’t add any rows , I checked the number of rows before and after, they are the same.
In addition, it only does this when changing a specific table, when I use the same process to update other tables in the same database, the database The size stays the same. It does not always increase the database size, only the total size increases every time.
How to change a single boolean value from Null to True to add 0.1 MB to my database?
I am using the following command to check the composition of my database:
Get the table size
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)) > To get the number of rows:
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
To get the database size:
SELECT pg_database_size('mydatabasename')
< div class="answer"> If you have not changed, then your fillfactor is 100% on the table, because this is the default value.
This means the table Each change in will mark the changed row as obsolete and will recreate the updated row. If you have indexes on your table, then the problem may be worse because these indexes should be updated for every row. You can imagine this It will also hurt the performance of UPDATE.
So technically speaking, if you read the entire table and update the smallest column after reading the row, then when your fillfactor is 100, it will make the table Double the size.
What you can do is to change your table, lower the fillfactor on it, and then VACUUM:
ALTER TABLE your_table SET (fillfactor = 90);
VACUUM FULL your_table;
Of course , With this step, your table will increase by approximately 10%, but Postgres will leave some room for your updates and will not change its size as you progress.
The reason why autovacuum is helpful , Because it will periodically clean up stale rows, so it will keep your table the same size. But it puts a lot of pressure on your database. If you happen to know you will do as described in the question at the beginning To proceed, then I suggest you adjust the fillfactor as needed.
I am using PgAdmin to retrieve data from the AWS database. This works well. The problem is that I am retrieving the corresponding Set a column to True after the row and initially set it to Null. Doing so will add a lot of data to my database.
I have checked that this is not due to other processes: it is only in It happens when my program is running.
I'm sure I didn't add any rows, I checked the number of rows before and after, they are the same.
In addition, it only does this when changing a specific table, When I use the same process to update other tables in the same database, the database size remains the same. It does not always increase the database size, only the total size increases every time it changes.
How to change a single boolean Change the value from Null to True to add 0.1 MB to my database?
I am using the following command to check the composition of my database:
Get the table size
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)) > To get the number of rows:
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
To get the database size:
SELECT pg_database_size('mydatabasename')
If you don’t change it, Then your fillfactor is 100% on the table, because this is the default value.
This means that every change in the table will mark the changed row as Obsolete, and updated rows will be recreated. If you have indexes on your table, then the problem may be worse, because these indexes should be updated for each row. You can imagine that this will also hurt the performance of UPDATE.
< p>So technically speaking, if you read the entire table and update the smallest column after reading the rows, then when your fillfactor is 100, it will double the size of the table.
You What you can do is to change your table, lower the fillfactor on it, and then VACUUM:
ALTER TABLE your_table SET (fillfactor = 90);
VACUUM FULL your_table;
Of course, with this step, your table will increase by approximately 10%, but Postgres will leave some room for your updates and will not change its size as you progress.
au The reason tovacuum is helpful is that it will periodically clean up obsolete rows, so it will keep your table the same size. But it puts a lot of pressure on your database. If you happen to know you will follow the beginning Operate as described in the question, then I suggest you adjust the fillfactor as needed.
