{"id":1114,"date":"2021-03-17T14:37:53","date_gmt":"2021-03-17T12:37:53","guid":{"rendered":"https:\/\/www.scalahosting.com\/kb\/?p=1114"},"modified":"2025-11-27T11:39:35","modified_gmt":"2025-11-27T09:39:35","slug":"how-to-determine-the-size-of-postgresql-databases-and-tables","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/kb\/how-to-determine-the-size-of-postgresql-databases-and-tables\/","title":{"rendered":"Check PostgreSQL Database Size Command"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Postgres is an <\/span><b>advanced, robust and reliable enterprise-class open-source relational database<\/b><span style=\"font-weight: 400;\">. It supports both <\/span><i><span style=\"font-weight: 400;\">relational (SQL) and non-relational (JSON) querying.<\/span><\/i><\/p>\n<p><b>Postgres is highly scalable<\/b><span style=\"font-weight: 400;\">. This rings true in both the volume of data it can handle and the number of concurrent users it can support.<\/span><\/p>\n<p><b>Popular use cases for Postgres includes:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">General-purpose OLTP (Online Transactional Processing) databases<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Geospatial databases<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Federated hub databases<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LAPP (<\/span><i><span style=\"font-weight: 400;\">Linux, Apache, Postgres and PHP, Python and Perl<\/span><\/i><span style=\"font-weight: 400;\">) databases<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">This article will be looking at how to determine the size of PostgreSQL databases and tables <\/span><b>using the psql command-line program<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">But first, let\u2019s take a look at Postgres architecture.<\/span><\/p>\n<h2><b>Understanding Postgres Architecture<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Postgres architecture runs on <\/span><b>the client and server model<\/b><span style=\"font-weight: 400;\">. The clients send requests to the server, and the server responds with the requests. They send these requests over a secured network.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-1115 aligncenter\" src=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h35_27.png\" alt=\"Check PostgreSQL Database Size Command, Understanding Postgres Architecture\" width=\"438\" height=\"307\" srcset=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h35_27.png 438w, https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h35_27-300x210.png 300w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/p>\n<p><i><span style=\"font-weight: 400;\">Image Credit: Edecba<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Client computers provide an interface to allow a Postgre user to request the server\u2019s services and display its results.<\/span><span style=\"font-weight: 400;\"> A Postgres server can handle <\/span><b>multiple concurrent sessions from the client<\/b><span style=\"font-weight: 400;\"> without logging any user out.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This capability makes Postgres suitable for <\/span><b>complex and large-scale operations<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><b>Postgres architecture consists of three components:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">The <\/span><b>shared memory<\/b><span style=\"font-weight: 400;\"> reserves the memory for transactions and log catches.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Background processes<\/b><span style=\"font-weight: 400;\">; these are software programs that run behind the scene to make other software run faster or perform their tasks.\u00a0<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">The <\/span><b>data directory<\/b><span style=\"font-weight: 400;\"> contains the physical files of the Postgres database server.<\/span><\/li>\n<\/ul>\n<p><i><span style=\"font-weight: 400;\">Now, how can you determine the size of the PostgreSQL databases and tables?<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Let\u2019s get to it.<\/span><\/p>\n<h2><b>How to Determine The Size of Postgres Databases<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">To determine your Postgre database\u2019s size from the command line, <\/span><b>you\u2019ll need to install a <\/b><a href=\"https:\/\/www.scalahosting.com\/kb\/access-server-ssh\/\"><b>secure shell (SSH) access<\/b><\/a><span style=\"font-weight: 400;\"> client on your local computer.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The SSH provides <\/span><b>secured command-line access<\/b><span style=\"font-weight: 400;\"> to your hosting accounts. This allows you to <\/span><b>log in to your server remotely<\/b><span style=\"font-weight: 400;\"> and run commands from anywhere. It also will enable you to perform specific tasks you couldn\u2019t do through the <\/span><a href=\"https:\/\/www.scalahosting.com\/spanel.html\"><b>SPanel<\/b><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">You can download SSH clients like <\/span><b>PuTTY<\/b><span style=\"font-weight: 400;\"> and <\/span><b>WinSCP<\/b><span style=\"font-weight: 400;\">, or you can use your OS&#8217; built-in command-line interface (PowerShell for Windows or the Terminal for macOS and Linux).<i><br \/>\n<\/i><\/span><\/p>\n<p><b>R<\/b><span style=\"font-weight: 400;\">un this command to access the database you want to determine its size:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">psql <\/span><i><span style=\"font-weight: 400;\">databname<\/span><\/i> <i><span style=\"font-weight: 400;\">username<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Of course, replace<\/span> <i><span style=\"font-weight: 400;\">databname<\/span><\/i> <span style=\"font-weight: 400;\">and <\/span><i><span style=\"font-weight: 400;\">username<\/span><\/i> <span style=\"font-weight: 400;\">with the name of the database you want to access and username, respectively. When prompted, <\/span><b>enter the database user\u2019s password<\/b><span style=\"font-weight: 400;\">. <\/span><span style=\"font-weight: 400;\">You\u2019re connected when the remote server&#8217;s command line prompt appears.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now, run this command to determine the database size.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT pg_size_pretty( pg_database_size(&#8216;<\/span><i><span style=\"font-weight: 400;\">databname<\/span><\/i><span style=\"font-weight: 400;\">&#8216;) );<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">databname<\/span><\/i> <span style=\"font-weight: 400;\">with the name of the database you want to access. Running this command will make the server return the result of the database size.<\/span><\/p>\n<h2><b>Determining The Size of PostgreSQL Tables<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">To determine the size of a Postgres database table from the command line, <\/span><b>log in to your server using SSH<\/b><span style=\"font-weight: 400;\"> and access the database with the table you want to check.<\/span><\/p>\n<p><b>Type this code<\/b><span style=\"font-weight: 400;\"> in the command line to determine the size of any of the database\u2019s tables.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">SELECT pg_size_pretty( pg_total_relation_size(&#8216;tablename&#8217;) );<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">tablename<\/span><\/i> <span style=\"font-weight: 400;\">with the name of the Postgres table you want to check. Doing this will allow the psql program to display the table\u2019s size.<\/span><\/p>\n<h2><b>Wrapping It Up<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">SSH access is a <\/span><b>time-saving solution<\/b><span style=\"font-weight: 400;\"> that helps people speed up many tasks. You can check your PostgreSQL databases sizes and Postgres table sizes remotely from the command line. This guide has demonstrated how to go about that. <\/span><b>Follow the steps<\/b><span style=\"font-weight: 400;\"> here to get started.\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Postgres is an advanced, robust and reliable enterprise-class open-source relational database. It supports both relational (SQL) and non-relational (JSON) querying. Postgres is highly scalable. This rings true in both the volume of data it can handle and the number of concurrent users it can support. Popular use cases for Postgres includes: General-purpose OLTP (Online Transactional [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_seopress_robots_primary_cat":"none","_seopress_titles_title":"Check PostgreSQL Database Size Command | ScalaHosting KB","_seopress_titles_desc":"","_seopress_robots_index":"","footnotes":""},"categories":[34],"tags":[],"class_list":["post-1114","post","type-post","status-publish","format-standard","hentry","category-web-hosting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/comments?post=1114"}],"version-history":[{"count":6,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1114\/revisions"}],"predecessor-version":[{"id":5835,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1114\/revisions\/5835"}],"wp:attachment":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/media?parent=1114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/categories?post=1114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/tags?post=1114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}