Overview #

The Photos Service currently uses SQLite as its primary data store. While SQLite has served well during early development, increasing read/write load, larger datasets, and multi-instance deployments require a more scalable, concurrent database. We propose migrating the backend to PostgreSQL to support horizontal scaling, improved concurrency, and richer query capabilities.

The service is written in Node.js using Fastify, and uses a thin database module for queries. This proposal outlines required changes and provides example code modifications.

Motivation #

Current Problems with SQLite #

• File-based locking limits write throughput.

• No built-in replication for high availability.

• Difficult to support multiple service instances writing simultaneously.

• Slow for complex queries needed for upcoming features (tag search, smart albums).

Benefits of PostgreSQL #

• True client/server database designed for concurrency.

• Built-in replication, backup tooling, and scaling options.

• Strong type system, indexing support, and JSON features.

• Well-supported Node.js ecosystem (pg library).

High-Level Changes #

1. Replace SQLite adapter with pg client.

2. Add connection pooling and configuration via environment variables.

3. Update migration scripts to generate PostgreSQL schemas.

4. Update SQL statements to use PostgreSQL syntax where required.

5. Deploy PostgreSQL instance (managed or self-hosted).

6. Run a one-time data migration job.

Schema Differences #

SQLite (current table): #

CREATE TABLE photos (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  path TEXT NOT NULL,
  created_at TEXT NOT NULL
);

PostgreSQL (proposed table): #

CREATE TABLE photos (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  path TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Code Changes #

Database Initialization #

Before (SQLite using better-sqlite3): #

import Database from 'better-sqlite3';

const db = new Database('./photos.db');

export function getPhoto(id) {
  return db.prepare('SELECT * FROM photos WHERE id = ?').get(id);
}

After (PostgreSQL with pg): #

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.PG_URL,
});

export async function getPhoto(id) {
  const result = await pool.query(
    'SELECT * FROM photos WHERE id = $1',
    [id]
  );
  return result.rows[0];
}

Insert Query #

Before (SQLite): #

db.prepare(
  'INSERT INTO photos (id, user_id, path, created_at) VALUES (?, ?, ?, ?)'
).run(id, userId, path, new Date().toISOString());

After (PostgreSQL): #

await pool.query(
  `INSERT INTO photos (id, user_id, path)
   VALUES ($1, $2, $3)`,
  [id, userId, path]
);

Postgres handles defaults for created_at.

Migration Plan #

1. Preparation

   • Add PostgreSQL connection settings to service config.

   • Build a new DB client module with pg.

   • Write schema migration SQL and automate with a migration tool (Knex, node-pg-migrate, or raw scripts).

2. Data Migration

   • Export SQLite data to NDJSON or CSV.

   • Transform IDs to UUIDs if needed.

   • Import into PostgreSQL using COPY for efficiency.

3. Deployment

   • Deploy PostgreSQL database.

   • Roll out a service version that supports both DBs (dual-read).

   • Switch write path to PostgreSQL.

   • After the stability window, disable SQLite and remove the compatibility layer.

4. Monitoring

   • Add metrics for connection pool usage, slow queries, and error rates.

   • Enable pg_stat_statements for observability.

Risks & Mitigations #

Success Criteria #

• Photos Service handles 10x current throughput without write contention.

• No downtime or lost data during cutover.

• Queries remain as fast or faster than with SQLite.

• The system supports multi-instance deployments.