Databases
Planted Jun 2021 Tended 4 years ago Linked from 2 notes
Contents
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
EXPLAINoutput. - 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.
