In-memory PostgreSQL database

Ben Finney wrote:
> 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

Ben Finney wrote:

> 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.

Laurenz Albe wrote:

> 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?

Not if the DB schema has been evolved with "emergent design" and makes
no use of triggers, stored procedures, advanced check constraints... if
you design an app like some of the youngsters do, the only difference
between sqlite and postgres is that the latter has better support for
unicode ;-)

Laurenz Albe writes:

> 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?

No. The point of unit testing is to test a unit of code, not the
entire application. To do so, it's best to isolate that section of
code from its external dependencies; a "real" database certainly
counts as an external dependency.

For "acceptance testing" or any other kind of full-application testing
(i.e. not unit testing), certainly one wants to use the real database.
That's not the issue here though.

> 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?

The point is less about the overhead, and more about isolating the
code so that each unit test exercises as small a part of the code as
feasible.

--
\ "Pinky, are you pondering what I'm pondering?" "Well, I think |
`\ so, Brain, but it's a miracle that this one grew back." -- |
_o__) _Pinky and The Brain_ |
Ben Finney

Marco Mariani writes:

> Ben Finney wrote:
>
> > 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.

I'm not interested in "performance", but rather reduction in external
dependencies for unit tests (i.e. unit tests should not rely on a
running database server).

Now that we've established that I want what I actually asked for, can
someone give suggestions on how to achieve it?

--
\ "If you can't annoy somebody there is little point in writing." |
`\ -- Kingsley Amis |
_o__) |
Ben Finney

Ben Finney wrote:

> I'm not interested in "performance", but rather reduction in external
> dependencies for unit tests (i.e. unit tests should not rely on a
> running database server).
>
> Now that we've established that I want what I actually asked for, can
> someone give suggestions on how to achieve it?

I'm not sure I understand.
You cannot connect to postgres and use all of the postgres features,
like you asked, withouth connecting to a running database server.

Is this what you're asking?

Because.. with sqlite in RAM you _ARE_ using the whole stack of the
database, only there is no separate server process.

Marco Mariani writes:

> Ben Finney wrote:
>
> > I'm not interested in "performance", but rather reduction in external
> > dependencies for unit tests (i.e. unit tests should not rely on a
> > running database server).
>
> I'm not sure I understand.
> You cannot connect to postgres and use all of the postgres features,
> like you asked, withouth connecting to a running database server.
>
> Is this what you're asking?

Yes. I want to connect to a PostgreSQL database without the external
dependency of a cluster and already-running server.

> Because.. with sqlite in RAM you _ARE_ using the whole stack of the
> database, only there is no separate server process.

And no filesystem interaction, and no concurrency, and many other
things that come with a "dedicated to this one process" database.

All of these make it very useful for unit testing a unit of code that
would, under normal operation, connect to the concurrent database with
its on-disk cluster and all the rest.

I'm wanting to have these advantages, but have it be PostgreSQL so
that I don't have to make an in-memory SQLite database pretend to be
an in-memory PostgreSQL one.

--
\ "Pinky, are you pondering what I'm pondering?" "I think so, |
`\ Brain, but if we give peas a chance, won't the lima beans feel |
_o__) left out?" -- _Pinky and The Brain_ |
Ben Finney

Ben Finney wrote:

> Yes. I want to connect to a PostgreSQL database without the external
> dependency of a cluster and already-running server.
>
>> Because.. with sqlite in RAM you _ARE_ using the whole stack of the
>> database, only there is no separate server process.
>
> And no filesystem interaction, and no concurrency, and many other
> things that come with a "dedicated to this one process" database.

Oh well.
How can I phrase that... the whole of Postgres is on the server.
There is nothing you can fake client-side.

With sqlite, there is no server part, so the point is moot.


> All of these make it very useful for unit testing a unit of code that
> would, under normal operation, connect to the concurrent database with
> its on-disk cluster and all the rest.

Actually, I see nothing wrong with having a running db for testing
purposes. I've read about that argument in some book, but I feel free to
disagree.

You could have mock objects that return sets of data, sure, and many
people just do that.

Marco Mariani writes:

> Actually, I see nothing wrong with having a running db for testing
> purposes.

Note that I'm specifically talking about unit testing here, which *by
definition* is supposed to be isolating the unit of code under test as
much as feasible.

For any kind of testing other than unit testing, I agree than having a
running database server is fine.

> I've read about that argument in some book, but I feel free to
> disagree.

I think you've just got different terminology.

> You could have mock objects that return sets of data, sure, and many
> people just do that.

Yes, that's what I'm doing in my unit tests at the moment.

However, mocking the feature-rich behaviour of PostgreSQL is rather
cumbersome, hence my desire to have PostgreSQL do the work of
implementing itself, without so many other dependencies during the
unit test cases.

Thanks for your feedback so far, it's useful to see what others think.

--
\ "Know what I hate most? Rhetorical questions." -- Henry N. Camp |
`\ |
_o__) |
Ben Finney