Blog Platform with Tags, Comments, and Full-Text Search

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with authors, posts (author, title, body, status draft or published, published at), tags (unique name), post_tags (a junction table with a composite primary key) and comments (post, author name, body, created at, and parent_id pointing to another comment, null for top-level comments). Add foreign keys with sensible ON DELETE rules and a check that a reply belongs to the same post as its parent (with a trigger). Done when: the schema loads without errors.

2. 02

   Add sample data

   ~1.5h

   Write seed.sql with about 10 authors, 200 posts with realistic titles and a few paragraphs of text, 15 tags with 1 to 4 per post, and several thousand comments where some are replies several levels deep. Use generate_series and random() for volume, plus some hand-written posts to test search with. Done when: the data loads, and some comment threads are at least 4 levels deep.

3. 03

   Set up full-text search

   ~1.5h

   Add a generated tsvector column combining the title (weight A) and body (weight B) with to_tsvector('english', ...) and setweight, and create a GIN index on it. Done when: a search with @@ and websearch_to_tsquery returns matching posts, and EXPLAIN shows the GIN index being used.

4. 04

   Write a ranked search

   ~1.5h

   Write a search query for published posts using websearch_to_tsquery (which understands quotes and minus signs from users), ranked by ts_rank with title matches counting more, with a highlighted snippet from ts_headline, and paginated. Wrap it in a SQL function search_posts(query, limit, offset). Done when: searching running finds posts containing run, and title matches rank higher.

5. 05

   Rebuild comment threads

   ~2h

   Write a recursive CTE that returns a post's comments in thread order: each top-level comment followed by its replies, depth first, with a depth column for indentation and a path array for ordering. Limit the depth to guard against bad data. Done when: the output lists every comment of a post in the order a reader would see them, with the right depth.

6. 06

   Report the most discussed posts

   ~1.5h

   Write a query ranking posts by comment count within each tag, using a window function (RANK or ROW_NUMBER over a partition by tag), returning the top 3 per tag, including posts with no comments shown as 0. Save it as a view. Done when: each tag lists its top 3 posts with correct comment counts.

7. 07

   Check performance and test

   ~1.5h

   Run EXPLAIN ANALYZE on the search, thread and ranking queries, add indexes where needed (for example on comments(post_id, parent_id)), and note the before and after timings in README.md. Write test.sql with checks on known data: search results, thread order and the reply trigger. Done when: test.sql passes, and README.md shows the timings.

Resources

• DocsPostgreSQL: full-text search
• DocsPostgreSQL: WITH queries (recursive CTEs)
• DocsPostgreSQL: GIN indexes
• DocsPostgreSQL: using EXPLAIN
• DocspgAdmin documentation