Slow Flask-SQLAlchemy query using association tables

Question

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?

Answer 1

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?