If you need a SQL-capable database-like solution that is file-based and embeddable in your existing Java Virtual Machine processes, Apache Derby is an open-source, zero cost, robust and performant option. You can use it to store structured relational data but in small files that can be deployed right alongside with your solution, such as storing a set of relational master data or configuration settings inside your binary package that is deployed/installed on servers or client machines.
Elasticsearch is a really scalable solution that can fit a lot of needs, but the bigger and/or those needs become, the more understanding & infrastructure you will need for your instance to be running correctly. Elasticsearch is not problem-free - you can get yourself in a lot of trouble if you are not following good practices and/or if are not managing the cluster correctly. Licensing is a big decision point here as Elasticsearch is a middleware component - be sure to read the licensing agreement of the version you want to try before you commit to it. Same goes for long-term support - be sure to keep yourself in the know for this aspect you may end up stuck with an unpatched version for years.
Apache Derby is SMALL. Compared to an enterprise scale system such as MSSQL, it's footprint is very tiny, and it works well as a local database.
The SPEED. I have found that Apache Derby is very fast, given the environment I was developing in.
Based in JAVA (I know that's an obvious thing to say), but Java allows you to write some elegant Object Oriented structures, thus allowing for fast, Agile test cases against the database.
Derby is EASY to implement and can be accessed from a console with little difficulty. Making it appropriate for everything from small embedded systems (i.e. just a bash shell and a little bit of supporting libraries) to massive workstations.
As I mentioned before, Elasticsearch's flexible data model is unparalleled. You can nest fields as deeply as you want, have as many fields as you want, but whatever you want in those fields (as long as it stays the same type), and all of it will be searchable and you don't need to even declare a schema beforehand!
Elastic, the company behind Elasticsearch, is super strong financially and they have a great team of devs and product managers working on Elasticsearch. When I first started using ES 3 years ago, I was 90% impressed and knew it would be a good fit. 3 years later, I am 200% impressed and blown away by how far it has come and gotten even better. If there are features that are missing or you don't think it's fast enough right now, I bet it'll be suitable next year because the team behind it is so dang fast!
Elasticsearch is really, really stable. It takes a lot to bring down a cluster. It's self-balancing algorithms, leader-election system, self-healing properties are state of the art. We've never seen network failures or hard-drive corruption or CPU bugs bring down an ES cluster.
To get started with Elasticsearch, you don't have to get very involved in configuring what really is an incredibly complex system under the hood. You simply install the package, run the service, and you're immediately able to begin using it. You don't need to learn any sort of query language to add data to Elasticsearch or perform some basic searching. If you're used to any sort of RESTful API, getting started with Elasticsearch is a breeze. If you've never interacted with a RESTful API directly, the journey may be a little more bumpy. Overall, though, it's incredibly simple to use for what it's doing under the covers.
We've only used it as an opensource tooling. We did not purchase any additional support to roll out the elasticsearch software. When rolling out the application on our platform we've used the documentation which was available online. During our test phases we did not experience any bugs or issues so we did not rely on support at all.
SQLite is another open-source zero-cost file-based SQL-capable database solution and is a good alternative to Apache Derby, especially for non-Java-based solutions. We chose Apache Derby as it is Java-based, and so is the solution we embedded it in. However, SQLite has a similar feature set and is widely used in the industry to serve the same purposes for native solutions such as C or C++-based products.
As far as we are concerned, Elasticsearch is the gold standard and we have barely evaluated any alternatives. You could consider it an alternative to a relational or NoSQL database, so in cases where those suffice, you don't need Elasticsearch. But if you want powerful text-based search capabilities across large data sets, Elasticsearch is the way to go.
Being Open source, the resources spent on the purchase of the product are ZERO.
Contrary to popular belief, open source software CAN provide support, provided that the developers/contributors are willing to answer your emails.
Overall, the ROI was positive: being able to experiment with an open source technology that could perform on par with the corporate products was promising, and gave us much information about how to proceed in the future.
We have had great luck with implementing Elasticsearch for our search and analytics use cases.
While the operational burden is not minimal, operating a cluster of servers, using a custom query language, writing Elasticsearch-specific bulk insert code, the performance and the relative operational ease of Elasticsearch are unparalleled.
We've easily saved hundreds of thousands of dollars implementing Elasticsearch vs. RDBMS vs. other no-SQL solutions for our specific set of problems.
