In a Flask web app using SQLAlchemy, how do I improve page load times when displaying data from a large PSQL database?

Question

I'm working on a simple Flask web app that displays data (2000+ items) from a PSQL database in a Bootstrap table. The problem is that the page where the table is displayed takes a VERY long time to load.

My models.py:

from app import db

class Movie(db.Model):
    __tablename__ = 'movies'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), index=True, unique=True)
    director = db.Column(db.String(60), index=True)
    release_date = db.Column(db.Date, index=True)
    duration = db.Column(db.Integer)

    def __repr__(self):
        return '<Movie: %s>' %(self.name)

My views.py:

@home.route('/')
    movies = Movie.query.all()
    return render_template('home/index.html', movies=movies)

The data is loaded as follows in index.html:

{% for movie in movies %}
<tr>
    <td> {{ movie.id }} </td>
    <td> {{ movie.name }} </td
    <td> {{ movie.director }} </td>
    <td> {{ movie.release_date }} </td>
    <td> {{ movie.duration }} </td>
</tr>
{% endfor %}

I'm using pagination for my table, although only visually (the data is still loaded entirely at the beginning). To achieve this I'm using Bootstrap-Table so my table looks something like this.

It seems to me like the best option would be to load 15 items first and display them. Once everything else has been loaded, then the remaining items can be loaded. However, I'm not sure how to go about doing this.

How can I improve the table page load time?

Answer 1

You should paginate. You could use AJAX requests to get more results without having to reload the page. You can paginate any query to avoid loading all the items at once. Instead, it will only load as many as you ask for at a time.

Consider the following view and template that demonstrate use of a paginate object.

@app.route('/', methods=['GET', 'POST'])
@app.route('/index', methods=['GET', 'POST'])
@app.route('/index/<int:page>', methods=['GET', 'POST'])
@login_required
def index(page=1):
    form = PostForm()
    if form.validate_on_submit():
        post = Post(body=form.post.data, timestamp=datetime.utcnow(), author=g.user)
        db.session.add(post)
        db.session.commit()
        flash('Your post is now live!')
        return redirect(url_for('index'))
    posts = g.user.followed_posts().paginate(page, POSTS_PER_PAGE, False).items
    return render_template('index.html',
                           title='Home',
                           form=form,
                           posts=posts)

Then the template

<!-- posts is a Paginate object -->
{% for post in posts.items %}
<p>
  {{ post.author.nickname }} says: <b>{{ post.body }}</b>
</p>
{% endfor %}
{% if posts.has_prev %}<a href="{{ url_for('index', page=posts.prev_num) }}">&lt;&lt; Newer posts</a>{% else %}&lt;&lt; Newer posts{% endif %} | 
{% if posts.has_next %}<a href="{{ url_for('index', page=posts.next_num) }}">Older posts &gt;&gt;</a>{% else %}Older posts &gt;&gt;{% endif %}

Produces a page like this, only waits for 3 results, even if there are a million possible results from the query.

You can see this code explained a bit more in The Flask Mega-Tutorial