Skip to content

What Is PostgreSQL, How Does It Work, and When Should It Be Chosen?

PostgreSQL is an open-source, object-relational database management system that grew out of research begun at Berkeley in the 1970s. The article explains how it manages concurrent access, its data types, and the points where it diverges from MySQL.

Nurullah Okatan · Content Writer
· Updated: · 11 min read

What Is PostgreSQL, How Does It Work, and When Should It Be Chosen?

PostgreSQL is an open-source, object-relational database management system. It does not lock rows during reads; instead, it keeps multiple versions of each row to manage concurrent access. It stands out in projects with heavy concurrent writes, projects that keep JSON and relational data together, and projects that want schema changes to be reversible.

The definition of PostgreSQL and its history from Berkeley

According to TÜBİTAK BİLGEM YTE's article "What Is PostgreSQL?", PostgreSQL is an open-source, platform-independent object-relational database management system (ORDBMS). Its relational part consists of the tables, rows, and SQL queries you already know. The object part means that tables can inherit from other tables and that users can define their own data types and functions.

Differences Between PostgreSQL and Other Databases

The roots of the system go back to a university laboratory. According to the same source, at the University of California, Berkeley, Michael Stonebraker developed the Ingres research project based on the relational model. PostgreSQL's history begins in 1977. The first 20 years of this period were spent in academia, and the next 20 years in industry. New features were added through the work of many developers on Postgres95. The first open source release was published in 1997, and from that year on the system began to be used in industry. TÜBİTAK BİLGEM YTE cites PostgreSQL as one of the oldest open source software programs.

Platform independence means the database behaves the same way on Linux, Windows, and macOS. A developer may use Windows on a laptop while the production server runs Linux. Schema, query, and backup files move between the two environments unchanged. When the hosting provider changes, the database does not have to be rewritten; only the installation and configuration are repeated on the new server.

AI assistant for this topic

Türk Bilişim AI answers your questions instantly
Online
  1. Hi! Ask whatever you're curious about.

How does PostgreSQL manage concurrent access?

PostgreSQL manages concurrent access with MVCC (Multi-Version Concurrency Control). According to OneUptime's MVCC guide, the system does not lock rows during reads. It keeps multiple versions of each row and shows each operation only the versions it should see. As a result, a query reading a report does not hold up a transaction writing to the same table.

How are row versions stored?

When a row is updated, it is not changed in place. According to OneUptime, PostgreSQL creates a new version and marks the old one as invalid. Leapcell's article "Diving Deep into MVCC in PostgreSQL" describes the same behavior: when a row is modified or deleted, a new version (tuple) is written. The old version remains on disk until the transactions that need to see it have finished.

Isolation levels

According to DEV Community's MVCC guide, the default isolation level is Read Committed. According to Leapcell, at this level every statement in a transaction sees a current snapshot of the database. According to OneUptime, PostgreSQL accepts the four SQL level names, but the levels behave as follows:

  • Read Uncommitted behaves in practice like Read Committed; uncommitted data is not read.
  • Read Committed takes a new snapshot for each statement.
  • Repeatable Read keeps the first snapshot throughout the transaction.
  • Serializable makes transactions produce results as if they had run one after another; on a conflict, one of the transactions receives an error.

Why is VACUUM necessary?

Invalid versions are not deleted automatically. VACUUM makes the space occupied by these dead rows available for reuse. It has a second task as well. According to OneUptime, PostgreSQL uses 32-bit transaction IDs that wrap around. By freezing old row versions when the IDs wrap, VACUUM prevents data visibility from being corrupted.

Looking for Android App Development?

PostgreSQL's data types and schema change features

In addition to standard number and text types, PostgreSQL stores semi-structured data. The jsonb type stores a JSON document in parsed binary form rather than as text. This type supports searching document contents with a GIN index. Operators such as @>, ->, and ->> query specific fields of a document. In this way, in a product table, fixed columns can remain relational while variable attributes are kept in a single jsonb column. Most projects do not need to set up a separate document database.

According to AWS's comparison of PostgreSQL and MySQL, PostgreSQL also supports additional data types such as arrays and XML. An array type holds a record's tags or permitted roles in a single column without creating a separate junction table. XML support is used to store and query XML documents, such as e-invoices or XML from legacy enterprise systems, in the database.

