4 ms·

Databases - a grumble after a weekend project

4 points by hoodoof 14y ago

I did a project over the weekend. I wanted a database where I could throw in JSON data, do some simple queries, get data back. You'd think that would be easy right? Install it, 5 minutes, install drivers, 5 minutes, write some code to throw in some JSON - 5 minutes, write some simple queries - 10 minutes. No way - it's not that easy. Man, databases have a long way to go. I knew not to use MongoDB because it takes an entire server and I was running on one single AWS micro instance. I tried Amazon Dynamodb - simply not possible to throw a JSON structure into it. I dropped it. I went back to Postgres - and spent hours doing configuration of users, tables, schema, and security, not writing code for my application. I deleted Postgres from my system. Why do SQL databases have massive layers of configuration and security on them - seems a relic of the past - why not let the application take care of all that. I tried to think what other database might allow me to simply throw JSON data in and do SIMPLE queries to get it back. Something without installation pain, configuration pain and usability pain. In the end I tried RethinkDB. It installed fast, the install documentation was perfect for an Ubuntu system, it seemed to work well, did not take over my machine and allowed my to throw JSON into it with no pain. However it was much more painful to do queries - Rethink is still improving in this area - even basic queries are painful. BUt the guys there are incredibly helpful and friendly and helped me to get some working queries built. SO..... databases just aren't there yet. Where is the database that simply allows me to throw in JSON and has a simple and intuitive query language that just works. One day someone will get that worked out. If rethinkDB can manage to get powerful and simple JSON queries implemented then maybe it will be The One. Can anyone suggest a database that allows simple installation, simple data loading and simple and powerful queries? Something that does not require layers and layers of arcane configuration just to get going with?

jlengrand 14y ago
There is one thing I don't understand. Why do you want to directly throw JSON at the database? Why not write a simple and minimal JSON to SQL layer? You can probably do that really fast with ANTLR (http://www.antlr.org/ http://www.antlr.org/) or any equivalent.
mattparlane 14y ago
I'm not sure where your idea about MongoDB taking "an entire server". It will use as much memory as it has available to it, but of course you can run other things alongside it. I'm currently running PHP, Ruby, Memcache, Postfix, nginx, plus probably a bunch of other stuff I'm forgetting, all on one server and it works fine. MongoDB can be configured entirely on the command line and is as simple as downloading a binary, unzipping it and running it.
- hoodoof 14y ago
  My understanding is that it takes all the available memory - not true?
  - mattparlane 14y ago
    It uses memory mapped files, which is different from malloc()ing a whole lot of memory. If you do a "top" or whatever, it won't show up as taking a lot of resident memory, but "free" will show it being taken up in the "cached" column. The end result is that it will "use" all available memory on the system, but if you want to run anything else alongside, the kernel will just de-allocate memory from MongoDB and allocate it to other processes. The only downside is that it's not very tuneable -- you are at the mercy of the kernel and its algorithms. In my experience if you have enough memory to hold your "working set" (which has a fairly loose definition), you should be fine. We have also disabled swap on our servers -- I would recommend trying it, but do so carefully, you don't want your database server being OOM'd while serving production traffic. We have 2 DB servers each with 24GB RAM and the whole database is about 90GB but that includes a bunch of logging stuff that is truncated periodically and it's not in our "working set". We are running other processes on the DB servers as well (the ones I mentioned) and everything is running fine.
    - hoodoof 14y ago
      I really liked ReThinkDB. Have you tried it?
      - 14y ago
kbenson 14y ago
My first thought was that maybe we don't need things to "be there" or "arrive", especially when they are complex and integral (in some respects) as a choice of a database, or at least the data representation or the database. Then I thought of SQLite, and what it's allowed, which is ubiquitous instance databases for light usage and/or rapid prototyping. Maybe what we need is the equivalent of SQLite for NoSQL databases. Does that exist yet?
- hoodoof 14y ago
  Richard Hipp, the author sqlite, came up with the Unql specification which was meant to be a standardised way to query JSON data http://www.unqlspec.org/display/UnQL/Home http://www.unqlspec.org/display/UnQL/Home You would imagine the logical next step would have been for him to implement Unql into a SQlite-like JSON application, but that did not seem to happen.
