Posts Tagged Tuning Postgres


I got a very interesting question today from a developer. What is the size of a tuple in a table. Well you kinda pause for a while and think ummm I thought I knew this one. Well postgresql has a contrib module that can help you out with this. Lets take a look at pgstattuple, this contrib module will give you information like table length, number of live tuples in a table, total length of live tuples, number of dead tuples, free space in a table and much more.

All this information is extremely helpful when you want to check the stats on a specific table. Lets go ahead and get this conrib module started in our current database:

Assuming you have source install for postgresql available

$ cd postgresql-8.4.9/contrib/pgstattuple
$ make
$ make install

Now we need to the pgstattuple functions to be available in the database we are trying to check the tuples on. For that run the sql:

$ psql dbname -f pgstattuple.sql

That’s it we are all set to check the tuples. Connect to your database and call the function as:

test=# SELECT * FROM pgstattuple('test.account_services');
-[ RECORD 1 ]------+---------
table_len          | 50683904
tuple_count        | 597431
tuple_len          | 47914138
tuple_percent      | 94.54
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 207320
free_percent       | 0.41

I got the approximate/average length of a tuple by tuple_len/tuple_count (not the smartest way to get that info but oh well it worked for me)

You can also use pgstatindex to get information on the indexes.

hdap=# SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version            | 2
tree_level         | 0
index_size         | 8192
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 37.54
leaf_fragmentation | 0


Leave a comment

Maintaining indexes

During the course of database and application maturity there are times when we try hard to improve the performance of the database to meet customer expectations in accordance to the agreed standards. These are the times when we think lets add an index on that old table to make it work faster.

There are a couple of things you should keep in mind when you are looking at adding an index to your table. Ask yourself the question “will this really help in performance”? Consider a scenario where you have a table that is pretty busy with inserts all day. You have a requirement for a report from that table during the night and add an index to speed up that report. Well works perfect for your little report running during off hours. How about the inserts happening during the day on the table that is being used excessively. You will notice there is a fall in performance there. Reason being you added another index and that index also needs to be updated with every insert/update. Increasing the time it takes and reducing the performance.

Make sure you run the below query once a month to get a report on the unused indexes on your database. This can be pretty useful when trying to boost your database’s performance. Also you will find that you have freed up quite some space that was lying around not being used by the database.

I would recommend running this off of crontab and sending the output via email every month.

Unused indexes:


    indexrelid::regclass as index, relid::regclass as table 



    JOIN pg_index USING (indexrelid) 


    idx_scan = 0 and indisunique is false;

NOTE: don’t just blindly drop the indexes listed in here. Make sure they are useless first. Also, running this is useless after a fresh restore or after calling pg_stat_reset(). If your stats have reset run at least one business cycle on the database before running the sql queries.

Index maintenance also covers removing duplicates. A lot of times I have seen indexes on a table with a unique key and then the same column has a primary key constraint. Primary keys are unique. Run the below SQL and identify the duplicate indexes that can be removed from the database. This will also help in improving performance and reclaiming lost space.

Duplicate Indexes:

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
       (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) AS KEY
    FROM pg_index) sub
ORDER BY sum(pg_relation_size(idx)) DESC;

If you ever thought about finding the foreign key columns that are missing indexes. Below is something that might help. Again check if you really need to add the index don’t just assume based on the query below:

Missing indexes:

select src_table, dst_table, fk_name, pg_size_pretty(s_size) as s_size, pg_size_pretty(d_size) as d_size, d
    from (
                distinct on (1,2,3,4,5)
                textin(regclassout(c.conrelid))  as src_table,
                textin(regclassout(c.confrelid)) as dst_table,
                c.conname                        as fk_name,
                pg_relation_size(c.conrelid)     as s_size,
                pg_relation_size(c.confrelid)    as d_size,
                array_upper(di.indkey::int[], 1) + 1 - array_upper(c.conkey::int[], 1) as d
        from pg_constraint c
        left join pg_index di on di.indrelid = c.conrelid
                and array_to_string(di.indkey, ' ') ~ ('^' || array_to_string(c.conkey, ' ') || '( |$)')
        join pg_stat_user_tables st on st.relid = c.conrelid
        where c.contype = 'f'
        order by 1,2,3,4,5,6 asc
    ) mfk
    where mfk.d is distinct from 0 and mfk.s_size > 1000000
    order by mfk.s_size desc, mfk.d desc;

, ,

Leave a comment

Tuning shared_buffers

It is always at the back of a DBA’s mind “have i tuned Postgres shared_buffers properly?” And we start looking at all the blogs about how to set it and a hit and trial method or increasing it and then decreasing it to find the optimal value. Although it is true to probably set it a maximum of 25% of your total memory and do the math around the database size but there is one such tool that can give you an idea if it is time to increase the shared_buffers or have we just made it too big that performance is falling down.

pg_buffercache contrib module will help make a decision for you. How? lets see:

Assuming you have the source installer in place just navigate to the contrib directory in source here you would find a module called pg_buffercache.

All you would need to do to install it is

$ make

$make install

To register the functions with your database

$ psql -d <database> -f pg_buffercache.sql

Its now time to check if we need to increase the shared_buffers. Remember to run the below SQL at high peak load times a couple of times to get a more realistic data.

The query below will show usage count data:

select usagecount, count(*), isdirty from pg_buffercache
group by isdirty, usagecount order by isdirty, usagecount;
usagecount | count  | isdirty
0 | 167072 | f
1 | 125923 | f
2 |  22361 | f
3 |  14248 | f
4 |  13284 | f
5 | 156717 | f
1 |  11605 | t
2 |   2528 | t
3 |   1996 | t
4 |   1239 | t
5 |   7315 | t

If there is a large number of buffers with high usage count of 4 or 5 your buffers are working well.

This query shows objects (tables and indexes) in cache:
 c.relname, count(*) AS buffers,usagecount
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount

This shows how much of  relations are in cache:
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname

, , ,

Leave a comment

Long running queries

Ever wondered if you did not get a call in the middle of the night just for some stupid stuck query that was not supposed to run that long and blocking all the other queries making the database load rise up till its not even manageable?

I did and finally got something to address it. Below is a script that logs all long running queries and kills them with pg_cancel_backend. Finally sends out an email alert, you can modify it according to your production requirement and increase the time of the query that should be killed. Just put it in a cronjob “AFTER TESTING IT PROPERLY”. I am not responsible for any data loss here.

Make sure you have postfix installed and configured for your mail to work.

# The script must run as postgres, since only postgres user has control over every database.
if [ `whoami` != "postgres" ]; then
exit 0;

# Selecting relevant columns, getting non-idle queries only where the query runs since at least 6 minutes.
psql -c "select procpid, client_addr, query_start, current_query from pg_stat_activity
where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now()
and substring(current_query, 1, 11) != 'autovacuum:'
order by query_start desc" > $LOGFILE

NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'`

if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then
# Getting the first column from the output discarding alfanumeric values (table elements in psql's output).
STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"`
for PID in $STUCK_PIDS; do
echo -n "Cancelling PID $PID ... " >> $LOGFILE

# "t" means the query is successfully cancelled.
SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"`
if [ $SUCCESS ]; then

cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes.";



, ,

Leave a comment

%d bloggers like this: