Part 4: How PostgreSQL Handles Millions of Blog Posts — Without Breaking

The Problem: Too Many Readers = Too Slow

Imagine you're a cashier at a store, and suddenly 1,000 people want to check out at the same time. You can't help them all simultaneously. You'd have a line, people would wait, and some might leave frustrated.

Databases have the same problem. When thousands of people read your blog at the same time, they all need data from the database. If it's not set up right, everything slows down.

What Is a Database?

A database is like a library:

When you read a blog post, you're asking the librarian to get it from the shelf. When 1,000 people read posts at the same time, they're all asking the librarian for different books.

The Smart Solution: Connection Pooling

Instead of opening a new connection every time (like hiring a new librarian), I use connection pooling:

It's like having 10 librarians instead of 1,000, because readers take turns.

How It Works in AllBengal

// In app/core/database.py
pool = await asyncpg.create_pool(
    host="supabase.postgres.net",
    min_size=2,    // Always keep 2 connections ready
    max_size=10,   // Never create more than 10 connections
)

// When a request comes in:
async with pool.acquire() as connection:
    // Use the connection to fetch data
    posts = await connection.fetch("SELECT * FROM posts")
    // When done, connection automatically goes back to the pool

PostgreSQL: Why This Database?

I chose PostgreSQL because:

1. It's reliable: Used by Netflix, Spotify, Instagram — companies that handle billions of requests
2. It's free: Open source, no licensing fees
3. It scales: Can handle millions of rows efficiently
4. It's hosted free on Supabase: I don't need to manage servers

Storing Blog Posts: The Schema

Here's how AllBengal stores your posts (simplified):

When you query for a post by slug:

SELECT * FROM posts WHERE slug = 'my-first-post'

PostgreSQL finds it in milliseconds, even if there are 1 million posts.

Indexing: The Fast Lookup

Databases use indexes like a book's table of contents:

AllBengal creates indexes on:

• slug (to find posts by URL)
• author_id (to find all posts by one author)
• created_at (to sort by newest first)

It's like having bookmarks in a giant book — you can jump to the right page instantly.

Async Connections: Super Fast

AllBengal uses async connections, which means:

User 1 requests: "Get me post #5"
Database: "Getting it... (1 second)..."
User 2 requests: "Get me post #3"
Database: "Wait, I'm busy with User 1"
User 2 waits...

User 1 requests: "Get me post #5"
User 2 requests: "Get me post #3"
Database: "I'll start both at the same time"
Both finish in ~1 second instead of 2

It's like a waiter taking multiple orders at once instead of finishing one completely before taking the next.

Real-World Numbers

With AllBengal's setup:

The Free Tier: How Much Can You Store?

Supabase free tier gives you:

When you need more, you upgrade to paid (around $25/month) and get:

• More storage (5 GB)
• More connections (20)
• Better backups