MiniProfiler - Saving results to PostgreSQL database - table creation scripts

MiniProfiler for .NET is a great tool for analyzing performance on isolated areas of code. I've been looking at storing results in the application database but found there didn't seem to be much in the way of documentation around this feature. A couple of posts I found suggest others have found the same.

https://github.com/MiniProfiler/dotnet/issues/381
https://stackoverflow.com/questions/14695460/how-to-store-mvc-miniprofiler-results

MiniProfiler DB persistence is configured by adding the below in your .NET core Startup.cs ConfigureServices method.

services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
    options.Storage = new PostgreSqlStorage(Configuration["Db:ConnectionStrings:PostgresSql"]);
});

MiniProfiler exposes the script for table creation in the TableCreationScripts property, which I found by looking in the MiniProfiler for .NET source. Presumably this is intended for use with DB migration runners. I didn't want to integrate MiniProfiler into my migration runner, I just wanted the scripts, so I added some code as below and set a breakpoint to allow me to copy out the script:

new PostgreSqlStorage(Configuration["Db:ConnectionStrings:PostgresSql"]).TableCreationScripts

The result for the PostgreSQL storage type at the time of writing is as follows:

CREATE TABLE MiniProfilers
(
    RowId                                serial primary key,
    Id                                   uuid not null, -- don't cluster on a guid
    RootTimingId                         uuid null,
    Name                                 varchar(200) null,
    Started                              timestamp(3) not null,
    DurationMilliseconds                 decimal(15,1) not null,
    "User"                               varchar(100) null,
    HasUserViewed                        boolean not null,
    MachineName                          varchar(100) null,
    CustomLinksJson                      varchar,
    ClientTimingsRedirectCount           integer null
);
-- displaying results selects everything based on the main MiniProfilers.Id column
CREATE UNIQUE INDEX IX_MiniProfilers_Id ON MiniProfilers (Id);

-- speeds up a query that is called on every .Stop()
CREATE INDEX IX_MiniProfilers_User_HasUserViewed_Includes ON MiniProfilers ("User", HasUserViewed);

CREATE TABLE MiniProfilerTimings
(
    RowId                               serial primary key,
    Id                                  uuid not null,
    MiniProfilerId                      uuid not null,
    ParentTimingId                      uuid null,
    Name                                varchar(200) not null,
    DurationMilliseconds                decimal(15,3) not null,
    StartMilliseconds                   decimal(15,3) not null,
    IsRoot                              boolean not null,
    Depth                               smallint not null,
    CustomTimingsJson                   varchar null
);

CREATE UNIQUE INDEX IX_MiniProfilerTimings_Id ON MiniProfilerTimings (Id);
CREATE INDEX IX_MiniProfilerTimings_MiniProfilerId ON MiniProfilerTimings (MiniProfilerId);

CREATE TABLE MiniProfilerClientTimings
(
    RowId                               serial primary key,
    Id                                  uuid not null,
    MiniProfilerId                      uuid not null,
    Name                                varchar(200) not null,
    Start                               decimal(9, 3) not null,
    Duration                            decimal(9, 3) not null
);

CREATE UNIQUE INDEX IX_MiniProfilerClientTimings_Id on MiniProfilerClientTimings (Id);
CREATE INDEX IX_MiniProfilerClientTimings_MiniProfilerId on MiniProfilerClientTimings (MiniProfilerId);

The information on this site is provided “AS IS” and without warranties of any kind either
express or implied. To the fullest extent permissible pursuant to applicable laws, the author disclaims all warranties, express or implied, including, but not limited to, implied warranties of merchantability, non-infringement and suitability for a particular purpose.