Hot answers tagged database-recommendation
36
NoSQL stands for "Not only SQL" and usually means that the database is not a relational database, which have been very popular the last decades.
The reason why NoSQL has been so popular the last few years is mainly because, when a relational database grows out of one server, it is no longer that easy to use. In other words, they don't scale out very well ...
16
First impressions
Depending on your performance requirements, 100TB is a fairly aggressive data volume. If you want Oracle, you should check out their Exadata systems. Also, take a look at the offerings from Netezza or Teradata. With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of ...
14
If there is one thing I can say about MySQL is that InnODB, its transactional (ACID-compliant) storage engine, is indeed multithreaded. However, it is as multithreaded as YOU CONFIGURE IT !!! Even right "out of the box," InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you ...
13
NoSQL is a very broad term and typically is referred to as meaning "Not Only SQL." The term is dropping out of favor in the non-RDBMS community.
You'll find that NoSQL database have few common characteristics. They can be roughly divided into a few categories:
key/value stores
Bigtable inspired databases (based on the Google Bigtable paper)
Dynamo ...
12
This solution is called a multi-tenant design where each tenant (customer) has their own database. Given that, there are some other considerations to the alternative approach which is a single database:
With a single database, everyone must be on the same version no matter what. It isn't possible to upgrade some customers and not others. This can be ...
11
I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, ...
11
Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target.
However... I would venture to say that there could not be such a tool.
Normalization, technically, is an attribute of each table. Within a given database, different tables may have ...
10
I don't see how anyone could make such a statement without having some actual facts to back it up. If your queries are CPU bound, then you should look to find ways to reduce that bottleneck.
It sounds as if your boss feels that a denormalized database will perform best, but I don't know enough about your application to say if that is right or not. What will ...
9
There is no reason to speculate whether Oracle "will" continue developing MySQL actively. The facts are easy to find out: look at the changelog and preview release announcements. The truth is that Oracle has accelerated MySQL development faster than it's ever happened before, and the releases are really good quality too, unlike Sun's 5.1 release or MySQL ...
9
In Oracle you can use the built in DBMS_ALERT package to facilitate this.
DBMS_ALERT supports asynchronous
notification of database events
(alerts). By appropriate use of this
package and database triggers, an
application can notify itself whenever
values of interest in the database are
changed.
Suppose a graphics tool is
displaying ...
9
Oracle Apex. A handy, easy-to-use web application environment built right into the database. Quite simply it makes it very simple to deploy 'single-box' applications with the web ui/application logic/database in a single integrated package.
PS. 11g XE (currently in beta) expands the storage to over 10GB.
9
If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant.
It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)
8
I think now is the time to play with MariaDB (just like now is the time to play with anything like this). Personally, I would hold off until I know a few more devs who have deployed MariaDB onto a live customer site before doing it myself.
Some situations could call for a change of application but something as serious as changing your database engine is not ...
8
NoSQL is a kind of database that doesn't have a fixed schema like a traditional RDBMS does. With the NoSQL databases the schema is defined by the developer at run time. They don't write normal SQL statements against the database, but instead use an API to get the data that they need. The NoSQL databases can usually scale across different physical servers ...
8
Here is my opinion:
Usually the bottleneck (or slowest part) of a DB system is the disk. The CPU only spikes during arithmetic operations, processing, or any other task that the CPU does. With proper architecture, multithreading can help to offset the load of a query onto the CPU instead of doing the slow disk reads/writes. There are cases where it is ...
8
To be very fast, the database should only use memory and not disc since disc operations usually takes much longer time. But then your data aren't persistent in case of a crash. What you could do is assynchronous disk operations, by that most of your writes will be persistent but it's not guaranteed that the last few writes are persistent. If this is okey for ...
8
Here is my opinion:
If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though.
Partition the table based on a broad category like year.
Put these partitions on different filegroups.
8
There are a number of options and please don't limit yourself to my answer here. In particular you may find array-native databases to be of help. My answer is going to be specifically about your questions on SQL-based databases.
It sounds to me like this is a question of geospacial information. SQL-based databases are in fact used in such fields quite ...
7
SQL Server 2000, 2005, 2008 has this capability
GRANT { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ class :: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]
ADDED
In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see ...
7
Certain database vendors also provide integrated message buses that your app can simply subscribe to:
Oracle Advanced Queueing
IBM DB2 with MQseries (now called WebSphere MQ)
Sybase RTMS
An alternative would be to route the data into the database in the first place via a message bus like Tibco/RV and simply "branch" it, on stream going into the DB and ...
7
All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.
Since we're not ...
7
If we're on about identity or autonumber columns, the value itself should have no meaning. (sometimes it does, as per SO's chat users nentioned by drachenstern, which I've done before myself)
However, generally you'd lose half of your range if you're using signed integers.
See: What to do when a field in a table approaches the max signed or unsigned 32 bit ...
7
Not all database systems even support unsigned integer types, MSSQL being one of those that doesn't. In these cases negative values are possible in integer key fields simply because they are possible in the type (you could use rules or triggers to block them, as shown in this example, but there is probably no need add the overhead of enforcing such rules to ...
7
As soon as you have multiple concurrent users or processes, or even a single process with multi-threaded database access, having a database that supports threading will become potentially interesting.
H2 is thread-safe, but serializes all requests to the database, which may become a potential performance issue in a heavy load scenario. Whether this is ...
7
Some implementations of SQL do recognise x = NULL as equality, the ISO/ANSI standard on the other hand does not. In SQL Server for instance, SET ANSI_NULLS OFF results in (NULL = NULL) = true.
SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END
SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END
7
Assuming that you are talking about tables containing the same kinds of entities, you typically want to have one table.
You would not have any performance differences and a whole lot of management differences between the two approaches, with the single table being easier to manage. Typically large tables do not have performance overheads compared to ...
7
IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively.
I would be surprised if a well-tuned db ...
6
In my experience you shouldn't create a db per costumer, let me give you an example:
Last year I worked with 70 databases (a lot less than 5000), each with the same scheme and all, in theory, things would go as planed (as you mention in the advantages section), but in reality not so much. We had many problems with updating schemes, user support, software ...
6
You'd likely want to keep another database to track what version each customer is at, so you could keep track of which ones have or haven't undergone the last round of modifications.
Scripting the upgrades wouldn't be that difficult ... you could write something that looks at the catalog of databases and applied the necessary changes to get each database to ...
6
You could use a Document-oriented database for this. You could then create a program in your preferred language to import the existing documents into the db, parsing the folder structure for the metadata (customer, job#, etc).
Only top voted, non community-wiki answers of a minimum length are eligible
