SQL Tips & Tricks – PostgreSQL Queries – Part 2

This is part 2 of PostgreSQL Queries Tips & Tricks from performance and overall backend side of your database. In Part 1 we have covered, duplicated, rarely used indexes and general amount of elements in your DB.

This time we will dive into the details of tables and indexes. Below you can find 2 queries to rule them all. Enjoy the read!

Get All Tables Details And Wasted Bytes

This query is a complex one. To better understand it, please go through pg_class table description as a starter: https://www.postgresql.org/docs/current/catalog-pg-class.html

Goal of this query is to show all table, (except ones in postgres schema) with size of wasted bytes in table greater than 0. For instance if you have removed rows from your table, and have done the vacuum then these deleted rows will still be there on the backend, treated as wasted bytes (oversimplification a bit but still to the point).

Result will give you, currently selected database, schema name and table name, total size of object in bytes and in “pretty” form (kB, MB etc.)  – together with indexes etc, table size in bytes and “pretty” form, table bloat and wasted bytes. You can find good description of bloat under this article (https://dba.stackexchange.com/questions/126258/what-is-table-bloating-in-databases).

Get All Indexes Details And Wasted Bytes

Above SQL statement is mostly build based on pg_index table and informations from PostgreSQL wiki https://wiki.postgresql.org/wiki/Index_Maintenance#Index_Bloat.

More about pg_index: https://www.postgresql.org/docs/current/catalog-pg-index.html

What it will produce: current database, schema name, table name and index name. For Indexes its size in bytes and “pretty” form, extra size (extra pages are subtracted), index fill factor and the size of index bloat (same as with bloat for tables).

Query is limited to all schema apart of postgres and to results where bloat_size is greater than 0.

Summary

Above two queries, won’t be used daily. However they should be for you to use them when you need it. I would recommend to add them to you regular maintenance scripts (activities). I can think of sending response to certain mail address for further investigation if needed etc.

 

More about performance, and useful queries you can find on official PostgreSQL wiki’s under following link: https://wiki.postgresql.org/wiki/Performance_Optimization

If you want to get weekly update from data driven world, feel free to sign up to our Data Craze Newsletter.

 
Data Craze Newsletter

If you want to be notified about new posts, and you want to get weekly dose of information from data world, feel free to sign up to this newsletter – join Data Craze community!


By clicking “Subscribe” you agree to send to You marketing information and to our Privacy Policy.
 

Thanks,

Krzysiek

 

Leave a Reply

Your email address will not be published. Required fields are marked *