What Is PostgreSQL?
What PostgreSQL Is and Where It Comes From
What is PostgreSQL? PostgreSQL, often called Postgres, is an open-source relational database that uses SQL and is known for standards compliance, data integrity and extensibility.
The project grew out of the POSTGRES research system developed at the University of California, Berkeley, and has been maintained by a global community for decades. It is released under the PostgreSQL License, a permissive license that allows free use, modification and distribution, including in commercial products.
Like other relational databases, PostgreSQL stores data in tables with defined types and relationships, and it enforces rules through constraints and transactions. A direct comparison with MySQL is in MySQL vs PostgreSQL.
Key Features That Set PostgreSQL Apart
PostgreSQL's strengths come from its focus on correctness and extensibility. Some of its best-known features are:
- Standards compliance: PostgreSQL follows the SQL standard closely and supports many of its advanced features.
- Rich data types: besides numbers and text, it offers arrays, ranges, UUIDs and custom types defined by users.
- JSONB: a binary format for JSON data that can be indexed and queried, useful when records have flexible fields.
- Extensions: add-ons such as PostGIS add geographic data types and queries, and many more extend the core engine.
- Concurrency: multi-version concurrency control (MVCC) lets readers and writers work at the same time, and in most cases they do not block each other.
An Example: Store Locator With Geographic Data
Imagine a website that shows the nearest store to each visitor. Each store has an address and a pair of coordinates. With PostGIS installed, the database can store those coordinates as geographic data and answer questions such as which stores lie within five kilometers of a point.
The same database can hold product records with varying attributes in JSONB columns, while keeping orders and customers in ordinary relational tables. This mix of structured and flexible data is one reason PostgreSQL appears in analytics, mapping and software-as-a-service products.
Frequently asked questions
Is PostgreSQL free to use?
Yes. PostgreSQL is released under a permissive open-source license, so you can use, modify and distribute it without license fees. Managed hosting services that run PostgreSQL for you may charge for their infrastructure, and paid support is available from third parties. Costs of that kind are covered in managed databases.
What is JSONB in PostgreSQL?
JSONB is a binary format for storing JSON documents in a column. PostgreSQL parses the JSON when it is saved, which makes queries on it faster than on plain JSON text, and it supports indexes on the fields inside. Use it for data that varies from record to record, and keep stable fields in regular columns.