How to enable my team to run integration tests against RedShift in parallel?

Question

I am working with Docker to run my integration tests, it works pretty nice:

• I startup my docker containers (one with my server and one with the database)
• I run the IT using arquillian against these containers

Now we are moving to AWS and we would like to use Amazon RedShift for some analysis. The problem is that RedShift is only available in AWS, so I cannot create a RedShift image for Docker.

I would like to have a way that many developers + CI server can run tests in parallel (without affecting each other's results).

I've searched for any references about how people do it, but I could not find anything except for this post from Serenytics, which is not exactly what I need.

The possibilities I see are:

1. Use one schema for each developer/CI server
2. Use a Postgres Docker image for the developers and a real RedShift for the CI Server.

About number 1: it would run against the real DB in all instances, but we would need to have some "private" configuration for each developer and we would have multiple schemas in the database just for testing.

Number 2: We would not run against the real DB engine, so maybe not everything would be supported by Postgres. It could cause problems in the near future.

I imagine I am not the first one with this problem. How can I setup my tests so many people can run the tests in parallel and it doesn't pollute my database?

Answer 1

I never worked with RedShift by myself, but the situation you describe is not so special for this specific technology.

Using two different database technologies can be tricky, however, when you have a chance to keep the architecture of your system open to different backend technologies with small effort, I would recommend to use that chance. So if your developers care for making the applications compatible with PostgreSQL and RedShift (which is, according to Wikipedia, based on PostgreSQL 8.0.2 as well), you gain a flexibility which is not just helpful for easier testing. This will also make it easier to switch to a newer backend version in the future, or to support additional host vendors if that will become necessary in the future.

So why not interpret your option #2 as an advantage, to have the possibility to run tests on two (slightly) different backend systems? Of course, you will have to "abstract away" the differences between the systems, and inform your devs which SQL restrictions they have to obey. Amazon gives a list here, and there are further details (like the ones mentioned by @AdrienChauve) your team has to learn about, but I would expect that to be an initial learning phase, not a thing which slows down your team constantly.

Of course, there might be some failing tests on the "real" RedShift backend which were overlooked using the PostgreSQL backend - that is what your tests are for, right? What you have to care for if you pick this route: make sure not to develop just on a PostgreSQL server for several weeks and switch afterwards to Redshift, but to test on Redshift regularly and immediatly when the tests on PostgreSQL are done. Since you mentioned a CI server, I guess that is what you already had in mind.

Additionally, I would also recommend to let the door open for option #1. A fixed schema name should not be hardcoded into your applications either, and multiple schemas in one database for testing can be fine, as long as you do not have to fill each test schema with a huge amount of test data. And if you really run into the situation where you need to run tests in parallel directly on RedShift, but for different schemas, you can still decide to do so.

Answer 2

In practice there are several small differences between Redshift and a recent Postgres, such as:

• order of rows in query set
• timezone conversion
• date diff function

so I prefer running the tests directly on Redshift.

I believe the best bet to setup your tests so that many people can run them in parallel is to use random names for your databases/tables (e.g. test_ + uuid or timestamp, so there is no collision between test runners). Then you need to make sure all these temporary databases/tables are correctly deleted at the end of the tests.

Answer 3

Create a snapshot of Redshift, and then spin up smaller Redshift clusters for each developer at integration test time. Trash the clusters when the tests are done. If performance testing is important then spin-up larger clusters. As long as you don't keep the cluster up too long it won't cost too much.

• You should use different AWS accounts for Production & development (and whatever other environments you use)
• Share the snapshot from production to the development AWS account
• There are some good tools you can use to automate this (in order of my preference): Terraform, Ansible, Cloudformation
• You can export a Redshift connection string from those processes which should be the only changing dependency between your containerized server and Redshift. If you have a proxy/bastion/jumperserver and some DNS then you can maintain a name for each developer and switch in the Redshift backend when needed and then your developers don't need to alter that connection string dependency.

Other options:

• You could use different database names or schemas but there's hidden dangers with that approach, and then you are either testing on Prod or you have a development cluster up all the time costing a lot, and tests colliding or performance interference.

• docker-amazon-redshift This is just postgres 8.02 in a container, it won't have any of the newer additions, functions or backports in it so testing might give a lot of false positive bugs that you won't get on real Redshift.

I hate the idea of CI finding errors that you can't detect during developer testing because the database engine is different. There's a lot of people who suggest you should be database-agnostic, but unless you are creating enterprise software for clients with different databases then I find that advice to be a bit unrealistic.

There really is no way to support multiple databases universally. ANSI/ISO SQL is not adhered to strictly by any vendor. You will end up supporting the quirks of multiple database vendors, even if you only aspire to the lowest common-denominator functionality. Or just give up and use sqlite for everything (don't do that).

It will not be a lovely elegant universal abstraction layer. Take a look at any decent ORM or database API from any language and see where the abstraction leaks or is unsupported for different target back-ends.