1--- 2stage: Enablement 3group: Database 4info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments 5--- 6 7# Serializing Data 8 9**Summary:** don't store serialized data in the database, use separate columns 10and/or tables instead. This includes storing of comma separated values as a 11string. 12 13Rails makes it possible to store serialized data in JSON, YAML or other formats. 14Such a field can be defined as follows: 15 16```ruby 17class Issue < ActiveRecord::Model 18 serialize :custom_fields 19end 20``` 21 22While it may be tempting to store serialized data in the database there are many 23problems with this. This document will outline these problems and provide an 24alternative. 25 26## Serialized Data Is Less Powerful 27 28When using a relational database you have the ability to query individual 29fields, change the schema, index data, and so forth. When you use serialized data 30all of that becomes either very difficult or downright impossible. While 31PostgreSQL does offer the ability to query JSON fields it is mostly meant for 32very specialized use cases, and not for more general use. If you use YAML in 33turn there's no way to query the data at all. 34 35## Waste Of Space 36 37Storing serialized data such as JSON or YAML will end up wasting a lot of space. 38This is because these formats often include additional characters (for example, double 39quotes or newlines) besides the data that you are storing. 40 41## Difficult To Manage 42 43There comes a time where you will need to add a new field to the serialized 44data, or change an existing one. Using serialized data this becomes difficult 45and very time consuming as the only way of doing so is to re-write all the 46stored values. To do so you would have to: 47 481. Retrieve the data 491. Parse it into a Ruby structure 501. Mutate it 511. Serialize it back to a String 521. Store it in the database 53 54On the other hand, if one were to use regular columns adding a column would be 55as easy as this: 56 57```sql 58ALTER TABLE table_name ADD COLUMN column_name type; 59``` 60 61Such a query would take very little to no time and would immediately apply to 62all rows, without having to re-write large JSON or YAML structures. 63 64Finally, there comes a time when the JSON or YAML structure is no longer 65sufficient and you need to migrate away from it. When storing only a few rows 66this may not be a problem, but when storing millions of rows such a migration 67can easily take hours or even days to complete. 68 69## Relational Databases Are Not Document Stores 70 71When storing data as JSON or YAML you're essentially using your database as if 72it were a document store (for example, MongoDB), except you're not using any of the 73powerful features provided by a typical RDBMS _nor_ are you using any of the 74features provided by a typical document store (for example, the ability to index fields 75of documents with variable fields). In other words, it's a waste. 76 77## Consistent Fields 78 79One argument sometimes made in favour of serialized data is having to store 80widely varying fields and values. Sometimes this is truly the case, and then 81perhaps it might make sense to use serialized data. However, in 99% of the cases 82the fields and types stored tend to be the same for every row. Even if there is 83a slight difference you can still use separate columns and just not set the ones 84you don't need. 85 86## The Solution 87 88The solution is very simple: just use separate columns and/or separate tables. 89This will allow you to use all the features provided by your database, it will 90make it easier to manage and migrate the data, you'll conserve space, you can 91index the data efficiently and so forth. 92