Vacuum analysis of all tables in postgres schema

Question

I have a very large postgres database, which has one specific schema that is logged and updated every night. After all the tables in this schema have been created, I want to analyze them in a vacuum, but the database is so large that if you run a full db VACUUM ANALYZE;, it takes about half an hour.

How can I perform a vacuum analysis of each table in this schema only without writing a separate SQL command for each table?

Answer 1

The bash function below uses the CLI tool psqlto vacuum analyze tables in a single schema, which can be identified by passing the schema name 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 yours .bashrcto provide an opportunity to call it from the command line at any time. Like the schema, Postgres connections and database values ​​can be set by providing 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 a combination of both. Values ​​passed as parameters will take precedence over the corresponding environment variables.

Answer 2

pl/pgsql script ( , ):

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 %1.%2', schemaName, tab.table_name;
    EXECUTE 'ANALYZE '||schemaName||'.'||tab.table_name;
  end loop;
end
$$;

Answer 3

, @Grant Humphries @Fritz, :

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 -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
  -c "select tablename from pg_tables where schemaname = '${PGSCHEMA}';" | \
  tail -n +3 | head -n -2); do
    psql -h ${PGHOST} -p ${PGPORT} -d ${PGDB} -U ${PGUSER} \
         -c "VACUUM (ANALYZE) ${PGSCHEMA}.${table};";
done

:

• pg_tables. , , head tail.
• , , .
• (PostgreSQL 9.0 ) . : " ".

Answer 4

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
$$

Answer 5

:

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

-t ( ), -A

Answer 6

In search of an answer to this exact question, I found the approach to the solution proposed by PPH more preferable. Unfortunately, this command line does not work out of the box (tested with psql 10.8 on Postgres v9.6.13 server). Here is the command line that I have successfully used for VACUUM ANALYZEall 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 must replace the top three terms -c with the values ​​that apply to your case. Worked flawlessly for me.