Dev logs
Running notes from building the term project. What broke, what fixed it, what's worth remembering for next time.
Aggregation and Cleaning Up Messy Data
This week moved away from filtering individual rows and into summarizing sets of them. The content strategy report used GROUP BY genre with COUNT() and AVG() to see which genres had the most reviews and the highest average scores. The HAVING clause filtered the grouped results down to genres with more than one review and an average above 7.0.
The reason you cannot use WHERE for that filter is that WHERE runs before grouping happens. At the point WHERE executes, the COUNT() and AVG() values do not exist yet. HAVING runs after GROUP BY, so the aggregated values are available to filter against.
COALESCE was new to me in practice. It takes a list of values and returns the first one that is not NULL. The homepage cleanup query used it to replace NULL user_email values with 'Anonymous Streamer'. On older MySQL projects this kind of display logic usually ended up in the application layer. Putting it in the query keeps the frontend from having to handle the NULL case at all.
The CASE expression for sentiment labeling worked like a standard conditional. Ratings 8.5 and above got 'Highly Acclaimed', 7.0 to 8.4 got 'Positive', anything lower got 'Mixed/Negative'. The main thing this week reinforced is that a lot of what feels like frontend logic can be pushed down into the query itself, which cuts round trips and keeps the transformation close to the data.
Joins and What Gets Left Out
The dataset had five movies, five ratings, one orphan (a rating with movie_id = NULL), and one unreviewed movie (Gladiator II with no ratings). The whole week was about thinking through which join to use based on what you want to exclude, not just what you want to include.
INNER JOIN returned only the four rows where both sides matched. The orphan disappeared. Gladiator II disappeared. Right for a live feed, wrong for an audit. LEFT JOIN from movies kept all five movies and matched ratings where they existed. Filtering on WHERE user_rating IS NULL surfaced only Gladiator II.
FULL OUTER JOIN kept everything: four valid pairs, Gladiator II with NULLs on the ratings side, and the ghost review with a NULL title. MySQL does not have FULL OUTER JOIN natively — you simulate it with a LEFT JOIN unioned with a RIGHT JOIN. Postgres supports it directly.
The practical difference between INNER and LEFT is what happens to unmatched rows from the left table. INNER drops them. LEFT keeps them with NULLs.
Filtering and a NULL Quirk
Nothing in this week's queries was surprising coming from MySQL. BETWEEN, AND, ILIKE — the catalog filter and keyword search worked as expected. ILIKE is Postgres-specific. MySQL handles case sensitivity at the collation level. In Postgres, LIKE is always case-sensitive unless you use ILIKE or cast to lowercase first.
The NULL behavior is worth noting. WHERE rating = NULL returns zero rows, always. SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. NULL is unknown. Comparing anything to an unknown value produces UNKNOWN, not FALSE. The WHERE clause only includes rows where the condition is TRUE, so every row gets filtered out — including the ones where rating is actually NULL.
IS NULL is a predicate, not a comparison. It does not evaluate to UNKNOWN. That is why it works. BETWEEN includes both endpoints, which trips people up. 1990 AND 1999 means you get 1990 and 1999, not just the years in between.
JSONB as a Pressure Valve
Added a details JSONB column to movies. Interstellar got technical specs: resolution, audio format, a cast array. Toy Story got studio, voice cast, an is_animated flag. Different keys, different structures, same column.
The rigid alternative is a column for every possible attribute. Most of them NULL for most rows. A table like that is hard to query and painful to extend. Every new attribute type is an ALTER TABLE, which on a production database means a migration and coordination overhead. JSONB lets each row store only what is relevant to it.
Arrow operator -> gives you JSON. Double arrow ->> gives you text. Used the wrong one in a WHERE clause and nothing worked — silent failure is the worst. The @> operator checks if JSON contains something. The GIN index indexes the keys and values inside the JSON, not just the column. That is what makes containment queries fast. B-Tree does not work here.
The cost of JSONB is that you lose column-level type enforcement. Postgres will not stop you from storing a string in a field you are treating as a number. Valid tradeoff for metadata, not for columns that core business logic depends on.
EXPLAIN ANALYZE
Seeded one million rows into ratings and ran the query unindexed. The plan showed a Sequential Scan: every row read, most of them discarded. Around 900,000 rows removed by filter. Execution time was several hundred milliseconds.
MySQL has EXPLAIN. Postgres has EXPLAIN ANALYZE, which actually runs the query and reports real execution times alongside the estimated plan. MySQL's EXPLAIN shows what the query planner thinks will happen. Postgres shows what actually happened. That distinction matters when you are debugging a slow query.
Added a B-Tree index on score. Same query, the plan switched to an Index Scan. Execution time dropped around 90%. The cost of an index is write overhead and disk space. Every insert or update has to update the index too. For score — a column that gets filtered constantly and changes rarely — that tradeoff is straightforward.
Foreign Keys Do the Work Your App Code Shouldn't
Added a ratings table with a foreign key back to movies using ON DELETE RESTRICT. The constraint means you cannot delete a movie that has ratings. The database stops you at the query level. The alternative is trusting every code path that deletes movies to also clean up ratings first.
ON DELETE CASCADE is the other common option, where deleting a movie removes its ratings automatically. For user-generated content that feels wrong. Ratings represent activity that happened. Silently removing them when a movie gets delisted is a different kind of integrity problem.
Tried adding a rating for movie_id 9999 (doesn't exist) and got rejected. Foreign key doing its job. Postgres as a traditional relational database requires data to conform to the table structure before it gets stored (Schema-on-Write). Data Lakes let you dump raw logs and sort them out later — pick based on how fast data's coming in.
ALTER TABLE and What It Costs
Built the StreamFlix movies table from scratch with CREATE TABLE, then immediately had to evolve it using ALTER TABLE to add a viewer_rating column and RENAME COLUMN to rebrand genre into category. On a dev database with five rows it is instant. On a production table with millions of rows, some ALTER TABLE operations cause a full table rewrite and block writes for the duration.
The TEMPORARY TABLE for session tracking was a concept I had not used much. The table exists only for the current session and drops automatically when the connection closes. Useful for intermediate results in a multi-step query, or session-specific state that does not belong in a permanent table.
TRUNCATE clears the table but keeps the structure. DROP deletes the whole thing. Took longer to understand than it should have. The Big Data question: at 500 million events per hour, Postgres can't keep up anymore. That's when you stop adding more RAM and switch to Spark. Postgres scales vertically. Spark scales horizontally, distributing the computation across machines. Know when to pick the right tool before you're drowning in data.
Type Decisions That Actually Matter
MySQL and Postgres handle types differently in a few places that bit me early on. MySQL is more permissive about what it will accept into a column. Postgres is stricter, and that strictness is useful.
The plant schema had a price column. FLOAT uses binary floating-point, which means $3.50 can end up stored as 3.4999999 internally. Fine for scientific data, wrong for money. NUMERIC(5,2) stores exact decimal values. The metadata column for external API data went to JSONB — MySQL has a JSON type now but it does not support the same indexing options Postgres JSONB does.
Ran into a UUID issue: decided to set a default to generate a random UUID if none is provided. JSONB syntax also caused confusion between single quotes and JSON format. Worth remembering: JSONB isn't lazy design, it's strategic flexibility for data you don't control.
No SSH Required
Coming from running MySQL on a VPS, the Supabase setup was disorienting. No apt install, no mysql_secure_installation, no opening ports in ufw, no .cnf file to dig through. You fill out a form, pick a region, wait two minutes, and there is a Postgres instance with a connection string ready to go.
The tradeoff is real though. On a VPS you know exactly what is running and where. You can inspect the config, tail the logs, tune max_connections yourself. Supabase abstracts all of that. You gain time. You give up visibility into the layer underneath.
The first query was just SELECT version(); to confirm the connection. PostgreSQL 15, running on hardware I will never touch. Worth remembering: Supabase hides all the DevOps complexity but you still need to save that database password somewhere safe — they're not storing it for you.