PostgreSQL Advanced Query Roadmap for Novel

Goal: learn PostgreSQL query skills beyond basic SELECT, WHERE, JOIN, and GROUP BY, using examples close to the Novel database: novels, chapters, tags, users, library, views, rankings, reports, and background jobs.

Assumed Novel tables

For practice, assume a simplified schema like this:

sql

novels (
    id uuid primary key,
    slug text not null unique,
    title text not null,
    description text,
    status text not null,
    is_published boolean not null,
    metadata jsonb,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

chapters (
    id uuid primary key,
    novel_id uuid not null references novels(id),
    title text not null,
    position int not null,
    access_level text not null, -- free, vip, password
    is_published boolean not null,
    published_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

novel_views (
    id bigint generated always as identity primary key,
    novel_id uuid not null references novels(id),
    user_id uuid,
    viewed_at timestamptz not null
);

library_items (
    user_id uuid not null,
    novel_id uuid not null references novels(id),
    status text not null, -- reading, completed, dropped
    last_read_chapter_id uuid,
    updated_at timestamptz not null,
    primary key (user_id, novel_id)
);

jobs (
    id uuid primary key,
    type text not null,
    payload jsonb not null,
    status text not null,
    created_at timestamptz not null,
    locked_at timestamptz
);

1. Learn `EXPLAIN`

[ ] Understand what query plan PostgreSQL chooses.
[ ] Learn the difference between Seq Scan, Index Scan, Bitmap Heap Scan, Nested Loop, Hash Join, and Sort.
[ ] Run EXPLAIN before adding indexes.

EXPLAIN shows the execution plan PostgreSQL expects to use. This is the first tool to learn when a query is slow, because PostgreSQL performance depends heavily on the chosen plan.

Example: chapter list by novel.

sql

EXPLAIN
SELECT id, title, position
FROM chapters
WHERE novel_id = '00000000-0000-0000-0000-000000000001'
  AND is_published = true
ORDER BY position
LIMIT 50;

What to check:

text

Seq Scan on chapters

This may be bad if chapters has hundreds of thousands of rows.

Better target:

text

Index Scan using idx_chapters_novel_position

2. Learn `EXPLAIN ANALYZE`

[ ] Use EXPLAIN (ANALYZE, BUFFERS) on real slow queries.
[ ] Compare estimated rows vs actual rows.
[ ] Check whether the query reads too many buffers.
[ ] Use this before and after adding an index.

EXPLAIN ANALYZE executes the query and reports actual runtime, row counts, loops, and buffer usage. It is more useful than plain EXPLAIN when debugging real performance.

Example:

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, position
FROM chapters
WHERE novel_id = '00000000-0000-0000-0000-000000000001'
  AND is_published = true
ORDER BY position
LIMIT 50;

Focus on:

text

actual time
actual rows
Rows Removed by Filter
Buffers: shared hit/read
Sort Method

If Rows Removed by Filter is huge, your condition is filtering too late. If Sort appears on a large result, you may need an index matching the ORDER BY.

3. Learn normal B-tree indexes

[ ] Add indexes for common WHERE conditions.
[ ] Add indexes for common join columns.
[ ] Do not index every column.
[ ] Measure write overhead after adding many indexes.

PostgreSQL CREATE INDEX supports normal indexes, multicolumn indexes, expression indexes, partial indexes, and different index methods. Indexes improve read performance, but they also cost storage and make writes more expensive.

Example: optimize chapter list by novel.

sql

CREATE INDEX idx_chapters_novel_id
ON chapters (novel_id);

Good for:

sql

SELECT *
FROM chapters
WHERE novel_id = $1;

But this index does not fully help with:

sql

WHERE novel_id = $1
ORDER BY position

For that, you probably want a composite index.

4. Learn composite indexes

[ ] Understand left-to-right column order.
[ ] Match index order with query filters and sorting.
[ ] Prefer composite indexes for common list endpoints.

Example:

sql

CREATE INDEX idx_chapters_novel_position
ON chapters (novel_id, position);

Good query:

sql

SELECT id, title, position
FROM chapters
WHERE novel_id = $1
ORDER BY position
LIMIT 50;

Why this is good:

text

novel_id filters rows first
position already gives correct order inside each novel
LIMIT 50 can stop early

Bad alternative:

sql

CREATE INDEX idx_chapters_position_novel
ON chapters (position, novel_id);

This is usually worse for this query, because the query filters by novel_id first.

Novel examples:

sql

CREATE INDEX idx_chapters_novel_published_position
ON chapters (novel_id, is_published, position);

CREATE INDEX idx_library_user_updated
ON library_items (user_id, updated_at DESC);

5. Learn partial indexes

[ ] Use partial indexes for common subsets.
[ ] Great for soft delete, published content, active rows, free chapters.
[ ] Make sure your query condition matches the index predicate.

A partial index only includes rows satisfying a predicate. PostgreSQL docs describe it as an index built over a subset of a table, useful when only part of the table is frequently queried.

Novel example: most public reads only need published chapters.

sql

CREATE INDEX idx_chapters_public_by_novel_position
ON chapters (novel_id, position)
WHERE is_published = true
  AND access_level = 'free';

Query that can use it:

sql

SELECT id, title, position
FROM chapters
WHERE novel_id = $1
  AND is_published = true
  AND access_level = 'free'
ORDER BY position;

Good cases in Novel:

sql

WHERE is_published = true
WHERE status = 'active'
WHERE deleted_at IS NULL
WHERE access_level = 'free'

Do not create partial indexes for conditions that are rarely used.

6. Learn expression indexes

[ ] Use expression indexes when your query transforms a column.
[ ] Common examples: lower(title), lower(username), normalized slug.
[ ] The query expression must match the index expression.

PostgreSQL supports indexes on expressions, not just raw columns. This is useful when queries use functions or computed values.

Example: case-insensitive slug lookup.

sql

CREATE INDEX idx_novels_lower_slug
ON novels ((lower(slug)));

Query:

sql

SELECT id, title
FROM novels
WHERE lower(slug) = lower($1);

Example: case-insensitive user lookup.

sql

CREATE INDEX idx_users_lower_username
ON users ((lower(username)));

Avoid doing this without an index on a large table:

sql

WHERE lower(username) = lower($1)

because PostgreSQL may need to compute lower(username) for many rows.

7. Learn `DISTINCT ON`

[ ] Use it to get the first row per group.
[ ] Great for “latest chapter per novel”.
[ ] Always control the chosen row with ORDER BY.

DISTINCT ON is PostgreSQL-specific. It keeps one row for each distinct group, and the ORDER BY decides which row is kept. PostgreSQL SELECT docs describe SELECT DISTINCT ON as eliminating rows that match on the specified expressions.

Example: latest published chapter per novel.

sql

SELECT DISTINCT ON (novel_id)
    novel_id,
    id AS chapter_id,
    title AS chapter_title,
    position,
    published_at
FROM chapters
WHERE is_published = true
ORDER BY novel_id, published_at DESC;

Use case in Novel homepage:

text

Show novel card + latest chapter

Alternative with window function:

sql

SELECT *
FROM (
    SELECT
        c.*,
        row_number() OVER (
            PARTITION BY novel_id
            ORDER BY published_at DESC
        ) AS rn
    FROM chapters c
    WHERE is_published = true
) x
WHERE rn = 1;

DISTINCT ON is often shorter and easier for this pattern.

8. Learn window functions

[ ] Learn row_number().
[ ] Learn rank() and dense_rank().
[ ] Learn lag() and lead().
[ ] Use window functions when you need aggregate-like values without collapsing rows.

Window functions compute values across related rows while still returning individual rows. They are useful for ranking, previous/next rows, running totals, and grouped calculations.

Example: rank novels by views.

sql

SELECT
    novel_id,
    views_count,
    dense_rank() OVER (ORDER BY views_count DESC) AS rank
FROM novel_stats;

Example: previous and next chapter.

sql

SELECT
    id,
    novel_id,
    position,
    lag(id) OVER (
        PARTITION BY novel_id
        ORDER BY position
    ) AS previous_chapter_id,
    lead(id) OVER (
        PARTITION BY novel_id
        ORDER BY position
    ) AS next_chapter_id
FROM chapters
WHERE novel_id = $1
  AND is_published = true;

This is useful if you want to precompute navigation data. But for a single chapter detail endpoint, two indexed queries may be simpler:

sql

SELECT id
FROM chapters
WHERE novel_id = $1
  AND is_published = true
  AND position < $current_position
ORDER BY position DESC
LIMIT 1;

SELECT id
FROM chapters
WHERE novel_id = $1
  AND is_published = true
  AND position > $current_position
ORDER BY position ASC
LIMIT 1;

9. Learn `LATERAL JOIN`

[ ] Use it for “top N children per parent”.
[ ] Use it when the subquery needs data from the current row.
[ ] Very useful for homepage cards.

PostgreSQL SELECT supports LATERAL, allowing a subquery in FROM to reference columns from earlier FROM items.

Example: list novels and their latest 3 chapters.

sql

SELECT
    n.id AS novel_id,
    n.title AS novel_title,
    c.id AS chapter_id,
    c.title AS chapter_title,
    c.position
FROM novels n
LEFT JOIN LATERAL (
    SELECT id, title, position
    FROM chapters c
    WHERE c.novel_id = n.id
      AND c.is_published = true
    ORDER BY c.published_at DESC
    LIMIT 3
) c ON true
WHERE n.is_published = true
ORDER BY n.updated_at DESC
LIMIT 20;

Use case:

text

Homepage:
- Novel title
- Cover
- Latest 3 chapters

Without LATERAL, this often becomes either multiple queries or a more complex window-function query.

10. Learn CTEs with `WITH`

[ ] Use CTEs to make complex queries readable.
[ ] Learn when WITH helps readability but not necessarily performance.
[ ] Learn MATERIALIZED and NOT MATERIALIZED.

PostgreSQL docs describe WITH queries as auxiliary statements for a larger query, commonly known as Common Table Expressions or CTEs.

Example: search novels, then join stats.

sql

WITH matched_novels AS (
    SELECT id, title
    FROM novels
    WHERE is_published = true
      AND title ILIKE '%' || $1 || '%'
)
SELECT
    n.id,
    n.title,
    s.views_count
FROM matched_novels n
LEFT JOIN novel_stats s ON s.novel_id = n.id
ORDER BY s.views_count DESC NULLS LAST
LIMIT 20;

Use CTEs when:

text

query has many logical steps
you want readable SQL
you want to reuse a result inside one query

But always check EXPLAIN ANALYZE if the query is slow.

11. Learn keyset pagination

[ ] Avoid deep OFFSET.
[ ] Use stable sort keys.
[ ] Use (updated_at, id) or (position, id) for cursor pagination.

Bad for deep pages:

sql

SELECT id, title
FROM novels
WHERE is_published = true
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100000;

Why bad:

text

PostgreSQL still has to walk through many skipped rows.

Better: keyset pagination.

sql

SELECT id, title, updated_at
FROM novels
WHERE is_published = true
  AND (updated_at, id) < ($last_updated_at, $last_id)
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Index:

sql

CREATE INDEX idx_novels_published_updated_id
ON novels (updated_at DESC, id DESC)
WHERE is_published = true;

Novel use cases:

text

novel listing
latest updates
user library
chapter list
notifications

For chapters:

sql

SELECT id, title, position
FROM chapters
WHERE novel_id = $1
  AND is_published = true
  AND position > $last_position
ORDER BY position
LIMIT 50;

12. Learn `INSERT ... ON CONFLICT`

[ ] Use it for upsert.
[ ] Use it instead of “check then insert”.
[ ] Combine it with unique constraints.

PostgreSQL supports ON CONFLICT on INSERT, commonly used for upsert behavior.

Example: update reading progress.

sql

INSERT INTO library_items (
    user_id,
    novel_id,
    status,
    last_read_chapter_id,
    updated_at
)
VALUES (
    $1,
    $2,
    'reading',
    $3,
    now()
)
ON CONFLICT (user_id, novel_id)
DO UPDATE SET
    last_read_chapter_id = excluded.last_read_chapter_id,
    updated_at = excluded.updated_at;

This is better than:

text

SELECT exists
IF exists UPDATE
ELSE INSERT

because the app-only check can race when two requests happen at the same time.

13. Learn `RETURNING`

[ ] Use RETURNING after insert/update/delete.
[ ] Avoid an extra SELECT.
[ ] Return only the columns needed by the app.

PostgreSQL supports RETURNING for data-modifying statements, allowing changed rows to be returned immediately.

Example: create chapter and return the row.

sql

INSERT INTO chapters (
    id,
    novel_id,
    title,
    position,
    access_level,
    is_published,
    created_at,
    updated_at
)
VALUES (
    gen_random_uuid(),
    $1,
    $2,
    $3,
    'free',
    false,
    now(),
    now()
)
RETURNING id, novel_id, title, position, created_at;

Example: update novel title.

sql

UPDATE novels
SET title = $2,
    updated_at = now()
WHERE id = $1
RETURNING id, slug, title, updated_at;

This is very useful in CQRS command handlers because you can update and build the response in one round-trip.

14. Learn JSONB queries

[ ] Use JSONB for flexible metadata.
[ ] Learn @>, ->, and ->>.
[ ] Index JSONB only when you query it frequently.
[ ] Do not hide relational data inside JSONB if you need joins or constraints.

Example metadata:

json

{
	"source": "manual",
	"license": "unknown",
	"crawler": {
		"provider": "old-system"
	}
}

Query novels imported from old system:

sql

SELECT id, title
FROM novels
WHERE metadata @> '{"source": "old-system"}';

Read a JSON field as text:

sql

SELECT
    id,
    title,
    metadata ->> 'source' AS source
FROM novels;

Good JSONB uses in Novel:

text

crawler metadata
legacy migration metadata
external provider IDs
feature flags for rare cases

Bad JSONB uses:

text

chapter title
novel status
access level
author relation
tag relation

Those should stay as normal columns/tables.

15. Learn GIN indexes

[ ] Use GIN for JSONB.
[ ] Use GIN for full-text search.
[ ] Use GIN for array-like containment queries.
[ ] Remember GIN indexes can be heavier than B-tree indexes.

GIN means Generalized Inverted Index. PostgreSQL docs say GIN is designed for cases where indexed items are composite values, such as documents containing multiple elements.

JSONB index:

sql

CREATE INDEX idx_novels_metadata_gin
ON novels USING gin (metadata jsonb_path_ops);

Query:

sql

SELECT id, title
FROM novels
WHERE metadata @> '{"source": "old-system"}';

Full-text index example:

sql

CREATE INDEX idx_novels_search_vector
ON novels USING gin (search_vector);

Use GIN carefully:

text

good for read-heavy search/filter
can slow down writes
can take more storage
must be verified with EXPLAIN ANALYZE

16. Learn PostgreSQL full-text search

[ ] Learn tsvector.
[ ] Learn to_tsvector.
[ ] Learn plainto_tsquery.
[ ] Learn ts_rank.
[ ] Use GIN index for search vectors.

PostgreSQL has built-in full-text search with documents, queries, ranking, highlighting, and indexing support.

Example: generated search vector for novels.

sql

ALTER TABLE novels
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'B')
) STORED;

