В чем разница между PostgeSQL и SQLite?

Разница между PostgreSQL и SQLite

PostgreSQL и SQLite — это обе реляционные БД, но предназначены для разных сценариев и имеют существенные отличия.

Архитектура

SQLite

Приложение
    ↓
SQLite3 (в памяти приложения)
    ↓
Файл .db на диске

Всё в одном файле, встроено в приложение.

PostgreSQL

Приложение
    ↓
SQL драйвер (psycopg2, asyncpg)
    ↓
Сетевое соединение TCP/IP
    ↓
PostgreSQL сервер (отдельный процесс)
    ↓
Данные на диске

Клиент-серверная архитектура.

Сравнительная таблица

Когда использовать

SQLite:

# ✓ Приложение Electron
import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER, name TEXT)')

# ✓ Мобильное приложение (Android, iOS)
# ✓ Desktop приложение
# ✓ Вложенная БД в большое приложение
# ✓ Прототипирование и разработка

PostgreSQL:

# ✓ Веб-приложение (Django, FastAPI)
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres password=secret")
cursor = conn.cursor()

# ✓ Enterprise приложения
# ✓ Высоконагруженные системы
# ✓ Production системы
# ✓ Data warehouse

Производительность

# SQLite
# Быстрый локальный доступ (~1-10ms)
result = cursor.execute("SELECT * FROM users WHERE id = 1")

# PostgreSQL
# Сетевая задержка + обработка (~10-100ms в зависимости от сети)
result = cursor.execute("SELECT * FROM users WHERE id = 1")

# Однако PostgreSQL быстрее при множественных запросах
# благодаря оптимизатору запросов

Параллелизм (Concurrency)

SQLite:

# SQLite использует file-level locking
# При записи — весь файл БД заблокирован

conn1.execute("UPDATE users SET name='John' WHERE id=1")
conn1.commit()

# В это время другое соединение не может писать
conn2.execute("UPDATE users SET name='Jane' WHERE id=2")
# Ждёт, пока conn1 закончит

PostgreSQL:

# PostgreSQL использует row-level locking
# Разные строки могут редактироваться одновременно

conn1.execute("UPDATE users SET name='John' WHERE id=1")
conn2.execute("UPDATE users SET name='Jane' WHERE id=2")
# Обе операции выполняются одновременно

Типы данных

SQLite (базовые):

# NULL, INTEGER, REAL, TEXT, BLOB
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        balance REAL,
        data BLOB
    )
''')

PostgreSQL (расширенные):

# JSON, JSONB, Array, UUID, Interval, Timestamp, и сотни других
cursor.execute('''
    CREATE TABLE users (
        id UUID PRIMARY KEY,
        name TEXT,
        age INTEGER,
        metadata JSONB,
        tags TEXT[],
        created_at TIMESTAMPTZ,
        properties JSONB
    )
''')

Практический пример

SQLite:

import sqlite3
from datetime import datetime

# Подключение встроено в приложение
conn = sqlite3.connect(':memory:')  # В памяти
cursor = conn.cursor()

# Создание таблицы
cursor.execute('''
    CREATE TABLE posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

# Вставка данных
cursor.execute(
    "INSERT INTO posts (title, content) VALUES (?, ?)",
    ("My Post", "Content here")
)
conn.commit()

# Запрос данных
cursor.execute("SELECT * FROM posts")
for row in cursor.fetchall():
    print(row)

conn.close()

PostgreSQL:

import psycopg2
from psycopg2.extras import RealDictCursor
from datetime import datetime

# Подключение к серверу
conn = psycopg2.connect(
    host='localhost',
    database='mydb',
    user='postgres',
    password='password'
)
cursor = conn.cursor(RealDictCursor)

# Создание таблицы
cursor.execute('''
    CREATE TABLE posts (
        id SERIAL PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        content TEXT,
        metadata JSONB DEFAULT '{}',
        tags TEXT[] DEFAULT ARRAY[]::TEXT[],
        created_at TIMESTAMPTZ DEFAULT NOW()
    )
''')
conn.commit()

# Вставка данных с JSON
cursor.execute(
    "INSERT INTO posts (title, content, metadata, tags) VALUES (%s, %s, %s, %s)",
    ("My Post", "Content", '{"author": "John"}', ["python", "database"])
)
conn.commit()

# Запрос с JSON функциями
cursor.execute("""
    SELECT 
        id,
        title,
        metadata->>'author' as author,
        tags
    FROM posts
    WHERE metadata->'author' = '\"John\"'
""")

for row in cursor.fetchall():
    print(row)

conn.close()

С Django

# Django с SQLite (разработка)
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

# Django с PostgreSQL (production)
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'postgres',
        'PASSWORD': 'secret',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

С SQLAlchemy

from sqlalchemy import create_engine

# SQLite
engine = create_engine('sqlite:///example.db')

# PostgreSQL
engine = create_engine('postgresql+psycopg2://user:password@localhost/dbname')

Выводы

Используй SQLite если:

• Desktop/мобильное приложение
• Прототипирование
• Одиночный пользователь
• Данные < 100GB
• Нужна простота развёртки

Используй PostgreSQL если:

• Веб-приложение
• Multiple пользователей
• Production система
• Нужна высокая конкурентность
• Нужны продвинутые типы данных
• Нужны транзакции и ACID
• Data warehouse/analytics

Правило: Начни с SQLite для разработки, переходи на PostgreSQL для production.