12

Currently, I'm working on a graduation project about designing a Document Management System (DMS) that incorporates some functionalities as follows:

  • It won't use folders to organize documents; instead, they will be stored in a flat structure.
  • Document organization heavily relies on assigning suitable metadata.
  • Searching will involve full-text search (FTS) on the document content and an advanced search based on metadata.
  • Metadata is in a key:value format, and there will be some kind of relation between values based on their semantic meaning (for example: iron man --belongs_to → marvel).
  • Document recommendations will be based on the relations among metadata.

Considering these functionalities, Elasticsearch would be the first choice for FTS. Still, to model relationships among metadata, we need to use Neo4j. However, since we are currently using PostgreSQL, I would like to ask if it is best practice to use Elasticsearch, Neo4j, and PostgreSQL in the same system like this.

Note: I'm open to any other suggestions on other approaches to this problem, especially the "relation among metadata" part. Is this approach suitable for the problem, considering that my professor's instructions about this part are somewhat vague.

I've done some research about this, and I've only seen people using Elasticsearch together with Neo4j for a knowledge graph. But considering the DMS also has other functionalities that can be added later, such as permissions, workflow, and version tracking, I think these would utilize joins from a relational database such as PostgreSQL.

13
  • 9
    What will be your "single source of truth" in this setup? The documents themselves, stored in a filesystem? Or the documents plus their metadata stored inside one of the systems (presumably PostgreSQL)? How much redundancy will these 3 databases have?
    – Doc Brown
    Nov 13, 2023 at 8:49
  • 3
    FWIW, when we developed our DMS, we gave this question a lot of thought and decided to use the file system for file storage, SQL Server for metadata and Lucene for search. The advantage is that, for each component, we use the right tool for the job. The disadvantage is that we need to keep those three systems in sync. It worked for us. However, had we not had the requirement to have the files accessible (read-only) via an SMB network share, putting everything in SQL Server (and using its built-in FTS capabilities) would have made everything easier.
    – Heinzi
    Nov 13, 2023 at 9:33
  • 6
    > "Elasticsearch would be the first choice for FTS. Still, to model relationships among metadata, we need to use Neo4j." > Don't discount a tech and introduce another, without being fully aware of the capabilities of each. For example, ES also supports graph queries and Neo4j also has FTS indexing and Postgres also has graph extensions.
    – S.D.
    Nov 13, 2023 at 10:30
  • 5
    "to model relationships among metadata, we need to use Neo4j" I don't think you need to use a graph DB at all. Can you explain why you feel a relational model wouldn't be adequate?
    – JimmyJames
    Nov 13, 2023 at 21:10
  • 3
    @siunhanbumpbump Any graph (in the CS sense) can be represented as a table of source nodes and target nodes. It's quite simple to represent. How deep are these structures?
    – JimmyJames
    Nov 14, 2023 at 17:47

4 Answers 4

40

best practice to use ES, Neo4j, and PostgreSQL in the same system like this?

No.

Only incur the cost of adopting yet another technology if you're sure the benefits are worth it. Here, there seems little justification for straying from your existing postgres setup.


Searching will involve full-text search (FTS) on the document content

Sounds like a good match to postgresql's mature support for indexing text corpora and flexibly searching them.

relation between values based on their semantic meaning

Seeing this in a requirements document could perhaps justify the use of neo4j in a project, to chase edges through arbitrary and perhaps cyclic graphs. But your example suggests a (potentially deep) tree structure, something that a WITH RECURSIVE CTE hierarchical query can easily tackle.

I have used all three of these technologies in production, and frankly found ElasticSearch a bit of a nightmare between its verbosely nested queries and an uptime record that didn't measure up to what the docs suggested. Neo4j is a fine tool if you need it. Plan on having all nodes memory-resident, and you'll still obtain much lower "result rows per second" throughput than you would from any mature RDBMS.

Time box it. Try to prove postgres can or can't do something that appears as a bullet point in your requirements document. Author an automated test for it, and write down the observed performance numbers.

