Why PostgreSQL should be your default database

TL;DR

PostgreSQL is a mature open-source relational database with native JSON, full-text search and a long track record for reliability. Unless you have a specific reason to use something else, it's a sensible default for any serious project.

Why PostgreSQL over MySQL or SQLite?

PostgreSQL gives you JSONB, built-in full-text search, geospatial queries through the PostGIS extension, window functions, CTEs and full ACID transactions, including schema changes. Instagram and Notion both run on it. SQLite is great for local tools and tests, but it isn't designed for many concurrent writers on a server.

PostgreSQL

✅ Native JSONB, with indexing
✅ Built-in full-text search
✅ Transactional DDL and strict data integrity
✅ Window functions and CTEs

MySQL

✅ Can be faster on simple, read-heavy workloads
✅ Available on more shared hosting
❌ Fewer advanced features
❌ Concerns about Oracle's stewardship

Queries you'll use every day

PostgreSQL: Essential SQL

-- Create a table
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert
INSERT INTO projects (title, tags, metadata)
VALUES ('Hesabi', ARRAY['nextjs','postgres'], '{"live": true}');

-- Query with JSON
SELECT title FROM projects
WHERE metadata->>'live' = 'true';

-- Array contains
SELECT * FROM projects
WHERE 'nextjs' = ANY(tags);

-- Full-text search
SELECT * FROM projects
WHERE to_tsvector('english', title) @@ to_tsquery('accounting');

Indexes: the biggest performance win

Without an index, PostgreSQL has to scan the whole table to answer a filter. On a large table, the right index takes a query from seconds to milliseconds. Index the columns you often filter, join or sort on, but don't index everything: each index slows down writes and takes disk space. Run EXPLAIN ANALYZE to confirm an index is actually used.

Indexes: When and How

-- Basic index
CREATE INDEX idx_projects_title ON projects(title);

-- Partial index (only index active records)
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;

-- GIN index for JSONB and arrays
CREATE INDEX idx_projects_tags ON projects USING GIN(tags);
CREATE INDEX idx_projects_meta ON projects USING GIN(metadata);

-- Check slow queries
EXPLAIN ANALYZE SELECT * FROM projects WHERE title = 'Hesabi';

Django works very well with PostgreSQL. django.contrib.postgres adds ArrayField and full-text search (SearchVector, SearchQuery), and JSONField is stored as JSONB, so you get these features without writing raw SQL.

Key takeaways

Use PostgreSQL by default. Start with a simple schema, add indexes when EXPLAIN shows you need them, and use JSONB for the parts of your data that really have no fixed shape. It scales comfortably from a side project to production.

PostgreSQL SQL Base de données Django Backend Tunisie