3 # Example script to split full table updates into chunks with a vacuum inbetween
4 # to try and avoid table bloat.
5 # Sometimes it's better to create a new table and swap them, but when that's not possible
6 # due to other complications something like this may do.
8 ## CREATE TABLE test(ax integer PRIMARY KEY, bx text);
9 ## INSERT INTO test SELECT i, 'SOME TEXT ' || i FROM generate_series(1,1000000) i;
11 psql_prefix="/usr/local/pgsql/bin"
16 fields="bx" # Comma separated
17 values="'SOMETHING ELSE ' || ax" # Comma separated
19 skip_pk_vals="0" # Comma separated
21 sql="SELECT count(*)/$chunks FROM $schema.$relation WHERE $pk NOT IN ($skip_pk_vals) AND (($fields) IS NULL OR ($fields) <> ($values));"
22 chunk_size=`$psql_prefix/psql -U $user -d $database -tAc "$sql"`
24 echo "CHUNK SIZE $chunk_size"
26 for i in `seq 1 $(($chunks-1))`; do
28 offset=$((($i-1)*$chunk_size))
31 UPDATE $schema.$relation a
32 SET ($fields) = ($values)
33 FROM (SELECT ctid FROM $schema.$relation WHERE $pk NOT IN ($skip_pk_vals) AND (($fields) IS NULL OR ($fields) <> ($values)) ORDER BY ctid LIMIT $chunk_size) b
35 AND (($fields) IS NULL OR ($fields) <> ($values));
39 result=`$psql_prefix/psql -U $user -d $database -c "$sql"`
42 sql="VACUUM $schema.$relation;"
44 result=`$psql_prefix/psql -U $user -d $database -c "$sql"`
48 sql="UPDATE $schema.$relation SET ($fields) = ($values) WHERE ($fields) <> ($values);"
50 result=`$psql_prefix/psql -U $user -d $database -c "$sql"`
53 sql="VACUUM ANALYZE $schema.$relation;"
55 result=`$psql_prefix/psql -U $user -d $database -c "$sql"`