Introduction to PostgreSQL, one of the most popular database engines

Jan 15, 2025

In this series, we’ve explored various database engines, such as SQLite, and the fundamental concepts of the relational model. Now, we’ll dive into PostgreSQL, a relational database management system (RDBMS) widely recognized for its robustness, scalability, and extensibility. Designed for complex applications, PostgreSQL combines a client-server architecture with advanced support for multiple users and specialized features. Its versatility makes it an ideal choice for both enterprise projects and personal developments.

Like other relational engines such as SQLite, PostgreSQL uses SQL (Structured Query Language) to interact with data. However, it stands out by offering advanced features such as support for JSON/JSONB, modular extensions, and efficient concurrency management. These make PostgreSQL particularly suitable for applications requiring flexibility, high availability, and efficient handling of large volumes of data.

In this article, we will set up PostgreSQL in a virtualized environment using Docker and design a database to model a transportation network. This practical exercise will include One-to-One, One-to-Many, and Many-to-Many relationships, allowing you to practice fundamental concepts of the relational model while exploring PostgreSQL’s capabilities. Additionally, we will lay the groundwork for future explorations of its advanced features.

1. Route: Defines the routes operating within the transportation network.
2. Stop: Lists the stops included in the routes.
3. Vehicle: Contains information about the vehicles operating on the routes.
4. Schedule: An intermediary table that manages the schedules assigned to specific routes and stops.

A visual diagram illustrating the database schema for a public transportation network. It includes the 'route', 'stop', 'vehicle', and 'schedule' tables, with their respective fields and foreign key relationships.

Running PostgreSQL with Docker

To run PostgreSQL in a local environment and connect to it using a database management client such as DBeaver or PgAdmin, you can use Docker. This will allow you to quickly set up a clean and controlled environment without needing to install PostgreSQL directly on your system.

Run the following command to create and launch a PostgreSQL container:

  
  docker run --name postgres-transport -e POSTGRES_PASSWORD=secret -d -p 5432:5432 postgres
  

If you’re not familiar with Docker, you can check out our series on Docker and return to this point once you feel comfortable. That said, this command will perform the following steps:

1. Download the official PostgreSQL image (if you don’t already have it on your machine).
2. Create a container named postgres-transport.
3. Set up the administrator user password (postgres) as secret.
4. Expose port 5432, the default port for PostgreSQL, so you can connect using external tools.

Connecting with an SQL Client

Once the container is running, you can use an SQL client to connect to the database. The required credentials are:

Host: localhost  
Port: 5432  
User: postgres  
Password: secret  
Database: postgres (default database)

In this example, we’ll use DBeaver as our SQL client, but feel free to use any other tool you prefer. Follow these steps to connect to the database:

1. Download and install DBeaver from its official website.
2. Open DBeaver and select PostgreSQL as the connection type.
3. Enter the credentials mentioned above.
4. Follow the wizard to complete the connection setup.

DBeaver interface showing the screen to create a new database connection. Multiple database engines, such as PostgreSQL, MySQL, and SQLite, are displayed as options.

Once connected, you can use DBeaver’s graphical interface to manage your database, add, modify, and delete tables, as well as execute SQL queries and much more.

Database Design

The model consists of four main tables: Route, Stop, Vehicle, and Schedule. Each table has primary keys and well-defined relationships to ensure referential integrity. You can open the table creation dialog in DBeaver by right-clicking on the relevant section in the navigation tree.

DBeaver interface with the context menu open in the database navigation tree. The option to create a new table within the 'public' schema of a PostgreSQL database is selected.

Table: Route

Table: Stop

Table: Vehicle

Table: Schedule

Relationship Summary

• One-to-Many:
  • Route ↔ Stop: A route can include multiple stops.
  • Route ↔ Vehicle: A route can have several assigned vehicles.
• Many-to-Many:
  • Schedule manages the relationship between routes and stops, adding context with timing and vehicles.

A visual diagram illustrating the database schema for a public transportation network. It includes the 'route', 'stop', 'vehicle', and 'schedule' tables, with their respective fields and foreign key relationships.

In PostgreSQL, the SERIAL data type is commonly used to define columns that serve as unique identifiers or primary keys in a table. This data type simplifies the creation of auto-increment sequences, automatically generating values for these columns whenever new records are inserted.

When you define a column as SERIAL, PostgreSQL automatically performs three actions:

1. Creates a sequence to generate unique numbers.
2. Assigns that sequence as the default value for the column.
3. Marks the column as a unique or primary key if specified.

Unlike other systems such as MySQL, where auto-increment functionality is directly tied to the column, PostgreSQL creates an independent sequence that allows for greater flexibility. This sequence can be customized, reused across multiple tables, or adjusted to set an initial value (START WITH) or increment (INCREMENT BY). Additionally, PostgreSQL provides variants such as SMALLSERIAL, SERIAL, and BIGSERIAL, which correspond to the data types SMALLINT, INTEGER, and BIGINT, respectively.

Advanced PostgreSQL Features

So far, we’ve explored how to design and work with a relational database in PostgreSQL, applying fundamental concepts such as table creation, relationships, and the use of data types like SERIAL. This knowledge provides an excellent foundation for further exploration and experimentation with this powerful database management system.

PostgreSQL stands out not only for its robustness and flexibility but also for its impressive range of advanced features that set it apart from other relational database systems. Some of these features include:

• JSON and JSONB: The ability to store and query data in JSON format makes PostgreSQL ideal for modern applications that handle semi-structured data. Its JSONB (binary JSON) variant further optimizes searches and operations on this type of data.

• Extensions: One of PostgreSQL’s unique strengths is its extension system, which allows you to expand its capabilities to meet specific project needs. Popular extensions like PostGIS (for geospatial data) or **pg_trgm ** (for similarity-based text searches) make PostgreSQL extremely versatile.

• Advanced Functions: PostgreSQL enables the creation of custom functions and triggers, allowing you to automate processes and execute business logic directly within the database.

• Concurrency Support: Thanks to its Multi-Version Concurrency Control (MVCC) model, PostgreSQL ensures consistent transactions even in environments with multiple users.

DBeaver interface showing the structure of the 'transport_network' database within the 'public' schema. The main section displays the details of the 'route' table, including columns like 'id', 'name', 'description', and 'created_at', along with their data types and constraints.

While we won’t dive deeper into these features in this article, exploring them can unlock a world of possibilities for your projects. As you continue practicing with the concepts covered in this series, we encourage you to learn more about the features that make PostgreSQL such a special tool. In future articles, we’ll delve deeper into these and other advanced PostgreSQL capabilities.

Keep learning and exploring! PostgreSQL has so much more to offer, but most importantly, Happy Coding!