How to bulk load 1.6 billion records into AWS RDS Postgres

The nightmare scenario.  We had a star schema on AWS Aurora Postgres (compatible with Postgres 9.6).  I designed the dimension tables with 32 bit autoincrement keys (the Postgres  SERIAL data type).  Everything was fine for a few months.  Then the data loads started failing: the sequences for the 32 bit autoincrement keys had hit their max values.

How could this have happened?  The dimension tables all had record counts under 100,000.  It turns out that we loaded the dimension tables using simple INSERT statements:

  INSERT INTO dim_table (...) VALUES (...) ON CONFLICT DO NOTHING;

The 'ON CONFLICT DO NOTHING' is a cool Postgres feature.  It means that we don't have to select before inserting; just do batch inserts and let the database ignore any record that is already there.  The problem is that when you have an insert conflict, YOU STILL INCREMENT THE SEQUENCE; which is how we ran out of numbers.

Our fact table had about 1.6 billion records.  We looked at changing the data types in place but it seemed to take too long.  So we made a new database with a modified version of the star schema that uses 64 bit keys (BIGSERIAL data type) and started migrating the data from the old to the new database.  We were able to use pg_dump / pg_restore to migrate the dimension tables.  But we ran into a number of problems with the big fact table.

We initially tried to use ETL tools to migrate the facts in hourly and daily tranches. These loads would start out fast.  I would walk away optimistically; only to come back a few hours later to discover that the data loads had slowed to a crawl.  This was caused by RDS IOPS thottling.  Unless you are willing to pay for provisioned IOPS, RDS gives you 3 IOPS per GB per 30 minute period, with a max of 3000 IOPS; and throttles your I/O performance if you do too much I/O.  We had provisioned the DB with 100GB, intending to let it automatically grow.  But the IOPS thottle means you want to provision 1000GB at the outset to get the 3000 IOPS sustained allowance.  (More info on this can be found here under 'I/O Credits and Burst Performance'.)

Then we ran into timeout problems with pg_dump / pg_restore.  Long-running jobs were vulnerable to network disconnects.  So we had to set up .pgpass files so that we could run pg_dump with nohup (without needing to manually type a password).  We ran pg_dump using the directory format for the single fact table.  This got us a large compressed tab-delimited file with the 1.6 billion facts.  We tried to load this in the new schema using pg_restore, but it would consistently fail after running for a few hours.

So after some intensive web searches, we came up with the following solution:

1. Split the big file into smaller files with a million records each:

    gunzip -c 3942.dat.gz | split -l 1000000

    This gave us about 1600 files to load: xaa, xab, xac, etc.

2. Use psql to run the following script:

    CREATE TEMP TABLE temp_fact AS SELECT * FROM fact_table WITH NO DATA; 
    \copy temp_fact from 'xaa'
    INSERT INTO fact_table SELECT * FROM temp_fact ON CONFLICT DO NOTHING;
    TRUNCATE temp_fact;
    \copy temp_fact from 'xab'
    INSERT INTO fact_table SELECT * FROM temp_fact ON CONFLICT DO NOTHING;
    TRUNCATE temp_fact;
    etc.

3.  We dropped foreign key constraints on the fact table before doing the loads, but retained a unique index that was required to detect duplicate records during the inserts. It took 8-12 hours to load all the data files using this approach and maybe another hour to rebuild the foreign key indices once the loads had completed.

Gradle 4: Copy War to Directory

This took me 90 minutes to figure out and the internet was no help.

I was trying to copy the output of my gradle war project to a Tomcat webapps directory so I can run it in the debugger. 

I defined "tomcathome" in gradle.properties.  So far so good.

Then after reading a bunch of confusing stuff about how to define gradle tasks, I came up with this:

    task deployTomcat (dependsOn: 'war') {
        doLast {
            mkdir "${tomcathome}/webapps/${project.name}"
            copy {
                from zipTree(war)
                into "${tomcathome}/webapps/${project.name}"
            }
        }
    }

This task got an error:

Execution failed for task ':deployTomcat'.
> Cannot convert the provided notation to a File or URI: task ':war'.
  The following types/formats are supported:
    - A String or CharSequence path, for example 'src/main/java' or '/usr/include'.
    - A String or CharSequence URI, for example 'file:/usr/include'.
    - A File instance.
    - A Path instance.
    - A URI or URL instance.

So it doesn't like 'zipTree(war)'.  I modified the code to print 'war' and 'war.outputs'.  'war.outputs' is an instance of org.gradle.api.internal.tasks.DefaultTaskOutputs. 

So I googled this, found the source on github, found that DefaultTaskOutputs has a 'files' property of type FileCollection, and FileCollection has a property called 'singleFile'.  So I added more prints and found that war.outputs.files.singleFile gives me the name of the war file.

So this is the working task:

task deployTomcat (dependsOn: 'war') {
    doLast {
        mkdir "${tomcathome}/webapps/${project.name}"
        copy {
            from zipTree(war.outputs.files.singleFile)
            into "${tomcathome}/webapps/${project.name}"
        }
    }
}

