01 — PostgreSQL Origins: Why an Object-Relational Database

"Just another database, pick whichever" was how I filed PostgreSQL, and the label that matters got lost in the shrug. The idea everything else hangs off: PostgreSQL is an object-relational database management system (ORDBMS), and that "object-relational" label is load-bearing. It stores data in strict, typed relations like any RDBMS, but it extends that model with user-defined types, inheritance, and functions the way an object-oriented system would [1]. Once I separated "relational" from "object-relational," most of Postgres's odd quirks — JSON columns, custom types, extension ecosystem — stopped feeling like bolt-ons and started feeling like the point.

What a relational database is, stripped down

The relational model is older than I assumed. E.F. Codd proposed it in 1970: data lives in relations (tables), each a set of tuples (rows), each tuple a set of typed attributes (columns) [2]. The word "relational" does not mean "tables relate to each other." It means each table is itself a mathematical relation — a set of rows conforming to a schema. Foreign keys and joins are conveniences built on top of that, not the definition.

PostgreSQL follows this model faithfully and adds the object layer on top, which is what the next notes cover. The reason I had to nail this down is the constant comparison that comes next: why relational at all, and why Postgres in particular against the NoSQL wave.

The case for relational: ACID is not optional decoration

The benefit I kept underestimating is data integrity. A relational system enforces a schema at write time — a column declared INTEGER NOT NULL rejects anything that isn't an integer, period. Add foreign keys, unique constraints, and check constraints, and the database itself becomes the guarantor of correctness, not whatever application code happens to be writing at the time [3]. That guarantee is bundled into ACID: Atomicity (a transaction is all-or-nothing), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions don't see each other's half-finished work), Durability (committed data survives a crash) [4].

The cost is honest about itself. RDBMSs scale vertically well — bigger box, more RAM — and horizontally with real difficulty, because splitting a relational graph across machines breaks the joins and transactions that make it valuable. Schema changes are coordinated migrations, not silent drift [3]. For unstructured, very-high-velocity, globally-distributed data, NoSQL's trade of consistency for scale is the right call. For most application data — users, orders, accounts, content — the integrity guarantee earns its keep.

PostgreSQL vs NoSQL, and the JSON escape hatch

The comparison that used to paralyze me was Postgres vs MongoDB. The framing that resolved it: Postgres optimizes for correctness and complex relationships; NoSQL optimizes for scale and schema flexibility [5]. MongoDB stores flexible JSON-like documents and shards horizontally by default; Postgres stores typed rows and shines on complex queries, joins, and transactions.

What dissolved the false dichotomy for me is that PostgreSQL has first-class JSON and JSONB types. A column can hold semi-structured documents, be indexed, and be queried with JSON-path operators — so the "I need flexible documents" use case doesn't automatically force a NoSQL choice [1][5]. The decision becomes: do I need horizontal scale and schema drift that badly, or is JSONB inside a transactional, typed database enough? For my work, it almost always is.

PostgreSQL vs the other RDBMSs

Among relational systems, Postgres's differentiators are its open-source license, its extensibility, and its feature depth [6]:

• vs Oracle / SQL Server — Postgres is free and open-source, and it lets users define custom data types, operators, and functions. The proprietary systems are polished and supported, but Postgres matches or exceeds them on functionality.
• vs MySQL — MySQL is fast and popular for read-heavy, simple workloads; Postgres prioritizes feature richness and strict ACID compliance. Complex queries, window functions, advanced indexing, full-text search, and GIS via PostGIS all lean Postgres [6].

The extensibility is the part I now treat as the headline feature. PostGIS for geo data, pg_stat_statements for query stats, custom types for domain logic — the database is a platform, not a fixed product [1].

How I use this

The practical payoff is a decision check. When I reach for a database, I ask two questions: does the data have natural relationships and integrity rules I want enforced, and do I need complex queries or transactions. If yes, Postgres is the default and I reach for JSONB columns when a slice of the data is genuinely semi-structured. If the entire workload is unstructured, globally scaled, and consistency-tolerant, NoSQL earns its place. I no longer default to whichever database the framework tutorial happened to use — the model, not the marketing, picks.

References

[1] PostgreSQL Global Development Group, "PostgreSQL Documentation," 2024. [Online]. Available: https://www.postgresql.org/docs/

[2] IBM, "What is a relational database?," 2024. [Online]. Available: https://www.ibm.com/cloud/learn/relational-databases

[3] Internshala, "15 Advantages and Disadvantages of RDBMS," 2023. [Online]. Available: https://trainings.internshala.com/blog/advantages-and-disadvantages-of-rdbms/

[4] Fauna, "What is ACID compliance? Atomicity, Consistency, Isolation," 2023. [Online]. Available: https://fauna.com/blog/what-is-acid-compliance-atomicity-consistency-isolation

[5] AWS, "What's the difference between MongoDB and PostgreSQL?," 2024. [Online]. Available: https://aws.amazon.com/compare/the-difference-between-mongodb-and-postgresql/

[6] Integrate.io, "PostgreSQL vs MySQL: The critical differences," 2023. [Online]. Available: https://www.integrate.io/blog/postgresql-vs-mysql-which-one-is-better-for-your-use-case/