3 In reality we'd only be bothered with this on large tables
8 TEST=# CREATE TABLE test(a integer PRIMARY KEY, b text);
12 TEST=# INSERT INTO test SELECT i, 'SOME TEXT ' || i FROM generate_series(1,1000000) i;
18 Schema | Name | Type | Owner | Size | Description
19 --------+------+-------+-----------+-------+-------------
20 public | test | table | pgcontrol | 50 MB |
24 Update half the table, and attempt a vacuum:
26 TEST=# UPDATE test SET b = 'SOMETHING ELSE ' || a WHERE a > 500000;
36 Schema | Name | Type | Owner | Size | Description
37 --------+------+-------+-----------+-------+-------------
38 public | test | table | pgcontrol | 78 MB |
42 We've got some bloat we can't remove easily. If we use the function to split
43 the update into chunks with intermediate vacuums we should have less bloat.
48 TEST=# TRUNCATE TABLE test;
50 TEST=# INSERT INTO test SELECT i, 'SOME TEXT ' || i FROM generate_series(1,1000000) i;
56 Schema | Name | Type | Owner | Size | Description
57 --------+------+-------+-----------+-------+-------------
58 public | test | table | pgcontrol | 50 MB |
62 For vacuum to do it's work best we should not have a long running transaction
63 in the database containing the table, so best to switch to another database
64 before running our function:
68 You are now connected to database "postgres"
70 postgres=# SELECT pg_chunk_update('public', 'test', 'b', E'\'SOMETHING ELSE \' || a', 'a > 500000', 200, 'dbname=TEST user=glyn');
71 NOTICE: 2012-02-25 12:36:18.155259: Starting update; chunks = 200 chunk_size = 2500
72 NOTICE: Equiv' full SQL:
73 UPDATE public.test SET (b)=('SOMETHING ELSE ' || a) WHERE a > 500000;
74 NOTICE: 2012-02-25 12:36:18.156007+00: Updating chunk 1 of 200 (elapsed time: 0.001303s est' remainig time: ?s)
75 NOTICE: 2012-02-25 12:36:20.159222+00: Chunk 1 status : UPDATE 2500 OK / VACUUM OK
76 NOTICE: 2012-02-25 12:36:20.159369+00: Updating chunk 2 of 200 (elapsed time: 2.004122s est' remainig time: 398.372727s)
77 NOTICE: 2012-02-25 12:36:22.061396+00: Chunk 2 status : UPDATE 2500 OK / VACUUM OK
78 NOTICE: 2012-02-25 12:36:22.061473+00: Updating chunk 3 of 200 (elapsed time: 3.906225s est' remainig time: 376.601544s)
79 NOTICE: 2012-02-25 12:36:23.968849+00: Chunk 3 status : UPDATE 2500 OK / VACUUM OK
80 NOTICE: 2012-02-25 12:36:23.968938+00: Updating chunk 4 of 200 (elapsed time: 5.813693s est' remainig time: 375.756421s)
84 NOTICE: 2012-02-25 12:41:36.605952+00: Updating chunk 198 of 200 (elapsed time: 318.450704s est' remainig time: 5.009034s)
85 NOTICE: 2012-02-25 12:41:38.275669+00: Chunk 198 status : UPDATE 2500 OK / VACUUM OK
86 NOTICE: 2012-02-25 12:41:38.275774+00: Updating chunk 199 of 200 (elapsed time: 320.120526s est' remainig time: 3.339504s)
87 NOTICE: 2012-02-25 12:41:39.947854+00: Chunk 199 status : UPDATE 2500 OK / VACUUM OK
88 NOTICE: 2012-02-25 12:41:39.947938+00: Updating chunk 200 of 200 (elapsed time: 321.79269s est' remainig time: 1.672085s)
89 NOTICE: 2012-02-25 12:41:41.62008+00: Chunk 200 status : UPDATE 2500 OK / VACUUM OK
90 NOTICE: 2012-02-25 12:41:41.620295+00: Final update pass (elapsed time: 323.465235s est' remainig time: 0s)
91 NOTICE: 2012-02-25 12:41:42.520855+00: Chunk 201 status : UPDATE 0 OK / VACUUM OK
103 Schema | Name | Type | Owner | Size | Description
104 --------+------+-------+--------+-------+-------------
105 public | test | table | admins | 54 MB |
110 It took a lot longer but we'd have had less blocking, and we've got less bloat; vacuum did it's work.