Index:

sql

CREATE INDEX idx_novels_search_vector
ON novels USING gin (search_vector);

Query:

sql

SELECT
    id,
    title,
    ts_rank(search_vector, plainto_tsquery('simple', $1)) AS rank
FROM novels
WHERE is_published = true
  AND search_vector @@ plainto_tsquery('simple', $1)
ORDER BY rank DESC
LIMIT 20;

For Vietnamese content, built-in full-text search may need extra design. For Novel, a practical approach is:

text

title search: pg_trgm
description search: simple full-text search
advanced search: later Meilisearch/OpenSearch if needed

17. Learn `pg_trgm`

[ ] Use it for fuzzy search.
[ ] Use it for title search, author search, tag search.
[ ] Add GIN trigram indexes for ILIKE '%keyword%'.

pg_trgm is a PostgreSQL extension for trigram matching. It is commonly used for fuzzy text search and accelerating LIKE / ILIKE patterns. The PostgreSQL extension system is one of the reasons PostgreSQL can add powerful specialized behavior without changing the core schema design.

Enable extension:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Index novel title:

sql

CREATE INDEX idx_novels_title_trgm
ON novels USING gin (title gin_trgm_ops);

Search:

sql

SELECT id, title
FROM novels
WHERE title ILIKE '%' || $1 || '%'
ORDER BY similarity(title, $1) DESC
LIMIT 20;

