What Is PostgreSQL? Features, Use Cases and How It Compares with SQL Server

PostgreSQL is an open source, enterprise-grade relational database management system with no license fees. It is developed by an independent community rather than a single company, and with reliable transaction handling, JSON support, an extension architecture and built-in replication it is one of the most widely used databases in the world. As of November 2026 the current version is PostgreSQL 18.

This guide is for IT managers, architects and software teams evaluating PostgreSQL for the first time. We summarize what PostgreSQL is, which workloads it suits, what it really costs and how it differs from SQL Server. We also show how case conversion and sorting behave with the well-known Turkish “i” example. We tested the examples on PostgreSQL 18.6.

PostgreSQL at a Glance

What Is PostgreSQL?

PostgreSQL (Postgres for short) is a relational database that stores data in tables and is queried with SQL. It is called “object-relational” because you can add your own data types, functions and index types on top of the classic relational model. Its SQL standard compliance is high: PostgreSQL 18 supports at least 170 of the 177 mandatory features required for SQL:2023 Core conformance.

Three things set PostgreSQL apart from commercial databases:

Versions and Support Schedule

The community releases a new major version every year and supports each one for five years. Security and bug fixes come in minor releases published at least every three months. As of early November 2026 these versions are supported:

PostgreSQL 13 and earlier are no longer supported. The first release candidate (RC1) of PostgreSQL 19 came out on October 15, 2026; general availability is planned for November 19, 2026. You can see which version your server runs with this query:

SELECT version();
-- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

A Short History

PostgreSQL traces back to the POSTGRES project, started in 1986 at the University of California, Berkeley, under Prof. Michael Stonebraker. In 1994 SQL support was added and the code was released as open source under the name Postgres95. Renamed PostgreSQL in 1996, the project is today one of the most mature open source databases, with close to 40 years of development behind it.

How Does PostgreSQL Work?

Four core concepts are enough to start running PostgreSQL correctly in production.

A field note on connection management: one of the common root causes of performance problems is idle connections that the application opens and never closes. PostgreSQL’s process model makes this problem more visible.

MVCC and VACUUM in an example

PostgreSQL does not change a row in place during an UPDATE; it writes a new version. Once no transaction can see the old version any more, it becomes a “dead row” and VACUUM cleans it up. In the example below, updating 1,000 rows leaves 1,000 dead rows:

CREATE TABLE stock (id int PRIMARY KEY, qty int) WITH (autovacuum_enabled = off);
INSERT INTO stock SELECT g, 10 FROM generate_series(1, 1000) g;

SELECT ctid, xmin, qty FROM stock WHERE id = 1;
-- ctid (0,1)  | xmin 778 | qty 10

UPDATE stock SET qty = qty - 1;

SELECT ctid, xmin, qty FROM stock WHERE id = 1;
-- ctid (4,97) | xmin 779 | qty 9   -- new row version, new physical location

SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'stock';
-- 1000 | 1000

VACUUM stock;
SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'stock';
-- 1000 | 0

We turned autovacuum off in the example only to make the effect visible. In real systems the autovacuum process does this work in the background, and it should not be turned off. VACUUM also updates the query planner’s statistics and prevents transaction ID wraparound. Watching autovacuum behavior on heavily updated tables is one of the core tasks of running PostgreSQL.

Key Features

The main new features of PostgreSQL 18 include an asynchronous I/O subsystem, the uuidv7() function for time-ordered UUIDs, virtual generated columns as the default and a pg_upgrade that keeps statistics during upgrades.

Example: relational and flexible data together with JSONB

Storing order details as a JSON document and querying it through a GIN index is one of the most popular ways to use PostgreSQL:

