Elegir entre PostgreSQL y MySQL es una de las decisiones mas importantes en el desarrollo backend.
Resumen rapido
PostgreSQL prioriza la conformidad con estandares, integridad de datos y extensibilidad. MySQL prioriza la simplicidad y velocidad para cargas de trabajo de alta lectura.
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, RDSComparacion de caracteristicas
PostgreSQL ha tenido historicamente un conjunto de caracteristicas mucho mas rico.
-- 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;Comparacion de rendimiento
El rendimiento bruto depende en gran medida del tipo de carga de trabajo.
-- 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 serverCuando elegir PostgreSQL
PostgreSQL es la eleccion correcta cuando necesitas caracteristicas SQL avanzadas.
- 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
Cuando elegir MySQL
MySQL sigue siendo una excelente opcion para muchos casos de uso, especialmente aplicaciones web de alta lectura.
- 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
Migracion entre bases de datos
Migrar de MySQL a PostgreSQL requiere una planificacion cuidadosa.
-- 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';Preguntas frecuentes
Es PostgreSQL mas rapido que MySQL?
Depende de la carga de trabajo. MySQL suele ser mas rapido para consultas simples. PostgreSQL sobresale en consultas analiticas complejas.
Que base de datos recomiendan los principales proveedores cloud?
Todos los grandes proveedores cloud ofrecen ambas. Supabase esta construido sobre PostgreSQL, PlanetScale sobre MySQL.
PostgreSQL admite JSON?
Si, PostgreSQL tiene soporte JSON excepcional con el tipo JSONB, que almacena JSON en formato binario con soporte de indexacion.
Cual es mejor para un nuevo proyecto en 2026?
Para la mayoria de los nuevos proyectos en 2026, PostgreSQL es la opcion predeterminada recomendada.