On the schema change side, PostgreSQL supports transactional DDL. CREATE TABLE and ALTER TABLE commands can be placed in a BEGIN … COMMIT block. If one step in the block fails, all changes are rolled back, and the database is not left open with a half-finished schema. According to AWS, PostgreSQL is fully ACID-compliant in all configurations. When an order record and the stock deduction are made in the same transaction, either both are written or neither is.

You can find more detailed examples of how these features are reflected in application architecture in our custom software development articles.

Differences between PostgreSQL and MySQL

According to AWS's comparison, PostgreSQL is an object-relational system that offers more features than MySQL, which is a relational system. The main differences between the two appear in replication and isolation behavior.

PostgreSQL Use Cases and Example Scenarios
TopicPostgreSQLMySQL
ModelObject-relationalRelational
ReplicationWAL-based physical streaming replication and table-level logical replicationBinary log-based replication
Repeatable ReadNo lost update problemLost update problem exists
Schema changeTransactional DDL, rolled back on errorDDL commands usually close the transaction automatically
Additional data typesjsonb, array, XMLJSON

PostgreSQL's physical streaming replication copies the primary server's write-ahead log (WAL) to the standby server byte for byte. Logical replication, on the other hand, copies only selected tables. Sending only the sales tables to a reporting server is done this way.

According to the guide on DEV Community, PostgreSQL's Repeatable Read level has no lost update problem. The same problem appears in MySQL because MySQL implements this level differently. When two transactions update the same balance at the same time, PostgreSQL stops one of them with an error. In MySQL, however, an update can be silently lost.

PostgreSQL is a better fit for projects with heavy concurrent writes, such as finance, inventory, or reservations. Projects that perform complex reporting and require a flexible data structure also belong to this group. The web software services page lists the infrastructure options that can be chosen depending on the project.

Things to plan before moving a project to PostgreSQL

On the server side, three matters are settled before installation: disk space, backup layout, and replication needs. You also choose the PostgreSQL version, set the connection limit, and review the autovacuum settings for your workload. At this stage it is also decided whether the live database will run on the same machine as the application server or on a separate one.

Storage monitoring must be planned separately because of MVCC. According to the guide on DEV Community, Postgres stores every version with all of its column data. A row with 100 versions means 100 times the storage for that row. In frequently updated tables, the number of dead rows and the table size should be monitored regularly. If a table grows while the amount of data stays the same, it is a sign that VACUUM is not keeping up.

A dedicated web application uses PostgreSQL to bring together user accounts, orders, and records with variable fields in a single database. Türk Bilişim, the best AI Chatbot & Customer Assistants company in Turkey, designs the data model according to the application's needs in Custom Web App & SaaS projects. The firm has been working as a digital agency since 1998 and has been developing artificial intelligence solutions since 2022. Server setup and monitoring work is carried out under Web Hosting & Server Management. The firm also provides maintenance, updates, and support after launch. Additional services such as backup and security are listed under infrastructure and support services.

Looking for AI Strategy & Consulting?

Frequently asked questions

Is PostgreSQL paid?

No, PostgreSQL is open source software and has no license fee. The cost comes from the server the database runs on and the effort of managing it.

Are PostgreSQL and Postgres the same thing?

Yes, Postgres is the short name of the same system. The name comes from the research work at Berkeley and from Postgres95, which came before the open source release.

Is PostgreSQL too much for a small website?

Because it runs with few resources, it causes no problems on small sites. Even so, on a site with only a few pages that writes no data, the database choice will not be a decisive factor.

Can an existing MySQL database be moved to PostgreSQL?

Yes, it can be moved with schema and data conversion tools. Before the move, data type mappings and queries in the application that depend on the Repeatable Read level should be tested in a separate test environment.

Have a project?

Türk Bilişim · Free Quote

Get a free quote for your AI Chatbots & Customer Assistants project

Leave your number and we will get back to you right away. The call is free.

Read the privacy notice

Nurullah Okatan Content Writer

The editorial desk of a team delivering websites, e-commerce and software projects since 1998.

LinkedInInstagramFacebook

Share this article

Comments

Comments

No comments yet.

Write a comment

Your rating (optional)

Privacy notice

We will send a confirmation link to your email so that your comment can appear.

Türk Bilişim · Digital Transformation Advisor

Online
Hi! Tell us the project or idea on your mind and let’s begin together.
Ask anything, Türk Bilişim AI answers instantly.