2018
Indexes on Rails: How to make the most of your Postgres database
Optimizing database queries is arguably one of the fastest ways to improve the performance of the Rails applications. There are multiple ways how you can approach it, depending on the kind of a problem. N+1 queries seem to be a pretty common issue, which is, fortunately, easy to address. However, sometimes you have some relatively simple-looking queries that seem to take way longer than they should be, indicating that they might require some optimization. The best way to improve such queries is adding a proper index.
Read on →Rails Quick Tips: Temporarily Disabling Touching with ActiveRecord.no_touching
Touching ActiveRecord models is quite a common thing in most of the Rails applications, especially useful for cache invalidation. By default, it updates updated_at timestamp with the current time, Here’s a typical example of using touch in a model:
PostgreSQL Quick Tips: Working With Dates Using EXTRACT function
Imagine that you are implementing an e-commerce platform and want to grab all orders from the current year. What would be the simplest way of doing it in Rails? Probably writing a query looking like this:
Read on →2016
Keeping Data Integrity In Check: Conditional Unique Indexes For Soft Delete
Soft delete is a pretty common feature in most of the applications. It may increase complexity of the queries, nevertheless, not deleting anything might be a right default as the data might prove to be useful in the future: for restoring if a record was removed by mistake, to derive some conclusions based on statistics and plenty of other purposes. It may seem like it's a pretty trivial thing: just adding a column like deleted_at and filtering out records that have this value present. But what happens when you need to do some proper uniqueness validation on both model layer and database level? Let's take a look what kind of problem can easily be overlooked and how it can be solved with a conditional index.
When validation is not enough: PostgreSQL triggers for data integrity
Is validation in your models or form objects enough to ensure integrity of the data? Well, seems like you can't really persist a record when the data is not valid unless you intentionally try to bypass validation using save: false option when calling save or using update_column. What about uniqueness validation?
A classic example would be a unique email per user. To make sure the email is truly unique we could add a unique index in database - not only would it prevent saving non-unique users when bypassing validation, but also it would raise extra error when 2 concurrent requests would attempt to save user with the same email. However, some validations are more complex that ensuring a value is unique and index won't really help much. Fortunately, PostgreSQL is powerful enough to provide a perfect solution to such problem. Time to meet your new friend: PostgreSQL triggers.
PostgreSQL In Action: Sorting With NULLIF Expression
Ordering with ActiveRecord and PostgreSQL may seem like an obvious and simple thing: in most cases you just specify one or several criteria with direction of ordering like order(name: :asc) and that's all, maybe in more complex scenarios you would need to use CASE statement to provide some more customized conditions. But how could we approach sorting with forcing blank values to be the last ones, regardless of the direction of the sort? You might be thinking now about NULLS LAST statement for this purpose, but that's not going to handle empty string. For this case you need something special: time to meet NULLIF conditional expression.