Free lesson · System Design Advanced

Postgres Origin and Design Philosophy

Hard9 min readsystem-designadvanced

Every technology carries the DNA of the problem it was born to solve. MySQL was born at a Swedish consulting company that needed a fast, simple database for web applications. MongoDB was born from the frustration of mapping objects to relational tables. Redis was born because a developer in Italy needed a fast real-time analytics system.

PostgreSQL was born at a university. And that origin story explains everything about why it is the way it is - why it's more correct than fast by default, why it has features nobody asked for until they needed them, and why it's the database that keeps getting recommended for problems it wasn't originally designed for.

The Berkeley Origin

graph TD
    subgraph "PostgreSQL Lineage"
        INGRES["INGRES (1974-1985)<br/>──────────────<br/>UC Berkeley<br/>Michael Stonebraker<br/><br/>One of the first relational<br/>database systems. Proved that<br/>relational was viable.<br/><br/>Spawned Sybase and Informix<br/>(which became SQL Server<br/>and IBM DB2 paths)"]
        
        POSTGRES_OG["POSTGRES (1986-1994)<br/>──────────────<br/>UC Berkeley<br/>Stonebraker again<br/><br/>'Post-INGRES' - designed to<br/>fix INGRES's limitations:<br/>• Extensible type system<br/>• Complex objects<br/>• Rule system<br/>• Better transactions<br/><br/>Academic project, not commercial"]
        
        POSTGRES95["Postgres95 (1994-1996)<br/>──────────────<br/>Andrew Yu & Jolly Chen<br/>(Berkeley grad students)<br/><br/>Added SQL support<br/>(original POSTGRES used QUEL)<br/>Released as open source"]
        
        POSTGRESQL["PostgreSQL (1996-present)<br/>──────────────<br/>Community-driven development<br/>Name changed to reflect<br/>SQL compliance<br/><br/>Became the 'most advanced<br/>open-source database'<br/><br/>30+ years of continuous<br/>development by a global<br/>community"]
        
        INGRES --> POSTGRES_OG --> POSTGRES95 --> POSTGRESQL
    end

This diagram shows PostgreSQL's lineage. INGRES was created at UC Berkeley in 1974 by Michael Stonebraker, proving relational databases were viable. In 1986, Stonebraker started POSTGRES, meaning Post-INGRES, to fix its limitations with an extensible type system and better transactions. In 1994, Berkeley grad students added SQL support and released it as open source as Postgres95. In 1996, it became PostgreSQL, maintained by a global community with over 30 years of continuous development.

The Three Design Principles

Principle 1: Correctness Over Speed

PostgreSQL was built by academics who cared about getting things right, not getting things fast. This shows up everywhere:

graph TD
    subgraph "Correctness-First Design Decisions"
        ACID["Full ACID by Default<br/>──────────────────<br/>Every transaction is fully<br/>serialisable by default<br/>(you can relax this, but<br/>the default is strictest)<br/><br/>MySQL's default was NOT<br/>fully ACID until InnoDB<br/>replaced MyISAM"]
        
        TYPES["Strict Type System<br/>──────────────────<br/>Won't silently convert<br/>'123abc' to 123<br/><br/>Won't truncate a string<br/>that's too long for the column<br/>(it errors instead)<br/><br/>MySQL historically truncated<br/>and warned instead of erroring"]
        
        SQL_COMPLIANCE["Full SQL Compliance<br/>──────────────────<br/>Most complete SQL standard<br/>implementation of any database<br/><br/>Window functions, CTEs, LATERAL<br/>joins, GROUPING SETS, recursive<br/>queries - all correct per spec<br/><br/>Other databases implement<br/>SQL subsets or extensions<br/>that deviate from the standard"]
        
        NULL_HANDLING["Correct NULL Handling<br/>──────────────────<br/>NULL != NULL (correct)<br/>NULL is not false (correct)<br/>Aggregate functions skip<br/>NULLs (correct per spec)<br/><br/>These seem obvious but<br/>many databases get them<br/>subtly wrong"]
    end

