Project: Blog Database Design

Introduction

This project designs a blog schema—users, posts, comments, categories, tags, and many-to-many links—then adds indexes and sample queries for list pages, detail pages, and tag statistics. You apply normalization, foreign keys, and pagination patterns from earlier chapters. Same scenario as MySQL blog project with PostgreSQL dialect: IDENTITY, RETURNING, TIMESTAMPTZ, and ON CONFLICT.

Prerequisites

• Constraints and Keys
• Indexes and EXPLAIN ANALYZE
• Joins and CTEs

Create Database and Schema

CREATE DATABASE blog_prod
  ENCODING 'UTF8'
  TEMPLATE template0;
 
\c blog_prod

Tables

Code explanation:

• Partial index idx_posts_published_list speeds published-only lists—PostgreSQL advantage over MySQL generic index
• ON DELETE CASCADE on comments and post_tags avoids orphans when a post is removed
• TEXT instead of VARCHAR(255)—Postgres best practice

Seed Sample Data

Query 1: Published List (Pagination)

Check plan:

EXPLAIN ANALYZE
SELECT id, title, published_at FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Expect Index Scan on idx_posts_published_list or idx_posts_user_published.

Query 2: Post Detail With Comments

Single query (wide result):

App-friendly split:

-- Post
SELECT p.*, u.display_name AS author
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.slug = 'hello-postgresql';
 
-- Comments
SELECT cm.body, cm.created_at, u.display_name AS commenter
FROM comments cm
JOIN users u ON u.id = cm.user_id
WHERE cm.post_id = 1
ORDER BY cm.created_at;

Query 3: Tag Cloud

SELECT
  t.name,
  COUNT(*) AS post_count
FROM tags t
INNER JOIN post_tags pt ON pt.tag_id = t.id
INNER JOIN posts p ON p.id = pt.post_id
WHERE p.published_at IS NOT NULL
GROUP BY t.id, t.name
ORDER BY post_count DESC, t.name ASC
LIMIT 20;

Upsert Tag (Postgres Pattern)

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
RETURNING id, name;

MySQL equivalent: ON DUPLICATE KEY UPDATE.

Design Notes

Deliverables

• Schema created in blog_prod
• Seed data with at least 2 users, 3 posts, tags, comments
• Published list query with EXPLAIN ANALYZE
• Detail + comments queries documented
• Tag cloud query returns counts

FAQ

MySQL vs Postgres for this schema?

Same relational shape—Postgres adds partial indexes, RETURNING, ON CONFLICT, native BOOLEAN.

Full-text search on posts?

Add tsvector column + GIN—JSONB and Advanced Types.

Soft delete posts?

Add deleted_at TIMESTAMPTZ and partial index WHERE deleted_at IS NULL.

API layer?

Wire to FastAPI + PostgreSQL or Spring JDBC—chapter 24.

MongoDB for blog content?

Flexible documents vs strict SQL—MongoDB modeling comparison in What Is PostgreSQL.

Next project?

E-commerce orders—transactions and inventory.