[00:02] cache? I'm only half joking when I say this. And Postgress is really my favorite database because it does so many things. Well, it's got relational data. It works well with JSON. It works well with vectors using DPG vector
[00:15] extension and you can also use it as a cache with the unlock tables feature. Now, Microsoft came out with a distributed cache implementation that uses Postgress. So, we're going to check it out in this video. I'll drop the link
[00:28] to this repo in the description of the video and the library name is Microsoft Extensions Caching Postgress. It's still pretty new. The number of downloads is quite low, but it looks like there's a stable version and we're going to
[00:41] explore it in this video. Now, the documentation for getting started is detailed enough. It nicely outlines the configuration options we have with the also configure the schema name, the table name, if we want the library to
[00:54] write ahead log or not. This is going to affect if we are using the unlock table feature or not, and then some settings about the configuration options. There's
[01:06] also a nice code snippet here showing you how to set these values in your code. And then you can just use this as an I distributed cache implementation, implementation, the most popular one
[01:18] probably using Reddus. And what this also means is that you can use this implementation of the I distributed cache interface with hybrid cache. So if you're already using Postgris, want a simple cache for your application and
[01:30] external service, then you can just go ahead and use your Postgres database. Plug in this library and you're off to the races. So let's see how we can use it. And I'm just going to copy the name of this library and let's head over to
[01:43] Visual Studio. From here I'm going to install this library as a Nougat package. So, let's browse for it. And the latest stable version is 1.2.0. And I'm going to install it right away. Let's also go ahead and copy the
[01:58] environment variables from here. And I'm going to drop them into my app settings JSON. So, let's add them here. And then I'm going to customize a few of them. So, the connection string I'm going to omit as I want to set this from my
[02:14] Aspire app host. As far as the posgress cache options go, I'm fine with using the default value specified here. And we can always customize this if we need to. Now, let's take care of this connection string. So, inside of my app host, I'm
[02:28] going to go to my API service and I'll say with environment and we can specify our environment variable. So, I'll say connection strings double underscore and
[02:40] then Postgress cache. I'm going to provide my database resource which will be resolved to a connection string that's going to be set in this variable. Of course, I can also use the existing connection string right here. So, just
[02:53] be aware that that is also a valid option. As far as how you can configure this, you need to say builder services add distributed posgress cache. Now what pains me is that the options that we have in the application settings aren't
[03:08] picked up automatically at least from my testing. So the bare minimum that I had to do is automatically set the connection string. So we can say builder configuration get connection string and let's grab the posgress cache connection
[03:21] string. I could also have reused the one that I already have which was called caching DB but let's stick with the example that I started with. And then to do something like this. I'll say builder configuration get section grab the
[03:37] posgress cache section and then I'll say bind and provide my options object. And this is going to bind the respective config values to the properties with the same name on the Postgress cache options. So how do we use this? Well, as
[03:52] I said, you only need to inject the I distributed cache interface. So let's add that. I'm also going to include the missing references. And I've got two simple endpoints here that I want to implement. And I'll use my distributed
[04:05] cache to set a string in the post endpoint where the argument is a cache item object containing a key and the value. And for the value, I'm accepting some object which I'll serialize as JSON. So that's it for my post endpoint.
[04:22] And then in the get endpoint, we can say await. And I'm missing the I distributed cache. So let's go ahead and inject that instead of the MPGSQL data source. And now I can say get string async. You could also use the binary API if you
[04:36] could also use the binary API if you want to. And I want to specify my key and I should get back the cache data. So if the cache data is null, then we can go ahead and return results and then not found. Otherwise, I'm going to say JSON
[04:51] serializer d serialize and I'm going to des serialize into an object and let's just pass in our cache data value. So there we have it. Two endpoints, one for setting the value in the cache, the other for fetching the value from the
[05:05] cache. So let's go ahead and run this. In the Aspire dashboard, you can see our services are up and running. You can just go ahead and quickly confirm that the connection strings that you are looking for are in place, which is the
[05:18] case here. So I expect the library to be able to connect to Postgress. Let's go ahead and test that. First, we're going to send a post request to the Postgress cache endpoint. Our cache key is going to be just test. And I'm specifying some
[05:31] dummy object here that I want to cache. So you can see this succeeds with a 200 okay response. And now I should be able to fetch this value and get back 200 okay and our cached object. And if I change the cache key, we should see 404
[05:46] working. But what's actually happening under the hood? So my trusty distributed traces come to the rescue and we can see our three requests here. Now let's take a look at the first one. So we can see a couple of queries sent here to posgress
[06:00] and the first one is just connecting to the posgress database. Then we've got a query here creating the schema because it doesn't exist. So it's going to create the public schema. This one is already taken care of if you just run a
[06:14] default Postgress instance. But the cache table is missing. And because we set the use right ahead log value to false, this is created as an unlogged table which is what makes posgress interesting for caching unlocked tables
[06:26] don't use the write ahead log. Therefore, they are faster to insert into. But the downside is if posgress crashes, you lose all the data in this table, which is fine for something volatile and short-lived like a cache.
[06:40] you better write performance. Then the next query we have here is the write or insert into our cache table. You can see it also takes care of any conflicts. So if there's already an existing record with the same key is going to perform an
[06:56] another query here which is going to delete any expired records. Now let's test out what happens if we write to the database again. So I'll just quickly send another request for the same cache key. And if we go back to our
[07:10] distributed traces and take a look here, you'll see that there's only our insert or update in case of a conflict. There's no delete statement like in the first example. So I didn't dive into the source code of the library too much, but
[07:24] I suspect that the delete statement is run every 30 minutes or so. If I recall controlling this value in when we were configuring the poss. The other two examples here are just a select from our cache which also performs an update on
[07:40] the expiration time. So I guess this is required for caching with postgress to work as expected but it's also some unnecessary work to compensate for the expiration for values in a relational database. Nonetheless, this is going to
[07:55] fetch the value from the cache. But what we are interested in next is what is the performance of this implementation. To test out the performance, I made some Ksix scripts that are going to test out the insert and the query performance.
[08:10] So, let's first run the insert test to see what it looks like. And for good measure, let's run it one more time. And in the results, we can see that we are getting an average response time for our overall request, including the cache
[08:24] insert of around 3.5 milliseconds. Overall, we are edging close to 14,000 requests per second. Let's go ahead and run the query test next to see what the read performance is like. And you can see that this is equally as fast. So I'm
[08:38] going to run it again. And the average request duration is 3.38 milliseconds. The P95 is 9.3 milliseconds. And also the P95 is 9.3 milliseconds. So remember that this was using the unlocked cache table. But how does the performance
[08:53] change if we are using a cache table with right ahead logging? So I went ahead and made some updates behind the scenes for the library to create a cache table with right ahead logging. So let's go ahead and rerun our tests first for
[09:06] inserting the data and let's observe the results. I'll run them again just for good measure. But we can already see there's a difference. The right ahead log adds some overhead to our caching. So now we are getting 4 and 1/2
[09:20] versus 3 and 1/2 milliseconds with unlock tables. The benefit here is that if posgress crashes, we won't lose any data. So if you care about consistency, this is definitely an option to explore.
[09:33] Let's go ahead and do the query test now and see if the results differ for reading the data. And let's do them again for good measure. And we can see again that the response time averages out at 4 1/2 milliseconds. So about 40%
[09:49] slower compared to the unlock version, which does surprise me a little. But this is what we observe when running the server locally. Now, what if we go ahead and test this with a remote server? I've got a dock ploy instance running on a
[10:02] VPS. And doc ploy, if you're not aware, is a platform as a service that makes it easy to self-host your applications and also run any additional services. One of those services is a database. And I'm going to run a Postgress instance. Let's
[10:16] call this the caching database. I'm going to leave most of the options with their default versions. But instead of Postgress 15, let's run Postgress 17 for example and I'm going to go ahead and create my container. And once it's
[10:30] created, I'm going to go ahead and deploy it. So now this should spin up my container instance after pulling the Docker image. And after a few moments, I've got a remote database up and running on my VPS. The next thing would
[10:44] be exposing it to the internet. Let's use the default Postgress port of 5432. And now I get an external host here that I can use. And I'm going to copy this and set it as my connection string. So I've got my remote Postgress instance
[10:59] running behind the scenes. And I'm going to kick off the insert test here. And I So you can see that something is actually happening on our Postgress database running in a VPS. And obviously because this is a remote server, the
[11:13] response time is going to vary. Now, what I'm seeing here on average per request a 16.8 millisecond response time, which is pretty decent. And if we go ahead and run the query test, I expect we're going to see similar
[11:27] performance. Now, I'm noting that this is using an unlock cache table behind moment more for the test to complete. And you can see that the request duration is again similar, 16.6 milliseconds. So, this isn't really all
[11:42] too bad considering that this is a remote server. Normally, you want your API and your database to be colloccated at least in the same data center to reduce the latency between them. So, you can see how we went from 3 to 4
[11:55] millisecond response time when running locally to 16 millisecond response time when running on a remote server. Now, I definitely agree that this isn't for everyone. However, it's an option to explore if what you're using is posgress
[12:08] additional infrastructure. If you enjoyed this video, I think you should watch this one next where I show you how to scale the Outbox pattern that's using Postgress behind the scenes for storage to process more than two billion
[12:21] messages per day. If you enjoyed this video, smash that like button. Thanks a lot for watching and until next time, stay awesome.