Good Novel use cases:

text

search novel title
search creator name
search tag name
admin quick search

18. Learn materialized views

[ ] Use materialized views for expensive aggregate queries.
[ ] Add indexes on materialized views.
[ ] Learn refresh strategy.
[ ] Use them for ranking and homepage stats.

PostgreSQL materialized views persist query results in table-like form, unlike normal views that are computed when queried.

Example: total views per novel.

sql

CREATE MATERIALIZED VIEW novel_view_totals AS
SELECT
    novel_id,
    count(*) AS total_views,
    count(*) FILTER (
        WHERE viewed_at >= now() - interval '7 days'
    ) AS views_7d
FROM novel_views
GROUP BY novel_id;

Index:

sql

CREATE UNIQUE INDEX idx_novel_view_totals_novel_id
ON novel_view_totals (novel_id);

CREATE INDEX idx_novel_view_totals_views_7d
ON novel_view_totals (views_7d DESC);

Query ranking:

sql

SELECT
    n.id,
    n.title,
    r.views_7d
FROM novel_view_totals r
JOIN novels n ON n.id = r.novel_id
WHERE n.is_published = true
ORDER BY r.views_7d DESC
LIMIT 20;

Refresh:

sql

REFRESH MATERIALIZED VIEW novel_view_totals;

