In-memory PostgreSQL database

#1

Howdy all,

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

--
\ “Leave nothing to chance. Overlook nothing. Combine |
`\ contradictory observations. Allow yourself enough time.” |
_o__) —Hippocrates |
Ben Finney

#2

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#3

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#4

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#5

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#6

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#7

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#8

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#9

Ben Finney <ben (AT) benfinney (DOT) id.au> wrote:

Quote:

I'm using PostgreSQL as the server for several of my applications, but
unit testing the application by setting up and SQLite in-memory
database during the test case. This is workable, and certainly better
than the unacceptable overhead of connecting to a PostgreSQL server
when running the unit tests.

This has limitations, though: there are many PostgreSQL features that
aren't supported in SQLite, so the unit test fixtures have to fake a
lot of it for the sake of the PostgreSQL-specific application code.

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

There is no "embedded database" in PostgreSQL.

I am very surprised that you even consider running unit tests with
anything else than the database you want to use in production. Doesn't
that take away much of the benefits of unit testing?

If it is the overhead of establishing server connections that worries
you, why don't you write your unit tests so that the connection is kept
open?

If you are worried about concurrent unit tests thrashing each other's data,
you could have one database per developer in one PostgreSQL cluster
on a central server.

You can also install testing databases locally on the developers' machines,
but that will mean that you have to have a database server process running
on each machine.

Yours,
Laurenz Albe

#10

Ben Finney wrote:

Quote:

What options are there for creating and connnecting to a PostgreSQL
database that exists only in memory, similar to the same thing in
SQLite (and, I believe, MySQL)?

I don't think you would gain anything by keeping the DB in RAM with
postgres, because it's CPU-bound if there is little data.

If the test DB fits in RAM, this means Postgres already leverages the
memory as cache (if configured properly)

In my current application, I truncate and refill all the tables after
each of the 500 test.

I tried creating a tablespace in ramdisk, and had no performance
improvement at all.