I have postgres table with jsonb field. Field size is about 2-4kb per row. My application updates 100k rows per day 2000 times (changing 0.1-0.5% of data in field). Autovacuum is off, vacuum full runs every day at night.
Vacuum frees about 100-300gb every day and takes a long time to go causing application downtime.
The question is: can I solve this problem with jsonb field or I must split that field onto other simple tables?
If your concern is long down time then yes VACUUM FULL requires exclusive lock on the table being vacuumed for entire period of run.
I'll suggest you to try pg_repack extension or pg_squeeze extension - depending upon postgres version. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. These extensions are really easy to install and use in postgres. These extensions can reduce your downtime significantly and also will help to reduce runs of VACUUM FULL.
