Member-only story
3 Things You Might Not Know About PostgreSQL
Advance your knowledge about the popular database
PostgreSQL is a popular relational database following SQL compliance. After the initial release back in 1996 it keeps attracting many digital businesses and developers. Being an open-source project allows everyone to go through the code and contribute to it. It demonstrates good performance and its benchmarks are able to compete with other databases such as MySQL, SQL Server, and Oracle Database.
SQL queries are standardized across all database management systems. They might have some minor differences, but that is not critical. What is more interesting for engineers to know is that Postgres has something beyond just storing the data. That can bring some insights to the team about the information they work on.
Get Size of Anything
When your system operates on a huge scale, you will be curious to understand what size of data you have collected. We would like to understand what is the size of the database, a specific table, or even a certain column. Such information is valuable in understanding if the space is being used efficiently and to predict any scaling events.
Postgres offers a good set of commands related to size. For example, to get the size of the entire database we can use the command pg_database_size(). It will print out the size of the specific database in bytes. The command pg_table_size() displays the size in bytes of a certain table in the current database excluding indexes. The command pg_total_relation_size() does almost the same but includes the space needed for indexes. And if we want to know how much space is required for a concrete column we can use the command pg_column_size().
To run those commands we need to go to the Postgres console by running psql in a terminal. We also could use third-party tools such as DataGrip from Jetbrains or similar. Once we are in the console, we can run SQL queries along with those commands.
SELECT pg_database_size('my_database');The query from the above prints out the following output.
pg_database_size
------------------
8258415