REFRESH MATERIALIZED VIEW replaces the content of the materialized view by re-running its backing query.

For Novel, refresh can be done by Quartz every few minutes:

text

every 5 minutes: refresh homepage/ranking materialized views
nightly: refresh heavier stats

19. Learn locking queries

[ ] Learn FOR UPDATE.
[ ] Learn SKIP LOCKED.
[ ] Use for background jobs and outbox processing.
[ ] Understand deadlocks.

Example: multiple workers safely pick jobs.

sql

BEGIN;

WITH picked_jobs AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs j
SET status = 'processing',
    locked_at = now()
FROM picked_jobs p
WHERE j.id = p.id
RETURNING j.*;

COMMIT;

Use cases in Novel:

text

process chapter media
generate thumbnails
sync search index
send notifications
refresh denormalized stats

Why SKIP LOCKED matters:

text

Worker A locks 10 jobs.
Worker B skips those locked jobs and picks other jobs.
They do not block each other unnecessarily.

20. Learn transactions, isolation, and race conditions

[ ] Understand READ COMMITTED.
[ ] Understand REPEATABLE READ.
[ ] Understand SERIALIZABLE.
[ ] Use unique constraints to prevent duplicates.
[ ] Use locks when modifying ordered data.
[ ] Test concurrent requests.

