If you're here, I'm hoping you're familiar with the basics of database management systems, and have an idea of how SQL queries and different database systems work. This article is about understanding and getting started with PostgreSQL as a beginner.
What Is PostgreSQL?
PostgreSQL is a free and open-source ORDBMS (object-relational database management system). Object-relational means it directly supports features like classes, inheritance, and objects — features of object-oriented programming — in the structure of the database and its query language.
PostgreSQL, initially called Postgres, was a research project started at the University of California, Berkeley. It's a cross-platform ORDBMS, and can run on a number of operating systems, including Windows, Linux, macOS, FreeBSD, OS X, and Solaris.
PostgreSQL features transactions with ACID properties — atomicity, consistency, isolation, and durability. It also manages concurrency through Multi-Version Concurrency Control (MVCC), which ensures isolation and consistency throughout the database when more than one user is making changes at a time.
Naming Conventions in PostgreSQL
Since different platforms have their own naming conventions, let's go over PostgreSQL's:
- Tables / Indexes → Relations
- Row → Tuple
- Column → Attribute
- Data Block / Data Cell → Page (on disk) or Buffer (in memory)
A data block, data cell, or page is the smallest unit of data storage in PostgreSQL — think of it as a cell in a table. Each table can be seen as an array of fixed-length pages. The default size of each page is 8KB, which can be tuned when required. An important convention: when pages are on disk, immediately accessible, they're called pages; when stored and fetched from memory, they're termed buffers.
Installing and Setting Up PostgreSQL on Windows
To install PostgreSQL, go to the official PostgreSQL download page.
On opening the website, click on your operating system family (Windows, in our case), click Download the installer, and download the latest version of the PostgreSQL installer available. At the time of writing, 14.2 was the latest version available.
The download may take a while, depending on your internet speed. Once the download is complete, go to the folder where PostgreSQL was downloaded, and open the .exe file with administrative privileges (right-click and select "Run as administrator"). From here, installation is pretty straightforward:
- Welcome to the PostgreSQL Setup Wizard — press Next.
- Specify the installation directory for PostgreSQL, and press Next.
- Select components to install, deselect any you don't want, and press Next. We recommend installing all components if you're not sure what each is for.
- Select where your database data will be stored, and press Next.
- Type in your superuser password, and press Next. The "superuser" is responsible for setting up all new users on the database — make note of and remember this password.
- Select the port for PostgreSQL, and press Next. By default, the port number is 5432. This can be changed later, but we recommend using the default.
- Select the language of your ORDBMS, and click Next. By default, it's set to
<Default locale>, which picks up your system language for the setup. - Review the summary of your installation choices, click Next, and let the installation complete — this may take a few minutes.
- After installation completes, you may uncheck the box to launch "Stack Builder" if you don't want to install add-ons right now. If you'd like to, go ahead and launch it by leaving the box checked.
- Click Finish.
Verifying the Installation
There are a few ways to verify a successful PostgreSQL installation:
-
Checking Windows services — go to Search → Services, and look for PostgreSQL running as a service. If you see a running instance in the list, the ORDBMS installed successfully.
-
psql (SQL shell) — go to Search → psql to open the SQL shell. On opening, it'll ask a few questions:
Server [localhost]:— since the server is installed locally, just press enter.Database [postgres]:— by default, PostgreSQL creates a database calledpostgreswhen none exists yet; just press enter.Port [5432]:— press enter.Username [postgres]:— press enter.Password for user postgres:— type your superuser password, then press enter.
You can view databases by running
\lin the shell — you'll see a couple of default databases created by PostgreSQL, even before you create any of your own. Use\qto quit the shell.
Now, to access PostgreSQL from anywhere in your system, you need to edit your system's environment variables. First, open the folder where PostgreSQL was installed (by default, something like C:\Program Files\PostgreSQL), and copy the path to its bin folder — and separately, the path to its data folder. Search for "advanced system settings" in Windows Settings, and click on Environment Variables.
Edit the PATH variable to add the path to the bin folder you copied earlier. Then click New, and add a variable named PGDATA with the value set to the path to the data folder you copied earlier.
Voilà — your PostgreSQL setup is complete. Happy engineering!