Databases

Know Your Options

Relational databases like PostgreSQL can take you really far, but it helps to know that no-SQL document databases exist when Postgres’ jsonb falls short, or how to use search-optimized databases like Elasticsearch when your needs grow beyond Postgres’ full-text search capabilities.

SQL Optimization

Prefixing a SQL query with EXPLAIN (or EXPLAIN ANALYZE to actually run it and get timing info) is the first step toward understanding (and then optimizing) the database’s plan for executing a query.

• This talk has a great explanation of the somewhat arcane terminology use by EXPLAIN.
• You can also paste the query plan into online tools like this one to better format and clarify the output.
• pgMustard is a paid tool (with a free trial) that can provide deeper insights into optimizing your queries, also by on parsing EXPLAIN output.
• Most optimization happens by creating and tuning indexes, and Knock has a great guide (to B-tree indexes at least)

When it comes to optimizing data-querying and processing, experience has taught me that SQL is almost always going to be faster (and less memory-intensive) than performing that computation at the application level with e.g. Elixir or Ruby. But this TestDouble post adds a useful bit of nuance to that:

Specifically, very complex SQL queries with many conditions can still be pretty slow, and in those cases application logic can still have a role to play in determining whether some of those conditions don’t apply to a given query and can be skipped, ultimately resulting in a smaller, simpler SQL query. The example is of a search query parsing the search input and determining in Ruby which tables/columns actually need to be searched, then building a query across only those tables/columns and passing it to the database.

Schema Changes at Scale

Changing the structure of a database when it has a lot of data and/or is being frequently accessed brings new challenges, as I learned scaling up at Lessonly. Certain changes can take a long time and lock the database while they run, leading to downtime, but there are often strategies to mitigate these effects:

• An ecto-focused (Elixir) guide
• An automated checking tool I’ve used (with a guide in the README) for Ruby

Forsaken Depths of SQL

I like this meme which depicts the progressively weirder aspects of SQL. I hope I never run into The Halloween Problem, but at least if I do I’ll recognize it.

Resources

• Some SQL Tricks of an Application DBA
• PostgreSQL
  • Some powerful, lesser-known featres