This diagram shows PostgreSQL's correctness-first design decisions. Full ACID transactions are the default with the strictest isolation level. The strict type system won't silently convert bad data or truncate strings - it errors instead. SQL compliance is the most complete of any database, including window functions, CTEs, lateral joins, and recursive queries. NULL handling follows the SQL standard precisely. These choices prioritise correctness over convenience.

Important: Correctness-first is a trade-off, not a free lunch. PostgreSQL's strict type system means your application can't insert sloppy data - which catches bugs but also means migration scripts that work on MySQL may fail on Postgres. Full ACID by default means stronger guarantees but higher overhead per transaction than databases that default to weaker isolation. The design philosophy is: it's better to be correct and let you opt into speed than to be fast and let you opt into correctness.

Principle 2: Extensibility

This is Postgres's superpower. Most databases are closed systems - you get the features the vendor ships. PostgreSQL is designed to be extended by users:

graph TD
    subgraph "PostgreSQL Extension Architecture"
        CORE["PostgreSQL Core<br/>──────────────<br/>Relational engine<br/>SQL parser/planner/executor<br/>MVCC, WAL, replication<br/>Buffer management"]
        
        CORE --> TYPES_EXT["Custom Types<br/>──────────<br/>Define entirely new data types<br/>with custom operators,<br/>indexes, and casts<br/><br/>PostGIS added the<br/>GEOMETRY type this way"]
        
        CORE --> INDEX_EXT["Custom Index Types<br/>──────────<br/>GiST, GIN, SP-GiST, BRIN,<br/>and user-defined index types<br/><br/>pgvector added the HNSW and<br/>IVFFlat index types for<br/>vector similarity search"]
        
        CORE --> LANG_EXT["Custom Languages<br/>──────────<br/>Write stored procedures in:<br/>PL/pgSQL, PL/Python,<br/>PL/Perl, PL/Rust, PL/Java<br/><br/>The language runtime is<br/>itself an extension"]
        
        CORE --> FDW_EXT["Foreign Data Wrappers<br/>──────────<br/>Query external systems<br/>AS IF they were Postgres tables<br/><br/>postgres_fdw: query other Postgres<br/>mysql_fdw: query MySQL<br/>mongo_fdw: query MongoDB<br/>file_fdw: query CSV files"]
        
        CORE --> FULL_EXT["Full Extensions<br/>──────────<br/>PostGIS: geospatial<br/>TimescaleDB: time-series<br/>pgvector: vector/AI embeddings<br/>Citus: distributed/sharding<br/>pg_cron: scheduled jobs<br/>pg_stat_statements: query stats<br/><br/>These are NOT forks.<br/>They run INSIDE Postgres."]
    end

This diagram shows PostgreSQL's extension architecture. The core provides the relational engine, SQL processing, MVCC, WAL, and replication. Extensions plug into this core in multiple ways: custom data types like PostGIS's geometry type, custom index types like pgvector's HNSW for vector search, custom languages for stored procedures including Python and Rust, foreign data wrappers that query external systems like MySQL or MongoDB as if they were Postgres tables, and full extensions like TimescaleDB for time-series and Citus for distributed sharding. These all run inside Postgres, not as separate forks.

Tip: In a system design interview, Postgres's extensibility is a legitimate architectural argument. When asked "would you use a separate vector database for embeddings or keep them in your primary database?" you can answer: "For our scale, pgvector inside our existing Postgres gives us vector similarity search without adding a new operational dependency. The trade-off: a dedicated vector database like Pinecone or Milvus will outperform pgvector at very large scale (100M+ vectors), but for our 10M vectors, pgvector with HNSW indexes is sufficient and dramatically simpler to operate."

Principle 3: Process-Per-Connection

Unlike MySQL (thread-per-connection) or MongoDB (thread pool), PostgreSQL forks a new OS process for each client connection:

