88

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my ...


53

PostgreSQL and BRIN indexes Test it for yourself. This isn't a problem on a 5 year old laptop with an ssd. EXPLAIN ANALYZE CREATE TABLE electrothingy AS SELECT x::int AS id, (x::int % 20000)::int AS locid, -- fake location ids in the range of 1-20000 now() AS tsin, -- static timestmap 97.5::numeric(5,2) AS temp, -- ...


24

David Hay's Enterprise Model Patterns. This is a beast of a book, but has some great patterns. Conventions of Thought. More stuff on MRP. A Meta-Data Map . Haven't read this one. Len Silverston's Data Model Resource Book Vol. 1. Your main data model patterns. Data Model Resource Book Vol. 2. Case studies by industry. Data Model Resource Book Vol. 3. ...


24

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 ...


21

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 materialised ...


20

I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to ...


20

You haven't really given us much info about what this data is going to be used for. I mean, you have said what data is going to be stored, but what are you going to do with it? If your purpose is storing the data then reporting on it, then I think you're looking in the wrong place. A simple MySQL or SQL Database would do just fine and the reporting tools ...


15

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 ...


14

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc. Storing JSON blobs in the database will kill performance when you're performing searches, so I ...


13

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 ...


13

It amazes me me that nobody here has mentioned benchmarking - that is until @EvanCarroll came along with his excellent contribution! If I were you, I would spend some time (and yes, I know it's a precious commodity!) setting up systems, running what you think will be (get end-user input here!), say, your 10 most common queries. My own thoughts: NoSQL ...


12

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.


12

A very common misconception about Databases can be dispelled with this: Database != File When you update a Row in a database, the underlying data file on disk isn't touched at all - at least not for "some time". Instead, the database makes a note of the change in its Transaction Log, then updates the value in memory. "Some time" later, the database ...


11

If you are not that concerned with relational logic, want really fast read speed, and you are willing to work with an RDBMS, I would prejudicially venture to say MySQL. Why ??? The MyISAM storage engine has an option that can allow the physical structure of the table to be augmented for better performance. What is that option ? The ALTER TABLE option ...


10

You should fully tune the MySQL Environment, particularly your InnoDB settings. (See the bottom of my Answer for tuning tips). This would be much better than fighting Amazon for elbow room in RAM/Disk. Why did I say fight? If you just spun up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL ...


10

Defragmentation strategies help improve scan speed to/from disk. The wide variety of opinions is because an environment's ideal defragmentation strategy should depends on many different factors. There are also multiple potential layers of fragmentation in play. Saying that your databases are stored on a SAN isn't enough information. For example: Are ...


10

Personally since you don't have a relation beyond barcode --> document I don't think a relational database is the best fit. If your requirements are really as simple as: Find a document Serve document to user Then any Key/Value store should work. This wikipedia article talks about some of the theory for Document Databases as well as some pro/cons vs ...


9

The first thing that comes to mind is a particular RDBMS that's familiar to me. I recognize, however, that it may not be the best for this application. So, my advice is to go with a database that are familiar to you. If you're familiar with Redis or MongoDB, then go with one of those. If you're more familiar with SQLite, then chose that. On a database ...


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)


9

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 ...


9

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 ...


9

This seems a little outside the scope of a StackExchange question. However..... NoSQL databases are, typically, build to resolve specific issues with the relational model. The most common issue addressed is scalability. However, because they're all designed to address different aspects of certain problems that some applications have with the relational ...


9

Refer the the Concepts Guide - Overview of Views for this sort of question: Overview of Views A view is a logical representation of one or more tables. In essence, a view is a stored query. [...] Characteristics of Views Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a ...


9

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit. I would suggest trying, in this order: Increase the memory allocation to SQL Server. You said the ...


8

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


8

If this were my design decision, I would probably go with more of an 'Option C' (modified option a). First, why not 'Option B': For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear. For another, the indexing strategy would always require ...


8

Here are some of my recommendations for InnoDB. In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs. http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html This is the buffer pool, where data and index are cached innodb_buffer_pool_size=(In Mb,...


8

Take a look at the Dell DVD Store database. It's available for multiple databases, SQL Server included. The Dell DVD Store is an open source simulation of an online ecommerce site with implementations in Microsoft SQL Server, Oracle, MySQL and PostgreSQL along with driver programs and web applications


8

Some other options to consider when dealing with massive data volumes like this include: Everything that @ConcernedOfTunbridgeWells posted Greenplum from EMC Parallel Data Warehouse from Microsoft Don't plan on skimping on hardware costs anywhere. A system with these sorts of specs is going to cost you some big bucks.


8

Trying to normalize addresses is generally a bad idea. There isn't a lot of value to normalizing addresses. Both of your designs are inappropriate for the vast majority of systems. There are two things you typically do with addresses: Use them to send mail or packages to that location. Use them to do geospatial analysis on that location. Since you are ...


Only top voted, non community-wiki answers of a minimum length are eligible