PostgreSQL Basics
History
The name PostgreSQL originates from an earlier database project called Ingres (INteractive Graphics and REtrieval System), which was developed at the University of California, Berkeley, in the 1970s and 1980s. Michael Stonebraker, one of the key figures behind Ingres, started a new project to experiment with new ideas in database management, and this new project was called "Post-Ingres" or "Postgres," indicating it was a successor or follow-up to Ingres.
In the early 1990s, the development of Postgres continued, and it was updated with improvements that included support for SQL query language in place of the original language, POSTQUEL. To signal the changes and improvements, including the adoption of SQL, Postgres was renamed to Postgres95 in 1995.
The name was later changed to PostgreSQL to better reflect its support for SQL and to establish itself as a leading open-source, relational database management system. The SQL part of the name indicates its compliance and focus on the SQL standard.
Structure
One of the interesting things about PostgreSQL is it's structure.
I typically think of a schema as a type of metadata on a table that defines the columns and datatypes of that table. But in PostgreSQL a schema is essentially a namespace that contains database objects such as tables, views, indexes, etc. It allows you to organize these objects in a way that can help avoid name collisions and improve manageability.
Comparison Table of Major SQL Frameworks
| Feature | PostgreSQL | MySQL | SQLite | SQL Server | Oracle DB |
|---|---|---|---|---|---|
| Open Source | Yes | Yes (community) | Yes | No (Commercial) | No (Commercial) |
| ACID Compliance | Full | Full (with InnoDB) | Full | Full | Full |
| Standards Compliance | Very strong | Good | Good | Very strong | Very strong |
| Data Integrity | Advanced features (FK, triggers) | Basic (with FK) | Basic (no FK by default) | Advanced features (FK, triggers) | Advanced features (FK, triggers) |
| Concurrency | MVCC | MVCC (with InnoDB) | Serialized write | Lock-based concurrency | MVCC |
| JSON Support | Yes (JSONB) | Yes | Limited (stored as text) | Yes | Yes |
| Indexing | Advanced (GiST, GIN, BRIN) | Basic (B-tree) | Basic (B-tree) | Advanced (B-tree, columnstore) | Advanced (B-tree, bitmap) |
| Partitioning | Native | Native | None | Native | Native |
| Performance Tuning | Highly configurable | Configurable | Minimal | Extensive features | Extensive features |
| Community | Large, active | Large, active | Large, active | Enterprise-focused | Enterprise-focused |
| Licensing | PostgreSQL License | GPL (for community) | Public domain | Proprietary | Proprietary |
Comments
Recent Work
Basalt
basalt.softwareFree desktop AI Chat client, designed for developers and businesses. Unlocks advanced model settings only available in the API. Includes quality of life features like custom syntax highlighting.
BidBear
bidbear.ioBidbear is a report automation tool. It downloads Amazon Seller and Advertising reports, daily, to a private database. It then merges and formats the data into beautiful, on demand, exportable performance reports.