graph TD
    subgraph "PostgreSQL Process Model"
        PM["Postmaster Process<br/>──────────────<br/>Listens on port 5432<br/>Accepts new connections<br/>Forks a backend process<br/>for each client"]
        
        PM -->|"fork()"| B1["Backend Process 1<br/>──────────────<br/>Handles Client 1's<br/>queries exclusively<br/>Has own memory context<br/>Crashes don't affect<br/>other backends"]
        
        PM -->|"fork()"| B2["Backend Process 2<br/>──────────────<br/>Handles Client 2's<br/>queries exclusively"]
        
        PM -->|"fork()"| B3["Backend Process 3<br/>──────────────<br/>Handles Client 3's<br/>queries exclusively"]
        
        PM --> BG1["Background Writer<br/>──────────────<br/>Writes dirty buffers<br/>to disk periodically"]
        
        PM --> BG2["WAL Writer<br/>──────────────<br/>Flushes WAL buffer<br/>to disk"]
        
        PM --> BG3["Autovacuum Launcher<br/>──────────────<br/>Manages vacuum workers"]
        
        PM --> BG4["Checkpointer<br/>──────────────<br/>Periodic checkpoint<br/>to ensure recovery point"]
    end

This diagram shows PostgreSQL's process model. The postmaster process listens for connections and forks a new backend process for each client. Each backend handles one client's queries exclusively with its own memory context - a crash in one backend doesn't affect others. The postmaster also manages background processes: the background writer for dirty buffers, the WAL writer for write-ahead log flushing, the autovacuum launcher for dead tuple cleanup, and the checkpointer for ensuring recovery points.

Warning: PostgreSQL without a connection pooler is a time bomb at scale. Each connection is an OS process consuming 5-10 MB of RAM. 1,000 connections = 5-10 GB of RAM just for connection overhead. A microservices architecture with 50 services, each opening 20 connections, hits 1,000 connections easily. The standard fix: PgBouncer (external connection pooler) or Pgpool-II. PgBouncer in transaction mode is the most common production pattern - it multiplexes hundreds of application connections across a smaller pool of actual Postgres connections.

Why Postgres Keeps Winning

graph TD
    subgraph "The Postgres Flywheel"
        CORRECT["Correctness builds trust<br/>──────────────<br/>'My data is safe in Postgres'<br/>becomes the default assumption"]
        
        CORRECT --> ADOPT["Adoption grows<br/>──────────────<br/>Startups default to Postgres<br/>Enterprises migrate to Postgres<br/>Cloud providers build managed<br/>Postgres (RDS, Cloud SQL,<br/>AlloyDB, Aurora)"]
        
        ADOPT --> EXTEND["Extensions multiply<br/>──────────────<br/>Community builds extensions<br/>because the user base is large<br/><br/>PostGIS, TimescaleDB, pgvector,<br/>Citus, pg_partman, pg_repack..."]
        
        EXTEND --> MORE_USE["More use cases are addressed<br/>──────────────<br/>Need time-series? Postgres.<br/>Need geospatial? Postgres.<br/>Need vectors? Postgres.<br/>Need graph? Postgres (AGE).<br/><br/>Each extension makes Postgres<br/>viable for more workloads"]
        
        MORE_USE --> CORRECT
    end

This diagram shows the PostgreSQL flywheel. Correctness builds trust - people believe their data is safe. This drives adoption by startups, enterprises, and cloud providers who build managed Postgres services. Large adoption motivates the community to build extensions like PostGIS, TimescaleDB, and pgvector. Each extension makes Postgres viable for more workloads - time-series, geospatial, vector search, graph. More use cases reinforce the trust, completing the flywheel.

The Production War Story

At Amazon, we inherited a system that used both PostgreSQL for transactional data and MongoDB for "flexible schema" document storage. The MongoDB cluster held 800 GB of semi-structured product metadata - nested objects, arrays of variants, optional fields that differed by product category.

