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(
    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

def posts():
    page_num = request.args.get('page', 1)
    posts = models.Post.query.join(models.post_mentions)\
                             .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?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.