PostgreSQL Explained: The Database You Default To

Introduction

Ask experienced developers which database to pick for a new project, and one answer comes up more than any other: PostgreSQL, usually called Postgres. It is free, open source, battle-tested for decades, and now the most loved database in developer surveys. If the SQL joins post taught you how to ask questions of data, this one explains where that data should live.

Tables, rows, and relationships

Postgres is a relational database: data lives in tables of rows and columns, and tables relate to each other by keys. A blog has users, posts, and comments — three tables, linked by IDs:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT
);

PRIMARY KEY uniquely identifies each row, REFERENCES users(id) says a post's author must be a real user, and UNIQUE prevents duplicate emails. These constraints are the database enforcing your rules even when application code forgets — which is exactly the safety net you want.

ACID: the promise that matters

Postgres is famous for ACID guarantees: Atomicity, Consistency, Isolation, Durability. In plain terms: a transfer that debits one account and credits another either completes fully or not at all, never halfway; the data is always left in a valid state; concurrent users don't corrupt each other's work; and once the database confirms a write, it survives a crash. For anything involving money, orders, or user accounts, ACID is not a luxury — it is the reason to choose a relational database in the first place.

Not just rows: JSON too

Modern Postgres also handles JSON data inside columns, so you don't need a second database for flexible, document-like data:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB
);
INSERT INTO products (details)
VALUES ('{"name": "Keyboard", "price": 49, "tags": ["mechanical", "wireless"]}');
SELECT details->>'name' FROM products WHERE details @> '{"price": 49}';

JSONB stores JSON efficiently and queryably — you can search inside it, index it, and combine it with normal columns in the same query. That flexibility is why many teams now use Postgres for everything, instead of one database per data shape.

Takeaway

Tables with enforced relationships, ACID guarantees for data you cannot afford to lose, and JSON support when you need flexibility. That combination is why Postgres is the default answer — and why "just use Postgres" is genuinely good advice for most new projects.

Comments

Post a Comment

Popular posts from this blog

Swift Explained: Apple's Language for iPhone, Mac, and More

Introduction Swift is Apple's language for its platforms: iPhone, iPad, Mac, Apple Watch, and Apple TV. Introduced in 2014 to replace the older Objective-C, it was designed to be safe, fast, and — unusually for a systems language — genuinely pleasant to read and write. Even if you never ship an app, Swift teaches one idea every developer should understand: optionals. Optionals: saying "this might be missing" Most bugs in app development come from one cause: code assuming a value exists when it does not. Swift makes that assumption impossible to hide. A value that might be missing is wrapped in an Optional, and you must unwrap it before use. var nickname: String? = nil nickname = "Waqas" if let name = nickname { print("Hello, \(name)!") } else { print("No nickname set.") } String? means "a String, or nothing". The if let unwraps it safely: inside the block, name is guaranteed to exist. There is no way to accidentally us...
Read more

Flutter Explained: One Codebase, Every Screen

Introduction Flutter is Google's framework for building apps from a single codebase: write once in Dart, and ship to iOS, Android, web, Windows, macOS, and Linux. It powers apps from Google Pay to the New York Times crossword app. Its core idea is simple and radical: everything on screen is a widget, and the interface is rebuilt from data whenever that data changes. Everything is a widget In Flutter, there are no separate concepts for layout, styling, and controls — everything is a widget, nested inside other widgets like HTML elements, but in code: import 'package:flutter/material.dart'; class Greeting extends StatelessWidget { @override Widget build(BuildContext context) { return Center( child: Text( 'Hello, Sara!', style: TextStyle(fontSize: 24, color: Colors.teal), ), ); } } Center positions its child, Text displays words, TextStyle styles them — each a widget, composed by nesting. Layout widgets (like Row, Column, ...
Read more

Go Explained: Simple Code That Runs Fast

Introduction Go was built at Google for a specific frustration: large programs that were slow to build and hard to read. The answer was a language that is deliberately small — fewer features, but the right ones, done well. If you know one programming language already, Go will feel familiar within an hour. The interesting parts are not the syntax, but two ideas: goroutines and channels. Goroutines: cheap concurrency Most languages make running things at the same time feel heavy — threads, thread pools, and careful setup. A goroutine is Go's lightweight version: you start one by writing go in front of a function call. package main import ( "fmt" "time" ) func say(text string) { for i := 0; i < 3; i++ { fmt.Println(text) time.Sleep(100 * time.Millisecond) } } func main() { go say("hello") say("world") time.Sleep(500 * time.Millisecond) } The go say("hello") line does not wait — it st...
Read more