Poor performances when migrating from SQLite to Postgresql

@martinbean Thank you @martinbean! Well, that's what I've been attempting to do, as I tried to demonstrate in my original post. I tried with multiple database providers, in different locations, tried to see where the overhead could come from (like if there was any chance it could come from inertia or the api resources). I even doubled my server resources to see if it could be some form of CPU bottleneck.

It clearly does, to some extent, come from the migration from sqlite to postgresql as even tinker is stalling, when ran on the server.

My current hypothesis is that it comes from something that's barely legal in Postgresql (hence why it doesn't give me any error), but that sqlite is very lenient with, like, a missing unique() on a constrained field or something. I just wondered if anyone ever came across such behaviour.

Either way I thank you for your answer! And I must agree, such questions are hard to answer, but I already spent multiple days debugging and trying multiple scenarios, so I reached for my peers.

@mathcrln You need to measure a single request rather than just swapping things in and out, hoping it’ll magically fix things.

There are many steps between you typing a URL into a browser’s address bar and hitting enter, and then getting a HTML page rendered in your browser. The following things will occur:

• Address is converted to IP address
• Request is sent to server at that IP address for handling
• Server uses web server like Apache or nginx to route the request
• A script (i.e. Laravel’s front controller) picks up the request for handling
• Laravel then has various stages it goes through:
  • Framework is booted
  • Global middleware is ran
  • Router tries to route request
  • Route-specific middleware is ran
  • Request is routed to a controller action for handling
  • The controller action that does things, including connecting to database if not already connected
  • Controller action performs business logic
  • Controller action returns response
  • Any middleware handling responses are ran
  • Response reaches your browser

So there are a lot of steps (and probably more I’ve missed given I’m typing this at almost 1 AM local time). You need to measure the time in between these steps for a single request, and see where the latest proportion of the time is spent. When you identify where a request is getting help up, you then have a better idea where the actual problem may lie, and where to focus your efforts.

@martinbean Thank you for taking the time at 1AM, i appreciate it!

And thank you as well for your insight! I admit that I still lack the tools to test every step of the lifecycle of my requests that you mentioned (if you have any specific tool other than debugbar, POSTGRESQL Analyze, \DB::enableQueryLog()). I'm not saying that Postgresql is the culprit, I'm just saying that it's the one that I did identify.

• Pages that don't rely on data being fetched from the database load almost instantly (eg contact page, though there is still the session handler being ran).
• As mentioned, no problem whatsoever is perceived with sqlite
• Even pages with the barest and most basic controllers/logic run slow

class PageController extends Controller
{

    public function __invoke(Request $request)
    {
        $page = Page::where('slug', $request->slug)->firstOrFail();

        return inertia('Pages/Show', compact('page'));
    }
}

class Page extends Model
{
    use HasFactory;
    use Publishable;

    protected $fillable = ['title', 'slug', 'description','content'];

    protected static function boot()
    {
        parent::boot();

        static::creating(function ($model) {
            if (is_null($model->created_by)) {
                $model->created_by = Auth::id();
            }
        });

        static::saving(function ($model) {
            if (is_null($model->last_updated_by)) {
                $model->last_updated_by = Auth::id();
            }
        });
    }

    public function createdBy():BelongsTo
    {
        return $this->belongsTo(User::class, 'created_by');
    }
    public function updatedBy(): BelongsTo
    {
        return $this->belongsTo(User::class, 'last_updated_by');
    }
}

(in web.php)

			Route::get('/{slug}', PageController::class)->name("page.show");

In fact, even a api route that 1) doesn't return a view 2) doesn't have any middleware applied return a 504 Gateway TIme-out

Route::get('/api/programs', [ProgramAPIController::class, 'index']); 
Route::get('/api/programs/{slug}', [ProgramAPIController::class, 'show']);

Beside my idea that it could be due to inefficient db design on my part, it could also be some form of bottleneck caused by php-fpm or misconfiguration with nginx? It's just hard to debug or fix.