PostgreSQL supports transaction isolation levels and explicit locking. For application correctness, constraints and transaction design are usually safer than checking conditions only in application code.

Example problem: creating a chapter at the next position.

Naive flow:

text

SELECT max(position)
INSERT position = max + 1

This can race if two requests create chapters at the same time.

Better: lock rows for the novel.

sql

BEGIN;

SELECT id
FROM chapters
WHERE novel_id = $1
ORDER BY position DESC
FOR UPDATE;

INSERT INTO chapters (
    id,
    novel_id,
    title,
    position,
    access_level,
    is_published,
    created_at,
    updated_at
)
VALUES (
    gen_random_uuid(),
    $1,
    $2,
    (
        SELECT coalesce(max(position), 0) + 1
        FROM chapters
        WHERE novel_id = $1
    ),
    'free',
    false,
    now(),
    now()
)
RETURNING id, position;

COMMIT;

Also add a unique constraint:

sql

ALTER TABLE chapters
ADD CONSTRAINT uq_chapters_novel_position
UNIQUE (novel_id, position);

That way, even if app logic fails, the database still protects the invariant.

Suggested 6-week study plan

Week 1: Query plan basics

[ ] Practice EXPLAIN.
[ ] Practice EXPLAIN (ANALYZE, BUFFERS).
[ ] Analyze chapter list query.
[ ] Analyze novel detail query.
[ ] Analyze library list query.

