我写了一个单元测试来修改一个表(INSERT 和 DELETE 的(，然后手动VACUUM 's 和 ANALYZE 's，然后我查询pg_stat_user_tables以确保VACUUM和ANALYZE确实有一些影响。
我使用以下 SQL：
select
tbl.relid,
tbl.schemaname,
tbl.n_tup_del,
tbl.n_live_tup,
tbl.n_dead_tup,
tbl.n_mod_since_analyze,
tbl.vacuum_count,
tbl.analyze_count
from
pg_stat_user_tables tbl
where
tbl.relname = lower('...')
and schemaname = current_schema();
对于常规表和
select
tbl.relid,
tbl.schemaname,
tbl.n_tup_del,
tbl.n_live_tup,
tbl.n_dead_tup,
tbl.n_mod_since_analyze,
tbl.vacuum_count,
tbl.analyze_count
from
pg_stat_user_tables tbl
join
pg_namespace nsp
on
tbl.schemaname = nsp.nspname
where
tbl.relname = lower('...')
and nsp.oid = pg_my_temp_schema();
对于临时的。
尽管如此，当我运行我的示例时，我观察到 vacuum_count 和 analyze_count 都是零，并且表包含许多死元组，例如：
relid = 913896
schemaname = pg_temp_20
n_tup_del = 10000
n_live_tup = 10000
n_dead_tup = 10000
n_mod_since_analyze = 30000
vacuum_count = 0
analyze_count = 0
这是为什么呢？
自包含代码示例如下所示：
package com.example;
import static com.example.AutoCommitMode.COMMIT;
import static com.example.AutoCommitMode.ON;
import static com.example.AutoCommitMode.ROLLBACK;
import static java.lang.String.format;
import static java.util.Arrays.asList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.eclipse.jdt.annotation.NonNull;
import org.eclipse.jdt.annotation.Nullable;
import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.ds.common.BaseDataSource;
import org.testng.Assert;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public final class PostgreSqlVacuumTest {
private static final String JDBC_URL = "jdbc:postgresql://localhost:5432/postgres?currentSchema=sandbox";
@DataProvider
private static @Nullable Object @NonNull[]@NonNull[] getParameters() {
@NonNull final List<@Nullable Object @NonNull[]> parameters = new ArrayList<>();
parameters.add(new @Nullable Object[] {"A", false, ON});
parameters.add(new @Nullable Object[] {"B", false, COMMIT});
parameters.add(new @Nullable Object[] {"C", false, ROLLBACK});
parameters.add(new @Nullable Object[] {"D", true, ON});
parameters.add(new @Nullable Object[] {"E", true, COMMIT});
parameters.add(new @Nullable Object[] {"F", true, ROLLBACK});
return parameters.toArray(new @Nullable Object @NonNull[0]@NonNull[]);
}
@Test(dataProvider = "getParameters")
@SuppressWarnings({"static-method", "incomplete-switch"})
public void test(@NonNull final String tableName,
final boolean temporary,
final AutoCommitMode mode)
throws SQLException {
final DataSource dataSource = new PGSimpleDataSource();
((BaseDataSource) dataSource).setUrl(JDBC_URL);
try (final Connection conn = dataSource.getConnection("user", "password")) {
try (final Statement stmt = conn.createStatement()) {
try {
stmt.executeUpdate(format("drop table %s", tableName));
} catch (@SuppressWarnings("unused") final SQLException ignored) {
// ignore
}
stmt.executeUpdate(format("create %stable %s (id bigint not null)",
temporary ? "temporary " : "",
tableName));
conn.setAutoCommit(mode.autoCommit);
for (int i = 0; i < 10000; i++) {
stmt.executeUpdate(format("insert into %s (id) values (%d)", tableName, i));
}
stmt.executeUpdate(format("delete from %s", tableName));
for (int i = 0; i < 10000; i++) {
stmt.executeUpdate(format("insert into %s (id) values (%d)", tableName, i));
}
switch (mode) {
case COMMIT:
conn.commit();
break;
case ROLLBACK:
conn.rollback();
break;
}
try (final ResultSet countHolder = stmt.executeQuery(format("select count(*) from %s", tableName))) {
Assert.assertTrue(countHolder.next());
final long count = countHolder.getLong(1);
Assert.assertEquals(count, mode == ROLLBACK ? 0L : 10000L);
Assert.assertFalse(countHolder.next());
}
switch (mode) {
case ON:
stmt.executeUpdate(format("vacuum analyze %s", tableName));
break;
case COMMIT:
case ROLLBACK:
// VACUUM cannot be executed inside a transaction block.
conn.setAutoCommit(true);
try {
stmt.executeUpdate(format("vacuum analyze %s", tableName));
} finally {
conn.setAutoCommit(false);
}
break;
}
diagnose(tableName, temporary, stmt);
}
}
}
private static void diagnose(@NonNull final String tableName,
final boolean temporary,
@NonNull final Statement stmt)
throws SQLException {
final List<String> columns = asList("relid", "schemaname", "n_tup_del", "n_live_tup", "n_dead_tup", "n_mod_since_analyze", "vacuum_count", "analyze_count");
final String diagSql = temporary
? "selectn" +
" tbl.relid,n" +
" tbl.schemaname,n" +
" tbl.n_tup_del,n" +
" tbl.n_live_tup,n" +
" tbl.n_dead_tup,n" +
" tbl.n_mod_since_analyze,n" +
" tbl.vacuum_count,n" +
" tbl.analyze_countn" +
"fromn" +
" pg_stat_user_tables tbln" +
"joinn" +
" pg_namespace nspn" +
"onn" +
" tbl.schemaname = nsp.nspnamen" +
"wheren" +
" tbl.relname = lower('%s')n" +
" and nsp.oid = pg_my_temp_schema()"
: "selectn" +
" tbl.relid,n" +
" tbl.schemaname,n" +
" tbl.n_tup_del,n" +
" tbl.n_live_tup,n" +
" tbl.n_dead_tup,n" +
" tbl.n_mod_since_analyze,n" +
" tbl.vacuum_count,n" +
" tbl.analyze_countn" +
"fromn" +
" pg_stat_user_tables tbln" +
"wheren" +
" tbl.relname = lower('%s')n" +
" and schemaname = current_schema()";
try (final ResultSet rset = stmt.executeQuery(format(diagSql, tableName))) {
while (rset.next()) {
System.out.println("---------------");
for (final String column : columns) {
System.out.println("t" + column + " = " + rset.getObject(column));
}
}
}
}
}
enum AutoCommitMode {
ON(true),
COMMIT(false),
ROLLBACK(false),
;
final boolean autoCommit;
AutoCommitMode(final boolean autoCommit) {
this.autoCommit = autoCommit;
}
}
VACUUM (不带 FULL( 将死元组标记为准备重用，它还能够在表的末尾截断死元组，但它不会消除这些死元组。
VACUUM FULL从头开始重写整个表，因此在此过程中删除了死元组，但它需要在表上独占锁定：
https://www.postgresql.org/message-id/dcc563d10710021855l7ac247ebv62c5fc44a321ee1f%40mail.gmail.com
如果您有一张大桌子，并希望在其上运行 VACUUM FULL，同时能够在过程中使用它，请考虑使用 https://github.com/reorg/pg_repack/。
您确定用户(用于手动运行 VACUUM 的用户(具有足够的权限吗？文档说：
"VACUUM processes every table in the current database that the current user has permission to vacuum"