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

Tidbit: git-add –patch on a new file.

Today, I wanted to add a file to the git index interactively.

No problem!

$ echo blah > somefile
$ git-add --patch somefile
fatal: exec add--interactive failed.

It turns out there is a problem. Until git knows about the file, it can’t actually diff against it, or go into interactive patch mode. As it turns out, someone has thought ahead.

$ git add -N somefile  # adds it!

“-N” is the “–intent-to-add” flag. Whether this is a win or not is left as an exercise for the reader, but it does exist.