Helps on how to create an empty PostgreSQL database quickly

[JonPSmith]

I have added PostgreSQL helpers to my EfCore.TestSupport. These create unique database names for each xUnit test class, options with logging etc.

I have found that creating an empty database using EnsureDeleted, then EnsureCreated takes ~12 seconds on my setup ( EnsureDeleted = 4 seconds, EnsureCreated = 8 second), which I think is a bit slow (I want fast tests!).

I have therefore created a method that uses Jimmy Bogart's Respawn library, and that takes 2.2 seconds if there is a database already there.

I also tried the drop all tables approach. The PostgreSQL commands run quickly from pgAdmin but it actually takes longer than the Respawn approach (something like 4 seconds) because EnsureCreated has to add the tables.

My questions are:

• Is 12 seconds for EnsureDeleted, then EnsureCreated a normal time, or is something I am doing wrong?
• Do you know of a quicker way to create an empty a PostgreSQL database?

NOTE: you can see the code for the fast / respawn approach here.

Include provider and version information

NOTE: My PostgreSQL 12 server is running in a Windows Subsystem for Linux (WSL2). Its a fast PC.

EF Core version: 6.0.0-rc.2
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0-rc.2"
Target framework: (e.g. .NET 6.0-rc.2)
Operating system: Windows
IDE: (e.g. Visual Studio 2022)

[roji]

Is 12 seconds for EnsureDeleted, then EnsureCreated a normal time, or is something I am doing wrong?

I gave it a try, and on my machine it's more like 7 seconds in total...

I also tried the drop all tables approach. The PostgreSQL commands run quickly from pgAdmin but it actually takes longer than the Respawn approach (something like 4 seconds) because EnsureCreated has to add the tables.

This is odd - actually creating tables should be very fast, the thing that takes up most of the time in EnsureCreated should be the database creation itself, which shouldn't be occurring in this case...

