I'm having trouble with some tests of a PostgreSQL-based system. Briefly, I have a set of functional tests that
- create a new database
- populate it with a data model
- run a Web server (in-process)
- test the integrated Web server - database functionality
The tests are now slow enough that I'm averse to writing new ones, so it's becoming important for me to figure out how to run them faster.
The main time sink appears to be in the fixtures, where I create a new database. Actually creating an empty postgres database is slow: it takes 18 seconds (on my server, normally a pretty fast computer...) to create a new, empty database.
So, how can I get a known-good database in place quickly?
The most obvious route is for me to do dev tests with something small and fast (sqlite?), but I can't switch to another database system because I'm using PostgreSQL-specific features.
I poked around the PostgreSQL documentation and tried using template databases but the problem persists: createdb is just slow to run.
I can't figure out how to build user-accessible snapshots (to which I could revert after tests...) and Point-in-Time-Recovery is only for superusers; I don't want users to have to be postgresql superusers to run my tests.
The parameters of the problem:
- At a minimum, my test fixtures need to (quickly!) construct a test-only database with a pre-loaded SQL data model, containing no data.
- Ideally, I would be able to specify a single snapshot and then revert to that snapshot at any time.
- No sysadmin access should be required, and certainly no raw filesystem manipulations should be required.
Any ideas? As usual, either comment or drop me a line.
Posted by Grig Gheorghiu on 2008-01-27 at 21:10.
Just a thought -- have you tried restoring a pg_dump of a known test database, instead of creating it from scratch? Grig
Posted by Moof on 2008-01-27 at 21:54.
How about a Drop all and recreate the tables? Is that any faster? Or just flushing the tables and resetting the various counters and indexes? This is the way Django's ORM works by default, at least avoiding the createdb overhead more than once (it still creates a new db at the beginning, and drops it after a successful run, but running the test suite only causes this to happen once) But yes, this a particular frustration of mine with postgres. Creating the test database takes significantly longer than running the tests.
Posted by Jonathan Ellis on 2008-01-27 at 22:50.
Using a ramdisk seems like the easiest solution, but it sounds like that violates your constraint #3. Greg's idea has merit; if the CREATE DATABASE is the problem, don't do it every time. :) It's straightforward to write a pl/pgsql (or pl/python but that would be a little clunkier since it's dml-heavy) function to query pg_class for existing tables and drop them all before starting to add test data. For bonus points you could query for the presence of the database and run CREATE only if that is needed.
Posted by Anders Pearson on 2008-01-27 at 23:08.
18 seconds for createdb to run? It only takes 0.6 seconds on my ancient Ubuntu desktop (normally a very slow computer). Do you have a million databases on your system or something? Have you tried this on a system that doesn't have many other databases? Or running a seperate, testing only instance of PostgreSQL? Is there something particularly odd about your PostgreSQL config? 0.6 seconds isn't great, but 18 seconds sounds way too slow.
Posted by Titus Brown on 2008-01-28 at 00:09.
Re trying to "flush" the database: I could end up with an unclean starting position that caused bugs and inconsistencies. But I guess it'd run faster :) Anders, thanks for confirming that it runs faster on some people's computers. I'll look into it. There's nothing unusual about my install, AFAIK; it's actually located on the system disk, but I don't think it's an unusually active disk. I'll take a look & benchmark it on some other computers. Thanks, everyone! I was hoping there'd be a magic solution but these suggestions are good starting points :)
Posted by Noah Gift on 2008-01-28 at 07:25.
Titus/If it helps my work needs to make this quicker as well, maybe we can have a late night Sprint on this at PyCon? I also second what Jonathan says, using an in memory version of Postgres could be a huge saving in time: <a href="http://www.redhatmagazine.com/2007/12/12 /tip-from-an-rhce-memory-storage-on- postgresql/">http://www.redhatmagazine.com/2007/12/12/tip-from-an- rhce-memory-storage-on-postgresql/</a> I suppose using an ORM can really save your butt sometimes, as you won't get locked into using database specific features, and will be able to convert tests to SQLite.
Posted by Grig Gheorghiu on 2008-01-28 at 13:17.
My timings are close to what Anders got: -bash-3.00$ time echo 'create database test2' | psql template1 CREATE DATABASE real 0m0.492s user 0m0.002s sys 0m0.007s This is on a fairly old Dell 1650 server running CentOS 4.4.
Posted by alex v. koval on 2008-01-28 at 14:20.
IMO, something is wrong with your postgres, here it is: alex@appserver ~ $ time echo 'create database test2' | psql template1 CREATE DATABASE real 0m0.280s user 0m0.004s sys 0m0.004s Also, as far I know, Django (a framework which I use) for example, in its internal tests does not recreate whole DB, instead it does tables flush, its faster this way.
Posted by bignose on 2008-01-29 at 04:24.
> The most obvious route is for me to do dev tests with something small and fast (sqlite?), but I can't switch to another database system because I'm using PostgreSQL-specific features. That's a bad code smell: If you need to do lots of tests against an actual PostgreSQL instance, then your code is too tightly coupled to PostgreSQL. Instead, those PostgreSQL-specific features should be abstracted away behind a very narrow layer in your code. Only that layer actually needs to have a real PostgreSQL instance to test against; the rest of the code should be tested against the interface, using test doubles (stubs, mocks, etc.) instead of the real code that implements the PostgreSQL-specific features.
Posted by Titus Brown on 2008-01-29 at 05:46.
bignose -- I disagree, for a few reasons. First, to all intents and purposes, my application is a thin Web presentation layer on top of a database. Introducing another layer of complexity for the purpose of better testing violates the KISS principle. (If I had some **other** use for that layer, there'd be a reason; I don't, at the moment.) Second, the PostgreSQL specific features are central to the app; that's not necessarily good, but I've been able to dramatically simplify my codebase by doing that. On balance, it's been positive. Third, I have this weird desire to actually test the application; I don't think unit tests are as useful for Web apps as are functional tests, because of the layering issue I mentioned above. ...but thank you for bringing it up. It is an interesting perspective and I haven't had to actively defend my design to anyone, so I appreciate the comment ;). There are some interesting posts I've seen about the merits of trying to become database-agnostic; how many people really need to switch between (say) PostgreSQL and MySQL? I haven't really decided one way or another, but the need to test things flexibly is making me lean towards database agnosticism. Ultimately I intend to rewrite the app to use SQLAlchemy, but that is unfortunately not something I have time to do soon :( Which actually speaks again to your point -- whether or not my original design is good, I'm stuck with it now and need to solve proximal problems first! It's a moderately large app and I won't be rewriting it from scratch any time soon. thanks again, --titus
Posted by Christoph Zwerschke on 2008-01-29 at 07:49.
As others have already noted, a createdb should really run much faster. You can also try running pg_restore with the "-c" option instead of dropping and recreating the database. Another idea is to shut down the database, copy your complete database cluster on the level of the file system, and restart the database again.
Posted by bignose on 2008-01-29 at 17:46.
> Third, I have this weird desire to actually test the application I don't think it's weird, it's quite essential. I'm not sure why you make this point. > I don't think unit tests are as useful for Web apps as are functional tests I think unit tests are very useful for **any** code. TDD and all that; write the tests at the time of designing the implementation, make each unit tests very focussed in its fixtures and assertions (thus fast), and run the entire unit test suite continually during development. However, I missed the mention in the opening paragraph that these are functional, not unit, tests. :-) Given that, why is it a problem for the functional test suite to take a bit of time? The functional tests of the entire application stack will necessarily invoke many subsystems that might be slow. This might point to performance issues, but certainly doesn't indicate shortcuts need to be taken in the tests themselves. You shouldn't expect the functional tests to execute as quickly as the unit tests, nor expect to be able to run them continually after every change the way you do with unit tests. Rather, the functional test suite should be run every time you commit a change to version control, or some other less-frequent-than-always event.
Posted by Titus Brown on 2008-01-30 at 02:01.
Re "weird desire", unit tests don't do a good job of testing the actual application; they test units of code. If you are mainly slinging information about (and not manipulating it), I don't find them all that useful. (I have unit tests built into the libraries that my app **relies** on, of course, and they're great there.) I want to speed up the functional tests because they're reducing my velocity of development. --titus