I have a huge table that I cannot delete the rows, only update the columns that are storing huge base64 data, that I should update to null to try to release space.
I am working with a PostgreSQL table with a large TEXT field, which is theoretically updated on a regular basis. I have thought about storing the data directly in the filesystem, but with TOAST, the data is already being stored off-page and compressed in the database so I figured I would keep things simple and just use the database storage.
I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided to remove all records but the first record for every minute. So now there are 8 records for each minutes and after this process it will store 1 record per minute which are older than 3 months.
I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer.
In my transaction, I am creating a temporary table:
At work, we have a database table we use for queued jobs, so it sees a lot of throughput. One issue we’ve run into is that after a weekend without any code changes, the indexes on this table fill with dead tuples. When we run VACUUM VERBOSE ANALYZE, this shows up as “600461 dead row versions cannot be removed yet, oldest xmin: 902335252” (see [1]).
I create a table with 1 million records, then I delete those records. (Common with some sort of processing list.)
Let’s create a temporary table (I choose temporary table because autovacuum don’t run for this kind of tables):
I’m trying to figure out why an UPDATE statement takes too long (>30 sec).
We are experiencing many slowdowns due to index bloat. On trying to optimize the index, recreating it seems to generate a much smaller index: