3

(Apologies if this question is better suited to Software Development or DBA!)

I'm planning a system to store and view a library of 10-50k documents and am trying to work out the best option for storing them. The data will only be written once so I'm focusing on read performance.

Most users will be searching for documents by date and/or location, but I'm planning on adding various user-editable searchable tag fields to each document. I'd like to offer full text search too. Further down the line I'd like to be able to run named-entity extraction on the documents so I'll need to store the results of that.

I'm currently considering running MongoDB or Cassandra for fast access alongside Elasticsearch for the greatest search flexibility but I'm wondering if that is going to be too much hassle and I should just use PostgreSQL or a similar relational database (my data is pretty uniformly strucured) that offers text searching.

Improve this question
0

1 Answer 1

Reset to default
1

Postgres meets all your needs:

  • 50k documents. Postgres easily handles millions of records.
  • Read performance. Postgres is very fast, having its own caching in memory plus the caching from disk provided by your host OS.
  • Searching by date. Postgres has some of the best date-time handling features in the industry, with extensive data types and built-in functions. If you mean to track specific moments such as when the document was first saved or was last modified, use data type TIMESTAMP WITH TIME ZONE. You can set a default function to be called when inserting a row, to capture the current moment. Or you can write a trigger to automatically record when a row is modified.
  • Search by location. Not sure what you mean by location. You should clarify in your Question. If you meant geolocation, Postgres leads the industry with its PostGIS add-on. See Wikipedia.
  • User-editable searchable tag fields. You can add columns and even tables on the fly in Postgres. Or rename columns. Such commands are called DDL for data definition language.
  • Full-text search. Postgres has strong support for this. See this chapter in the manual.

And Postgres provides enterprise-quality reliability with ACID compliance, unlike the “NoSQL” products.

Improve this answer
1
  • Thanks, that's a really good case for Postgres. I wasn't aware of its date functions so that's an even better fit than I thought.
    – MorayM
    Commented Dec 16, 2019 at 13:32

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.