When To Use This Checklist

A small Flask blog can run on SQLite for a surprisingly long time. The risky part is not SQLite itself; the risky part is operating without a query baseline. As the site grows, the home page, category pages, article detail pages, sitemap generation, tag pages, comments, search logs, and admin screens all add small database reads. One slow page then becomes hard to explain. Is the data set larger? Did a template loop add extra queries? Did a new filter defeat an index? Or is search doing a full scan across Markdown bodies?

This checklist is written for a production-style Flask blog like Stepnex: SQLAlchemy models, a local database.db, public article pages, bilingual routes, tags, categories, comments, and scheduled publishing. It is not a migration guide. The goal is to make the SQLite phase measurable before you decide whether a migration is necessary. If you are already planning a database move, pair this article with the internal guide Migrating a Flask Blog from SQLite to MySQL. If you want operational context around request logs, also read Nginx Access Log Review for a Flask Blog.

The practical tradeoff is simple. Do not add indexes to every column just because indexes sound fast. Also do not wait until the site is visibly slow before taking notes. A personal blog usually has a small number of high-value paths: public feeds, category feeds, article detail lookup, sitemap generation, admin pagination, and comment moderation. Those paths deserve a repeatable baseline with SQL, command output, logs, and a review checklist.

Map The Real Query Paths

The first step is not creating an index. The first step is naming the queries that matter. In this application shape, the home page usually filters by status='published' and the current language, then sorts by is_top, published_at, and created_at. A category page adds category_id. An article detail page looks up one row by slug, language, and status. A sitemap walks every published article. The admin article list orders by creation time and may add a status filter. Search is a separate case because LIKE '%keyword%' over title, summary, and body behaves very differently from a feed query.

Create a small query inventory before changing the schema. Use columns such as route, SQLAlchemy expression, filter columns, sort columns, expected row count, and acceptance threshold. The threshold does not have to be scientific at first. For example: the home page should render under 300 ms with 1,000 local articles; sitemap generation should stay under one second; admin page ten should still feel responsive. These numbers are not promises to search engines or users. They are regression tripwires for your own maintenance work.

Start with this list:

  1. Home feed: filter by status and language; sort by is_top, published_at, and created_at.
  2. Category feed: filter by status, language, and category_id; sort by publish time.
  3. Article detail: exact lookup by slug, language, and status.
  4. Sitemap: list all published articles by language and update time.
  5. Admin list: paginate articles by created_at, with optional status filtering.
  6. Search: keyword matching across title, summary, and content.

The main pitfall is mixing search with ordinary feed performance. A B-tree index can help a feed query that filters and sorts predictable columns. It will not magically solve a contains search across Markdown text. If search becomes important, plan for SQLite FTS5 or another search layer. Keep it outside the first indexing pass so the feed pages get a clean, low-risk improvement.

Capture SQLAlchemy Output Before Tuning

The second step is to capture the SQL that the application actually sends. Many performance issues are not visible from the model definition. A template may touch a relationship inside a loop. Pagination may run a count query that is heavier than the item query. A route may join tags for related articles when a simple category lookup would be enough.

In development, you can temporarily enable SQLAlchemy engine output. In production, avoid logging every SQL statement for long periods because it can create noisy logs and leak parameters. A safer approach is to compile specific baseline statements inside an application context.

Example configuration for a local run:

APP_ENV=development
QUERY_BASELINE_ENABLED=true
SQLALCHEMY_ECHO=false

Then run a targeted command:

python - <<'PY'
from app import app, Article
with app.app_context():
    query = Article.query.filter_by(status="published", language="en").order_by(
        Article.is_top.desc(), Article.published_at.desc(), Article.created_at.desc()
    ).limit(8)
    print(query.statement.compile(compile_kwargs={"literal_binds": True}))
PY

On Windows PowerShell:

@'
from app import app, Article
with app.app_context():
    query = Article.query.filter_by(status="published", language="en").order_by(
        Article.is_top.desc(), Article.published_at.desc(), Article.created_at.desc()
    ).limit(8)
    print(query.statement.compile(compile_kwargs={"literal_binds": True}))
'@ | python -

Save the compiled SQL with the route name and the date. For a small team, a Markdown file such as docs/query-baseline.md is enough. Add three pieces of information for each route: the SQL shape, the expected index, and where to find the relevant log. The Nginx access log tells you which URL is slow. The application log tells you whether the request hit a database error or an unexpected branch. The SQL baseline tells you whether the database plan still matches your assumptions.

Validate With EXPLAIN QUERY PLAN

After you have the SQL, validate it with SQLite. The useful command is EXPLAIN QUERY PLAN. It shows whether SQLite is scanning a table or searching with an index. A public feed that repeatedly shows SCAN article deserves attention. A detail lookup that already hits the unique slug index may not be the real bottleneck.

Start with local commands:

