What is PostgreSQL?
ACID, MVCC, and why Postgres is the world's most advanced open-source database
Skills taught: pg.what-is-postgresql, pg.acid, pg.mvcc, pg.use-cases
PostgreSQL vs SQLite vs MySQL
SQLite
✓ Zero configuration — just a file on disk
✓ Perfect for development, testing, mobile apps
✗ One writer at a time — bad for concurrent web apps
✗ Limited SQL: no stored procedures, limited ALTER TABLE
MySQL
✓ Fast reads, widely used in LAMP stacks
✗ Surprising behavior: silent truncation, non-strict mode defaults
✗ Weaker SQL standard compliance
PostgreSQL
✓ ACID-compliant — data is never silently wrong
✓ Full SQL standard compliance
✓ MVCC — readers and writers don't block each other
✓ Rich type system: UUID, JSON, arrays, enums, ranges
✓ Extensible: PostGIS for geography, pgvector for ML embeddings
✓ Horizontal read scaling with logical replication
✗ More configuration needed than SQLite
ACID — what it guarantees
- Atomicity — A transaction is all-or-nothing. If you transfer $100 from account A to B, both the debit and the credit succeed, or neither does. Partial failures leave no trace.
- Consistency — Every transaction takes the database from one valid state to another. Constraints (foreign keys, CHECK, NOT NULL) are enforced — corrupt data cannot be written.
- Isolation — Concurrent transactions behave as if they ran sequentially. One transaction's in-progress changes are invisible to others until committed.
- Durability — Once a transaction commits, it is permanent. Even if the server crashes immediately after COMMIT, the data is safe on disk thanks to the WAL.
Tip: ACID is not just a buzzword. It is the difference between a database you can trust with money, health records, and user data versus one where you spend weekends investigating why numbers don't add up.
MVCC — why reads don't block writes
Time →
Transaction A (reads users table):
BEGIN;
SELECT * FROM users; -- sees 1000 rows, snapshot at T1
... (takes 5 seconds)
SELECT * FROM users; -- still sees 1000 rows (same snapshot)
COMMIT;
Transaction B (inserts, runs concurrently with A):
BEGIN;
INSERT INTO users VALUES (...);
COMMIT; -- 1001 rows in DB now
Transaction C (starts after B commits):
SELECT COUNT(*) FROM users; -- sees 1001 rows
→ A sees a consistent snapshot from T1
→ B's write doesn't block A's read
→ C sees B's committed write
Frequently Asked Questions
Is PostgreSQL free?
Yes. PostgreSQL is open-source under the PostgreSQL License, which is similar to BSD/MIT — you can use it in commercial products for free. Managed hosting (AWS RDS, Supabase, Neon) costs money but that is the hosting, not the database software.
Should I use PostgreSQL or MySQL?
PostgreSQL for anything new. It has stricter SQL standard compliance (no silent data truncation), better types, MVCC concurrency, and a more active development community for modern features. MySQL's main advantage is that some legacy tools or hosting providers only support MySQL. For greenfield projects, choose PostgreSQL.