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 →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.
Scaling Up Rails Applications With PostgreSQL Table Partitioning - Part 3
After publishing recent blog posts about table partitioning - its SQL basics part and how to use in in Rails application I was asked quite a few times what is the real performance gain when using table partitioning. This is a great question, so let's answer it by performing some benchmarks.
Read on →Scaling Up Rails Applications With PostgreSQL Table Partitioning - Part 2
In the previous blog post we learned some basics about table partitioning: how it works and what kind of problems it solves. So far we've been discussing mostly basic concepts with raw SQL examples. But the essential question in our case would be: how to make it work inside Rails application then? Let's see what we can do about it.
Read on →Scaling Up Rails Applications With PostgreSQL Table Partitioning - Part 1
You've probably heard many times that the database is the bottleneck of many web applications. This isn't necessarily true. Often it happens that some heavy queries can be substiantially optimized, making them really efficient and fast. As the time passes by, however, the data can remarkably grow in size, especially in several years time which can indeed make the database a bottleneck - the tables are huge and don't fit into memory any longer, the indexes are even bigger making queries much slower and you can't really optimize further any query. In many cases deleting old records that are no longer used is not an option - they still can have some value, e.g. for data analysis or statystical purposes. Fortunately, it's not a dead end. You can make your database performance great again with a new secret weapon: table partitioning.
Read on →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.
2013
CentOS 6.4 server setup with Ruby On Rails, Nginx and PostgreSQL
Server setup with the entire environment for Rails applications can be quite tricky, especially when you do it for the first time. Here is step by step guide how to setup CentOS 6.4 server with a basic environment for deploying Rails applications. I encourage you to choose CentOS Linux - it is a reliable distro (well, based on Red Hat Enterprise Linux), easy to handle and doesn't require advanced Unix knowledge like Gentoo (especially while updating system related stuff).
Read on →