#Poor performances when migrating from SQLite to Postgresql

23 messages · Page 1 of 1 (latest)

leaden steppe
#

Description

I am using Laravel 11 with Inertia.js and React. Until now, as I was prototyping, I was using SQLite in development environment as well as in a test prod environnement I had set up.

Recently attempted to deploy to a real production server based in Switzerland (I'm based in Montréal), with Laravel Forge to provision the server, and Postgresql instead of SQLite (hosted on the same server).

Ever since doing the switch from SQLite to Postgresql, I observe huge performances issues, on every page change, to the point where, when deployed, most pages return 504 timeout. In my local environnement, when connected to the same database, I don't have 504, but the requests are very noticeably slow, taking 10, 40 seconds sometimes.

I considered that the issue might come from latency, but while the exact delay changes, I observed the same behaviour with running the database:

  • On the Forge provisionned server I mentioned and accessing the website hosted on that server (so no network latency)
  • On that same server but connecting with a VPS tunnel on my machine
  • On my local machine (macbook pro M3) with psql
  • On Supabase with a database hosted in Paris (further from me)
  • On Supabase with a database hosted in Canada (closer to me)

I attempted to refactor my code, my migrations, adding indexes, changing types.

Debugbar show relatively efficient requests, I do eager loading when necessary. Most are around 300ms.

When switching back to SQLite, on the exact version of the code, the website is back to being snappy and performant. I know that, SQLite being in memory, it's supposedly faster, but here the performances with posgresql are really abyssimal.

As such, I assume that there might be some framework related overhead of my doing, like a missing constraint, etc, but I can't identify it on my own and I wonder if anyone came across such behaviour?

I am not excluding that the issue might come from somewhere else in my code (controllers, services, API resources, etc), but just that it only appears when i switch from SQLite to Postgresql.

Relevant code

I don't have any specific relevant code to show as it applies to pretty much every page/controller. I will be open to showing anything you think might me relevant though.

Thank you in advance for your help!

#

Some additionnal notes:

  • Doing some tasks like seeding the database is very slow when i connect to the forge hosted database (can take up to 6 minutes), while it's rather fast (albeit noticeably slower than sqlite) when on a local database
  • Tinker takes slightly more time to load from scratch and answer queries once open, but it's barely noticeable.
main fiber
#

Database location is going to matter. If your server is in Switzerland and your DB is in Canada, then every single DB interaction is going to be incredibly slow as your webserver needs to communicate with it multiple times. A DB should be as close as possible to your webserver, not to your users.

leaden steppe
# main fiber Database location is going to matter. If your server is in Switzerland and your ...

Yes I agree and latency was my first thought, but the most noticeable problems are when the database and the server are on the same machine (provisioned by forge) and not using the network at all.

I, as the developer, am located in Canada, but most of my users will be located in Western Europe, hence why I chose the server there. But yes, here, after all my tests, while I think that latency does matter, I don’t think it’s the issue.

mellow shadow
#

A long time ago I tested MySQL and Postgres with pure PHP using only the native functions like mysqli and PDO and I remember that Postgres was incredibly slow compared to mysql. Do you have a specific reason to use Postgres instead of mysql? From my personal experience and anecdotal evidence, mysql works better with PHP ecosystem than postgres. I don't know very many projects that default to Postrges except Symfony framework. WP, Laravel, Prestashop, Drupal, Joomla, etc. everything is MySQL first.

#

The guy who specializes in databases Aaraon Francis uses MySQL instead of Postgres as well, so, unless you need a specific thing that can only Postgres do, I wouldn't use it. To this day, the only time I wanted Postgres functionality was when I wanted to join two tables in a weird way via full outer join or something. MySQL didn't have it but Postgres did, so I ended up doing some workarounds in the end. But that was because of my bad db design and it was not very nice join so plan your db structure carefully 😉

#

From anecdotal experience mysql feels faster for selects. Also I think it's better setup to do the job, out of the box. Postgres needs more tweaking in my opinion. But benchmarks say it's better in the end. However, it's not my experience as an average web dev. So, if you can set it up, it probably will perform better. But my choice would be MySQL.

leaden steppe
# mellow shadow A long time ago I tested MySQL and Postgres with pure PHP using only the native ...

I actually don't have a specific reason other than Postgresql being the most popular option these days! Few months ago, for the same project, but with a different stack (next.js), I was using MySQL with PlanetScale data, and had to migrate out, to Supabase when they removed their free/cheap plan, it was rather painful. I hoped to use postgresql to use managed services like Supabase as a fallback plan + rely on the ecosystem as a whole, but i am not against using MySQL.

Truth is I'm very new to the laravel/php ecosystem, so i do wonder what other people use!

Either way, I still think that there is something wrong, somewhere in my migrations, because it's really abnormally slow, to the point where I get 504 timeouts instead of my pages. Even if I do go with MySQL in the end, I would like to fix it.

leaden steppe
leaden steppe
# mellow shadow From anecdotal experience mysql feels faster for selects. Also I think it's bett...

Hey, so I continued to investigate with Postgresql, but followed your advice and gave more chances do MySQL. I had to change some things in my migrations that MySQL highlighted, but other than that, it works out of the box and is up to par with my expectations in terms of speed, even if the server/database is half accross the world!

I will continue to investigate, since it's rather frustrating not to know why, but other than that, I plan on staying with MySQL. Any tips you would give? Or pitfalls i should avoid falling into?

Either way, a great thank you for convincing me!

leaden steppe
#

In fact, performances seem to have greatly improved with postgresql as well, following that change, whether local or remote. I still find it much slower than mysql, in a noticeable but acceptable way.

For later reference, here was the problem MySQL highlighted, that I think made postgresql bug too.

  $table->unsignedBigInteger('created_by')->nullable();
  $table->foreign('created_by')->references('id')->on('users')->onDelete('set null');

In my "media" table (used everywhere) I had a "onDelete('set null')" reference on the users table, but the field was not nullable. I had the same issues in a couple of other migration files.

Thank you again for your help and suggestions!

main fiber
#

Simple selects from an index are going to be a bit slower, due to the way InnoDB and posgresql work. The former would be faster, the latter slightly slower albeit way better at doing complex queries. But not multiple seconds

glass jolt
leaden steppe
glass jolt
leaden steppe
glass jolt
#

thanks

leaden steppe
#

You should ask about it in the turso discord. The found was recently telling me that they have an official PHP adapter that should work, I think.

glass jolt
leaden steppe
glass jolt
leaden steppe
#

Otherwise yes, consider Laravel sail instead of Herd maybe?