How do you find the disk size of a Postgres / PostgreSQL table and its indexes

Title: "How to Easily Find the Disk Size of a Postgres Table and its Indexes"

Hey there, fellow tech enthusiasts! 👋

So, you've recently made the switch from Oracle to Postgres, but now you're scratching your head wondering how to find the disk size of a specific Postgres table and its corresponding indexes. Fear not, for I have the perfect guide to help you sail through this seemingly daunting task! 🚀

Understanding the Challenge

As you mentioned, in your Oracle days, you had a nifty query that peeked into user_lobs and user_segments to retrieve the desired answer. However, in the world of Postgres, things are a little different. But worry not, my friend – Postgres has got your back with an intuitive solution! 💡

The Solution: Utilizing the Information Schema

Postgres provides an ingenious way to fetch information about database objects, such as tables and indexes, through the information_schema tables. Let's dive into the steps:

1. Connect to your Postgres database using your preferred client tool or through the command line.

2. Execute the following query to fetch the table size (in bytes) of a specific table:

SELECT pg_size_pretty(pg_total_relation_size('your_table_name')) AS table_size;

Replace 'your_table_name' with the actual name of the table you want to query. This query utilizes the pg_total_relation_size function, which calculates the total disk space used by the table, including all associated indexes, auxiliary and toast tables.

1. Execute the following query to fetch the size (in bytes) of the indexes associated with the same table:

SELECT pg_size_pretty(pg_indexes_size('your_table_name')) AS index_size;

Again, replace 'your_table_name' with the actual table name. This query employs the pg_indexes_size function, which calculates the total disk space occupied by all indexes related to the specified table.

Voila! You now have the table size and index size at your fingertips! 🎉

Going Beyond: Finding Sizes for All Tables

If you're interested in retrieving sizes for all tables within your database, I've got just the solution for you! 🌟

Execute the following query to fetch the sizes (in bytes) of all tables in your Postgres database:

SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name)) AS table_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_size DESC;

This query leverages the information_schema.tables view to retrieve details about all tables in the specified schema (in this case, the "public" schema). The pg_total_relation_size function is then used to calculate the total disk space for each table.

Get Engaged! 📢

And there you have it – an easy, step-by-step guide to help you find the disk size of a Postgres table and its associated indexes. Say goodbye to headaches and hello to simple awesomeness! 🙌

If you found this guide helpful, or if you have any questions or other Postgres-related topics you'd like me to cover, drop a comment below and let's get the conversation rolling! 💬

Don't forget to share this blog post with your fellow tech-savvy pals who may benefit from this knowledge. Sharing is caring, after all! 😊

Stay tuned for more tech tips and tricks! Until next time! ✌️

Additional resources

• Postgres Documentation: System Information Functions

• Postgres Documentation: Information Schema

Disclaimer: This blog post is aimed at providing a simple and straightforward solution based on the information available at the time of writing. Please refer to the official Postgres documentation and consult with experts for comprehensive and up-to-date information.