CODEWITHSUNDEEP
postgresql
Grant Humphries
Grant Humphries

Reputation: 3016

Vacuum analyze all tables in a schema postgres

I have a very large postgres database that has one particular schema in it which is dropped in and recreated nightly. After all of the tables in that schema are created I want to vacuum analyze them, however the database is so large that if a do a full db VACUUM ANALYZE; it takes about a half hour.

How can I go about vacuum analyzing each of the tables in this schema only without writing a separate SQL command for each table?

Upvotes: 26

Views: 77230

Answers (8)

n0099
n0099

Reputation: 1363

https://www.postgresql.org/docs/current/app-vacuumdb.html

-n schema --schema=schema Clean or analyze all tables in schema only. Multiple schemas can be vacuumed by writing multiple -n switches.

-N schema --exclude-schema=schema Do not clean or analyze any tables in schema. Multiple schemas can be excluded by writing multiple -N switches.

and even for tables:

-t table [ (column [,...]) ] --table=table [ (column [,...]) ] Clean or analyze table only. Column names can be specified only in conjunction with the --analyze or --analyze-only options. Multiple tables can be vacuumed by writing multiple -t switches.

Upvotes: 2

Robert
Robert

Reputation: 311

A solution based on those of @Grant Humphries and @Fritz but somewhat shorter and simpler:

PGUSER=your_postgres_username
PGHOST=your_postgres_host
PGPORT=your_postgres_port
PGDB=your_postgres_db_name
PGSCHEMA=your_postgres_schema  

for table in $(psql -At -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
  -c "select tablename from pg_tables where schemaname = '${PGSCHEMA}';"); do
    psql -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
         -c "VACUUM (ANALYZE) ${PGSCHEMA}.${table};";
done

Differences to the mentioned solutions:

  • Get table names through a direct query on the system catalog view pg_tables. This makes it easier to extract the table names as we only need to remove the header and footer in the output, using head and tail.
  • Added port number, for those installations where it is required.
  • Updated the vacuum statement to the current (PostgreSQL 9.0 and later) syntax with parentheses. According to the documentation: "the unparenthesized syntax is deprecated."

Upvotes: 7

swiss_knight
swiss_knight

Reputation: 7831

An other way to do this is to use the \gexec command:

SELECT format('VACUUM FULL ANALYZE %I.%I;', table_schema, table_name)
  FROM information_schema.tables
 WHERE table_schema = 'your_schema'
   AND table_type = 'BASE TABLE' \gexec

To be run from a psql console.

Upvotes: 7

Fritz
Fritz

Reputation: 472

You can use the following pl/pgsql script (if you only want to analyze, vacuum cannot be executed from a function or multi-command string):

DO $$
DECLARE
  tab RECORD;
  schemaName VARCHAR := 'your_schema';
BEGIN
  for tab in (select t.relname::varchar AS table_name
                FROM pg_class t
                JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE t.relkind = 'r' and n.nspname::varchar = schemaName
                order by 1)
  LOOP
    RAISE NOTICE 'ANALYZE %.%', schemaName, tab.table_name;
    EXECUTE format('ANALYZE %I.%I', schemaName, tab.table_name);
  end loop;
end
$$;

Upvotes: 25

Grant Humphries
Grant Humphries

Reputation: 3016

The bash function below utilizes the CLI tool psql to vacuum analyze tables in a single schema which can be identified by either passing the name of the schema as the first parameter to the function or setting the environment variable PG_SCHEMA:

vacuum_analyze_schema() {
    # vacuum analyze only the tables in the specified schema

    # postgres info can be supplied by either passing it as parameters to this
    # function, setting environment variables or a combination of the two
    local pg_schema="${1:-${PG_SCHEMA}}"
    local pg_db="${2:-${PG_DB}}"
    local pg_user="${3:-${PG_USER}}"
    local pg_host="${4:-${PG_HOST}}"

    echo "Vacuuming schema \`${pg_schema}\`:"

    # extract schema table names from psql output and put them in a bash array
    local psql_tbls="\dt ${pg_schema}.*"
    local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*/\1/p"
    local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}"  | sed -nr "${sed_str}" )
    local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )

    # loop through the table names creating and executing a vacuum
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" \
            -c "VACUUM (ANALYZE) ${pg_schema}.${t};"
    done
}

This function can be added to your .bashrc to provide the ability to invoke it from the command line at any time. Like the schema, Postgres connection and database values can be set by either supplying them as function parameters:

# params must be in this order
vacuum_analyze_schema '<your-pg-schema>' '<your-pg-db>' '<your-pg-user>' '<your-pg-host>'

or by setting environment variables:

PG_SCHEMA='<your-pg-schema>'
PG_USER='<your-pg-user>'
PG_HOST='<your-pg-host>'
PG_DB='<your-pg-db>'

vacuum_analyze_schema

or by a combination of both. Values passed as params will take precedence over corresponding environment vars.

Upvotes: 15

khituras
khituras

Reputation: 1091

While searching for the answer of this exact question, I found the solution approach given by PPH the preferable. Unfortunately, the given command line does not quite work out of the box (tested with psql 10.8 on a Postgres server v9.6.13). Here is the command line I successfully used to VACUUM ANALYZE all tables of a specific schema in a specific PostgreSQL database:

psql -t -A -d "YOUR_DATABASE" -c "select format('vacuum analyse verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'YOUR_SCHEMA' order by 1" | psql -U postgres -d "YOUR_DATABASE"

You have to replace the three upper-case terms with the values applying to your case. Worked flawlessly for me.

Upvotes: 3

PPH
PPH

Reputation: 138

Wouldn't it be simpler to:

psql -t -A -U postgres -c "select format('analyse verbose %I.%I;', n.nspname::varchar, t.relname::varchar) FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace WHERE t.relkind = 'r' and n.nspname::varchar = 'your_schema' order by 1" | psql -U postgres

Options -t only prints rows (no headers) and -A avoid formatting

Upvotes: 10

Andrey L
Andrey L

Reputation: 17

do
$$
declare
  r record;
  schemaname varchar := 'contact';
begin
    perform dblink_connect('vacuum_connection', 'dbname=' || current_database());
    for r in (  select t.oid::regclass::text tname
                from pg_class t
                where t.relkind = 'r' and t.relnamespace = schemaname::regnamespace)
    loop
        raise notice '%1', r.tname;
        perform dblink_exec('vacuum_connection', 'vacuum analyze ' || r.tname);
    end loop;
    perform dblink_disconnect('vacuum_connection');
end
$$

Upvotes: -1

Related Questions