---
title: "How to see how much space your tables are using"
publication: "Postgres Weekly"
issue: 294
published_at: "2019-02-27"
canonical_url: "https://postgresweekly.com/issues/294"
---

The latest Postgres news, articles, tools, and projects.      

Did you know we're running a tip of the week at the bottom of each issue now? If not, scroll down and check it out! :-)   

#294 — February 27, 2019 

[Read on the Web](https://postgresweekly.com/issues/294)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/bmmyebm9gbi5slivgwj8.jpg)](https://www.percona.com/blog/2019/02/21/parallel-queries-in-postgresql/)   

[Playing with Parallel Queries](https://www.percona.com/blog/2019/02/21/parallel-queries-in-postgresql/) — _“Parallel queries in PostgreSQL allow us to utilize many CPUs to finish report queries faster. The parallel queries feature was implemented in 9.6 and helps. Starting from PostgreSQL 9.6 a report query is able to use many CPUs and finish faster.”_ 

Nickolay Ihalainen   [![\](https://copm.s3.amazonaws.com/620f4361.png)](https://postgresvision.com/) 

[Don’t Miss Postgres Vision 2019](https://postgresvision.com/) — Join us June 24 - 26, Boston, MA. Register now. 

EnterpriseDB sponsor   

[The Current State of Open Source Backup Management for Postgres](https://severalnines.com/blog/current-state-open-source-backup-management-postgresql) — A deep-dive into the most popular open source backup programs available for Postgres, what their current state is, and how they compare to one another. 

Achilleas Mantzios   

[PostgreSQL BRIN Indexes: Big Data Performance With Minimal Storage](https://info.crunchydata.com/blog/postgresql-brin-indexes-big-data-performance-with-minimal-storage) — A block range index (or ‘BRIN’) can help you significantly reduce the amount of disk space required for high performance queries on big data. They were introduced in Postgres 9.5. 

Jonathan S. Katz   

[If Postgres is The Fastest Growing Database, Why's the Community So Small?](https://blog.timescale.com/postgresql-database-community/) — Andrew Staller of Timescale notes that Postgres continues to be called the ‘fastest growing DBMS’ but that meetups and community groups tend to remain quite small. 

Andrew Staller (Timescale)   

[PostgreSQL `fsync` Failure Fixed (And Why!)](https://www.percona.com/blog/2019/02/22/postgresql-fsync-failure-fixed-minor-versions-released-feb-14-2019/) — Postgres recently fixed a long standing issue with its use of `fsync`. This post nearly explains what the problem was. 

Avinash Vallarapu   

[Creating a Simple Contacts List with Laravel and Postgres](https://blog.digitalocean.com/create-simple-contacts-laravel-postgresql/) — Modern PHP is certainly a lot nicer than the PHP I remember wrestling with! 

Kamal Nasser   

[eBook: Best Practices for Optimizing Postgres Query Performance](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklySecondary) — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk in this free pganalyze eBook. 

pganalyze sponsor   

[How We Used Delayed Replication for Disaster Recovery with Postgres](https://about.gitlab.com/2019/02/13/delayed-replication-for-disaster-recovery-with-postgresql/) — Replication is no backup. Or is it? Here’s a look at delayed replication and how GitLab used it to recover from accidental label deletion. 

Andreas Brandl   

[Running a Bakery on Emacs and Postgres](https://bofh.org.uk/2019/02/25/baking-with-emacs/) — Here’s what happens when a programmer turns their skills to organizing a bakery. 

Piers Cawley   

[WAL-G: Archival and Restoration for Postgres](https://github.com/wal-g/wal-g) — Created as a successor to [WAL-E](https://github.com/wal-e/wal-e), WAL-G is a complete rewrite with better performance, parallelization, and compression options. 

Citus Data   

[Proj6 in PostGIS](http://blog.cleverelephant.ca/2019/02/proj4-postgis.html) — One for the geospatial crowd only! Proj is a library Postgres uses when converting spatial coordinates from one system to another. Proj6 is the latest version. 

Paul Ramsey       supported by [![\](https://res.cloudinary.com/cpress/image/upload/v1545062952/na5isnukkrtglivbcqvs.png)](https://www.strongdm.com/)  

💡 Tip of the Week 

See how much space your tables (and indexes!) are taking up  

I've been working on a small app (a [Hacker News title edits tracker](https://hackernewstitles.netlify.com/)) and using a very small [ElephantSQL](https://www.elephantsql.com/) instance for storage. Keeping an eye on my data usage is important to me, but how to do it? 

Leaning on a provider's admin system or a DB management tool to show you your total usage is a fine idea, but if you want to quickly grab some numbers from `psql`, there are some ways to do the same thing with SQL:   

SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total,
pg_size_pretty(pg_relation_size(relid)) AS internal,
pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;    

It's not the best looking query in the world, but gets you results like this:   

![](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/evcxzs4e1s9c3zptx0c8.jpg)    

Here we can see how much space a table is taking up in total (if you include both data stored in the table itself and in attached [TOAST tables](https://malisper.me/postgres-toast/)) as well as the indexes for each table. 

If you want to look at each index in question, rather than having all the indexes for a table bundled into one, there's [a slightly more complex query](https://gist.github.com/peterc/05fca349f6f0ad683941e36e82a97b9e) you can use.  

This week's tip is sponsored by _[strongDM](https://www.strongdm.com/)_, the best way to secure access to your servers/databases. [Schedule a 10 min demo.](https://www.strongdm.com/) 

If you have any ideas for your own tips, [reach out here](https://cooperpress.typeform.com/to/bTSq7v) - we'll pay up to $100 for your tip if we use it in an issue.          

Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens) 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom