5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple testsmy query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

4 typos, clarify
source | link

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versionversions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their mimimumminimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row version on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their mimimum size, though. And excessive bloat after changes to the table definition is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

3 edited body
source | link

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row version on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusiveexclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their mimimum size, though. And excessive bloat after changes to the table definition areis best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row version on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their mimimum size, though. And excessive bloat after changes to the table definition are best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row version on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their mimimum size, though. And excessive bloat after changes to the table definition is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.

2 added 80 characters in body
source | link
1
source | link