Both vacuum_count and analyze_count are zero after VACUUM ANALYZE

Question

I've written a unit test which modifies a table (INSERT's and DELETE's), then manually VACUUM's and ANALYZE's it, and then I query pg_stat_user_tables to make sure that VACUUM and ANALYZE did have some effect.

I use the following 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();

for regular tables and

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();

for temporary ones.

Still, when I run my example, I observe that both vacuum_count and analyze_count are zero, and the table contains lots of dead tuples, e.g.:

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

Why's that?

The self-contained code sample is below:

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
                ? "select\n" +
                        "  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_count\n" +
                        "from\n" +
                        "  pg_stat_user_tables tbl\n" +
                        "join\n" +
                        "  pg_namespace nsp\n" +
                        "on\n" +
                        "  tbl.schemaname = nsp.nspname\n" +
                        "where\n" +
                        "  tbl.relname = lower('%s')\n" +
                        "  and nsp.oid = pg_my_temp_schema()"
                : "select\n" +
                        "  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_count\n" +
                        "from\n" +
                        "  pg_stat_user_tables tbl\n" +
                        "where\n" +
                        "  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;
    }
}

Answer 1

VACUUM (without FULL) marks dead tuples as ready for reuse, it’s also able to trumcate dead tuples at the end of the table, but it doesn’t eliminate those dead tuples.

VACUUM FULL rewrites the whole table from scratch so dead tuplesgwt removed in the process, but it requires an exclusive lock on the table:

https://www.postgresql.org/message-id/dcc563d10710021855l7ac247ebv62c5fc44a321ee1f%40mail.gmail.com

If you have a large table and would like to run VACUUM FULL on it while being able to use it in the process, consider using https://github.com/reorg/pg_repack/.