Practice queries:

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, position
FROM chapters
WHERE novel_id = $1
ORDER BY position
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM novels
WHERE slug = $1;

Week 2: Index design

[ ] Learn B-tree indexes.
[ ] Learn composite indexes.
[ ] Learn partial indexes.
[ ] Learn expression indexes.
[ ] Add indexes only after measuring.

Practice indexes:

sql

CREATE INDEX idx_chapters_novel_position
ON chapters (novel_id, position);

CREATE INDEX idx_chapters_public_by_novel_position
ON chapters (novel_id, position)
WHERE is_published = true;

CREATE INDEX idx_novels_lower_slug
ON novels ((lower(slug)));

Week 3: Query patterns for lists and cards

[ ] Learn DISTINCT ON.
[ ] Learn window functions.
[ ] Learn LATERAL JOIN.
[ ] Build homepage query examples.

Practice:

sql

SELECT DISTINCT ON (novel_id)
    novel_id,
    id,
    title,
    published_at
FROM chapters
WHERE is_published = true
ORDER BY novel_id, published_at DESC;

Week 4: Write queries and pagination

[ ] Learn keyset pagination.
[ ] Learn ON CONFLICT.
[ ] Learn RETURNING.
[ ] Replace check-then-insert flows.

Practice:

sql

INSERT INTO library_items (...)
VALUES (...)
ON CONFLICT (user_id, novel_id)
DO UPDATE SET updated_at = now()
RETURNING *;

Week 5: Search and flexible data

[ ] Learn JSONB query operators.
[ ] Learn GIN indexes.
[ ] Learn full-text search.
[ ] Learn pg_trgm.

Practice:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_novels_title_trgm
ON novels USING gin (title gin_trgm_ops);

Week 6: Ranking, jobs, and concurrency

[ ] Learn materialized views.
[ ] Learn REFRESH MATERIALIZED VIEW.
[ ] Learn FOR UPDATE SKIP LOCKED.
[ ] Learn transaction isolation basics.
[ ] Test concurrent chapter creation.

Practice:

sql

CREATE MATERIALIZED VIEW novel_view_totals AS
SELECT novel_id, count(*) AS total_views
FROM novel_views
GROUP BY novel_id;

Practical checklist for Novel

Chapter list endpoint

[ ] Query by novel_id.
[ ] Filter is_published.
[ ] Sort by position.
[ ] Add composite or partial index.
[ ] Confirm with EXPLAIN ANALYZE.

Recommended index:

sql

CREATE INDEX idx_chapters_public_by_novel_position
ON chapters (novel_id, position)
WHERE is_published = true;

Novel detail endpoint

[ ] Query by slug or id.
[ ] Add unique index on slug.
[ ] Consider lower(slug) if case-insensitive.
[ ] Fetch latest chapter with LATERAL or separate indexed query.

Recommended index:

sql

CREATE UNIQUE INDEX uq_novels_slug
ON novels (slug);

Homepage latest updates

[ ] Use keyset pagination.
[ ] Use latest chapter per novel.
[ ] Avoid OFFSET for deep pages.
[ ] Consider materialized view if query becomes heavy.

Recommended pattern:

sql

SELECT *
FROM novels
WHERE is_published = true
  AND (updated_at, id) < ($last_updated_at, $last_id)
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Ranking endpoint

