In those cases SQLite will probably have much better speed and a lower memory footprint.
There are a number of different ways to do it which range from Next.js with WP as a headless CRM (my current preference), or using a static site generator plugin inside of WP. I can recommend "Simply Static" as a handy plugin that (combined with nginx) will make as much of your site as possible static but still updates quickly and can allow some dynamism.
But it does introduce complexity, and the more frequently the site changes the less it makes sense. As the site grows also most sites end up wanting dynamic stuff that often can't (or shouldn't) be done on the client-side, slowly making the static build less and less useful. It's just not that simple.
On WordPress, you can change a tag, hit save, and the change is live.
On a static site, the change would look more like: edit a file, rebuild the site, deploy to production.
I left WordPress for static sites 8 years ago and never looked back. It's so nice to eliminate the database and web server from your stack and have everything under source control.
I don't know much about WordPress but I wouldn't expect a simple website's DB to be more than a few MB.
SQLite can get tricky, if the database files get bigger. But up to a few GB there should be no problem at all (SQLite supports up to 281 TB though)
For an affiliate site with millions of pages you need much more to make WordPress sluggish. On the other hand, it only takes a single badly written plugin to achieve the same effect. :D
There are a variety of mature MySQL dialect parsers available, and MySQL should have its own public APIs for transforming a query into an AST. Any of those would be a safer and more correct alternative.
Or at least how much WP could be changed to meet at a common ground. How hard would be to run WP on Postgres? MSSQL? Or that legal company that sells a DB?
Using that + a WP composer package (like https://packagist.org/packages/roots/wordpress) is much easier to maintain that a full "fork" of WP.
- the WP-sqlite-db plugin
- akismet added
- themes before twentytwenty removed
- an empty 'wordpress.db'
- wp-config.db setup set to use the WP-sqlite-db plugin with the right file
Which is all work to do to get setup correctly.
NOTE: the wp-config.php also needs to be modified to remove the keys and salts as well.
It's really just a Wordpress install with this plugin preconfigured: https://github.com/aaemnnosttv/wp-sqlite-db
That plugin is where the interesting stuff happens.
Short version: With MySQL, I can't simply backup or deploy a copy of WP by zipping up a folder full of files. The database has to be backed-up and deployed separately but in sync with the folder of files too. Moving to an architecture where the database are files inside that folder would simplify all of this.
One of wordpress's delights on the DB layer at the time (may still be true?) is that a sizeable number of the database entries were marshalled php objects. So many of them also ended up encoding the domain name in some way, that what we ended up doing was writing some code to essentially just rsync files from one server to the other, and then download the tables from MySQL, update all the records one at a time, and then upload to the new database location. It worked, remarkably well, but yikes. The potential fragility in that solution always scared me.
When I left that company was when static site generators started to take off, but they were all still very techie oriented, with markdown files, rather than any kind of dynamic website to create the content in.
That was one of those rare cases where it felt like I could actually see a potential product & market.
I suppose one could automate the process and temporarily de-weaponize the website to enable updates then swap the html back in when done.
But I could have been completely wrong. Anyone have real world experience?
(BTW - My site is small and 99% of users are not logged in, and 99% of pages are unchanging, so a perfect use case for a static site.)
Some perf tweaks I made were to point the main site’s wp-content/uploads at the WP htdocs via a nginx path mapping, and to tell SS to never export anything in uploads. Reduces the site generation to just the html, css, and JS.
The other caution is that it doesn't export anything that's not linked. So if you've got no links to the yearly archives, then you get no index.html files for /YYYY/ either. Unfortunately, the Archives block in Wordpress doesn't offer a toggle between months and years, so you can't just create another block in the "archive finder" page and set it to years.
The support forum section of wordpress.org for the plugin appears to be a lot of people asking for support and no comments by the plugin author. If it works for you, then that's probably not an issue.
My homepage running on vanilla Wordpress: https://www.skyfaller.space/
The same site using Simply Static: https://static.skyfaller.space/
Does it feel slightly faster?
I think people underestimate what's possible cheaply and simply with static pages. (Not saying Cloudflare is not free for this load but it is a separate additional step you'd need.)
This can be fixed by adding the following in db.php at the end
$GLOBALS['wpdb']->query('PRAGMA journal_mode = wal;');
This "serverless" part confused me initially, since that term has somehow come to mean "the platform handles routing and scaling up/down your servers".
But this use of the term, "there is no server process", is much more sensible!
WP is high on reads, low on writes and caches easily. It would also make backups so much easier and without the need to rely on wacky, untrustworthy WP plugins.
Amazon did this in reverse; if I’m not misremembering their Postgres support is actually a shim that translates pgsql queries to those compatible with their Aurora db, a MySQL derivative. I don’t know if they only support the subset of operations that can be translated one-to-one or if they also added some core features to support Postgres-specific functionality.
An interesting case is going from SQLite to anything else - you can actually just create a VFS (SQLite storage abstraction plugged directly into the library/engine) to store the underlying data on a remote MySQL/PostgreSQL db altogether without translating anything. Reads and writes would continue to go through the SQLite api but end up stored on the rdbms of your choice, locally or remotely.
It'd be great of WordPress supported SQLite officially but I don't think I'd recommend this approach to anyone for a real site, even a personal one.
Yes you can compile some of these in as extensions but for example even the sqlite3 CLI you have from your distro comes with almost none of these extensions.
The problem is that it’s designed so you can go from SQLite to pgsql, not the other way around - as you mention, SQLite is lean on features (and associated syntax).
Now it's near impossible to see what changes they do over original WP core.
The predecessor plugin, SQLite Integration, was fairly stable with most other plugins, but plugins trying to do unconventional stuff or hit the DB directly would refuse to work. The main issue was that the plugin author stopped updating the plugin, while newer versions of WordPress didn't suppport it.
Is this making a comparison, or is it just saying it's fast and secure in absolute terms? I have a feeling there's not much of a difference either way