The MongoDB cluster was a constant operational burden. Schema-less sounds liberating until you have 200 engineers writing to the same collections with no schema enforcement - data quality degraded over two years until queries returned unpredictable field types. A field called price was sometimes a number, sometimes a string, sometimes an object with currency subfields.

We migrated the entire dataset to PostgreSQL using JSONB columns for the semi-structured parts. The schema became: a set of strongly-typed relational columns for the fields every product has (id, title, category, created_at) and a JSONB column for category-specific metadata. We added GIN indexes on the JSONB column for the query patterns we needed.

The result: one database instead of two, ACID transactions across both relational and document data, schema enforcement where we wanted it and flexibility where we needed it, and GIN-indexed JSONB queries that performed within 20% of MongoDB's query speed for our workload.

The lesson: PostgreSQL's extensibility means you often don't need a second database. JSONB gives you document storage. PostGIS gives you geospatial. pgvector gives you vector search. Every additional database you add to your architecture is an operational burden - a separate backup strategy, a separate monitoring setup, a separate failure mode, a separate expertise requirement. The honest question before adding any specialised database is: "can Postgres do this well enough with an extension?"

Six Takeaways

1. PostgreSQL was born at a university, and it shows. Correctness, SQL compliance, and extensibility over raw speed. The default is strict - you opt into relaxation, not into correctness.

2. Extensibility is Postgres's superpower. PostGIS, TimescaleDB, pgvector, Citus, and hundreds of other extensions run inside Postgres, not as forks. This means one database can serve multiple workload types.

3. Process-per-connection requires connection pooling at scale. Each connection is a 5-10 MB OS process. PgBouncer in transaction mode is the standard solution - multiplexing many application connections across fewer database connections.

4. Postgres is stricter than MySQL by design. It won't silently truncate data, convert types, or accept malformed input. This catches bugs but means migrations from other databases may surface data quality issues.

5. JSONB gives you document storage inside a relational database. With GIN indexes, JSONB queries perform well enough for most workloads, often eliminating the need for a separate document database.

6. The honest question before adding a specialised database: "can Postgres do this well enough?" Sometimes the answer is no - at 100M+ vectors, you might need Pinecone. But often the answer is yes, and "yes with Postgres" is architecturally simpler than "yes with a new operational dependency."

Exercises

Exercise: Starter: Your startup is choosing a primary database. (a) List three reasons to choose PostgreSQL over MySQL. List two reasons MySQL might be better. (b) Your application needs to store user profiles with a mix of required fields (name, email) and optional fields that vary by user type (employer, school, portfolio_url). Design the schema using both a pure relational approach and a relational+JSONB hybrid. Which is better for your use case? (c) You have 200 microservices, each opening 10 connections. How many backend processes does Postgres fork? How much RAM does this consume? Design the PgBouncer configuration to reduce this.

Exercise: Intermediate: Your e-commerce platform uses PostgreSQL for orders and a separate Elasticsearch cluster for product search. (a) Could you replace Elasticsearch with Postgres full-text search (tsvector/tsquery)? What would you gain and lose? (b) Your team also uses a separate Redis for caching and a separate TimescaleDB for metrics. Evaluate each: could Postgres replace it? At what scale would Postgres stop being sufficient? (c) Design the extension stack for a PostgreSQL deployment that handles: relational data, document data, full-text search, geospatial queries, and time-series metrics. List each extension and its role.

Exercise: Advanced: You're designing the database architecture for a platform that needs: OLTP transactions (orders, payments), document storage (product metadata), vector search (AI-powered recommendations), time-series (IoT sensor data), and geospatial queries (delivery routing). (a) Design two architectures: one using Postgres+extensions for everything, one using specialised databases for each workload. (b) Compare them on: operational complexity, consistency guarantees, query performance, team expertise requirements, and cost. (c) At what scale (data volume, query rate, team size) does the specialised-database architecture become worth the complexity? (d) Your company acquires a startup that uses MongoDB. Their 500 GB dataset needs to be integrated. Design the migration strategy: timeline, data mapping, dual-write period, and cutover plan.

Back to System Design Advanced