sqlite3 database.db ".indexes article"
sqlite3 database.db "EXPLAIN QUERY PLAN SELECT id,title,slug,published_at FROM article WHERE status='published' AND language='en' ORDER BY is_top DESC, published_at DESC, created_at DESC LIMIT 8;"
sqlite3 database.db "EXPLAIN QUERY PLAN SELECT id,title,slug FROM article WHERE status='published' AND language='en' AND category_id=1 ORDER BY published_at DESC, created_at DESC LIMIT 8;"

If sqlite3 is not available in the environment, use Python:

python - <<'PY'
import sqlite3
con = sqlite3.connect("database.db")
sql = "SELECT id,title,slug FROM article WHERE status=? AND language=? ORDER BY published_at DESC, created_at DESC LIMIT 8"
for row in con.execute("EXPLAIN QUERY PLAN " + sql, ("published", "en")):
    print(row)
PY

The validation method should be repeatable. Record the plan before the index, add one index, record the plan after the index, and then load the affected pages. Do not stop at a nicer plan line. SQLite indexes improve reads, but they also increase write cost and database size. On a blog that publishes a few articles per day, that cost is usually fine. It should still be written down so a future maintainer knows why the index exists.

Add A Minimal Index Set

In the current model shape, slug, language, status, and title may already have single-column indexes. Single-column indexes are not always enough for a feed query that filters multiple columns and sorts by time. A small compound index set is usually more useful than many isolated indexes.

For a Flask blog like this, start with public feed and category feed paths:

CREATE INDEX IF NOT EXISTS ix_article_public_feed
ON article (status, language, is_top, published_at, created_at);

CREATE INDEX IF NOT EXISTS ix_article_category_feed
ON article (category_id, status, language, published_at, created_at);

CREATE INDEX IF NOT EXISTS ix_article_translation_status
ON article (translation_key, status, language);

If you use Flask-Migrate, put these indexes into a migration instead of typing them directly on the server. If you do not have a migration workflow yet, create a controlled maintenance script and run it after a backup.

Backup and migration commands:

cp database.db backups/database-before-index-$(date +%Y%m%d%H%M%S).db
flask db migrate -m "add article feed indexes"
flask db upgrade

Controlled script shape:

from app import app, db

INDEX_SQL = [
    "CREATE INDEX IF NOT EXISTS ix_article_public_feed ON article (status, language, is_top, published_at, created_at)",
    "CREATE INDEX IF NOT EXISTS ix_article_category_feed ON article (category_id, status, language, published_at, created_at)",
    "CREATE INDEX IF NOT EXISTS ix_article_translation_status ON article (translation_key, status, language)",
]

with app.app_context():
    for statement in INDEX_SQL:
        db.session.execute(db.text(statement))
    db.session.commit()

The common pitfalls are predictable. Do not add a normal index on the full Markdown body. Do not expect ordinary indexes to fix contains search. Do not create a huge compound index just because a route touches many columns. Field order matters. For these feed queries, status and language are stable filters, while published_at and created_at are stable sort fields. That is why they belong in the first pass. view_count can be handled later if popular-article pages become a measured problem.

Validate The Release

Treat index work as a small production release. It changes database structure even when application behavior stays the same. Before the release, copy the database and run the baseline commands. After the release, check the affected routes and logs.

Application checks:

python -m compileall app.py
python scripts/baidu_union_sprint.py --submit-limit 1 --dry-run
curl -I https://stepnex.cn/
curl -I https://stepnex.cn/category/tech
curl -I https://stepnex.cn/sitemap.xml

Database checks:

sqlite3 database.db "PRAGMA integrity_check;"
sqlite3 database.db "PRAGMA index_list('article');"
sqlite3 database.db "EXPLAIN QUERY PLAN SELECT id FROM article WHERE status='published' AND language='en' ORDER BY published_at DESC LIMIT 8;"

Log checks matter as much as command output. Review the app startup log for migration failures. Review the Nginx access log for 5xx responses on the home page, category page, and sitemap. Review the application log for database locks or syntax errors. If the site uses scheduled publishing, publish a small draft in staging or run the publishing script in dry-run mode to verify the write path still works.

Review Checklist

Use this review list after every query or index change:

  • Scope: the change targets home feed, category feed, detail lookup, sitemap, admin list, or search.
  • Steps: SQLAlchemy statement, SQLite plan, and commands are saved.
  • Configuration: the index lives in a migration or controlled script, not only in shell history.
  • Validation: home page, category page, article detail page, sitemap, and admin list were checked.
  • Logs: Nginx log, application log, and query plan can be connected to the same route.
  • Pitfall control: full-text search, body indexing, over-indexing, and long-term production SQL logging were explicitly excluded.
  • Rollback: database backup path and restore command are written down.

This turns SQLite from a temporary convenience into an observable part of the stack. When the site eventually moves to MySQL or PostgreSQL, the same baseline becomes the migration acceptance test. Same route, same query intent, same validation method. Without that baseline, a migration can feel successful just because the database name changed. With it, you can prove whether the user-facing pages actually became easier to maintain.