About PostgreSQL Basics

Why Postgres, first queries, unique features, beginner mistakes. Part of the DevTools Surf developer suite. Browse more tools in the Info / Guides collection.

Use Cases

• Beginners choosing their first production database for a web app
• MySQL developers curious about Postgres-specific features and advantages
• Students learning SQL fundamentals with a professional-grade database
• Startup CTOs evaluating Postgres vs managed database services

Tips

• Start with the first queries section to learn SELECT, INSERT, and UPDATE
• Check the unique features list to see what sets Postgres apart from MySQL
• Review the beginner mistakes section to avoid common schema design errors

Fun Facts

• PostgreSQL's MVCC (Multi-Version Concurrency Control) means readers never block writers and writers never block readers — a key advantage over MySQL's InnoDB locking.
• PostgreSQL is the only major open-source database to support both ACID transactions and native JSON/JSONB, bridging relational and document-store paradigms.
• Stack Overflow's 2023 Developer Survey ranked PostgreSQL as the most-wanted and most-admired database for the second consecutive year.

FAQ

Why Postgres over MySQL?

JSONB, window functions, CTEs, better standards compliance, more robust transactional guarantees. MySQL wins on simpler replication and slightly faster reads for basic queries.

What makes Postgres special?

JSONB (indexed JSON), full-text search, array types, GIS (via PostGIS), range types, and extensibility. It's a database + a Swiss army knife.

Beginner mistakes?

Storing dates as TEXT (use TIMESTAMPTZ), forgetting to index foreign keys, running long transactions that block VACUUM.

psql essentials?

\l (list databases), \c (connect), \dt (tables), \d (describe), \e (edit in editor), \i (include a file). Learn these and you're productive in the shell.