Generally speaking, just use postgres, it'll be fine. There are many ways to optimize for speed but the limits of MySQL are significant and will likely eventually come to bite you.
SQLite is good for native app local storage and also I think it's really a good way to transfer data in some use cases.
I've used both MySQL and Postgres, and somewhat prefer postgres nowadays. Though I'm not sure what you mean by "limits of MySQL are significant". In what way is it so significantly different? At my last job the I saw MySQL being used housing tens of thousands of tables, some of which having billions of records and over a petabyte of production data being used to analyze marketing metrics. Honestly just curious: what are the limits? I don't have any evidence to say what the limits to MySQL are.
Well. I won't give a comprehensive list. But list a few examples that should give you an idea of what I'm talking about. Until MySQL 8.0 there wasn't even support for any window functions. In 8.0 there's minimal support for window functions. There's no support for row level security. Upsert constraints are reduced to INSERT or REPLACE (which is dicey).
Extending on Jacob's points, one that I find inexcusable (and has in fact hit me in the face a few moments ago at work) is the fact that MySQL does not support Transactional DDL and only supports Atomic DDL starting from 8.0. Whereas both PostgreSQL and SQLite support this.
To specify it's significance for those unaware: say your application manages migrations and needs to do several steps in one go (e.g. alter some column name and some data modification), you should be able to start a transaction and run all steps. If something goes wrong (e.g. the application is somehow terminated mid-migration, as happened here), then all migration steps are perfectly reverted. Hence it will be completely ready for a new apply, or, you can go back to the old version of the software and you know the database should be in a state as if there was no update whatsoever.
Yes, this can be alleviated by making a backup before upgrading, which we did, but it's also demonstrated by two systems, one of which (SQLite) I always saw as a simplistic database, that it can be done. I can kill the application modifying the schema of an SQLite database without any ill effects.
I agree that the real options are SQLite and Postgres. If you want SQL database for offline use in a single app, SQLite is the way to go. For everything else you should pick up Postgres.
The whole point of MySQL and MariaDB is backwards compatibility. New projects should not start with either.
yep I agree SQLite for little in app stuff, and Postres for all the rest, but if I am forced to use the MySQl/MariaDB route then MariaDB is better, it keeps you out of clutches of Oracle
Generally speaking, just use postgres, it'll be fine. There are many ways to optimize for speed but the limits of MySQL are significant and will likely eventually come to bite you.
SQLite is good for native app local storage and also I think it's really a good way to transfer data in some use cases.
I've used both MySQL and Postgres, and somewhat prefer postgres nowadays. Though I'm not sure what you mean by "limits of MySQL are significant". In what way is it so significantly different? At my last job the I saw MySQL being used housing tens of thousands of tables, some of which having billions of records and over a petabyte of production data being used to analyze marketing metrics. Honestly just curious: what are the limits? I don't have any evidence to say what the limits to MySQL are.
Well. I won't give a comprehensive list. But list a few examples that should give you an idea of what I'm talking about. Until MySQL 8.0 there wasn't even support for any window functions. In 8.0 there's minimal support for window functions. There's no support for row level security. Upsert constraints are reduced to INSERT or REPLACE (which is dicey).
So more of a functional limitation rather than a scalability one then right?
Extending on Jacob's points, one that I find inexcusable (and has in fact hit me in the face a few moments ago at work) is the fact that MySQL does not support Transactional DDL and only supports Atomic DDL starting from 8.0. Whereas both PostgreSQL and SQLite support this.
To specify it's significance for those unaware: say your application manages migrations and needs to do several steps in one go (e.g. alter some column name and some data modification), you should be able to start a transaction and run all steps. If something goes wrong (e.g. the application is somehow terminated mid-migration, as happened here), then all migration steps are perfectly reverted. Hence it will be completely ready for a new apply, or, you can go back to the old version of the software and you know the database should be in a state as if there was no update whatsoever.
Yes, this can be alleviated by making a backup before upgrading, which we did, but it's also demonstrated by two systems, one of which (SQLite) I always saw as a simplistic database, that it can be done. I can kill the application modifying the schema of an SQLite database without any ill effects.
I agree that the real options are SQLite and Postgres. If you want SQL database for offline use in a single app, SQLite is the way to go. For everything else you should pick up Postgres.
The whole point of MySQL and MariaDB is backwards compatibility. New projects should not start with either.
Sqlite might also be an option in some online cases. But yeah postgres is usually the way to go.
yep I agree SQLite for little in app stuff, and Postres for all the rest, but if I am forced to use the MySQl/MariaDB route then MariaDB is better, it keeps you out of clutches of Oracle
I'm thinking of doing an app with RedwoodJS, and use Postgres for everything.
good to know, thanks for the insight!
For offline first web apps sqllite-worker is worth consideration as well.
Thanks, not familiar but I'll take a look!