Which RDBMS?
-
SQLite can certainly be used as an in-memory database, but only within the context of a single process. So maybe not useful in your use case
"A little song, a little dance, a little seltzer down your pants" Chuckles the clown
Well, not for this scenario, but the dataset thing was intended for single process, in memory stuff. Think Datasets in .NET but for C++, for example, and queryable.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
I come from a Microsoft background. I'm pretty good at managing SQL Server databases, making them parallel and fast, and I know T-SQL quite well. To a lesser degree I know SQL92 but I sometimes get T-SQL mixed into it. I'm trying to decide what RDBMS to run on my Debian VPS. Obviously it's not going to be SQL Server. I expect light traffic. I'm wondering if Postgre might be the best option (most familiar) for me? Or if it would be overkill as I only really need simple stuff for the most part. I don't think I'll need triggers or jobs, for example. Maybe I'd also be taking on too much management. I'd prefer it be something i can set up and forget about more or less. Mysql is another option and might be worth learning because it's so ubiquitous, but my main concern with it is overhead in terms of learning curve. My biggest priority is to reduce that curve. I don't want to spend time learning about a different RDBMS way of doing things wherever I can avoid it. I just don't want to invest the time. I'm not sure which one would be a good fit for my use cases, which are still open ended at this point, except light traffic and simple, smallish datasets. Like I said, by biggest priority is a flattish learning curve, so the closer I can get to MS SQL Server "feel" the happier I'll be. In the alternative, an RDBMS that's fairly automatic with few user facing moving parts outside of SQL/DDL/DML would be okay. I'm not sold on those two offerings either. If someone has a better idea, I'm all for trying it.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
When you look for things, look for ANSI SQL. Those are the common bits across the things SQL. There are a few implementation/platform specific things. Like Oracle does 'sequences' and MSSQL has 'identity'. But a big swath of most standard DDL/DML is highly portable. And I'd say what I think really matters in RDBMS is more about set theory than it is about SQL dialects/specifics. Normalized vs denormalized and stuff like how to identify where indexes are going to help or where they may be more costly (in disk space and maintenance) than the ROI (in performance).
-
When you look for things, look for ANSI SQL. Those are the common bits across the things SQL. There are a few implementation/platform specific things. Like Oracle does 'sequences' and MSSQL has 'identity'. But a big swath of most standard DDL/DML is highly portable. And I'd say what I think really matters in RDBMS is more about set theory than it is about SQL dialects/specifics. Normalized vs denormalized and stuff like how to identify where indexes are going to help or where they may be more costly (in disk space and maintenance) than the ROI (in performance).
I believe SQL92 is ANSI? Also my big concern isn't language, but actual maintenance of the DB.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
I believe SQL92 is ANSI? Also my big concern isn't language, but actual maintenance of the DB.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
One of the good things about PostgreSQL is that it does not need as much maintenance as SQL Server. Our customers usually don't have the skills to maintain an SQL Server properly. Of course this is dependent on how complex the database is.
-
I believe SQL92 is ANSI? Also my big concern isn't language, but actual maintenance of the DB.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
Yeah I think so. "These editions colloquially became known as SQL-86, SQL-89, and SQL-92. So, if you hear those names in reference to an SQL format, note that it is referring the various editions of this standard." Read more at the ANSI Blog: The SQL Standard – ISO/IEC 9075:2023 (ANSI X3.135) https://blog.ansi.org/?p=158690
-
It's now owned and managed by Oracle, and they've been viewed as not the best to deal with an open-source product. MariaDB is created by the original creator of MySQL, so it's pretty much (older versions anyway) a drop-in replacement. I'd either go with MariaDB or Postgres if I had to choose.
TTFN - Kent
Kent Sharkey wrote:
MariaDB is created by the original creator of MySQL, so it's pretty much (older versions anyway) a drop-in replacement.
As I understand it that description is not correct. When MySQL was bought by Oracle it was open source. At point in time a branch was taken of MySQL and then given the name MariaDB. The open source nature of development has continued on that branch. Following provides information on contributors to MariaDB SHOW CONTRIBUTORS - MariaDB Knowledge Base[^] I do know of Percona in that list and I can say that they have done, to my mind, extensive work improving both the open source version and there own variation which they sell/license. And specifically in terms of performance. Following is release history and it seems pretty active to me MariaDB Server - All releases - MariaDB.org[^] Conversely the release history for MySQL is much more sparse. There has been speculation that the focus of Oracle is not on MySQL and certainly not on the free version (they have a paid one). One might suppose quite reasonably that that is because Oracle wants either their paid version used or even Oracle database used instead. MariaDB Server - All releases - MariaDB.org[^]
-
When you look for things, look for ANSI SQL. Those are the common bits across the things SQL. There are a few implementation/platform specific things. Like Oracle does 'sequences' and MSSQL has 'identity'. But a big swath of most standard DDL/DML is highly portable. And I'd say what I think really matters in RDBMS is more about set theory than it is about SQL dialects/specifics. Normalized vs denormalized and stuff like how to identify where indexes are going to help or where they may be more costly (in disk space and maintenance) than the ROI (in performance).
jochance wrote:
When you look for things, look for ANSI SQL.
Been doing DB for 40 years and that never came up. First of course the goal is the enterprise solution and not the nuts and bolts. Architecture and design matter far more. Most SQL is rather easy an mundane. And ANSI SQL works for that. But more complex problems cannot be done at all in ANSI SQL. So attempting to limit oneself to that means ignoring features of the database that have been optimized over years or even decades to provide features like that. Versus silly things like attempt to figure out how to do it in a programming language rather than using the database itself. Not to mention of course the real costs associated with licensing, maintenance and even the real cost of using existing knowledge of a technology versus attempting a new one. So even if you can find a database that is not compliant, especially for most of the major parts, the consideration is just not worth the time.
-
I come from a Microsoft background. I'm pretty good at managing SQL Server databases, making them parallel and fast, and I know T-SQL quite well. To a lesser degree I know SQL92 but I sometimes get T-SQL mixed into it. I'm trying to decide what RDBMS to run on my Debian VPS. Obviously it's not going to be SQL Server. I expect light traffic. I'm wondering if Postgre might be the best option (most familiar) for me? Or if it would be overkill as I only really need simple stuff for the most part. I don't think I'll need triggers or jobs, for example. Maybe I'd also be taking on too much management. I'd prefer it be something i can set up and forget about more or less. Mysql is another option and might be worth learning because it's so ubiquitous, but my main concern with it is overhead in terms of learning curve. My biggest priority is to reduce that curve. I don't want to spend time learning about a different RDBMS way of doing things wherever I can avoid it. I just don't want to invest the time. I'm not sure which one would be a good fit for my use cases, which are still open ended at this point, except light traffic and simple, smallish datasets. Like I said, by biggest priority is a flattish learning curve, so the closer I can get to MS SQL Server "feel" the happier I'll be. In the alternative, an RDBMS that's fairly automatic with few user facing moving parts outside of SQL/DDL/DML would be okay. I'm not sold on those two offerings either. If someone has a better idea, I'm all for trying it.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
honey the codewitch wrote:
Mysql is another option and might be worth learning
There are two parts: Programming and Operations. For the first the vast majority between the major SQL databases are similar enough that one can get through it. There can be gotchas for things like the exact way one creates a stored proc but examples allow one to get through it. At least for me one problem with MySQL which I only just recently learned is that it has a history of 'losing' the seed indexes (auto increment). I can't state for certain how significant a problem it is in general but it seemed pretty significant to me that it happened at all. As for Operations I think you need to carefully consider your commitment to handling this for your users. Given your other posts maybe this must be a server only solution but I will say that the cloud solutions eliminate the vast majority of maintenance work and can be very low cost as long as one very carefully throttles everything.
-
honey the codewitch wrote:
Mysql is another option and might be worth learning
There are two parts: Programming and Operations. For the first the vast majority between the major SQL databases are similar enough that one can get through it. There can be gotchas for things like the exact way one creates a stored proc but examples allow one to get through it. At least for me one problem with MySQL which I only just recently learned is that it has a history of 'losing' the seed indexes (auto increment). I can't state for certain how significant a problem it is in general but it seemed pretty significant to me that it happened at all. As for Operations I think you need to carefully consider your commitment to handling this for your users. Given your other posts maybe this must be a server only solution but I will say that the cloud solutions eliminate the vast majority of maintenance work and can be very low cost as long as one very carefully throttles everything.
I don't really want to add another paid service to my list of worries, as anything that costs me money is just more potential source for anxiety - like for example, when my bank card's fraud protection kicks in and cancels my card even though it shouldn't have, and then all my autopays need to be redone. So like, I keep it minimal. I'm more concerned about operations than programming, so otherwise a cloud solution would be ideal, but maybe I'll check to see if my existing VPS provider offers it. Adding extra cost to an existing bill is something i'm much more inclined to do vs adding another bill.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
Yeah I think so. "These editions colloquially became known as SQL-86, SQL-89, and SQL-92. So, if you hear those names in reference to an SQL format, note that it is referring the various editions of this standard." Read more at the ANSI Blog: The SQL Standard – ISO/IEC 9075:2023 (ANSI X3.135) https://blog.ansi.org/?p=158690
I wrote a SQL92 parser once so I'm pretty familiar with it except I often mix T-SQL in with SQL92 because the lines always blur for me.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
jochance wrote:
When you look for things, look for ANSI SQL.
Been doing DB for 40 years and that never came up. First of course the goal is the enterprise solution and not the nuts and bolts. Architecture and design matter far more. Most SQL is rather easy an mundane. And ANSI SQL works for that. But more complex problems cannot be done at all in ANSI SQL. So attempting to limit oneself to that means ignoring features of the database that have been optimized over years or even decades to provide features like that. Versus silly things like attempt to figure out how to do it in a programming language rather than using the database itself. Not to mention of course the real costs associated with licensing, maintenance and even the real cost of using existing knowledge of a technology versus attempting a new one. So even if you can find a database that is not compliant, especially for most of the major parts, the consideration is just not worth the time.
For my part, as long as the database supports SQL92 as a baseline I'm willing to learn some of the DB specific features I need to be effective with it. I just intend to lean heavily on common SQL that works across DBs where I can.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
Kent Sharkey wrote:
MariaDB is created by the original creator of MySQL, so it's pretty much (older versions anyway) a drop-in replacement.
As I understand it that description is not correct. When MySQL was bought by Oracle it was open source. At point in time a branch was taken of MySQL and then given the name MariaDB. The open source nature of development has continued on that branch. Following provides information on contributors to MariaDB SHOW CONTRIBUTORS - MariaDB Knowledge Base[^] I do know of Percona in that list and I can say that they have done, to my mind, extensive work improving both the open source version and there own variation which they sell/license. And specifically in terms of performance. Following is release history and it seems pretty active to me MariaDB Server - All releases - MariaDB.org[^] Conversely the release history for MySQL is much more sparse. There has been speculation that the focus of Oracle is not on MySQL and certainly not on the free version (they have a paid one). One might suppose quite reasonably that that is because Oracle wants either their paid version used or even Oracle database used instead. MariaDB Server - All releases - MariaDB.org[^]
From MariaDB versus MySQL - Compatibility - MariaDB Knowledge Base[^]
Quote:
Until MariaDB 5.5, MariaDB versions functioned as a "drop-in replacement" for the equivalent MySQL version, with some limitations. From MariaDB 10.0, it is usually still very easy to upgrade from MySQL.
But I agree that MariaDB has been much better supported that MySQL.
TTFN - Kent
-
I don't really want to add another paid service to my list of worries, as anything that costs me money is just more potential source for anxiety - like for example, when my bank card's fraud protection kicks in and cancels my card even though it shouldn't have, and then all my autopays need to be redone. So like, I keep it minimal. I'm more concerned about operations than programming, so otherwise a cloud solution would be ideal, but maybe I'll check to see if my existing VPS provider offers it. Adding extra cost to an existing bill is something i'm much more inclined to do vs adding another bill.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
I once tried the free Postgres hosting option of Heroku, but could not get it working sadly, it all seemed geared towards their "app ecosystem" which I was not interested in. Anyway they stopped offering the free option, so it's best to look elsewhere. Maybe one of these: Top 9 free online databases[^]
-
jochance wrote:
When you look for things, look for ANSI SQL.
Been doing DB for 40 years and that never came up. First of course the goal is the enterprise solution and not the nuts and bolts. Architecture and design matter far more. Most SQL is rather easy an mundane. And ANSI SQL works for that. But more complex problems cannot be done at all in ANSI SQL. So attempting to limit oneself to that means ignoring features of the database that have been optimized over years or even decades to provide features like that. Versus silly things like attempt to figure out how to do it in a programming language rather than using the database itself. Not to mention of course the real costs associated with licensing, maintenance and even the real cost of using existing knowledge of a technology versus attempting a new one. So even if you can find a database that is not compliant, especially for most of the major parts, the consideration is just not worth the time.
-
I wrote a SQL92 parser once so I'm pretty familiar with it except I often mix T-SQL in with SQL92 because the lines always blur for me.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
I'd say T-SQL has SQL92 in it since T-SQL is essentially a superset, but I know what you mean.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
I'd say T-SQL has SQL92 in it since T-SQL is essentially a superset, but I know what you mean.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
T-SQL is roughly a superset of SQL92
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
T-SQL is roughly a superset of SQL92
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
I come from a Microsoft background. I'm pretty good at managing SQL Server databases, making them parallel and fast, and I know T-SQL quite well. To a lesser degree I know SQL92 but I sometimes get T-SQL mixed into it. I'm trying to decide what RDBMS to run on my Debian VPS. Obviously it's not going to be SQL Server. I expect light traffic. I'm wondering if Postgre might be the best option (most familiar) for me? Or if it would be overkill as I only really need simple stuff for the most part. I don't think I'll need triggers or jobs, for example. Maybe I'd also be taking on too much management. I'd prefer it be something i can set up and forget about more or less. Mysql is another option and might be worth learning because it's so ubiquitous, but my main concern with it is overhead in terms of learning curve. My biggest priority is to reduce that curve. I don't want to spend time learning about a different RDBMS way of doing things wherever I can avoid it. I just don't want to invest the time. I'm not sure which one would be a good fit for my use cases, which are still open ended at this point, except light traffic and simple, smallish datasets. Like I said, by biggest priority is a flattish learning curve, so the closer I can get to MS SQL Server "feel" the happier I'll be. In the alternative, an RDBMS that's fairly automatic with few user facing moving parts outside of SQL/DDL/DML would be okay. I'm not sold on those two offerings either. If someone has a better idea, I'm all for trying it.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
If you want an easy setup for Postgresql you can try this: https://pigsty.io/