Database for searching lots of plain data

Question

I currently use MySQL and Sphinx to manage a pretty basic database containing nearly 350,000,000 records. It is expected to hit a billion records in less than 200 days (anywhere from 2000 to 5000 records per minute). MySQL is dreadfully slow and Sphinx improves the speed quite a bit, but delta indexing is a pain and I'm not sure if RT indicies are an option.

The data consists of several integer values and one float value. We search through the database based on integer matches and integer ranges (like match maybe 4 integer columns exactly, and we'll give another integer column a range between x and x+20000000).

Ideally, the best software would be free (in both freedom and free-beer), easy to configure, and returns searches quickly (<0.5 seconds). It should also have libraries available for NodeJS or any other scripting language (not really required).

Answer 1

Two suggestions: 1) Oracle Database 11gR2, 2) MongoDB.

1) Oracle Database 11gR2:

The most advanced and the most powerful DBMS for transaction-heavy solutions. Can be downloaded and used for free (Support is commercial).

I've tested it on an ordinary laptop with a dataset of approximately 90M records. Searches based on the Id field(which was Integer) were returned within 0.1s.

2) MongoDB:

The most popular NoSQL-Based DBMS which has a great capability of integration with Node.Js. In fact, it's a Document-Based DBMS suitable for the Big Data.

Which way to go?

1) If you want the feeling of working with a RDBMS(just like MySQL) and if your search is based on integer values(You should create wisely indexes for those columns).

2) If integration with Node.Js is important for you and your data will grow unstructured.

Answer 2

Postgres

Postgres is the go-to solution if you need an enterprise-quality database that offers both "freedom" (open-source) and "free-beer" (free of cost).

But no silver bullet, no way to get around the need for:

• Very healthy amount of memory (RAM) installed with a 64-bit OS.
• Careful design of tables/columns and indexes.
  Your extreme needs may justify de-normalizing data.
• Study and practice with various memory & cache settings in Postgres and OS.
  For most people and most projects, the default settings in Postgres are good enough. But your case is likely to be helped by some tweaking and profiling. The Postgres community offers excellent documentation, mailing lists, trade books, and expert consulting companies to assist.