Re: speeding up table creation

On Tue, Oct 14, 2008 at 5:08 PM, Rainer Mager <rainer(at)vanten(dot)com> wrote:
> I have an interesting performance improvement need. As part of the automatic
> test suite we run in our development environment, we re-initialize our test
> database a number of times in order to ensure it is clean before running a
> test. We currently do this by dropping the public schema and then recreating
> our tables (roughly 30 tables total). After that we do normal inserts, etc,
> but never with very much data. My question is, what settings can we tweak to
> improve performance is this scenario? Specifically, if there was a way to
> tell Postgres to keep all operations in memory, that would probably be
> ideal.

I'm not sure we've identified the problem just yet.

Do you have the autovacuum daemon enabled? Are your system catalogs
bloated with dead tuples? Could you approach this by using a template
database that was pre-setup for you and you could just "create
database test with template test_template" or something like that?

Also, if you're recreating a database you might need to analyze it
first before you start firing queries.

PostgreSQL buffers / caches what it can in shared_buffers. The OS
also caches data in kernel cache. Having more memory makes it much
faster. But if it writes, it needs to write. If the database server
where this is happening doesn't have any important data in it, you
might see a boost from disabling fsync, but keep in mind a server that
loses power / crashes can corrupt all your databases in the db server.

If you can fix the problem with any of those suggestions you might
need to throw hardware at the problem.