Att välja mellan PostgreSQL och MySQL är ett av de mest avgörande besluten i backend-utveckling.
Snabböversikt
PostgreSQL prioriterar standardöverensstämmelse, dataintegritet och utbyggbarhet. MySQL prioriterar enkelhet och hastighet för läsintensiva arbetsbelastningar.
Feature PostgreSQL MySQL 8.x
------------------------------------------------------------
License PostgreSQL (free) GPL / Commercial
ACID Compliance Full Full (InnoDB)
JSON Support JSONB (excellent) JSON (good)
Full-Text Search Built-in tsvector FULLTEXT index
Replication Streaming + Logical Binary log + GTID
Max DB Size Unlimited 256TB
Partitioning Declarative RANGE/LIST/HASH
Window Functions Full support Partial (8.x+)
Extensions Rich ecosystem Plugins (fewer)
Default in Cloud Supabase, RDS PlanetScale, RDSFunktionsjämförelse
PostgreSQL har historiskt sett en mycket rikare funktionsuppsättning.
-- Feature Comparison: PostgreSQL vs MySQL
-- 1. JSON Support
-- PostgreSQL JSONB (binary, indexed)
SELECT data->>'name' FROM users WHERE data @> '{"active": true}';
CREATE INDEX idx_users_data ON users USING GIN (data);
-- MySQL JSON
SELECT JSON_EXTRACT(data, '$.name') FROM users
WHERE JSON_EXTRACT(data, '$.active') = true;
-- 2. Full-Text Search
-- PostgreSQL (built-in tsvector)
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & performance');
-- MySQL FULLTEXT
SELECT * FROM articles
WHERE MATCH(content) AGAINST ('postgresql performance' IN NATURAL LANGUAGE MODE);
-- 3. CTEs (Common Table Expressions)
-- PostgreSQL supports recursive CTEs natively (since 8.4)
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS level
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;
-- 4. Window Functions (both support, PostgreSQL more complete)
SELECT
employee_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;Prestandajämförelse
Råprestanda beror starkt på arbetsbelastningstyp.
-- Performance Tuning Examples
-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;
-- PostgreSQL-specific indexes
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status)
WHERE status != 'cancelled'; -- Partial index
CREATE INDEX idx_products_attrs ON products USING GIN (attributes); -- JSONB index
-- MySQL: EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;
-- Connection pooling configuration
-- PostgreSQL (PgBouncer or pg pool settings)
max_connections = 100 -- in postgresql.conf
-- Use connection pooler for high concurrency
-- PgBouncer pool_mode = transaction (recommended for web apps)
-- MySQL
max_connections = 200 -- in my.cnf
innodb_buffer_pool_size = 4G -- 70-80% of RAM for dedicated MySQL serverNär du ska välja PostgreSQL
PostgreSQL är rätt val när du behöver avancerade SQL-funktioner.
- Complex queries — Advanced window functions, CTEs, lateral joins
- JSON/document storage — JSONB with GIN indexes rivals MongoDB
- Geospatial data — PostGIS extension is the gold standard
- Strict data integrity — CHECK constraints, exclusion constraints, custom domains
- High-concurrency writes — MVCC handles concurrent writes better than MySQL
- Full-text search — Built-in, no external service needed
- Analytics/reporting — Better at complex aggregations and window functions
När du ska välja MySQL
MySQL förblir ett utmärkt val för många användningsfall, särskilt läsintensiva webbapplikationer.
- Simple CRUD applications — WordPress, Drupal, and many CMSes default to MySQL
- Read-heavy workloads — InnoDB is highly optimized for read-heavy patterns
- Existing MySQL ecosystem — If your team has deep MySQL expertise
- PlanetScale — MySQL-compatible serverless database with excellent DX
- Replication simplicity — MySQL replication is well-understood and widely deployed
Migrera mellan databaser
Att migrera från MySQL till PostgreSQL kräver noggrann planering.
-- MySQL to PostgreSQL Migration: Common Differences
-- 1. AUTO_INCREMENT -> SERIAL/IDENTITY
-- MySQL
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
-- PostgreSQL
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- or BIGSERIAL for large tables
name VARCHAR(100)
);
-- Modern PostgreSQL (v10+):
-- id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- 2. String functions differ
-- MySQL: IFNULL
SELECT IFNULL(phone, 'N/A') FROM users;
-- PostgreSQL: COALESCE (also works in MySQL)
SELECT COALESCE(phone, 'N/A') FROM users;
-- 3. String concatenation
-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;
-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;
-- Or use CONCAT (PostgreSQL also supports it)
-- 4. LIMIT/OFFSET syntax (both support the same)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 5. Boolean values
-- MySQL: TRUE/FALSE or 1/0
-- PostgreSQL: TRUE/FALSE or 't'/'f' or 'true'/'false'
-- 6. Timestamp with timezone
-- MySQL: DATETIME or TIMESTAMP (local time only)
-- PostgreSQL: TIMESTAMPTZ (timezone-aware, recommended)
ALTER TABLE events
ALTER COLUMN created_at TYPE TIMESTAMPTZ
USING created_at AT TIME ZONE 'UTC';Vanliga frågor
Är PostgreSQL snabbare än MySQL?
Det beror på arbetsbelastningen. MySQL är ofta snabbare för enkla läsförfrågningar. PostgreSQL utmärker sig i komplexa analytiska förfrågningar.
Vilken databas rekommenderar stora molnleverantörer?
Alla stora molnleverantörer erbjuder båda. Supabase är byggt på PostgreSQL, PlanetScale på MySQL.
Stöder PostgreSQL JSON?
Ja, PostgreSQL har utmärkt JSON-stöd med JSONB-typen, som lagrar JSON i binärt format med indexeringsstöd.
Vilket är bättre för ett nytt projekt 2026?
För de flesta nya projekt 2026 är PostgreSQL det rekommenderade standardvalet.