# If using Docker:
docker run --name pixelcraft-postgres \
-e POSTGRES_PASSWORD=secret \
-p 5432:5432 -d postgres
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE analytics_events (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
event_type VARCHAR(50) NOT NULL,
-- 'upload', 'filter_apply',
-- 'export', 'login'
metadata JSONB,
created_at TIMESTAMP DEFAULT NOW()
);
-- Insert users
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com');
-- Insert events
INSERT INTO analytics_events
(user_id, event_type, metadata)
VALUES (1, 'upload',
'{"format": "jpeg", "size_mb": 4.2}');
-- Basic queries
SELECT * FROM users WHERE name LIKE 'A%';
SELECT COUNT(*) FROM analytics_events
WHERE event_type = 'upload';
SELECT event_type, COUNT(*) as count
FROM analytics_events
GROUP BY event_type
ORDER BY count DESC;
-- Top 10 users by upload count
SELECT u.name,
COUNT(e.id) as upload_count
FROM users u
JOIN analytics_events e
ON u.id = e.user_id
WHERE e.event_type = 'upload'
GROUP BY u.name
ORDER BY upload_count DESC
LIMIT 10;
| Criteria | MongoDB | PostgreSQL |
|---|---|---|
| Schema | Flexible documents | Strict tables |
| Best for | Rapid prototyping, varied data | Complex queries, reporting |
| Relationships | Embedding / referencing | JOINs (native) |
| PixelCraft use | Image metadata | Analytics events |
git switch -c feature/PIXELCRAFT-053-postgres-analytics
git add server/
git commit -m "Add PostgreSQL analytics with SQL queries (PIXELCRAFT-053)"
git push origin feature/PIXELCRAFT-053-postgres-analytics
# PR → Review → Merge → Close ticket ✅
Relational algebra.
SQL is the practical implementation of a mathematical theory by Edgar Codd (1970).