Databases with Postgres

Database is a persistence layer, which performs the operations of CRUD.

In context of Databases for Backend, we mean Disk based ( secondary storage or Hard Disk based storage). The reason we use Disk based storage for backend is:

• Cheaper than Ram

• Way more Storage Capacity than Ram

Ram based storage like Redis, are used for Caching or queueing.

What a DBMS Should Provide

1. Structured Schema & Relationships
A DBMS should enforce a clear structure (tables, data types, relationships) so data isn’t random or inconsistent.

For example, a Orders table referencing a non-existent UserID should be rejected automatically.

2. Data Integrity & Constraints
It must prevent invalid data from entering the system. If an email column is marked UNIQUE, the system should not allow two users to register with the same email.

3. Concurrency Control
When multiple users access data at the same time, changes shouldn’t overwrite each other. If two admins update a product’s price simultaneously, one update shouldn’t silently erase the other.

---

Why Not Just Use Plain Text Files?

1. Parsing Problems
With text files, every read requires manual parsing and validation. A missing comma in users.txt could break the entire application logic.

2. No Structure Enforcement
There’s no built-in way to guarantee relationships between files. You might delete a user from one file but still have their orders sitting in another file.

3. Concurrency Issues
Two processes writing to the same file can corrupt it. Imagine two threads appending to a log file at the same time—data can overlap or be lost.

---

Relational vs Non-Relational DBMS

Relational DBMS (RDBMS) store data in structured tables made of rows and columns, with relationships defined using keys. They follow a fixed schema and are well suited for applications where data consistency and relationships are important.

Example:
A CRM (Customer Relationship Management) system stores structured data such as customers, orders, and payments. These entities are related (e.g., a customer can have many orders), which makes relational databases like MySQL or PostgreSQL suitable.

Non-Relational DBMS (NoSQL) store data in flexible formats such as documents, key-value pairs, graphs, or wide columns. They do not require a fixed schema and are useful when handling large volumes of varied or unstructured data.

Example:
A CMS (Content Management System) manages different types of content such as blog posts, images, comments, and metadata. Since the structure of content can vary, NoSQL databases like MongoDB are often used to store this flexible data.

---

Database Migrations:

Database migrations are a structured way to manage and track changes to a database schema over time. Instead of manually modifying the database whenever the application evolves, migrations allow developers to define schema changes in version-controlled files that can be applied consistently across development, testing, and production environments.

Each migration typically contains two parts: up migrations and down migrations. An up migration describes the changes to apply to the database, such as creating a table, adding a column, or introducing an index. It moves the database schema forward to a new version. A down migration, on the other hand, defines how to reverse those changes. It allows the database to be rolled back to its previous state if a deployment fails or a change needs to be undone.

By maintaining migrations as part of the codebase, teams can ensure that database structure evolves in sync with application code, making deployments more reliable and collaborative development easier.

---

Using DBMate and TablePlus for Database Migrations

DBMate is a lightweight, database migration tool designed to manage schema changes through versioned SQL files. It follows a simple approach where each migration file contains two sections: an up migration that applies schema changes and a down migration that reverses them. DBMate keeps track of applied migrations using a schema_migrations table, ensuring migrations run only once and in the correct order.

Below is an example PostgreSQL migration that demonstrates enum creation and different relationship types: one-to-one, one-to-many, and many-to-many.

---

Example Migration

-- migrate:up

-- ENUM TYPE
CREATE TYPE user_role AS ENUM ('admin', 'member', 'guest');

-- USERS TABLE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    role user_role DEFAULT 'member'
);

-- ONE TO ONE RELATION
CREATE TABLE profiles (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url TEXT
);

-- ONE TO MANY RELATION
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MANY TO MANY RELATION
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

---

Down Migration

The down migration reverses the schema changes in the opposite order to avoid dependency errors.

-- migrate:down

DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS users;

DROP TYPE IF EXISTS user_role;

---

Parameterised Queries

Parameterised queries allow user input to be passed to SQL statements using placeholders instead of directly inserting values into the query string. In PostgreSQL, parameters such as \(1, \)2, etc., are used to represent values that will be supplied separately. This prevents SQL injection attacks, because the database treats the supplied values strictly as data rather than executable SQL.

For example, a PostgreSQL query using parameters can be written as:

PREPARE get_user(text) AS
SELECT * FROM users WHERE email = $1;

EXECUTE get_user('user@example.com');

Here, $1 acts as a placeholder for the email value. Since PostgreSQL binds the parameter separately from the query structure, even if the input contains malicious SQL, it cannot alter the query logic, effectively protecting the database from SQL injection.

Generally the parameterisation is handled by the language driver one would be using while developing the backend.

---

Database Indexes

A database index is a data structure that improves the speed of data retrieval in a table. Instead of scanning every row to find matching data, the database uses the index to quickly locate the required records. It works similarly to the index of a book, where you can jump directly to the relevant page rather than reading the entire book.

Indexes are commonly created on columns that are frequently used in search conditions, sorting, or joins, such as user emails, timestamps, or foreign keys.

For example, if an application frequently searches for users by email during login, an index can be created on the email column:

CREATE INDEX idx_users_email ON users(email);

This helps queries like the following run more efficiently:

SELECT * FROM users WHERE email = 'user@example.com';

Indexes are also useful for sorting results. If an application often retrieves the most recent records, an index on a timestamp column can improve performance:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

While indexes significantly improve read performance, they slightly increase storage usage and can slow down write operations because the index must also be updated. Therefore, indexes should be applied thoughtfully to columns that are frequently queried.

---

Why Not Index Every Column?

Although indexes significantly improve query performance, indexing every column is not practical. Each index consumes additional storage and must be updated whenever data in the table changes. This means that operations such as INSERT, UPDATE, and DELETE become slower because the database has to maintain both the table data and all associated indexes.

For example, if a table has many indexes and a new row is inserted, the database must update each index structure along with the table itself. As the number of indexes increases, write operations become progressively more expensive.

Indexes are therefore most beneficial on columns that are frequently used in search conditions, sorting, joins, or foreign key lookups. Columns that are rarely queried or contain highly repetitive values often provide little performance benefit from indexing.

For this reason, database design typically involves carefully choosing indexes based on the most common query patterns rather than indexing every column in a table.

---

Conclusion

Migrations, parameterized queries, and indexes are fundamental tools for building secure, maintainable, and efficient database-backed applications. They help manage schema changes safely, protect systems from SQL injection, and improve query performance as data grows. However, even with well-designed queries and indexes, repeatedly fetching the same data from the database can become a performance bottleneck. In the next section, we will explore caching, a technique used to reduce database load and speed up applications by storing frequently accessed data closer to the application.