PostgreSQL for beginner: tables, indexes, migrations

PostgreSQL is one of the most popular relational databases. It is used by startups, large companies and open-source projects. It is powerful, stable and well suited for both small applications and high-load systems.

But if you’re just starting out with databases, it’s easy to get confused about the basic concepts:

• what is a table
• why we need indexes
• why developers use migration
• how to change the base structure right

In this article, we will discuss three fundamental things:

• tables - where the data is stored
• **indices ** how to speed up search
• **regulation: how to change the structure of the base safely

First in simple words, then in practice.

## What is PostgreSQL

PostgreSQL is a relational database.

That means:

• the data is stored in ** tables**
• tables are interconnected
• the data is accessed via SQL

Example of a simple application:

Each table contains records and columns.

## Tables in PostgreSQL

Table is the main structure of data storage.

The easiest way to imagine it is as a table in Excel.

In the database, this will be the users table.

### Creation of a table

In PostgreSQL, a table is created using SQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Let's see what's going on here.

### Addition of data

Add a user.

INSERT INTO users (name, email)
VALUES ('Alex', 'alex@mail.com');

Now there's a record in the table.

### Reading data

Get all users:

SELECT * FROM users;

Get one user:

SELECT * FROM users
WHERE id = 1;

This is the basic way to work with tables.

But there's a problem.

When the table is small, everything works quickly.

When there are a million lines in it, requests start to slow down.

This is where the indexes come in.

## Indices: why they are needed

An index is a data structure that speeds up search in a table.

Without an index, PostgreSQL does a full scan of the table.

I mean, I read every line.

Present the table:

users
10,000,000 lines

Request:

SELECT * FROM users WHERE email = 'alex@mail.com';

Without the index, the database will check 10 million lines.

With an index, it will find a line almost instantly.

### How the index works

The index is like a ** table of contents in a book.

Instead of flipping through the book, you look into the table of contents and go to the desired page.

PostgreSQL uses structures like **B-tree.

### Creating an index

CREATE INDEX idx_users_email
ON users(email);

Now, email search will be quick.

### When to add indexes

Usually an index is created for:

Example:

SELECT *
FROM posts
WHERE author_id = 10;

Better to create an index:

CREATE INDEX idx_posts_author
ON posts(author_id);

### When indexes are harmful

Indices speed up reading, but ** slow down writing.*.

Each INSERT and UPDATE updates the index.

Therefore, you can not create indexes for everything.

Bad example:

10-column
hardly used

It slows down the base.

## Migration: Base scheme management

As the project evolves, the structure of the base is constantly changing.

For example:

• column
• change the type
• spreadsheet
• add

You can do this manually

ALTER TABLE users ADD COLUMN age INTEGER;

But real projects don’t do that.

They're using **migration.

### What is migration

Migration is a versioned change in the structure of the database.

Each change is stored as a separate file.

Example:

001_create_users_table.sql
002_add_email_index.sql
003_add_age_column.sql

This allows:

• base structure
• roll back
• sync

### Example of migration

Creation of the table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Next migration:

ALTER TABLE users
ADD COLUMN email TEXT;

Each migration is applied ** once **.

PostgreSQL or framework stores information about the migrations performed.

### Migration table

Usually a service table is created.

This helps the system understand:

which migrations have already been applied.

## Migration in frameworks

Almost all backend frameworks have a migration system.

An example of migration to Node (Knex):

exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary()
    table.string('email').unique()
    table.timestamps(true, true)
  })
}

## Typical Beginner Mistakes

### No primary key

Each table must have PRIMARY KEY.

This is usually id.

### No indexes

Large tables without indexes quickly begin to slow down.

### Manual base changes

If the developer changes the database manually rather than through migration:

• environment begins to differ
• chaos

### Changing existing migrations

You can't change old migrations.

We need to create a new one.

## A practical example of the base structure

Imagine an API for a blog.

Tables:

users
posts
comments

### users

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

### posts

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

Index:

CREATE INDEX idx_posts_author
ON posts(author_id);

### comments

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id),
    text TEXT
);

Index:

CREATE INDEX idx_comments_post
ON comments(post_id);

Such a scheme is already suitable for a real application.

## How to apply it through AI

AI assistants can significantly speed up the work with the database.

For example:

• design
• migration
• indexing
• slow-query analysis

An example of a universal prompt:

Design a PostgreSQL schema for a blog application.

Conditions:

tables:
- users
- posts
comments

Requirements:
- correct primary keys
- Foreign keys.
- indices for basic queries
- created at fields
- SQL for migration

Also explain why these indices were selected.

AI can generate:

• layout
• SQL migration
• recommendations on indices.

## Outcome

Simply put, PostgreSQL is based on three fundamental things:

Understanding these three elements is the basis for any backend development.