8. PostgreSQL features the app can use

The migration is successful when the app runs on PostgreSQL. It becomes valuable when the app uses PostgreSQL for work SQLite could not do cleanly: full-text search, JSONB queries inside tracks.raw_json, and EXPLAIN ANALYZE to prove indexes are helping.

Full-text search

Track and artist search is a good fit for PostgreSQL full-text search. It ranks matches instead of merely checking whether a string appears.

schema_postgres.sql

CREATE INDEX IF NOT EXISTS idx_tracks_search
ON tracks USING GIN (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(artist_name, ''))
);

app.py

@app.route('/search')
def search():
    query = request.args.get('q', '').strip()
    if not query:
        return render_template('search.html', results=[])

    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT track_id, name, artist_name, album_name,
                       ts_rank(
                           to_tsvector('english', coalesce(name, '') || ' ' || coalesce(artist_name, '')),
                           plainto_tsquery('english', %s)
                       ) AS rank
                FROM tracks
                WHERE to_tsvector('english', coalesce(name, '') || ' ' || coalesce(artist_name, ''))
                      @@ plainto_tsquery('english', %s)
                ORDER BY rank DESC
                LIMIT 20
            """, (query, query))
            results = cursor.fetchall()
        return render_template('search.html', results=results, query=query)
    finally:
        return_db_connection(conn)

JSONB queries against raw_json

The raw payload lets you ask questions that were not worth first-class columns in Chapter 16. PostgreSQL can query those fields directly.

psql

-- Tracks with a cached ISRC in the original provider payload
SELECT name, artist_name, raw_json->'external_ids'->>'isrc' AS isrc
FROM tracks
WHERE raw_json->'external_ids' ? 'isrc'
LIMIT 20;

-- Tracks by release year, using album.release_date inside raw_json
SELECT LEFT(raw_json->'album'->>'release_date', 4) AS release_year,
       COUNT(*) AS tracks
FROM tracks
WHERE raw_json ? 'album'
  AND raw_json->'album' ? 'release_date'
GROUP BY release_year
ORDER BY release_year DESC
LIMIT 20;

-- Explicit tracks, using JSONB containment
SELECT name, artist_name
FROM tracks
WHERE raw_json @> '{"explicit": true}'::jsonb
LIMIT 20;

The containment query uses the GIN index on raw_json. The release-year query demonstrates nested extraction with -> and ->>. The ISRC query shows a key-existence check against a nested object.

Expression indexes for extracted fields

If the app frequently sorts or filters by a specific JSONB field, add an expression index. Sorting tracks by release year is a good example.

schema_postgres.sql

CREATE INDEX IF NOT EXISTS idx_tracks_release_year
ON tracks ((LEFT(raw_json->'album'->>'release_date', 4)::integer));

psql

SELECT name, artist_name, LEFT(raw_json->'album'->>'release_date', 4)::integer AS release_year
FROM tracks
WHERE raw_json->'album' ? 'release_date'
ORDER BY LEFT(raw_json->'album'->>'release_date', 4)::integer DESC
LIMIT 50;

EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and shows the plan PostgreSQL chose. Use it when you need to confirm an index is earning its keep.

psql

EXPLAIN ANALYZE
SELECT name, artist_name, LEFT(raw_json->'album'->>'release_date', 4)::integer AS release_year
FROM tracks
WHERE raw_json->'album' ? 'release_date'
ORDER BY LEFT(raw_json->'album'->>'release_date', 4)::integer DESC
LIMIT 50;

Output (representative)

Limit  (cost=0.28..18.42 rows=50 width=96) (actual time=0.091..0.612 rows=50 loops=1)
  ->  Index Scan Backward using idx_tracks_release_year on tracks
        (cost=0.28..682.10 rows=1872 width=96)
        (actual time=0.090..0.604 rows=50 loops=1)
        Filter: ((raw_json -> 'album') ? 'release_date'::text)
Planning Time: 0.319 ms
Execution Time: 0.655 ms

The Index Scan Backward line confirms PostgreSQL used the expression index to return the highest values first. Without that index, the database would scan every row, extract the field, sort the result, and only then apply the limit.

That closes the chapter's technical work. The Music Time Machine is on PostgreSQL, deployed with a managed database, and using JSONB where the app actually benefits from it. The final page is the chapter review.