Scribd Logo
Upload

Welcome to Scribd!

  • 7 views

    Database Maintenance

    Uploaded by

    Stephen Efange
    database maintenance

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Database Maintenance

    Uploaded by

    Stephen Efange
    7 views2 pages
    database maintenance

    Original Title

    database maintenance

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    database maintenance

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    7 views2 pages

    Database Maintenance

    Uploaded by

    Stephen Efange
    database maintenance

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 2

    Autovacuum is done every 60 secs if its turned on.

    It is off by default.

    Do database maintenance in one of 3 ways:

    1. With Maintenance tools: Manually

    analyze - This will update the statistics of the optimizer


    Vacuum
    cluster

    2. With the OS tool vacuumdb - This can be ran manually or scripted and automated
    in cron

    3. We can set up autovacumming in which case the postgres process will run a vacuum
    every 60 secs by default.

    show autovacuum;

    alter system set autovaccum to off;

    select pg_reload_conf();

    show autovacuum;

    analyze is more or less the optimizer or helps update its stats:


    The statistics are stored in the pg_class and pg_stat_user_tables
    The columns analyze_count in pg_stat_user_tables show you how many times an analyze
    command has been ran on a table or relation.

    #\x
    select * from pg_stat_user_tables

    Table contains the Pages

    Pages contains the dead - deleted and the live rows: In pg_stat_user_tables
    n_live_tup
    n_dead_tup

    Deleted rows will be marked as dead.

    analyze verbose tablename; --where tablename is the table you want to run analyze
    on in a verbose manner.

    How do we remove the dead rows or deal with the Fragmentation issue?

    In other words how do we De-fragment.

    The deleted or obsoleted rows have to be removed by vacuuming.

    Vacuuming will help you shrink the data file size when required.

    Vacuuming can be automated using the autovaccum process.

    Vacuuming needs to take an exclusive lock so any long running transaction that is
    already running can block the vacuum operation.
    vacuum will clear the dead rows at the page level. it will not give the spave back
    to the disk.

    vacuump will update the visibility map that will help speed up the index only scan.

    select pg_size_pretty(pg_relation_size('tablename'));

    vacuum tablename;
    select pg_size_pretty(pg_relation_size('tablename'));
    analyze verbose tabblename;
    select pg_size_pretty(pg_relation_size('tablename'));

    vacuum full will release the disk space as its a more aggressive vacuum algorythym.

    vacuumdb -j 4 -z -t tablename -d databasename

    vacuumdb -j 4 -z -t actor -d dvdrental

    You might also like