What is PostgreSQL?
Author: Domeny.ForSale editorial team Published
In brief: PostgreSQL is an open-source database management system that stores information and lets applications read and modify it. It works well for online stores, business systems and web services, among other uses, where the integrity of related data matters. It can be used without licence fees, but running and maintaining a database requires resources and administration, or paying for a managed service.
What is PostgreSQL and how does it work?
PostgreSQL, also called Postgres, is an object-relational system. Its foundation is tables: organised sets of rows and columns that can be linked to one another. The “object” part refers, among other things, to the ability to define custom data types; when getting started, however, understanding tables and relationships is the most important thing.
In a store’s database, one table might contain customers, another orders, and a third products. An order refers to a particular customer through their identifier. This means there is no need to copy all the customer’s data with every purchase.
Communication with the database primarily uses SQL, a language for describing data and performing operations on it. An example query retrieving the names of available products might look like this:
SELECT name, price
FROM products
WHERE stock_quantity > 0
ORDER BY price;
PostgreSQL usually runs as a separate server process. The application connects to it through an appropriate driver, sends queries and receives results. The user’s browser generally does not communicate directly with the database — that is handled by the backend, the part of the application running on the server.
What protects data integrity?
One of PostgreSQL’s most important features is transactions: groups of operations treated as a single unit. If the operations cannot be completed correctly, the changes can be rolled back rather than leaving partially saved data.
Imagine buying the last unit of a product. The application must create an order and reduce the stock quantity. A transaction allows both changes to be committed together, while appropriately designed locking or a conditional update helps prevent the same unit from being sold to two customers. Simply having a database, however, is no substitute for correct application logic.
PostgreSQL supports ACID properties: principles that ensure transactions are executed consistently and durably. Atomicity means performing all the operations or none of them; consistency means preserving the declared data rules; isolation means controlling the effects of concurrent transactions; and durability means retaining committed changes in accordance with the guarantees of the system configuration.
Data integrity is also supported by constraints defined in the database structure. You can enforce unique email addresses, prohibit empty product names, or use a foreign key: a rule requiring an order to refer to an existing customer. These safeguards work independently of the forms on the website.
What is PostgreSQL used for?
PostgreSQL is useful wherever data has a defined structure, is interconnected and needs to be searched in different ways. Typical uses include:
- Stores and booking systems: products, availability, orders and change history.
- Business applications: customer contacts, documents, permissions and service processes.
- Web services: user accounts, subscriptions and organisation settings.
- Reporting: combining data from multiple tables and calculating summaries.
Besides ordinary text or numeric columns, PostgreSQL supports JSONB, among other types — a format for storing JSON documents that allows their contents to be searched and indexed. It is useful, for example, for storing additional product attributes that differ between categories. This does not mean, however, that the entire database should be built as a collection of documents without a defined structure.
Extensions expand the system’s capabilities. One example is PostGIS, which enables work with spatial data, such as finding points within a specified distance of the user’s location.
PostgreSQL, MySQL or SQLite — which should you choose?
The choice depends primarily on the application’s requirements, not on how popular the name is. If an off-the-shelf system requires a particular database, check its official documentation first. Switching database engines is not always possible without changes to the software.
| System | When to consider it |
|---|---|
| PostgreSQL | The application needs complex queries, data integrity rules or extensions such as PostGIS. |
| MySQL | The chosen application requires it, or the team has an established environment and experience maintaining it. |
| SQLite | Data needs to be stored locally, without a separate server, for example in a tool or a small application. |
MySQL also supports transactions in the widely used InnoDB engine, and SQLite provides transaction support too. They are therefore not inherently “less secure” databases. Differences include architecture, concurrent writes, features and maintenance methods; there is no single winner in all performance comparisons.
How do you get started, and what needs maintaining?
For learning, a local PostgreSQL installation and a client — a tool for sending queries — are enough. This can be the command-line psql or the graphical pgAdmin. Create a test database with customer and order tables, link them with a foreign key, then practise reading data and rolling back a transaction.
For a production deployment, you need to take care of updates, permissions, backups and restore tests. The application’s access should be limited to the operations it needs, and the database port should not be publicly accessible without justification and appropriate safeguards. Indexes, structures that speed up searches, are chosen to suit actual queries — they also increase the cost of writes and take up space.
A managed service can take over some of these responsibilities. Before purchasing, check the provider’s documentation for the supported version, available extensions, connection limits, backup policies and restore costs. The first step in choosing a database for a particular project should be to confirm compatibility with the application and establish who will be responsible for maintaining it.
Read also
