Find the size of a table in Postgres

You often need to know the size of a table in postgres and there is no particularly easy way to get that information directly from the psql client.

Fortunately, all of the information is in there and is available through nice simple functions.

All you have to do is pull it together and a stock query will normally do.

The below query will get you a list of all of the tables in your database, it will show you the size of the associated indexes as well as the size of the associated TOAST objects.

If you want the size of your database, I would tend to just do a ‘\l+’ in psql but if you want to do it in SQL for some reason then you can just do:

And then if you want the size of all of your databases then its a simple change so that it looks like:

And finally, if you want a size breakdown of all of the objects in your database – so that you can see all of the indexes as well as the tables then you can find that with:

Postgres Vacuum and AutoVacuum.

Basics of vacuum Postgres maintains multi version consistency by keeping old versions of changes tuples instead of actually deleting them. Eventually, keeping all of those out of date versions becomes big burden in terms  of storage and performance. Eventually, you end up with bloated tableland indexes. If not felt with, eventually they would fill up tour disks but they would probably make the database unusable before then so we have a handy process to clean it all up. That is Vacuum. Postgres Vacuum goes through your tables and indexes an cleans out had tuples – that is tuples that can no longer be needed by a transaction.

Continue reading “Postgres Vacuum and AutoVacuum.”

Backing up and Restoring Postgres using pg_basebackup

There are several great tools available that handle backing up and managing the backups of your PostgresQL database. It is really important to understand the underlying process that these tools use though as well as the standard postgres commands are that you would need to run in case you ever need to do it manually.

There are 2 types of backups that you can take in Posgres, logical and physical. Logical backups are in the form if the SQL statements necessary to recreate the database (not necessarily in a human readable form). The 2 tools to take logical backups are pg_dump and pg_dumpall.

Continue reading “Backing up and Restoring Postgres using pg_basebackup”