Your project needs to be complete prior to graduation date. Do the simplest thing that could possibly work.

2
  • 7
    PostgreSQL also has surprisingly robust plugins. A good chunk of what you would be using Neo4j in your project for could be replaced with Apache AGE.
    – jrose
    Nov 13, 2023 at 23:25
  • 2
    Upvoted for the last paragraph which can be easily generalized for any project. Nov 15, 2023 at 11:21
11

Justified complexity is usually not the target of seemingly anti-complexity guidelines. The overall goal of these guidelines is to cut out unnecessary complexity, which can be countered by properly justifying its existence.

You haven't really spent much time justifying the need for this complexity. The general question to ask yourself here is:

Are the gains worth the cost?

In other words:

  • How much more difficult is it going to be to build a system that relies on all three techs (as opposed to two, or one)?
  • What do you stand to gain from doing so?
  • How much do you need those gains?

You can really only answer your main question when you find answers to these questions and really consider your priorities here. Some contexts justify any squeezed drop of performance, whereas other contexts are better off with a more easily maintained codebase at the cost of minor performance improvements. I can't answer that for you.


The alternate solution, i.e. a suggestion that uses less complexity and achieves everything you want anyway, is not something I can offer. I haven't worked with these technologies so I'm not the right person to answer that.

1
  • 1
    Exactly. I'm not familiar with the specific tech either - but unless you really know that you'll need all that complexity, it's much better to keep it simple. And since this is described as a "graduation project", it's very doubtful that the complexity will pay for itself. Nov 13, 2023 at 22:17
4

There is a trade-off between:

  • Ease of use.
  • Ease of development/maintenance.
  • Richness of the feature set.

There is a real risk that you will leak the details of your implementation into your user interface. Pick one of three types of queries and based on your choice, you get different functionality.

You can certainly put additional development effort into hiding this. However, you will quickly run into the N+1 problem: run a query against one data source which generates a set of results. Then you need to run N queries against a different data source and aggregate all the results.

Picking a single data source will reduce the feature set you can provide, but it will dramatically simplify the development effort and you may find that with a bit of clever planning you can satisfy most of the requirements that three datasources would have provided, with only a single source.

When you "justify" your three datasource decision, it is insufficient to simply say that you needed data source X for functionality Y, if one of the other datasources could have delivered most of functionality Y... Did you really need the missing functionality? Is that enough to justify the added complexity?

As an example, I have meet most of your requirements using only Solr (the predecessor of Elasticsearch) although some additional processing was needed when the metadata was fed to the indexing process. Solr can't deliver the same free form queries as Neo4j, but I knew the queries in advance.

1
  • I would add "Ease of administration" as a 4th bullet point. Using 3 different database management system means 3 times the effort for sysadmins to keep their servers running and their software updated.
    – Philipp
    Nov 14, 2023 at 10:29
4

Good engineering is not about always using the ‘perfect’ tool for the job. It’s about recognizing when a tool is good enough to meet your requirements, and then not overthinking things.

Sure you could use Elasticsearch for search indexing, but PostgreSQL has its own FTS engine, and I strongly suspect that that will be good enough for what you’re actually doing here. Yes, it might not scale as well as Elasticsearch would, but you likely don’t need absolute maximal scalability here (and even if you do, it may still be good enough if you can throw enough resources at it). So no, unless you could prove to me that you need the scalability (or some other feature that Elasticsearch offers), I would say it’s not justified to use it here.

As far as metadata handling, you could use Neo4J for that, but a full graph database sounds like it’s beyond overkill here. What your stated requirements seem to describe is RDF, so that’s probably what you should be looking into using. The Redland RDF libraries are probably the first thing I would look into; they do add an extra set of dependencies, but they don’t need a new backend component because they can store their data directly in PostgreSQL. It may still make sense to use Neo4J if you have significant experience working with that though, because saving time on development is generally a good thing if you can do so without compromising quality (and while it’s almost certainly overkill, it should still work here), but this is something I would seriously research first (if you can do everything with RDF, and the performance is good enough, do you really want to deal with yet another backend component?).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.