PostgreSQL – Fundamentals

What is PostgreSQL? Why is it different from other RDBMS?

PostgreSQL is an open-source, relational database used to store and query structured data using SQL. It is designed for correctness, reliability, and advanced querying, not just basic data storage.

Why is it different from other RDBMS?

• Strict and standards-compliant: enforces data integrity instead of silently allowing bad data
• Advanced data types: native support for JSONB, ARRAY, UUID, ENUM, etc.
• Strong concurrency: uses MVCC, so reads and writes don’t block each other
• Powerful queries: supports CTEs, window functions, recursive queries
• Highly extensible: custom types, functions, and extensions (e.g., PostGIS)

Data Types

type	use case	example	bonus points
SMALLINT	small numbers	age	saves space
INTEGER	counts, ids	user_count	fast, common default
BIGINT	large ids	event_id	safe for scale
NUMERIC / DECIMAL	money, precision	price	exact arithmetic
DOUBLE PRECISION	measurements	distance	fast, approximate

type	use case	example	bonus points
TEXT	general strings	name	preferred default
VARCHAR(n)	length-limited text	email	same perf as text
CHAR(n)	fixed-width	country_code	space-padded, avoid

type	use case	example	bonus points
BOOLEAN	flags	is_active	supports t/f/1/0

type	use case	example	bonus points
DATE	calendar date	dob	no timezone
TIME	time only	opening_time	rarely used
TIMESTAMP	date + time	created_at	no timezone
TIMESTAMPTZ	date + time	created_at	timezone-safe, prefer

type	use case	example	bonus points
JSON	raw json	payload	text storage
JSONB	structured data	metadata	indexable, fast

type	use case	example	bonus points
TEXT[]	small lists	roles	avoid joins
INT[]	numeric lists	scores	simple containment

type	use case	example	bonus points
hstore	key-value data	attributes	legacy, use jsonb

type	use case	example	bonus points
ENUM	fixed states	status	enforces valid values

type	use case	example	bonus points
UUID	unique ids	id	safe for distributed systems

type	use case	example	bonus points
POINT	coordinates	location	simple geometry
LINE	paths	route	niche usage
BOX	areas	region	bounding boxes
CIRCLE	radius data	coverage	basic spatial logic

PostgreSQL Schema, Tables, and Constraints (Primary Key, Foreign Key, Unique, Check, NOT NULL, DEFAULT)

Normalization (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) and Denormalization

nf	what goes wrong (bad design)	bad table columns	good table(s) after fix
1nf	multiple values in one cell	users(id, name, phones)	user_phones(user_id, phone)
2nf	partial dependency on composite key	enrollments(student_id, course_id, student_name, course_name)	students(student_id, student_name) courses(course_id, course_name) enrollments(student_id, course_id)
3nf	indirect (transitive) dependency	users(user_id, city_id, city_name)	users(user_id, city_id) cities(city_id, city_name)
bcnf	non-key column determines another	classes(student, subject, teacher)	subjects(subject, teacher) enrollments(student, subject)
4nf	mixing multiple independent facts in one table	user_profile(user, skill, hobby)	user_skills(user, skill) user_hobbies(user, hobby)
5nf	a fact that can be fully derived by joining other tables	spr(supplier, product, region)	supplier_product(supplier, product) supplier_region(supplier, region) product_region(product, region)
denormalization	intentional duplication for speed	normalized joins across tables	orders(order_id, user_id, city_name)

Views, Materialized Views, and Temporary Tables

View

• Virtual table (stores SQL, not data)
• Always shows latest data
• Used for abstraction, reuse, security
• Does not improve performance

Materialized View

• Stores result of a query
• Fast reads, but data can be stale
• Must be refreshed manually
• Used for analytics and heavy queries

Temporary Table

• Real table, exists only for a session
• Automatically dropped
• Used for intermediate / scratch data

feature	view	materialized view	temp table
stores data	no	yes	yes
always fresh	yes	no	yes
performance	normal	fast	fast
needs refresh	no	yes	no
scope	global	global	session

Functions, Stored Procedures and Triggers

Function

• a database object that returns a value
• used for reusable logic or calculations
• can be called inside sql queries
• calculate tax, format strings, validate input
• select calculate_tax(1000);

Stored Procedure

• a database object that performs actions
• does not return a value
• used for multi-step or transactional workflows
• transfer money between accounts
• run batch updates
• call transfer_money(1, 2, 500);

Trigger

• automatically executes when a table event occurs
• fires on insert, update, or delete
• always calls a trigger function
• auto-update updated_at timestamp
• insert audit logs on data changes
• enforce complex rules not possible with constraints