[ ] Do not count views from raw table on every request.
[ ] Use summary table or materialized view.
[ ] Refresh by Quartz.
[ ] Index ranking columns.

Recommended pattern:

sql

CREATE MATERIALIZED VIEW novel_rankings_daily AS
SELECT
    novel_id,
    count(*) AS views_count
FROM novel_views
WHERE viewed_at >= now() - interval '1 day'
GROUP BY novel_id;

Search endpoint

[ ] Use pg_trgm for title search.
[ ] Use full-text search for longer description search.
[ ] Use GIN indexes.
[ ] Measure write cost.

Recommended title search:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_novels_title_trgm
ON novels USING gin (title gin_trgm_ops);

Background jobs

[ ] Use FOR UPDATE SKIP LOCKED.
[ ] Keep transactions short.
[ ] Make job handling idempotent.
[ ] Retry failed jobs safely.

Recommended worker query:

sql

WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs j
SET status = 'processing',
    locked_at = now()
FROM picked p
WHERE j.id = p.id
RETURNING j.*;

Final priority order

For Novel, learn in this order:

EXPLAIN (ANALYZE, BUFFERS)
Composite indexes
Partial indexes
Keyset pagination
DISTINCT ON
Window functions
LATERAL JOIN
ON CONFLICT
RETURNING
Materialized views
pg_trgm
Full-text search
JSONB + GIN
FOR UPDATE SKIP LOCKED
Transaction isolation and race-condition testing

This order gives the most direct benefit for Novel API performance: chapter list, novel detail, homepage, ranking, search, and background jobs.

PostgreSQL Advanced Query Roadmap for Novel ​

Assumed Novel tables ​

1. Learn EXPLAIN ​

2. Learn EXPLAIN ANALYZE ​

3. Learn normal B-tree indexes ​

4. Learn composite indexes ​

5. Learn partial indexes ​

6. Learn expression indexes ​

7. Learn DISTINCT ON ​

8. Learn window functions ​

9. Learn LATERAL JOIN ​

10. Learn CTEs with WITH ​

11. Learn keyset pagination ​

12. Learn INSERT ... ON CONFLICT ​

13. Learn RETURNING ​

14. Learn JSONB queries ​

15. Learn GIN indexes ​

16. Learn PostgreSQL full-text search ​

17. Learn pg_trgm ​

18. Learn materialized views ​

19. Learn locking queries ​

20. Learn transactions, isolation, and race conditions ​

Suggested 6-week study plan ​

Week 1: Query plan basics ​

Week 2: Index design ​

Week 3: Query patterns for lists and cards ​

Week 4: Write queries and pagination ​

Week 5: Search and flexible data ​

Week 6: Ranking, jobs, and concurrency ​

Practical checklist for Novel ​

Chapter list endpoint ​

Novel detail endpoint ​

Homepage latest updates ​

Ranking endpoint ​

Search endpoint ​

Background jobs ​

Final priority order ​

PostgreSQL Advanced Query Roadmap for Novel

Assumed Novel tables

1. Learn `EXPLAIN`

2. Learn `EXPLAIN ANALYZE`

3. Learn normal B-tree indexes

4. Learn composite indexes

5. Learn partial indexes

6. Learn expression indexes

7. Learn `DISTINCT ON`

8. Learn window functions

9. Learn `LATERAL JOIN`

10. Learn CTEs with `WITH`

11. Learn keyset pagination

12. Learn `INSERT ... ON CONFLICT`

13. Learn `RETURNING`

14. Learn JSONB queries

15. Learn GIN indexes

16. Learn PostgreSQL full-text search

17. Learn `pg_trgm`

18. Learn materialized views

19. Learn locking queries

20. Learn transactions, isolation, and race conditions

Suggested 6-week study plan

Week 1: Query plan basics

Week 2: Index design

Week 3: Query patterns for lists and cards

Week 4: Write queries and pagination

Week 5: Search and flexible data

Week 6: Ranking, jobs, and concurrency

Practical checklist for Novel

Chapter list endpoint

Novel detail endpoint

Homepage latest updates

Ranking endpoint

Search endpoint

Background jobs

Final priority order