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 ...
PostgreSQL and BRIN indexes
Test it for yourself. This isn't a problem on a 5 year old laptop with an ssd.
CREATE TABLE electrothingy
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, -- ...
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 ...
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 ...
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 ...
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 ...
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:
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
LISTEN / NOTIFY for PostgreSQL
in the database...
NOTIFY static_channel_name, 'static-message';
or in a function/trigger:
perform pg_notify('dynamic-channel-name', 'dynamic-message');
in the database client:
LISTEN some_channel_name; --note the lack of quotes
The LISTEN client will receive ...
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 ...
Let's break this question up into a few parts.
Q: I need to insert 1mm rows a day. Is that a lot?
Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning.
Using SQL Server ColumnStore indexes
Well, okay, just one -- a clustered CS index.
If you want to read about the hardware I did this on, head over here. Full disclosure, I wrote that blog post on the website of the company I work for.
On to the test!
Here's some generic code to build a pretty big table. Same warning as Evan, this can take a while to ...
I suggest you take a very close look at this :
It explains fairly well why "stall all reads and writes" is nothing more than an inevitable logical consequence if you want partitions and the data they contain to be consistent.
What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to ...
If you have not already, take a look at a time series DBMS, since it is optimized for storing and querying data where the primary focus is the date/time type. Typically time series databases are used for recording data in the minute/second/sub-second ranges, so I'm not sure if it is still appropriate for hourly increments. That said, this type of DBMS ...
You would only need to put the SQL Server instances in single user mode if you were restoring the master database. For user databases, you have to make sure there are no active connections to the database you're restoring. You'd either have to determine and kill any active SPID's (which would not require the database to be in single user mode) or actually ...
Start understanding that the problems you face have nothing to do with the database technology you use. They are caused by physics, and physics does not care about oracle, microsoft or open source. It is the same.
Generally - depending on the queries you may want a HUGH server (albeit "billions of rows" sounds too much for a phone dictionary). Or cluster of ...
It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA).
Maintenance plans are not bad, but when your environment grows, the limited flexiblity and functionality that maintenance plans provide wont be sufficient.
For e.g Maintenance plans suffer ...
Hierarchic Self-Referential Data
Syntax shown using the free and open source database, PostgreSQL
You need a self-referential table, which creates hierarchy in the database. This is how it looks.
CREATE TABLE messages (
ts_entered timestamp DEFAULT now(),
reply_to int REFERENCES messages,
id int PRIMARY KEY
Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else. Over time your big-ball-of-mud will get ...
Part of the point of using formal methods in developing relational theory was to develop procedures that could be automated. I'm pretty sure C.J. Date comes right out and says that in his Introduction to Database Systems.
Several CASE tools released in the late 1980s or early 1990s could derive every possible 5NF schema either by analyzing ER diagrams or by ...
Basically you a bunch of options here.
Hybrid (basically relational data with some extended data in a non-relational form, like XML or the like).
Your initial discussion suggests that your data is essentially tabular so a relational model is naturally fits your data.
The second point to keep in mind is that the relational model ...
With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...
Your existing index on DATE is obviously useless for the query. The first obvious step for your query:
SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';
is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like:
CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b);