3

I'm about to develop an application that will mainly store user-encrypted data. Each piece of data being encrypted thanks to an AES key, itself encrypted with the public RSA key of each granted user.

Given that the volume of non-encrypted data is negligible (probably only users login, some dates and foreign keys), I wonder if "standard DBMS" such as PostgreSQL or MySQL would be a good choice.

Indeed, these DBMS are optimized to handle different types of data (text, integers, dates, ...), indexes, process calculus, aggregations and so on.

In my case, the vast majority of the data I will store would be large blocs of text (or maybe binary data). And most of the required calculus will be processed on the client side, after decryption.

Therefore, I won't either be able to parse my data. So even if I had a huge database, it wouldn't be qualified as "Big data". However, I wonder if MongoDb, MemSQL or whatever wouldn't be a more accurate choice. And if not, what would be the best DBMS and the best way to use it for my purpose ?

In other words, I think each DBMS have to make sacrifices in order to be more efficient on most important features. I also think the case detailed above is not that casual. So I assume there is a lot of features I don't need (or I can't use, such as FULLTEXT search for instance). On the other hand, I may need features that are discarded by "standard DBMS".

Improve this question
4
  • 1) I for one don't see how encryption at all affects the criteria in the your decision? If the client is doing all the encrypting and decrypting then the database doesn't know or care about the bytes being encrypted 2) I don't think you know what "De facto" means - totally baffling use of that term. 3) What does "Big Data treatments" mean? That's sort of buzzwordy and not at all clear. Commented Jan 17, 2014 at 23:02
  • Encryption does not directly affect the criteria in my decision. However, this means the vast majority of the data I will handle would be inexploitable textual information. Then, I wonder what would be the best engine for that purpose. I will edit my question, and try to make it clearer, sorry. Commented Jan 18, 2014 at 12:59
  • Thanks for the question improvements; I understand the question better. Unfortunately, I think more criteria than what is given is required to answer authoritatively. The scale, performance expectations and financial underpinnings would guide you better than the single criteria provided. Commented Jan 21, 2014 at 21:51
  • This is actually more about self-learning on cryptography, database design and performance optimizations. I feel really concerned about privacy on the internet and saving computer resources. This project is a way to enforce my theoretical knowledge. If there was a financial underpinning though, it would be the safety of the data stored on the database. As I said, I also do anything to make my application as fast and the least greedy as possible (but I don't have real-time processing constraints either). Commented Jan 22, 2014 at 18:20

3 Answers 3

Reset to default
7
+50

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features.

Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing with this for over 20 years now - and it is worth making use of them just for that. In addition to this, relational databases get you the benefits of a long history of performance tuning. They run highly optimised native code so you don't have to struggle with poor Java implementations, bad network stacks, overuse of memcpy, garbage collection, coarse locking and all the other pathologies new products (especially the noSQL stuff) tends to have.

To store encrypted data, use the BINARY data types. MSSQL, MySQL and Postgres all support these types. You may not want to do operations directly on these types (though you CAN do binary manipulation if you want to). Also note that it is relatively easy to implement the encryption/decryption in the database itself as all of the above support crypto libraries. You will also benefit from indexing on the key/foreign columns so you can find your data fast. A relational database is an excellent key/value store for small value types - SQL Server will easily get you over 1-10M key/value lookups/sec even on a small box - I expect MySQL and PostGres would deliver results in the same ballpark.

It is also easy to find programmers who can query a relational database efficiently. SQL has been around for a very long time and it is an extraordinarily powerful language. MSSQL will even give you automated parallel execution. Some programmers wont "get" it - but if they don't, chances are they also won't grok parallelism or lambda expressions (two crucial skills of a modern coder).

On top of all of this goodness, you also get powerful backup and monitoring tools for all of the standard relational databases.

So basically, unless you have a REALLY good reason to use NoSQL - just use relational databases.

Improve this answer
5
  • 2
    Plus: relational DBMS (especially Postgres) have been adding more and more "NoSQL features" (key/value storage, JSON, XML, arrays, ...) Commented Jan 19, 2014 at 9:48
  • 2
    I dont necessarily see that as an advantage :) Commented Jan 19, 2014 at 10:00
  • I do. You don't have to use those things if you don't need them. But if you do have requirements that can't easily be solved using a relational DBMS you don't need to introduce another piece of software which just adds complexity to the whole system. Commented Jan 19, 2014 at 10:27
  • 4
    Considering that Postgres is not even done implementing parallelism and user mode scheduling, I think there are bigger fish to fry than storing XML in the database. Commented Jan 19, 2014 at 12:21
  • Thank you for your detailed explanation. That's exactly what I expected. Commented Jan 20, 2014 at 17:07
1

There isn't enough information in the question to make an informed decision, but here are some basic points. If you want more detail, please explain more about how the data is going to be queried and how large is it expected to grow, and how large are encrypted segments supposed to be and so on.

So, in general - regardless of what else is decided, I would put the "control data" (users login, some dates, etc.) into an RDBMS of your choice. As far as the rest of the data is concerned, there are some considerations:

how is the encrypted info going to be retrieved? Are you going to search for binary strings? have some metadata that will help locate the right value? key value pairs?

If access is by key look-up, a Berkley DB or Mongo with some sort of a local cache (like memcache) would be more than adequate.
If a successful look-up is going to require more "thinking" a relational store may be needed to support the look-up logic.

Improve this answer
0

It is not advisable to use a DBMS that is pointing to a file (like Mongo does) for storing text, at least not for text of your length. Each time you would want to access a single string, means I/O, and we all know what that does to performance. So indeed, you should stick to MySQL or even SQL Server (if able to pay for it).

Mainly because each of them have a datatype specialized for this kind of data: TEXT. I heard many times that TEXT is suitable for storing just text, as an alternative to VARCHAR or even VARCHAR(MAX). This is WRONG. TEXT datatype is handled differently by the database engine, and of course optimised for situations like yours.

Further, if the rows in your table are not accessed entirely ie. the other columns are queried frequently BUT your TEXT one (not "SELECT *" kind of queries), you should consider converting the table in 1NF, and reference the huge TEXT columns only when needed.

BUT, if you decide to keep all the data in a single table, an index is mandatory. The last thing you want is a full-scan, that will fill your buffer-cache in no time.

Improve this answer
2
  • 1
    In MSSQL the TEXT type was intended for text, hence the 16bit/char NTEXT variant. For binary data IMAGE was preferred. All three of threes types have been deprecated since 2008 (technet.microsoft.com/en-us/library/ms143729(v=sql.100).aspx) and may be removed completely from future versions - you should instead use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) respectively. Commented Jan 19, 2014 at 13:36
  • text and ntext are being phased out. it isn't a good idea to use them in new dev Commented Jan 19, 2014 at 18:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.