6 ms·

a bit tangential, but when do you move form using in-application data structures (maps, trees, vector/arrays) to using a database? Is it basically when the data

• manigandham 7y ago
  Persistence, portability, and durability. SQLite is an actual file on disk, can be easily read by multiple programs/languages, and has write-ahead logging to keep your data safe from crashes.
• dragontamer 7y ago
  > when do you move form using in-application data structures (maps, trees, vector/arrays) to using a database? The killer feature of databases, including no-sql or older ones like BerkeleyDB, is multiprocess synchronization. If two programs (or two copies of the same program) need to coordinate their data, a database is often far easier to use than writing your own transaction layer through files, mutexes, and other primitives. Web applications, such as forums or discussion boards, have many users and processes adding data simultaneously. That's why databases are so popular in web backends.
• typon 7y ago
  Any medium size or greater application either uses SQL or recreates a shitty version of it. You just can't write complex code without relations.
• d0mine 7y ago
  Anytime you want persistency: sqlite is a replacement for fopen() https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
• shakna 7y ago
  Structured transactions between applications is one place. You can get by with files, but they're slower. A DB is the right choice. Structured data anytime you're hitting multiuser - web, networked games, collab programs, live maps, etc. Anything that's massively stateful. A DB hands you a lot of guarantees. The filesystem has some of them, but is much slower.
• bob1029 7y ago
  I have found that you can go for a disturbingly-long period of time using primitive schemes like LINQ<->Objects<->JSON to persist your business data before things start to get hairy. Personally, I would say 10 megs of persisted data is about the upper limit before I am going to start reaching for SQLite. If you start to get clever with schemes like one file per serialized entity, you could potentially avoid using a 3rd party database indefinitely. I have found that the technical cost of using SQLite from the start is so low that I just start out using it by default even if the file will never exceed 1MB.
  • geokon 7y ago
    will you store large objects like images or audio file directly in SQL?
    • bob1029 7y ago
      In most cases I will. Pulling a blob out of a row is a lot faster than opening an additional file handle. There aren't really any downsides to this either unless you are running table scans. SQLite does support indexes (even full-text) and they do work miracles so do use them when necessary. I would go so far as to argue that SQLite could be used to store all of the assets for any large piece of software (I.e. a AAA game). This would probably wind up faster and more reliable than most alternative solutions out there today.
      • bsenftner 7y ago
        Can confirm SQlite is used in AAA game engines as well as VFX production pipelines. I know because I put it there.
        • solarkraft 7y ago
          So - is SQlite a good file system? Should I just use it for everything?
          • 7y ago
• threatripper 7y ago
  Is all your data on disk read-only and the data in RAM can be forgotten when your application finishes/closes? Then you don't need any of this. If your state consists of a single data structure you could write it to disk into a new file and move-replace the old file. This works as long as you have a single instance of your application. For anything more complex SQLite is a good way to keep your data in a consistent state. If you store it on a local drive, the performance is stellar. Thinking about going multi-process or multi-user? SQLite can still give you an easy head-start because it performs OK in most situations with a database stored on a network drive.
• closeparen 7y ago
  SQL queries tend to be much smaller than their equivalent data-structure traversal procedures. This can be beneficial even when you still want the data in your process's address space, hence embedded database engines like sqlite. Libraries like Linq can also provide the same expressive power over a programming language's native objects and collections. As to why you'd want a separate DB server process: long lived mutable structures tend to drift into unexpected states, which is why the standard computer troubleshooting procedure since forever is to restart. Database management systems specialize in not having that problem. You deploy one that's widely used and hardened over many years, change it infrequently, and operate it with great care. Or pay a cloud provider to do that. Then everything else gets to outsource the burden of persistence to it, and the vast majority of the workloads you develop and operate are stateless. These are drastically more convenient and more resilient to mistakes. They're effectively "restarted" with each API request, and you can stand them up, tear them down, migrate them to new machines, etc. as much as you want without fear of data loss.
  • geokon 7y ago
    hmm, it seems to map to my workflow with Clojure lately. But there I'm massaging and generating new data sets (even though it's statless) but I can see that if you have a very settled data format then this would work in a language agnostic data-interface kind of way. I could see like measurement/time series data being stored in SQL and accessed that way would be much cleaner than opening and closing CSV files for all sorts of datasets
  • FpUser 7y ago
    "long lived mutable structures tend to drift into unexpected states" Why are those "tend to drift" ? As an example I wrote sort of like game server for one of my applications. Internally it has those exact forever lived mutable structures. I've never observed it to drift into any unpredictable state. Works like a charm and running for many month. I only reboot it when I need to update it to a new version. The only catch here is that all data fit into RAM. With the amount of RAM modern computers can be stuffed with I do not really see if my server would ever run out of it. Of course it backed up by database but it merely serves as a persistence layer for this particular application.
• bernawil 7y ago
  > when do you move from using in-application data structures? as soon as you need any of these: * a relational model -because you need to model your data that way or you are required to because someone wants to consume it with tableau-. * concurrently read/read data between 1+ instances. * a standard way of doing backups. moving from that to a database would probably equal a rewrite.
• wpietri 7y ago
  I'm sure people will give you the orthodox answer, so let me give you mine: when other things become more important than performance, ease of development, and code clarity. Many years ago I happened to meet the creator of Prevayler, an open-source persistence framework that provided ACID guarantees and was thousands of times faster than a database as long as your data fit in RAM. I tried it out for a project and we loved it. Our hundreds and hundreds of unit tests ran in a few seconds. Our pages rendered in ~5 milliseconds. It was structured around a log of actions, so if you wanted to know how the data ended up like it did, every change was logged. What people who haven't worked this way often miss is that a database doesn't make things simpler, it just makes certain things easier. Once you add a traditional database to your project, you're importing a million lines of mysterious code into your project, and demanding to pay a serialization/deserialization penalty any time your code wants to look at data. When it works, it can be swell, but when you have a problem, suddenly things can get hairy. Database performance optimization is a murky art in a way that just isn't true if all your data is right there in RAM. Prevayler of course didn't catch on. It was just too weird for most people, who had grown up on databases and for whom data structures were something that they hadn't really thought about since their last CS exam. But I sometimes dream of the world where it did catch on. At the time, fitting in RAM was a big limitation. But now I can get an off-the-shelf desktop with 768 GB of RAM, and Amazon's servers go up to 24 TB of RAM. If you're going past working sets at those levels, traditional databases have anyhow fallen out of favor of big data tools. But it would have been a much better fit for today's world of microservices and distributed systems.
  • notduncansmith 7y ago
    This is exactly the style in which I build applications these days. Most of the time I just describe it as “hella caching” but it is a different paradigm from treating the database as the primary state engine. The speed and simplicity of working on in-memory structures are great. When you outgrow a single server’s RAM capacity, you can use Kafka or another durable message queue as your application’s WAL and shard your data across multiple servers.
    • wpietri 7y ago
• JyB 7y ago
  That's a weird question, I don't really understand. When you want to persist data beyond the lifespan of your process?
• jtms 7y ago
  How do you persist state across a restart of the app or device? Do you dump state to a file? Do you not need to persist state ever in 10 years of software development?