6 ms·

As a hardcore c++ guy, I recently switched to a company heavily into databases. I never had contact to databases before. And I'd like to go one step further: Wh

As a hardcore c++ guy, I recently switched to a company heavily into databases. I never had contact to databases before. And I'd like to go one step further: Why databases? I come from an industry that heavily uses custom binary file formats. And I'm still bewildered by the world of databases. They seem to solve many issues on the surface, but not really in pratice. The heavy limitations on data types, the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea. I get the interop benefits, and maybe with extreme data volumes. But for anything else, what's the point? Genuinely asking

treflop 3y ago
Well a database is just a binary file format with an API attached on. Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.
- bun_terminator 3y ago
  ok I get that, but at least our applications usually only have one thing (one thread of one process on one machine) operating on the database. So maybe it's just our silly usecase
  - deleted 3y ago
    [deleted]
  - afandian 3y ago
    It's a narrow use-case that doesn't cover RDMS. SQLite fits it though.
    - bun_terminator 3y ago
      The first thing I did was ask why we don't use sqlite or at least postgres. The answer was that they are free and therefore we don't trust them. So Oracle it is. Which is bananas because our customers have to buy an oracle license, which is money that we don't get. Pretty wild
      - themerone 3y ago
        Sqlite is running on billions of devices and has a test suite with 100% branch coverage. Only a fool would put more trust in Oracle.
        realaleris149 3y ago
        > has a test suite with 100% branch coverage Is actually even more than that, has 100% MC/DC coverage [1], which is something that is not easily achievable for most projects. [1] https://www.sqlite.org/testing.html#:~:text=In%20this%20way%2C%20SQLite%20also,addition%20to%20100%25%20branch%20coverage https://www.sqlite.org/testing.html#:~:text=In%20this%20way%....
      - vhcr 3y ago
        Your experience with databases was probably clouded by having to use Oracle.
        int_19h 3y ago
        It could be worse; there's always DB2...
      - edwardsdl 3y ago
        > The answer was that they are free and therefore we don't trust them. If you don’t mind a bit of unsolicited advice: run.
        bun_terminator 3y ago
        I mean you do a job because of the pay, not because you like the people or they are particularly competent. So it's a good gig for two years or so
        edwardsdl 3y ago
        I’ve found that surrounding myself with competent people - or better yet, people far better than me - has lead me to much better paying jobs.
  - marcosdumay 3y ago
    That's unusual. The rule is that you replicate the database consumers lots and lots of times. Still then, a database will improve your data access, validate your data, and provide interop in case you need to use the file with different software. It's also a very high quality serialization library, that replaces one of the most vulnerability-enabling layers of your software. (But then, I've just read you use Oracle, so maybe forget that one.)
  - tootie 3y ago
    I think it's important context that gets lost in so many tech conversations. Software engineering is a vast field covering almost every domain of human endeavor and they're all going to have different constraints. Some are technical and some are social. If you're working with 30 other engineers trying to marshal and unmarshal the same set of data for different purposes, it's really easy to tell them all "use SQL" instead of "here's the binary format invented by the guy who quit 7 years ago" even if that binary format was technically superior.
acaloiar 3y ago
It's a decent question. I believe there's a simple answer that explains a large part of the reason people choose databases over flat files. People want ACID compliance _and_ data distribution, and databases are traditionally the only things that provide both.
- bun_terminator 3y ago
  I have never heard the term ACID so I don't believe that's a reason for us at least
  - jakey_bakey 3y ago
    tl;dr it's an acronym that explains how the operations happening inside a database are reliable. For example, when making a bank transfer, the database guarantees that the update doesn't happen twice by accident.
  - hobs 3y ago
    Just because you haven't heard it doesn't mean it's not a fundamentally important part of storing data :)
dig1 3y ago
> They seem to solve many issues on the surface, but not really in practice I believe you haven't had a chance to work on problems that require an actual database. Multi-user access, ACID support, unified API (odbc/jdbc), common query language... all of these would require many man-years to set properly with a custom solution. > the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea What update disasters? If you meant by updating database versions, these aren't things you do frequently because the database is expected to be running 24/7. But Postgres and Mysql are already rock solid here. Wrt SQL engine incompatibilities, you usually set with a single database vendor in practice. If you suddenly start to switch databases in the middle of the project, something needs to be fixed with the process design, not database.
- bun_terminator 3y ago
  All these comments seem to fuel my suspicion that we in fact shouldn't use databases, because we don't use any of these features. We just use them as external data storage for a single application. And not even that much data, like <10 gigs. But the updating I would have expected to go more smoothly. If you make a point of using a software dedicated to managing data, I sure as hell would expect an update to go so smooth that I don't have to worry about or even notice it. In reality updates more often than not seem to come with undocumented errors. That is a constant source of frustration for me.
  - dig1 3y ago
    > because we don't use any of these features. We just use them as external data storage for a single application. You are using it :) Reboot the server where the database runs or suddenly cut off the connection. Unless you have ACID-compatible storage, you'll have malformed data. Plan for the future and use a database from the start. When your project/company expands and starts to use multiple applications/services (and that inevitably happens), you'll see (one of) the benefits of the database. > But the updating I would have expected to go more smoothly I'm not sure what you are talking about. Database updates are one of the smoothest (critical) software updates you'll find, assuming the database has a good track record.
crabbone 3y ago
Over years, I've seen a lot of poorly made binary formats used by various programs. Databases like PostgreSQL are where such binary formats had evolved through benchmarking, testing for various edge cases, and are generally better than whatever you will find in the bespoke formats developed for a particular program. You have to be really good at understanding how storage works, have a lot of time and resources to develop your own program to beat something like PostgreSQL. They have zillions of man-hours on you when you start, experience and knowledge. It's not impossible that you could find a case where a bespoke format would beat an established storage product, but over a range of cases, you most likely won't. And, of course, there's a convenience aspect. Outside of niche technologies, SQL offers the richest language for data description and operations on data. As for the inconsistencies between SQL implementations: in practice, it matters very little: most programs will never migrate between different SQL implementations anyways. As for upgrades: it's a doubly-edged sword. You get a very expressive data format and it's not surprising that it's hard to upgrade. But, try to match the abilities of SQL in your own format, and you'll probably find out that it's hard to have generic tools for upgrading it too. None of this means that you cannot do better than SQL databases. It'd be ridiculous to think there could be a way to prove that SQL is somehow the best we can get. It's just that it's very hard to do better. Especially if you want a universal tool
bsdpufferfish 3y ago
Read the original relational database paper by Codd. The problem is that trees of data are not flexible for querying, you really need a graph. If you draw it out on paper, you will find a relation is a very efficient way to store general graphs. I'm actually suprised to hear this from a C++ engineer, because sorting and searching columns of data is the name of the game in C++. Another key problem they solve is separating the logical representation from the on disk representation. You can evolve and and optimize storage without breaking anything. The other problem with files is that you have a disconnect between in memory and on disk. You have to constantly serialize and deserialize. Sqlite has quite a bit of info on this: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
- bun_terminator 3y ago
  But that's the thing: All that is nice on paper. Of course the relational nature is nice. Of course the disk representation is nice. But I never feel like that's worth the trouble day in day out. The costs, the mystery box of performance, the insane statements, the extra hardware, the updates, the strange data types, the box of tricks everyone needs to make them behave. But I've been made aware that our usecases suck, so I'll attribute most to that.
  - hobs 3y ago
    The problems are not specific to SQL though - if you want to store and manage huge amounts of data you're going to need some specialized data structures, some way to manage disks paging and memory, manage consistency, isolation, durability, and atomicity of your changes. The general idea is that its such a PITA to deal with all that so just let a database do it, and of course the consequence is an often leaky abstraction because computers suck.
    - dgsb 3y ago
      It's more than a PITA, it's almost impossible to implement ACID correctly. It's very very hard to do. There almost no point in implementing that in house when we have sqlite3.
ndriscoll 3y ago
It gives you an easy, high-level way to use high performance data-structures and algorithms. You don't need to explicitly write or rewrite code to maintain hash maps or b-trees or whatever and to use the right structures for fast lookups from one set of data to another. You just say "CREATE INDEX name ON table USING HASH(column)", and from then on, your hash map will maintain itself, and any lookups that would benefit from that hash map will automatically use it. No need to rewrite any of your code that needs to work with that column. In some cases, it will also automatically do things like make temporary hashmaps or bitmaps for just the life of a query to speed it up. You mention further down a "mystery box of performance", but if you understand what data structures it's using and how it uses them, then it's generally pretty straightforward. Mostly you can reason about what indices are available and how trees work (and e.g. whether it can walk two trees side-by-side to join data) to know what query plan it should make, and you can ask it to tell you what plan it makes and which indices it used. Likewise, if you have a query plan you want it to run (loop over this table, then use this column to look up in this table, etc.), you'll know what indices are needed to support that plan. If people struggle with using a database correctly, they're really going to struggle with using something like a b-tree in a way where you don't corrupt your data in the event of a power loss or crash, or in a way where multiple threads don't clobber each other's updates or create weird in-between states (or you just use a global lock, but then you lose performance).
- bun_terminator 3y ago
  It's pretty opaque to me. If something in my cpp code is dodgy or runs slow, I can use a number of debugging and profiling tools. While I don't really even know how databases work on the insides, let alone profile or diagnose them. To this day my colleagues rewrite equivalent SQL statements because some run better than others. And we regularly run into unexpected latency spikes where most of the time a statement runs fine, but even nth time it's several magnitudes of times slower - and no one knows why. So we cobble code and caches around things. It all seems pretty mind boggling to me.
  - ndriscoll 3y ago
    If you're going to find yourself working with databases, I'd suggest learning some about the internals (that's probably true of anything). In particular Markus Winand's information[0] is great for building an intuition about why different types of queries work with different types of indices. I don't know about Oracle, but Postgres and Mysql have pretty detailed documentation once you have that foundational knowledge. If you learn about the internals of a thing, especially when your background is in lower level dev like C++, then the use-cases are more obvious: you use the thing whenever you would've done what it does internally, but it gives you that functionality off-the-shelf and wrapped up in a way where you can write business logic without getting bogged down in details of tree-traversal and stuff. Once you get comfortable with it, you expand that to using it when you might not have done things exactly that way, but eh it's close enough and lower effort. Sometimes truly equivalent SQL statements will be faster just because the optimizer is not perfect. e.g. I've had cases where I had a templated query with a GROUP BY some id, and then other code added on a HAVING for that same id, and I know it should be algebraically valid to push the HAVING into a WHERE so it runs before the GROUP BY (and filtering before the aggregation would be much cheaper), but mysql just didn't have that optimization. Dealing with this kind of thing can be annoying. Other times you might have something like a compound index, and you might add a WHERE that you know for business reasons is redundant because the thing you're trying to filter on is not the first column in the index. Understanding why that works comes down to understanding what a compound index "looks like" as a tree. One thing that I imagine a database from the future could do is let you define logical implications like that (e.g. StateOrProvince = California implies Country = United States, or maybe deleted_at >= modified_at >= created_at, or a.id > b.id implies a.created_at > b.created_at) that it could use for query planning. But in general, if you learn how it works, and then think of it as a way to not have to write that functionality yourself (but understand that the trade-off is some rigidity in your ability to customize it), it will make more sense, and you'll be able to become one of those wizards that just knows how to rewrite a query to something that ought to be the same, but is for some reason much faster. [0] https://use-the-index-luke.com/ https://use-the-index-luke.com/
cryptonector 3y ago
Having written a small, bespoke query engine (why? long story not for today, but mainly it was super-optimized for a very particular type of query, and very fast, with 10-50us average query times), I don't ever want to do it again except if -and only if- the performance we can wring out of it is absolutely essential and otherwise unobtainable. I'd rather use an off-the-shelf query engine any day and every day. I would happily work on query engines, it's just I don't recommend using bespoke ones unless absolutely necessary. I think you'll come around eventually.
DanielHB 3y ago
Besides what others are saying here, data integrity is a lot easier with a relational database if your data is heavily relational. I have managed systems with <1gb of data that would have been much harder to build without primary and foreign keys About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database. I can see the appeal to replace noSQL solutions though, a lot of people are using S3 (and other storage solutions) as a makeshift database lately
- bun_terminator 3y ago
  I think my wtf moment was that you can't have real variable length strings. And I'm pretty pampered by c++ highly nuanced numeric types with varying sizes and signed/unsigned.
  - DanielHB 3y ago
    fair enough, relational DBs are optimised for querying so you often see tradeoffs like this.
  - garfij 3y ago
    Other people have said it in this thread, but I think your experience is being severely clouded by Oracle. Postgres, MySql, and all have the `TEXT` datatype which are variable length (though with some upper bound)
dgsb 3y ago
For custom binary file you could and should use sqlite, it has many benefits instead of writing your own custom file format. You just have acid without any cost. It's a single file database you can access throuhg lib statically linked in your application and that's done. You can check these links: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html https://sqlite.org/aff_short.html https://sqlite.org/aff_short.html

treflop 3y ago

Well a database is just a binary file format with an API attached on. Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.

bun_terminator 3y ago
ok I get that, but at least our applications usually only have one thing (one thread of one process on one machine) operating on the database. So maybe it's just our silly usecase
- deleted 3y ago
  [deleted]
- afandian 3y ago
  It's a narrow use-case that doesn't cover RDMS. SQLite fits it though.
  - bun_terminator 3y ago
    The first thing I did was ask why we don't use sqlite or at least postgres. The answer was that they are free and therefore we don't trust them. So Oracle it is. Which is bananas because our customers have to buy an oracle license, which is money that we don't get. Pretty wild
    - themerone 3y ago
      Sqlite is running on billions of devices and has a test suite with 100% branch coverage. Only a fool would put more trust in Oracle.
      - realaleris149 3y ago
        > has a test suite with 100% branch coverage Is actually even more than that, has 100% MC/DC coverage [1], which is something that is not easily achievable for most projects. [1] https://www.sqlite.org/testing.html#:~:text=In%20this%20way%2C%20SQLite%20also,addition%20to%20100%25%20branch%20coverage https://www.sqlite.org/testing.html#:~:text=In%20this%20way%....
    - vhcr 3y ago
      Your experience with databases was probably clouded by having to use Oracle.
      - int_19h 3y ago
        It could be worse; there's always DB2...
    - edwardsdl 3y ago
      > The answer was that they are free and therefore we don't trust them. If you don’t mind a bit of unsolicited advice: run.
      - bun_terminator 3y ago
        I mean you do a job because of the pay, not because you like the people or they are particularly competent. So it's a good gig for two years or so
        edwardsdl 3y ago
        I’ve found that surrounding myself with competent people - or better yet, people far better than me - has lead me to much better paying jobs.
- marcosdumay 3y ago
  That's unusual. The rule is that you replicate the database consumers lots and lots of times. Still then, a database will improve your data access, validate your data, and provide interop in case you need to use the file with different software. It's also a very high quality serialization library, that replaces one of the most vulnerability-enabling layers of your software. (But then, I've just read you use Oracle, so maybe forget that one.)
- tootie 3y ago
  I think it's important context that gets lost in so many tech conversations. Software engineering is a vast field covering almost every domain of human endeavor and they're all going to have different constraints. Some are technical and some are social. If you're working with 30 other engineers trying to marshal and unmarshal the same set of data for different purposes, it's really easy to tell them all "use SQL" instead of "here's the binary format invented by the guy who quit 7 years ago" even if that binary format was technically superior.

acaloiar 3y ago

It's a decent question. I believe there's a simple answer that explains a large part of the reason people choose databases over flat files. People want ACID compliance _and_ data distribution, and databases are traditionally the only things that provide both.

bun_terminator 3y ago
I have never heard the term ACID so I don't believe that's a reason for us at least
- jakey_bakey 3y ago
  tl;dr it's an acronym that explains how the operations happening inside a database are reliable. For example, when making a bank transfer, the database guarantees that the update doesn't happen twice by accident.
- hobs 3y ago
  Just because you haven't heard it doesn't mean it's not a fundamentally important part of storing data :)

dig1 3y ago

> They seem to solve many issues on the surface, but not really in practice I believe you haven't had a chance to work on problems that require an actual database. Multi-user access, ACID support, unified API (odbc/jdbc), common query language... all of these would require many man-years to set properly with a custom solution. > the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea What update disasters? If you meant by updating database versions, these aren't things you do frequently because the database is expected to be running 24/7. But Postgres and Mysql are already rock solid here. Wrt SQL engine incompatibilities, you usually set with a single database vendor in practice. If you suddenly start to switch databases in the middle of the project, something needs to be fixed with the process design, not database.

bun_terminator 3y ago
All these comments seem to fuel my suspicion that we in fact shouldn't use databases, because we don't use any of these features. We just use them as external data storage for a single application. And not even that much data, like <10 gigs. But the updating I would have expected to go more smoothly. If you make a point of using a software dedicated to managing data, I sure as hell would expect an update to go so smooth that I don't have to worry about or even notice it. In reality updates more often than not seem to come with undocumented errors. That is a constant source of frustration for me.
- dig1 3y ago
  > because we don't use any of these features. We just use them as external data storage for a single application. You are using it :) Reboot the server where the database runs or suddenly cut off the connection. Unless you have ACID-compatible storage, you'll have malformed data. Plan for the future and use a database from the start. When your project/company expands and starts to use multiple applications/services (and that inevitably happens), you'll see (one of) the benefits of the database. > But the updating I would have expected to go more smoothly I'm not sure what you are talking about. Database updates are one of the smoothest (critical) software updates you'll find, assuming the database has a good track record.

crabbone 3y ago

Over years, I've seen a lot of poorly made binary formats used by various programs. Databases like PostgreSQL are where such binary formats had evolved through benchmarking, testing for various edge cases, and are generally better than whatever you will find in the bespoke formats developed for a particular program. You have to be really good at understanding how storage works, have a lot of time and resources to develop your own program to beat something like PostgreSQL. They have zillions of man-hours on you when you start, experience and knowledge. It's not impossible that you could find a case where a bespoke format would beat an established storage product, but over a range of cases, you most likely won't. And, of course, there's a convenience aspect. Outside of niche technologies, SQL offers the richest language for data description and operations on data. As for the inconsistencies between SQL implementations: in practice, it matters very little: most programs will never migrate between different SQL implementations anyways. As for upgrades: it's a doubly-edged sword. You get a very expressive data format and it's not surprising that it's hard to upgrade. But, try to match the abilities of SQL in your own format, and you'll probably find out that it's hard to have generic tools for upgrading it too. None of this means that you cannot do better than SQL databases. It'd be ridiculous to think there could be a way to prove that SQL is somehow the best we can get. It's just that it's very hard to do better. Especially if you want a universal tool

bsdpufferfish 3y ago

Read the original relational database paper by Codd. The problem is that trees of data are not flexible for querying, you really need a graph. If you draw it out on paper, you will find a relation is a very efficient way to store general graphs. I'm actually suprised to hear this from a C++ engineer, because sorting and searching columns of data is the name of the game in C++. Another key problem they solve is separating the logical representation from the on disk representation. You can evolve and and optimize storage without breaking anything. The other problem with files is that you have a disconnect between in memory and on disk. You have to constantly serialize and deserialize. Sqlite has quite a bit of info on this: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html

bun_terminator 3y ago
But that's the thing: All that is nice on paper. Of course the relational nature is nice. Of course the disk representation is nice. But I never feel like that's worth the trouble day in day out. The costs, the mystery box of performance, the insane statements, the extra hardware, the updates, the strange data types, the box of tricks everyone needs to make them behave. But I've been made aware that our usecases suck, so I'll attribute most to that.
- hobs 3y ago
  The problems are not specific to SQL though - if you want to store and manage huge amounts of data you're going to need some specialized data structures, some way to manage disks paging and memory, manage consistency, isolation, durability, and atomicity of your changes. The general idea is that its such a PITA to deal with all that so just let a database do it, and of course the consequence is an often leaky abstraction because computers suck.
  - dgsb 3y ago
    It's more than a PITA, it's almost impossible to implement ACID correctly. It's very very hard to do. There almost no point in implementing that in house when we have sqlite3.

ndriscoll 3y ago

It gives you an easy, high-level way to use high performance data-structures and algorithms. You don't need to explicitly write or rewrite code to maintain hash maps or b-trees or whatever and to use the right structures for fast lookups from one set of data to another. You just say "CREATE INDEX name ON table USING HASH(column)", and from then on, your hash map will maintain itself, and any lookups that would benefit from that hash map will automatically use it. No need to rewrite any of your code that needs to work with that column. In some cases, it will also automatically do things like make temporary hashmaps or bitmaps for just the life of a query to speed it up. You mention further down a "mystery box of performance", but if you understand what data structures it's using and how it uses them, then it's generally pretty straightforward. Mostly you can reason about what indices are available and how trees work (and e.g. whether it can walk two trees side-by-side to join data) to know what query plan it should make, and you can ask it to tell you what plan it makes and which indices it used. Likewise, if you have a query plan you want it to run (loop over this table, then use this column to look up in this table, etc.), you'll know what indices are needed to support that plan. If people struggle with using a database correctly, they're really going to struggle with using something like a b-tree in a way where you don't corrupt your data in the event of a power loss or crash, or in a way where multiple threads don't clobber each other's updates or create weird in-between states (or you just use a global lock, but then you lose performance).

bun_terminator 3y ago
It's pretty opaque to me. If something in my cpp code is dodgy or runs slow, I can use a number of debugging and profiling tools. While I don't really even know how databases work on the insides, let alone profile or diagnose them. To this day my colleagues rewrite equivalent SQL statements because some run better than others. And we regularly run into unexpected latency spikes where most of the time a statement runs fine, but even nth time it's several magnitudes of times slower - and no one knows why. So we cobble code and caches around things. It all seems pretty mind boggling to me.
- ndriscoll 3y ago
  If you're going to find yourself working with databases, I'd suggest learning some about the internals (that's probably true of anything). In particular Markus Winand's information[0] is great for building an intuition about why different types of queries work with different types of indices. I don't know about Oracle, but Postgres and Mysql have pretty detailed documentation once you have that foundational knowledge. If you learn about the internals of a thing, especially when your background is in lower level dev like C++, then the use-cases are more obvious: you use the thing whenever you would've done what it does internally, but it gives you that functionality off-the-shelf and wrapped up in a way where you can write business logic without getting bogged down in details of tree-traversal and stuff. Once you get comfortable with it, you expand that to using it when you might not have done things exactly that way, but eh it's close enough and lower effort. Sometimes truly equivalent SQL statements will be faster just because the optimizer is not perfect. e.g. I've had cases where I had a templated query with a GROUP BY some id, and then other code added on a HAVING for that same id, and I know it should be algebraically valid to push the HAVING into a WHERE so it runs before the GROUP BY (and filtering before the aggregation would be much cheaper), but mysql just didn't have that optimization. Dealing with this kind of thing can be annoying. Other times you might have something like a compound index, and you might add a WHERE that you know for business reasons is redundant because the thing you're trying to filter on is not the first column in the index. Understanding why that works comes down to understanding what a compound index "looks like" as a tree. One thing that I imagine a database from the future could do is let you define logical implications like that (e.g. StateOrProvince = California implies Country = United States, or maybe deleted_at >= modified_at >= created_at, or a.id > b.id implies a.created_at > b.created_at) that it could use for query planning. But in general, if you learn how it works, and then think of it as a way to not have to write that functionality yourself (but understand that the trade-off is some rigidity in your ability to customize it), it will make more sense, and you'll be able to become one of those wizards that just knows how to rewrite a query to something that ought to be the same, but is for some reason much faster. [0] https://use-the-index-luke.com/ https://use-the-index-luke.com/

cryptonector 3y ago

Having written a small, bespoke query engine (why? long story not for today, but mainly it was super-optimized for a very particular type of query, and very fast, with 10-50us average query times), I don't ever want to do it again except if -and only if- the performance we can wring out of it is absolutely essential and otherwise unobtainable. I'd rather use an off-the-shelf query engine any day and every day. I would happily work on query engines, it's just I don't recommend using bespoke ones unless absolutely necessary. I think you'll come around eventually.

DanielHB 3y ago

Besides what others are saying here, data integrity is a lot easier with a relational database if your data is heavily relational. I have managed systems with <1gb of data that would have been much harder to build without primary and foreign keys About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database. I can see the appeal to replace noSQL solutions though, a lot of people are using S3 (and other storage solutions) as a makeshift database lately

bun_terminator 3y ago
I think my wtf moment was that you can't have real variable length strings. And I'm pretty pampered by c++ highly nuanced numeric types with varying sizes and signed/unsigned.
- DanielHB 3y ago
  fair enough, relational DBs are optimised for querying so you often see tradeoffs like this.
- garfij 3y ago
  Other people have said it in this thread, but I think your experience is being severely clouded by Oracle. Postgres, MySql, and all have the `TEXT` datatype which are variable length (though with some upper bound)

dgsb 3y ago

For custom binary file you could and should use sqlite, it has many benefits instead of writing your own custom file format. You just have acid without any cost. It's a single file database you can access throuhg lib statically linked in your application and that's done. You can check these links: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html https://sqlite.org/aff_short.html https://sqlite.org/aff_short.html