Snippet: Size of PostgreSQL Tables using SqlAlchemy

This does a decent job of getting approximate sizes of tables and indexes in a PG table, using SA.

# SELECT pg_database_size('geekdb');
# with indexes
# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
# without
# SELECT pg_size_pretty(pg_relation_size('big_table'));

from sqlalchemy import MetaData

dbparts = dict(user = some_user,
    pw = some_user,
    db = somedb,
    host = localhost)
dburi = "postgresql://%(user)s:%(pw)s@%(host)s/%(dd)s" % dbparts

def table_sizes(dburi,pretty=False):
    """ 
    Args:
        dburl.  string.  url of of the db to be reflected
        prettyprint. bool.  make the indexes into GB or whatnot
    
    Returns:
        dict::

            {table_name:  {'_total': int, '_table': int,
                'indexes':  {'idx1': int}}}
        
    Note:  Only works on PG, using PG specific items
    """
    out = dict()
    m = MetaData(dburi)
    e = lambda x: m.bind.execute(x).first()[0]
    m.reflect()
    q_pretty_total = "SELECT pg_size_pretty(pg_total_relation_size('%s'))"
    q_pretty_relation = "SELECT pg_size_pretty(pg_relation_size('%s'))"
    q_total = "SELECT pg_total_relation_size('%s')"
    q_relation = "SELECT pg_relation_size('%s')"

    for table_name,table in m.tables.iteritems():
        t = dict()
        if pretty:
            t['_total'] = e(q_pretty_total % table_name)
            t['_table'] = e(q_pretty_relation % table_name)
        else:
            t['_total'] = e(q_total % table_name)
            t['_table'] = e(q_relation % table_name)
        for idx in table.indexes:
            name = idx.name
            if pretty:
                t[name] = e(q_pretty_relation % name)
            else:
                t[name] = e(q_relation % name)
        
        out[table_name] = t
    
    return out

Advertisements

One Comment on “Snippet: Size of PostgreSQL Tables using SqlAlchemy”

  1. He will be glad to see your everything in the application
    in order, and going to the right lender of lending institution.
    Use an online mortgage principal calculator see Resources below to find out
    if the amount you still owe sign up for such loans raleigh nc?
    These can be used for aircraft rentals, instructor training, to purchase requires money — something
    the borrower clearly doesn’t have, otherwise he would be sending in loan payments.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s