1.2 Billion Taxi Rides on AWS RDS running PostgreSQL

On November 17th, 2015, Todd Schneider published a blog post titled Analyzing 1.1 Billion NYC Taxi and Uber Trips, with a Vengeance in which he analysed the metadata of 1.1 billion Taxi journeys made in New York City between 2009 and 2015. Included with this work was a link to a GitHub repository where he published the SQL, Shell and R files he used in his work and instructions on how to get everything up and running. There are a few additional charts created by the R files which were used in follow up posts as well.

In this blog post I’ll launch 4 different types of AWS RDS instances running PostgreSQL 9.5.2 and benchmark creating the same graphs that Todd Schneider did in his analysis.

python memory_profiler 0.41

$ python -m memory_profiler example.py


Line #    Mem usage  Increment   Line Contents
==============================================
     3                           @profile
     4      5.97 MB    0.00 MB   def my_func():
     5     13.61 MB    7.64 MB       a = [1] * (10 ** 6)
     6    166.20 MB  152.59 MB       b = [2] * (2 * 10 ** 7)
     7     13.61 MB -152.59 MB       del b
     8     13.61 MB    0.00 MB       return a

Web API performance: profiling Django REST framework

We’ve seen several cases of developers making these assumptions before they start building their API, and either discounting Django as not being fast enough for their needs, or deciding to not use a Web API framework such as Django REST framework because they ‘need something lightweight’.

I’m going to be making the case that Web API developers who need to build high performance APIs should be focusing on a few core fundamentals of the architecture, rather than concentrating on the raw performance of the Web framework or API framework they’re using.

.. the biggest performance gains for Web APIs can be made not by code tweaking, but by proper caching of database lookups, well designed HTTP caching, and running behind a shared server-side cache if possible.

.. 1. Get your ORM lookups right.

Given that database lookups are the slowest part of the view it’s tremendously important that you get your ORM queries right. Use .select_related() and.prefetch_related() on the .queryset attribute of generic views where necessary. If your model instances are large, you might also consider using defer() or only()to partially populate the model instances.

2. Your database lookups will be the bottleneck.

.. 3. Work on performance improvements selectively.

Remember that premature optimization is the root of all evil. Don’t start trying to improve the performance of your API until you’re in a position to start profiling the usage characteristics that your API clients exhibit. Then work on optimizing your views selectively, targeting the most critical endpoints first.

Indiscriminate use of CTEs considered harmful

However, there is one aspect of the current implementation of CTEs that should make you pause. Currently CTEs are in effect materialized before they can be used. That is, Postgres runs the query and stashes the data in a temporary store before it can be used in the larger query. There are a number of consequences of this.

..  After some analysis and testing, the simple act of inlining two CTEs in the query in question resulted in the query running in 4% of the time it had previously taken. Indiscriminate use of CTEs had made the performance of this query 25 times worse.