CREATE TABLE orders (
    id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO orders (doc) VALUES
  ('{"customer": "Acme", "city": "İstanbul", "status": "new",     "items": [{"sku": "A1", "qty": 2}]}'),
  ('{"customer": "Beta", "city": "Ankara",   "status": "shipped", "items": [{"sku": "B7", "qty": 1}]}'),
  ('{"customer": "Gama", "city": "İzmir",    "status": "new",     "items": [{"sku": "A1", "qty": 5}]}');

CREATE INDEX orders_doc_gin ON orders USING gin (doc);

SELECT id, doc->>'customer' AS customer, doc->>'city' AS city
FROM orders
WHERE doc @> '{"status": "new"}';
--  id | customer | city
--   1 | Acme     | İstanbul
--   3 | Gama     | İzmir

You can report on the same data with a CTE. This query sums item quantities in new orders by city:

WITH new_items AS (
    SELECT doc->>'city' AS city, (item->>'qty')::int AS qty
    FROM orders, jsonb_array_elements(doc->'items') AS item
    WHERE doc->>'status' = 'new'
)
SELECT city, sum(qty) AS total_qty
FROM new_items
GROUP BY city
ORDER BY total_qty DESC;
--  city     | total_qty
--  İzmir    | 5
--  İstanbul | 2

Example: identity columns

Auto-incrementing keys use the SQL-standard identity columns. GENERATED ALWAYS prevents manual values; when you need one on purpose, for example during a data migration, OVERRIDING SYSTEM VALUE overrides the rule:

INSERT INTO orders (id, doc) VALUES (100, '{"customer": "Delta"}');
-- ERROR:  cannot insert a non-DEFAULT value into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

INSERT INTO orders (id, doc) OVERRIDING SYSTEM VALUE VALUES (100, '{"customer": "Delta"}');
-- INSERT 0 1

Example: full-text search with stemming

Text search configurations reduce words to their stems, so a search for one form of a word also finds the others. With the turkish configuration, a search for “yedek” (backup) also matches “yedeklerini” (their backups):

SELECT to_tsvector('turkish', 'Veritabanı yedeklerini düzenli olarak test ediyoruz');
-- 'düzenli':3 'ediyor':6 'olarak':4 'test':5 'veritaba':1 'yedek':2

SELECT to_tsvector('turkish', 'Veritabanı yedeklerini düzenli olarak test ediyoruz')
       @@ websearch_to_tsquery('turkish', 'yedek test') AS match;
-- t

Collations in Practice: The Turkish I Problem

Collations decide how text is compared, sorted and converted between upper and lower case. The Turkish alphabet is the classic test case: the upper case of “i” is “İ” and the upper case of “ı” is “I”, while in English locales the upper case of “i” is “I”. In PostgreSQL, lower(), upper(), ILIKE and sorting all follow the rules of the collation in use.

The example below ran in a database whose default locale is en_US.utf8, and compares the results with the ICU-based Turkish collation (tr-TR-x-icu):

CREATE TABLE cities (name text);
INSERT INTO cities VALUES ('İSTANBUL'), ('istanbul'), ('Istanbul'), ('IĞDIR'), ('ığdır');

SELECT name,
       lower(name)                       AS lower_default,
       upper(name)                       AS upper_default,
       lower(name COLLATE "tr-TR-x-icu") AS lower_tr,
       upper(name COLLATE "tr-TR-x-icu") AS upper_tr
FROM cities;
--  name     | lower_default | upper_default | lower_tr | upper_tr
--  İSTANBUL | istanbul      | İSTANBUL      | istanbul | İSTANBUL
--  istanbul | istanbul      | ISTANBUL      | istanbul | İSTANBUL
--  Istanbul | istanbul      | ISTANBUL      | ıstanbul | ISTANBUL
--  IĞDIR    | iğdir         | IĞDIR         | ığdır    | IĞDIR
--  ığdır    | ığdır         | IĞDIR         | ığdır    | IĞDIR

In summary:

You can see the difference in search and sorting yourself with these queries:

SELECT string_agg(name, ', ' ORDER BY name) AS ilike_default
FROM cities WHERE name ILIKE 'istanbul';
-- istanbul, Istanbul, İSTANBUL

SELECT string_agg(name, ', ' ORDER BY name) AS ilike_tr
FROM cities WHERE name COLLATE "tr-TR-x-icu" ILIKE 'istanbul';
-- istanbul, İSTANBUL

SELECT string_agg(w, ' ' ORDER BY w)                       AS sort_default,
       string_agg(w, ' ' ORDER BY w COLLATE "tr-TR-x-icu") AS sort_tr
FROM (VALUES ('ilik'), ('ılık'), ('cam'), ('çam'), ('oda'), ('öykü')) AS v(w);
-- sort_default: cam çam ilik ılık oda öykü
-- sort_tr:      cam çam ılık ilik oda öykü

For case-insensitive comparison, instead of calling lower() in every query you can define a nondeterministic ICU collation:

CREATE COLLATION tr_ci (provider = icu, locale = 'tr-TR-u-ks-level2', deterministic = false);

SELECT string_agg(name, ', ' ORDER BY name) FROM cities WHERE name = 'istanbul' COLLATE tr_ci;
-- istanbul, İSTANBUL

SELECT string_agg(name, ', ' ORDER BY name) FROM cities WHERE name = 'ığdır' COLLATE tr_ci;
-- IĞDIR, ığdır

Nondeterministic collations have a performance cost; they work with = and LIKE but not with ILIKE or regular expressions.

Is PostgreSQL Free? The Real Cost

In terms of licensing, yes. You can use PostgreSQL for any purpose, including commercial projects, at no cost; there are no per-core or per-user license fees and no features restricted by edition. This changes the cost calculation significantly, especially in environments that need many servers or high core counts.

Total cost of ownership is not zero, though. For PostgreSQL running in production, budget for these items:

Once the license fee disappears, the weight of the budget shifts to expertise and operations. That is why the PostgreSQL decision should be made together with the question of who will run it. If you would like a quote for PostgreSQL consulting, migration or managed database services, contact us.

PostgreSQL vs SQL Server

Both are mature, enterprise-grade relational databases. The choice between them usually depends less on technical superiority than on licensing, ecosystem and application requirements.

Case sensitivity is the topic that causes the most surprises in migration projects. A query that works fine on SQL Server can return different results on PostgreSQL, and quoted versus unquoted object names are a common source of errors:

SELECT 'Ankara' = 'ankara' AS equal;
-- f

CREATE TABLE Musteri (id int);      -- unquoted: stored as musteri
CREATE TABLE "Siparis" (id int);    -- quoted: stored as Siparis

SELECT count(*) FROM MUSTERI;       -- works
SELECT count(*) FROM Siparis;
-- ERROR:  relation "siparis" does not exist

When to Choose PostgreSQL and When to Stay on SQL Server

For applications you develop yourself, moving from SQL Server or Oracle to PostgreSQL is a realistic option. Most of the work is not moving data; converting stored procedures, triggers and the SQL inside the application, collation differences and testing make up the real effort. Our database migration service starts this process with an inventory and a risk analysis.

Getting Started with PostgreSQL

1. Choose the version. Start new projects on PostgreSQL 18, supported until November 2030.
2. Choose the installation method. The postgresql.org/download page lists the official package repositories for Linux and installers for Windows and macOS. Our PostgreSQL installation guide walks through each option.
3. Decide on the locale. If you will store language-specific text, choose the locale and collation before installation.
4. Connect. Connect with the command-line client psql or the graphical pgAdmin and create your first database.
5. Follow the official tutorial. The PostgreSQL tutorial goes step by step from installation to your first queries.

Production Checklist

Installing PostgreSQL on a developer laptop takes minutes; running it safely in production takes discipline. Your checklist should cover at least these items. For the full list, see our PostgreSQL best practices guide.

When Should You Bring in an Expert?

If any of the following applies to you, working with an experienced PostgreSQL team saves time and reduces risk:

• You are taking PostgreSQL to production for the first time and want backup, high availability and monitoring designed right from the start.
• You are evaluating a move from SQL Server or Oracle to PostgreSQL.
• Queries have slowed down, tables have bloated or autovacuum cannot keep up.
• You run a version whose support has ended or is about to end.
• There is no senior database administrator (DBA) in-house who can be on call 24/7.

Aryasoft offers every service it provides for SQL Server for PostgreSQL as well, with senior experts: consulting, architecture and installation, performance tuning, health checks, version upgrades, migrations from SQL Server and Oracle to PostgreSQL, high availability and disaster recovery (HA/DR), and PostgreSQL on Azure and AWS. For ongoing support, our remote DBA model offers 8×5 or 24/7 maintenance-support and full remote DBA SLA options; see our managed database services page for details. We manage your environment, not your data: no customer data is kept on Aryasoft’s side.

Frequently Asked Questions

Sources

The technical details in this guide were checked against the official sources below in October and November 2026. Version and support dates can change; check the linked pages before you decide.

1. PostgreSQL: About
2. PostgreSQL License
3. PostgreSQL Versioning Policy
4. A Brief History of PostgreSQL
5. Architectural Fundamentals
6. Resource Consumption (shared_buffers)
7. Write-Ahead Logging (WAL)
8. Concurrency Control: Introduction (MVCC)
9. Routine Vacuuming
10. JSON Types
11. Identity Columns
12. Collation Support
13. Locale Support
14. Pattern Matching (LIKE, ILIKE)
15. Lexical Structure (identifiers)
16. Log-Shipping Standby Servers (streaming replication)
17. Failover
18. Logical Replication
19. Backup and Restore
20. pg_upgrade
21. PostgreSQL 18 Release Notes
22. PostGIS and pgvector
23. Patroni
24. Microsoft Learn: Editions and supported features of SQL Server 2025
25. Microsoft Learn: What is SQL Server on Linux?
26. Microsoft Learn: What is an Always On availability group?
27. Microsoft Learn: JSON data type
28. Microsoft Learn: Collation and Unicode support
29. Microsoft Learn: IDENTITY property and CREATE SEQUENCE