N-queries
You should avoid making (generating) multiple DB queries from the application if you can fetch the needed data in a single query. Always check what queries are executed in your (Rails) log.
Also, when you work on a complex SQL calculation, avoid pulling intermediate results to your application, which will be used for the next step in your query. It can be a very resource-intensive operation.
UNION (ALL)
When you use the UNION operator for combining result sets of two or more query statements, double-check if you should use UNION or UNION ALL.
The UNION operator is less performant because it removes all duplicate rows from the combined data set.
To retain the duplicate rows, you use the UNION ALL instead.
VIEWs
A database VIEW is a pretty useful SQL mechanism. You can use it for:
- hiding the complexity of queries
- reducing complexity by naming a part of the complex query
- exposing a data subset to the outer world
A plain VIEW contains only a definition - that means the query will be executed each time you fetch data from the view.
The results can be "cached" by using a MATERIALIZED VIEW. Basically, a MATERIALIZED VIEW acts as a read-only plain table. To update data in materialized views, use the REFRESH statement.
REFRESH completely replaces the content of a materialized view. During the refresh process, the view is locked. To refresh a materialized view without locking, use REFRESH MATERIALIZED VIEW CONCURRENTLY.
Plain or materialized views?
So, the rule of thumb when you have to choose between plain or materialized VIEW is the frequency of data updates.
VIEWs are generally used when data is to be accessed infrequently and data in the table get updated frequently.
On the other hand, MATERIALIZED VIEWs are used when data is to be accessed frequently and data in the table does not get updated frequently.
You can add indexes to materialized views, but not plain database views.
Handling views from Rails
When using Rails and ActiveRecord, views behave like a plain models:
class Report < ApplicationRecord
belongs_to :property
def readonly?
true
end
end
Report.where(property: property)
.group(:date)
.sum(:revenue)
Also, for managing (materialized) views in your Rails application, it's recommended to use Scenic gem. The gem adds methods for easier management (create, delete, update, and refresh) of database views.
PostGIS
When you want to work with geographical data, most likely you'll end up using PostGIS. PostGIS is an extension for the PostgreSQL database.
In the official documentation, you can find more information about data types, functions, and operators. Here's the list of some useful functions and geometry constructors, which can be your starting point when you start playing with PostGIS:
PostGIS might be overkill in some scenarios - read more here why you (probably) don't need PostGIS.