Database Optimization

[dhextras]

The current database connection handling has several critical inefficiencies.

1. Repeated Connection Initialization:

   • The database connection is being initialized for each request, which could become a bottleneck. Creating new connections increases latency and failure rates as user demand grows.

2. Connection Initialization:

   • Even if needed to initialize the connection for each request, there's no need to initialize a connection at the top if the URL/code is already found in the cache, as this avoids unnecessary overhead.

3. Access Count Management:

   • Last access count and access count should be saved in the cache when redirecting and only updated when accessing via the stats page, reducing unnecessary database requests.

4. Cache Utilization:

   • The cache is only being used in the redirect handler. It should also be implemented in other handlers to improve overall performance.

[Hamza12700]

The database connection is being initialized for each request, which could become a bottleneck. Creating new connections increases latency and failure rates as user demand grows.

This is fine because the Go database/sql package have a connection pool, so it reuse the connections and closing the connection just returns the connection to the connection pool.

[dhextras]

The database connection is being initialized for each request, which could become a bottleneck. Creating new connections increases latency and failure rates as user demand grows.

This is fine because the Go database/sql package have a connection pool, so it reuse the connections and closing the connection just returns the connection to the connection pool.

Ohh got it, that's cool then, but what about the other 3 issues mentioned above

[Hamza12700]

The cache is only being used in the redirect handler. It should also be implemented in other handlers to improve overall performance.

The reason the caching is only being used in the redirect route is because the redirect is going to be handling a lot of request per second so caching the results would make a huge difference in performance the rest is taking user input and operating on them.

[dhextras]

The reason the caching is only being used in the redirect route is because the redirect is going to be handling a lot of request per second so caching the results would make a huge difference in performance the rest is taking user input and operating on them.

Ohh okay got it, But why not use cache first on other handler, even if it just gives a slight improvement, is there any particular reason not to use cache on other handlesr

[Hamza12700]

Ohh okay got it, But why not use cache first on other handler, even if it just gives a slight improvement, is there any particular reason not to use cache on other handlesr

We don't wanna cache things that aren't frequently used because that would just increase the memory load.

[dhextras]

We don't wanna cache things that aren't frequently used because that would just increase the memory load.

Ohh okay got it. what about the other 2 shouldn't we cache the access count and last accessed upon redirection and update them on the db only when they get accessed via the stat's page?

[Hamza12700]

Ohh okay got it. what about the other 2 shouldn't we cache the access count and last accessed upon redirection and update them on the db only when they get accessed via the stat's page?

Yeah, we definitely wanna cache access_count and last_accessed and also update them in cache and maybe after some time write them it the database.

[dhextras]

Ohh okay got it. what about the other 2 shouldn't we cache the access count and last accessed upon redirection and update them on the db only when they get accessed via the stat's page?

Yeah, we definitely wanna cache access_count and last_accessed and also update them in cache and maybe after some time write them it the database.

Yeah I was thinking either update from cache when visiting the stats page or if the cache became to old and never been updater afterwards then push it the db

[dhextras]

@Hamza12700

Alr then I'll work on the issue some time later today or tmr, if you gonna do it make sure to assign it you before doing so

[Hamza12700]

Yeah I was thing either update from cache when visiting the stats page or if the cache became to old and never been updater afterwards then push it the db

Yeah, in the stats page if have to make a call to the db. Visiting the stats page and instead reading it from the database we would read it from the cache because cache has the updated data we need and afterward write that in the database and clear the cache.

[dhextras]

Yeah I was thing either update from cache when visiting the stats page or if the cache became to old and never been updater afterwards then push it the db

Yeah, in the stats page if have to make a call to the db. Visiting the stats page and instead reading it from the database we would read it from the cache because cache has the updated data we need and afterward write that in the database and clear the cache.

Yup exactly