PostgreSQL Databases🌱 Beginner

A single PostgreSQL installation is known as a server (or cluster). Inside that server, you can host multiple isolated databases, which themselves contain schemas and tables.

What is the Hierarchy?

PostgreSQL organizes data structurally. Understanding this hierarchy is critical for managing permissions and architectural layout.

Database Server (Port 5432)
└─ Database: "ecommerce_db"
   ├─ Schema: "public" (The default workspace)
   │  ├─ Table: "users"
   │  └─ Table: "orders"
   └─ Schema: "analytics" (A custom workspace)
      └─ Table: "yearly_reports"

Why Use Schemas?

By default, when you create a table, it goes into a namespace called public. While one database per application is standard, schemas allow you to subdivide that database. You might use schemas to separate microservice tables (e.g., a billing schema vs an inventory schema) or to build multi-tenant applications where every client gets their own isolated schema.

How to Manage Databases and Schemas

You can create and destroy these structures using standard SQL commands:

-- 1. Create a new database for your project
CREATE DATABASE ecommerce_db;

-- (Switch to it in psql using: \c ecommerce_db)

-- 2. Create a custom schema inside the database
CREATE SCHEMA analytics;

-- 3. Create a table explicitly inside that new schema
CREATE TABLE analytics.yearly_reports (
    id SERIAL PRIMARY KEY,
    report_data JSONB
);