- hoodoof 14y ago
  How practical is sqlite for anything more than single user applications?
  - kbenson 14y ago
    Probably not very. That said, for single user applications (such as ios/android apps) or rapid prototyping, they may be very practical. Then again, prototyping is only as useful as it is easy to trade out for the eventual solution, and if it has a different query syntax than what you replace it with, you were probably better off with the pain to set that up in the first place. I could see a SQLite-alike JSON database doing really well in the phone app space. Especially for those using a framework like phonegap that allows you write in javascript. In fact, I think this is obvious enough that there probably exist solutions for this, and I'm just not knowledgeable enough in the subject to know of them.
    - jaredsohn 14y ago
      >prototyping is only as useful as it is easy to trade out for the eventual solution It is also useful if you find out from prototyping that the project is not viable. Also, in general, if you need to iterate a lot using something that iterates faster at the prototype stage would have extra benefit. (Although in this particular case I think using SQLite only saves you initial configuration time.)
dragonwriter 14y ago
> I went back to Postgres - and spent hours doing configuration of users, tables, schema, and security, not writing code for my application. I deleted Postgres from my system. Why do SQL databases have massive layers of configuration and security on them - seems a relic of the past - why not let the application take care of all that. "SQL databases" don't. Standalone multiuser, nondedicated database servers (many of which happen to be SQL-based RDBMSs) do, because they are designed to support direct user access (through general purpose client applications) and multiple applications -- both of which need to authenticate against the database -- from the same database server. So the application can't reasonably handle security and configuration. There are SQL databases that have less security and configuration, because they are embedded databases rather than database servers (SQLite is a particularly well-known example.)
- hoodoof 14y ago
  In Postgres for example there is just so much arcane configuration that needs to be done before you can start writing code. All that extra guff should be an add on option for "enterprise users". Why not just the database functionality on its own, stripped of all the complex surrounding bits? Have you ever experienced the pain of configuring pg_hba.conf? Or trying to work out the right user access control to your database entities and then keep it all ok when the database setup needs to be changed? Nightmare.
  - dragonwriter 14y ago
    > In Postgres for example there is just so much arcane configuration that needs to be done before you can start writing code. Not really. > All that extra guff should be an add on option for "enterprise users". It may not be right for whatever toy project you are doing, but that doesn't mean that it isn't right for the baseline of a product that does what Postgres is intended to do. > Why not just the database functionality on its own, stripped of all the complex surrounding bits? The things you call "complex surrounding bits" are part of the core functionality of a RDBMS server. > ave you ever experienced the pain of configuring pg_hba.conf? I've made changes to pg_hba.conf, though for simple development against a default install I usually don't have to. I never found it painful. > Or trying to work out the right user access control to your database entities and then keep it all ok when the database setup needs to be changed? Never had any problem with that. If you don't have really complex security needs -- and especially for the kind of development where its sensible to consider application-managed security rather as an alternative to DB managed security -- its straightforward. You can just create a user for the application, and create a database in the cluster owned by that user, and bam, you're done. For more complex cases, sure, its more work, but then the work is inherent in the more complex cases, not a product of the DB features.
jaredsohn 14y ago
I have found that Meteor provides a very simple/fast way to deploy/use mongodb if you want to build something quickly. (I just did so myself this past weekend.)
balazskiss1988 14y ago
You could also try out redis, it's a key-value store that is easy to use and needs little to no configuration.
memracom 14y ago
More people should think of SQLITE when doing a simple project over the weekend... For this use case, you can use the BLOB datatype and just write a simple shim with get and set methods that interfaces with the db.

4 points by hoodoof 14y ago

jlengrand 14y ago

There is one thing I don't understand. Why do you want to directly throw JSON at the database? Why not write a simple and minimal JSON to SQL layer? You can probably do that really fast with ANTLR (http://www.antlr.org/ http://www.antlr.org/) or any equivalent.

mattparlane 14y ago

I'm not sure where your idea about MongoDB taking "an entire server". It will use as much memory as it has available to it, but of course you can run other things alongside it. I'm currently running PHP, Ruby, Memcache, Postfix, nginx, plus probably a bunch of other stuff I'm forgetting, all on one server and it works fine. MongoDB can be configured entirely on the command line and is as simple as downloading a binary, unzipping it and running it.

hoodoof 14y ago
My understanding is that it takes all the available memory - not true?
- mattparlane 14y ago
  It uses memory mapped files, which is different from malloc()ing a whole lot of memory. If you do a "top" or whatever, it won't show up as taking a lot of resident memory, but "free" will show it being taken up in the "cached" column. The end result is that it will "use" all available memory on the system, but if you want to run anything else alongside, the kernel will just de-allocate memory from MongoDB and allocate it to other processes. The only downside is that it's not very tuneable -- you are at the mercy of the kernel and its algorithms. In my experience if you have enough memory to hold your "working set" (which has a fairly loose definition), you should be fine. We have also disabled swap on our servers -- I would recommend trying it, but do so carefully, you don't want your database server being OOM'd while serving production traffic. We have 2 DB servers each with 24GB RAM and the whole database is about 90GB but that includes a bunch of logging stuff that is truncated periodically and it's not in our "working set". We are running other processes on the DB servers as well (the ones I mentioned) and everything is running fine.
  - hoodoof 14y ago
    I really liked ReThinkDB. Have you tried it?
    - 14y ago