Yay.

Solution for missing /vagrant file system.

If you are messing around with Vagrant and VirtualBox, you will sometimes end up with a vm where the /vagrant shared file system does not mount properly.  Vagrant complains that the vboxsf kernel module is not loaded properly and gives you a mount command to run manually.  When you try to mount it manually with

sudo mount -t vboxfs -o uid=500,gid=500 vagrant /vagrant

You get this error:

/sbin/mount.vboxsf: mounting failed with the error: No such device

  According to this page, you have to reinstall the VBox guest additions with this command:

cd /opt/VBoxGuestAdditions-*/init
sudo ./vboxadd setup

However, for a generic box, it fails because gcc and the kernel sources are not installed.  So when you run into this problem on a Centos 6 box (specifically, a vm based on the bento/centos-6.8 vagrant box), here is the exact sequence of commands that fixes the problem:

cd /opt/VBoxGuestAdditions-*/init
sudo yum install gcc make kernel-devel
sudo ./vboxadd setup

Then rerun the special mount command to mount the /vagrant file system.

Changing Custom Field Types in Redmine

At Brick Street, we use Redmine to manage engineering and product management. We host it ourselves on an AWS instance because we're cheap and because we want to integrate it with our other line-of-business systems. We recently upgraded from Redmine 2.1 to 3.3. In Redmine 2.1, we had some custom fields on Issue records that held text to include in our release notes. (We have a program that scans subversion logs for Issues, and then generates release notes by pulling the Redmine text for the issues fixed in the branch.)

In Redmine 2.1, these custom fields are 'text' type, which shows up in the Redmine UI as a single line (an HTML input element). In Redmine 3.3, they added a 'Long Text' type which shows up in the UI as a resizable multiline control (an HTML textarea element).

We wanted to convert the 'text' custom fields to 'long text'. So I played with our preproduction instance and reverse engineered the schema: The custom field definitions are in the CUSTOM_FIELDS table:

MySQL> desc custom_fields;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| type            | varchar(30)  | NO   |     |         |                |
| name            | varchar(30)  | NO   |     |         |                |
| field_format    | varchar(30)  | NO   |     |         |                |
| possible_values | mediumtext   | YES  |     | NULL    |                |
| regexp          | varchar(255) | YES  |     |         |                |
| min_length      | int(11)      | YES  |     | NULL    |                |
| max_length      | int(11)      | YES  |     | NULL    |                |
| is_required     | tinyint(1)   | NO   |     | 0       |                |
| is_for_all      | tinyint(1)   | NO   |     | 0       |                |
| is_filter       | tinyint(1)   | NO   |     | 0       |                |
| position        | int(11)      | YES  |     | NULL    |                |
| searchable      | tinyint(1)   | YES  |     | 0       |                |
| default_value   | mediumtext   | YES  |     | NULL    |                |
| editable        | tinyint(1)   | YES  |     | 1       |                |
| visible         | tinyint(1)   | NO   |     | 1       |                |
| multiple        | tinyint(1)   | YES  |     | 0       |                |
| format_store    | text         | YES  |     | NULL    |                |
| description     | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

The field_format value for text fields is 'string'. The field_format value for long text fields is 'text'.

The actual values of the custom fields are stored in the CUSTOM_VALUES table:

MySQL>; desc custom_values;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int(11)     | NO   | PRI | NULL    | auto_increment |
| customized_type | varchar(30) | NO   | MUL |         |                |
| customized_id   | int(11)     | NO   |     | 0       |                |
| custom_field_id | int(11)     | NO   | MUL | 0       |                |
| value           | mediumtext  | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

The customized_id field is a foreign key to the record in the ISSUES table. The custom_field_id field is a foreign key to the CUSTOM_FIELDS record. The value field stores the custom field value. This query will show you all the custom field values for Issue 2001:

select * from custom_values where customized_id = 2001;

So it turns out that you can change the type of a custom field value by updating the custom_field_id field of the CUSTOM_VALUES record. In our Redmine, we changed the text fields to long text by following this procedure:

1. Define a new long text field in the UI.
2. Look in the CUSTOM_FIELDS table to get the id of the text field (e.g. id 5) and the new long text field (e.g. id 15).
3. Run this update query to change types of all the custom field values:
   

   update CUSTOM_VALUES set custom_field_id = 15 where custom_field_id = 5;
   

   
   
4. Delete the custom field definition of the old field (id 5) in the UI.

This approach works great for Text custom fields. List custom fields are harder because the custom field definition includes a list of possible values (in the CUSTOM_FIELDS.possible_values). We have a number of multiselect fields that contain lists of release version. My next task is to convert these to the new Version custom field type.

Schema Extensibility in Commercial Enterprise Software

This describes some work I did in the late 90s. It has some limitations today, primarily because modern object-relational mapping systems, like JPA, do not handle run-time schema changes very well. Treat this as a historical document telling you how enterprise software was built in the days when every company also needed to write its own application platform.

This document describes how schema extensibility is typically achieved in commercial enterprise software applications; in particular, in customer relationship management (CRM) applications.