Stepping back, is the idea to create a different table schema on the same database (I'm not sure why that would be necessary), or simply to empty the tables from their data? If it's the latter, then rather than dropping tables (like with the DROP SCHEMA public CASCADE approach), you could use the PG TRUNCATE command (which is an efficient form of DELETE FROM table); note that it supports CASCADE as well.

If you really need to drop and recreate all tables, then the DROP SCHEMA public CASCADE approach seems reasonable, but I can't imagine the whole thing taking so long - can you share a project/schema?

[ajcvickers]

I guess ProstgreSQL is just a slow database system. 😉

[roji]

🔥 🧨 👊

[roji]

Yeah, databases in general are known to be more heavyweight in PG compared to e.g. SQL Server. Among other things, a connection is always made to a specific database, and switching the database on an existing connection simply isn't possible. PG definitely isn't made for creating/dropping lots databases quickly (but I'm not sure that's a good pattern even in other databases where it's faster...).

[ajcvickers]

You are so easy to troll. 😹

[roji]

I know, I know... I'm extremely trollable when it comes to PG :)

[JonPSmith]

Hi @roji,

Thanks for looking at this, and sorry for opening you up to @ajcvickers remarks ☹️ !!

I gave it a try, and on my machine it's more like 7 seconds in total...

OK, that sounds that my 12 second, which might be because you are on a pure Linux system. I remember before EF Core 5 (well really the Microsoft.Data.SqlClient change) EnsureDeleted + EnsureCreated took 10 seconds, and now its 1.5 seconds.

This is odd - actually creating tables should be very fast...

I enabled LogFor with timings and it looks like opening a connection to the server takes 1.5 seconds. To try this run the test TestPostgreSqlHelpers.TestEnsureCreatedAndEmptyPostgreSqlOk and see the output (details of the repo at the end).

Stepping back, is the idea to create a different table schema on the same database?

Ideally I would like a PostgreSQL version of your SQL Server EnsureClean method inside the test code of EF Core. @ajcvickers pointed this out and I have copied it into the EfCore.TestSupport library - its really fast (500 ms!) and it makes sure the database is up to date to the EF Core Model too.

However, Having a fast "empty all rows" approach is acceptable because the EfCore.TestSupport library has a DeleteAllPostgreSqlUnitTestDatabases that deletes all the PostgreSql databases that start by the test common database name. So, when the EF Core Model changes the developer has to run this and the next run of the test will create a new database.

can you share a project/schema?

The open-source library is in the repo https://github.com/JonPSmith/EfCore.TestSupport and the code to look at:

• PostgreSql helpers TestSupport.EfHelpers.PostgreSqlHelpers. NOTE: The EnsureCreatedAndEmptyPostgreSqlAsync method in this class contains the "drop all tables" approach, but commented out because it was slower than respawn.
• Tests of PostgreSql helpers Test.UnitTests.TestDataLayer.TestPostgreSqlHelpers

If you want to run the tests, then you need to edit PostgreSqlConnection connection string in the the appsetting.json in the Test project. The actual database name starts with the database name in that connection string but has the test class name added on the end so that each parallel unit test class has its own, unique database.

[roji]

I enabled LogFor with timings and it looks like opening a connection to the server takes 1.5 seconds. To try this run the test TestPostgreSqlHelpers.TestEnsureCreatedAndEmptyPostgreSqlOk and see the output (details of the repo at the end).

I ran TestEnsureCreatedAndEmptyPostgreSqlOk - the first time it took about 6 seconds, the second time about 1.6. This seems to be because the test does EnsureCreated, but not EnsureDeleted, so almost nothing has to happen the second time around; this seems problematic, by the way - if the schema changes, this test will start failing since EnsureCreated doesn't make sure the schema is in sync with the model. Adding EnsureDeleted before EnsureCreated makes the test run consistently in about 7.5 seconds.

To summarize, any approach which drops/creates an entire database per test or test class is generally going to be slow; maybe especially so on PostgreSQL, but probably on other databases as well (compared to alternative approaches, in any case). I don't think there's any way around that really.

Ideally I would like a PostgreSQL version of your SQL Server EnsureClean method inside the test code of EF Core. @ajcvickers pointed this out and I have copied it into the EfCore.TestSupport library - its really fast (500 ms!) and it makes sure the database is up to date to the EF Core Model too.

That exists - see NpgsqlDatabaseCleaner, you can use that if you want. Generally all EF Core providers which implement the EF specification test suite should have an implementation for this; I've opened #26550 to consider bringing this functionality into the product and exposing it to users writing integration tests.

One note on the implementation of database create/drop logic... Rather than copying database-specific logic (as you've done e.g. in DeleteAllPostgreSqlUnitTestDatabases), you may want to consider setting up a simple DbContext with the appropriate connection string, and then simply using context.Database.EnsureDeleted/Created; this would leverage the provider's IDatabaseCreator which already knows how to do all this, rather than copying the logic out for each database.

DeleteAllPostgreSqlUnitTestDatabases

Just to nitpick 🤣 Should this be called "unit test" databases? Typically "unit test" implies no real database is involved (as opposed to integration test).

[JonPSmith]

Hi @roji,

I ran TestEnsureCreatedAndEmptyPostgreSqlOk - the first time it took about 6 seconds, the second time about 1.6.

Yes, I assume that the EF Core Model doesn't change a lot so I was testing the "already have database" version. Other tests time the EnsureDeleted + EnsureCreated situation. I explain that if the EF Core Model you need to call the "DeleteaAll" method, but in SQL Server having to delete all the test databases isn't used so much because of SQL Server EnsureClean.

That exists - see NpgsqlDatabaseCleaner, you can use that if you want.

That would be perfect!! I will certainly look at that. For the SQL Server EnsureClean I had to edit the CreateDatabaseModelFactory ctor, which I will need to do with NpgsqlDatabaseCleaner. I have added a comment on issue #26550.

One note on the implementation of database create/drop logic

Yes, using context.Database.EnsureDeleted/Created would be a better way. I'll have a go at that.

Just to nitpick 🤣 Should this be called "unit test" databases...

Good point. I will change that.

Thanks for looking at this and providing really good tips/code. Thanks.

[roji]

My pleasure, and thanks for working on this and raising these thoughts - as I said I'm thinking about testing at the moment for the docs, so this is very relevant.

[JonPSmith]

Hi @roji

OK, I have copied the NpgsqlDatabaseCleaner code from the https://github.com/npgsql/efcore.pg repo (and added a heading with the efcore.pg license) and I have got it to run.

Issues I came across

• The NpgsqlDatabaseCleaner contains a code to detect ServerCompatibilityMode.Redshift, but it uses a NET6 only feature (conn.Settings). My library is multi-framework (netstandard2.1 = EF Core 5, net6.0 = EF Core 6). I have used an #if NET6_0_OR_GREATER which assumes that the database doesn't have the ServerCompatibilityMode.Redshift setting in EF Core 5. Is that sensible?
• The NpgsqlDatabaseCleaner uses an internal NpgsqlSqlGenerationHelper. Shouldn't be a problem, but the SQL Server Cleaner didn't use any internal types.

Performance figures

Here are the timings for the three approaches. NOTES:

• All tests start with an existing database, because this is the common situation.
• The tables are empty

Approach	7 tables	100 tables
EnsureDeleted + EnsureCreated	12.8 sec	13.4 sec
EnsureClean (PostgreSQL)	4.3 sec	5.1 sec
Respawn	2.2 sec	2.8 sec

I will provide both approaches:

• context.Database.EnsureClean();
• context.EnsureCreatedAndEmptyPostgreSqlAsync();

Certainly EnsureClean is a better approach to new developers as it automatically handles EF Core Model changes, so I will list the EnsureClean approach first with a recommendation to use that. Personally I think I would go with the Respawn approach because its nearly 50% quicker than EnsureClean (PostgreSQL), but its async, which is a pain as exception stacktrace is harder to read - only using these approaches for real will tell me what my preference is.

PS. If you want to look at the code its in the dev branch of the https://github.com/JonPSmith/EfCore.TestSupport . I don't put it into master branch until its ready to be turned into a NuGet package.