kbenson 14y ago

My first thought was that maybe we don't need things to "be there" or "arrive", especially when they are complex and integral (in some respects) as a choice of a database, or at least the data representation or the database. Then I thought of SQLite, and what it's allowed, which is ubiquitous instance databases for light usage and/or rapid prototyping. Maybe what we need is the equivalent of SQLite for NoSQL databases. Does that exist yet?

hoodoof 14y ago
Richard Hipp, the author sqlite, came up with the Unql specification which was meant to be a standardised way to query JSON data http://www.unqlspec.org/display/UnQL/Home http://www.unqlspec.org/display/UnQL/Home You would imagine the logical next step would have been for him to implement Unql into a SQlite-like JSON application, but that did not seem to happen.
hoodoof 14y ago
How practical is sqlite for anything more than single user applications?
- kbenson 14y ago
  Probably not very. That said, for single user applications (such as ios/android apps) or rapid prototyping, they may be very practical. Then again, prototyping is only as useful as it is easy to trade out for the eventual solution, and if it has a different query syntax than what you replace it with, you were probably better off with the pain to set that up in the first place. I could see a SQLite-alike JSON database doing really well in the phone app space. Especially for those using a framework like phonegap that allows you write in javascript. In fact, I think this is obvious enough that there probably exist solutions for this, and I'm just not knowledgeable enough in the subject to know of them.
  - jaredsohn 14y ago
    >prototyping is only as useful as it is easy to trade out for the eventual solution It is also useful if you find out from prototyping that the project is not viable. Also, in general, if you need to iterate a lot using something that iterates faster at the prototype stage would have extra benefit. (Although in this particular case I think using SQLite only saves you initial configuration time.)

dragonwriter 14y ago

> I went back to Postgres - and spent hours doing configuration of users, tables, schema, and security, not writing code for my application. I deleted Postgres from my system. Why do SQL databases have massive layers of configuration and security on them - seems a relic of the past - why not let the application take care of all that. "SQL databases" don't. Standalone multiuser, nondedicated database servers (many of which happen to be SQL-based RDBMSs) do, because they are designed to support direct user access (through general purpose client applications) and multiple applications -- both of which need to authenticate against the database -- from the same database server. So the application can't reasonably handle security and configuration. There are SQL databases that have less security and configuration, because they are embedded databases rather than database servers (SQLite is a particularly well-known example.)

hoodoof 14y ago
In Postgres for example there is just so much arcane configuration that needs to be done before you can start writing code. All that extra guff should be an add on option for "enterprise users". Why not just the database functionality on its own, stripped of all the complex surrounding bits? Have you ever experienced the pain of configuring pg_hba.conf? Or trying to work out the right user access control to your database entities and then keep it all ok when the database setup needs to be changed? Nightmare.
- dragonwriter 14y ago
  > In Postgres for example there is just so much arcane configuration that needs to be done before you can start writing code. Not really. > All that extra guff should be an add on option for "enterprise users". It may not be right for whatever toy project you are doing, but that doesn't mean that it isn't right for the baseline of a product that does what Postgres is intended to do. > Why not just the database functionality on its own, stripped of all the complex surrounding bits? The things you call "complex surrounding bits" are part of the core functionality of a RDBMS server. > ave you ever experienced the pain of configuring pg_hba.conf? I've made changes to pg_hba.conf, though for simple development against a default install I usually don't have to. I never found it painful. > Or trying to work out the right user access control to your database entities and then keep it all ok when the database setup needs to be changed? Never had any problem with that. If you don't have really complex security needs -- and especially for the kind of development where its sensible to consider application-managed security rather as an alternative to DB managed security -- its straightforward. You can just create a user for the application, and create a database in the cluster owned by that user, and bam, you're done. For more complex cases, sure, its more work, but then the work is inherent in the more complex cases, not a product of the DB features.

jaredsohn 14y ago

I have found that Meteor provides a very simple/fast way to deploy/use mongodb if you want to build something quickly. (I just did so myself this past weekend.)

balazskiss1988 14y ago

You could also try out redis, it's a key-value store that is easy to use and needs little to no configuration.

memracom 14y ago

More people should think of SQLITE when doing a simple project over the weekend... For this use case, you can use the BLOB datatype and just write a simple shim with get and set methods that interfaces with the db.