Overview
This lecture compares three major open-source relational database management systems—SQLite, MySQL, and PostgreSQL—focusing on their data types, strengths, weaknesses, and best-use cases.
Relational Database Fundamentals
- The relational data model stores data in tables made of rows (tuples) and columns (attributes).
- A database management system (DBMS) is software that interacts with and manages a database.
- Relational DBMSs (RDBMSs) use SQL (Structured Query Language) for data operations but may have their own SQL dialects.
- Data types and constraints (e.g., UNIQUE, NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK) govern data storage and integrity.
SQLite
- SQLite is a lightweight, serverless, self-contained, and file-based RDBMS.
- Supports storage classes: null, integer, real, text, blob.
- Advantages: small size, no server/configuration needed, high portability.
- Disadvantages: limited write concurrency, no user management, less security.
- Best for embedded/mobile apps, disk access replacement, or testing.
- Not suitable for large databases, high write volume, or networked/multi-user scenarios.
MySQL
- MySQL is a popular, feature-rich, client/server RDBMS known for speed and reliability.
- Supports detailed numeric, date/time, string, and set data types.
- Advantages: popular, easy to use, strong user management, security, replication, fast performance.
- Disadvantages: not fully SQL-compliant, some features are proprietary, slower community development.
- Best for web apps, distributed systems, expected scaling/growth.
- Not ideal when full SQL compliance or high read/write concurrency is required.
PostgreSQL
- PostgreSQL is an advanced, extensible, open-source, object-relational RDBMS.
- Supports many data types, including geometric, network, JSON, and text search types.
- Advantages: high SQL compliance, strong community support, extensibility, ACID-compliance, robust concurrency.
- Disadvantages: higher memory usage per connection, historically less popular, fewer third-party tools.
- Best for applications needing data integrity, complex queries, tool/language integration.
- Less ideal for simple, read-heavy setups or where speed and mature replication are top priorities.
Key Terms & Definitions
- RDBMS — Relational Database Management System, organizes data into tables.
- SQL — Structured Query Language, used for querying and managing relational data.
- ACID — Atomicity, Consistency, Isolation, Durability; transaction properties for data reliability.
- Concurrency — Ability to handle multiple operations at once.
- MVCC — Multi-Version Concurrency Control, improves concurrency without read locks.
Action Items / Next Steps
- Review the "How To Manage an SQL Database" cheat sheet for practical SQL usage.
- Explore NoSQL databases if non-relational options are of interest.