Slow Flask-SQLAlchemy query using association tables I have two models in Flask-SQLAlchemy (Post and Comment) that have many-to-many relationship that is manifested in the third model (post_mentions):

post_mentions = db.Table(
    'post_mentions',
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True),
    db.Column('comment_id', db.Integer, db.ForeignKey('comments.id'), primary_key=True),
)

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True, nullable=False)
    mentions = db.relationship('Comment', secondary=post_mentions, lazy='dynamic')

    def __eq__(self, other):
        return self.name.lower() == other.name.lower()

    def __hash__(self):
        return hash(self.name.lower())


class Comment(db.Model):
    __tablename__ = 'comments'

    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.Integer, nullable=False)


There is also a /posts endpoint that triggers the following query:

# flask and other imports

@app.route('/posts')
def posts():
    page_num = request.args.get('page', 1)
    posts = models.Post.query.join(models.post_mentions)\
                             .group_by(models.post_mentions.columns.post_id)\
                             .order_by(func.count(models.post_mentions.columns.post_id).desc())\
                             .paginate(page=int(page_num), per_page=25)
    return render_template('posts.html', posts=posts)


There are more than 14k+ posts and 32k+ comments stored in SQLite database. As you can see from the snippet above, when someone hits /posts endpoint, SQLAlchemy loads all data at once to the memory and then subsequent queries (e.g. retrieving posts, comments to that posts, etc..) take sub-millisecond time, since data is being served from the memory without hitting the database. Initial load takes 10s+ on my laptop, which is, to put it mildly, suboptimal. 

So the question is: Considering that users won't view 97+% of posts, how can I both order posts by number of mentions in comments and load them on demand instead of doing it in one swoop?
Saw your post on indiehackers. I don't know this orm, but generally speaking, I see you have two options.

Decide to preload/precache the data when your app starts and refresh it occasionally, if you insist on having all records available. 

But some good advice I've read is : never do in real time what you can do in advance. So... Why not even build some "top posts" table and seed that?