Hot answers tagged

27

As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services. If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM ...


15

The reason to use BLOBs is quite simply manageability - you have exactly one method to back and restore up the database, you can easily do incremental backups, there is zero risk of the image and its meta data stored in DB tables ever getting out of sync, you also have one programming interface to run queries or load/save images, so you don't need to give ...


13

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true? Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference You lose: data integrity restore integrity (data in DB7 will be later then DB1) You gain complexity: ...


13

CRUD is meant to define the characteristics necessary for a database as it relates to persistent storage. It is not meant to describe everything that could be done by a database engine. To make a comparison, fundamentally a vehicle is a device used for transport. While true, this definition certainly doesn't include all the detail entailed in a modern ...


13

I don't use blobs -- mostly from a backup and restore perspective, as I don't want the blob data slowing down my backups. I don't store a full URL, however ... I only store the filepath below a certain point, and build the path as I have more than one way in which people & programs access my files (FTP, HTTP, local directory, NFS mounted directories). ...


13

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors. However, I'm going to focus on two factors primarily, with them being: (1) How serious is this project that it deserves your effort to optimize the schema? (2) What are your query access patterns really going to be like? With those questions in ...


9

FILETABLE data is built upon the FILESTREAM feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX) data in discrete NTFS files on the filesystem of the server itself. This data is not stored in the database, therefore the database file size will not include these 10GB+ files. Database backups will, by default, include the ...


9

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


8

If you are working with linux, storing the images in the filesystem and not in the database has significant better performance, see this excerpt of Brad Ediger's book Advanced Rails.


8

I'm a big fan of storing the "reference" copy of the image in the database -- from a managability/disaster recovery standpoint this is really the way to fly. Now, you can still do lots of things to serve the image out of the filesystem for most applications so you are not putting that much pressure on the database server itself to do things it doesn't ...


8

Some databases can run many engines, depending on the best fit for the job. For example, many of my applications use InnoDB for most data (key constraints and row level locking), MyISAM for session data (fast, less processing) and ArchiveDB for audit trail (compressed and insert/select only, no update/delete). The "rest" of the database software provides a ...


8

Ah, what is the problem? a table with 54 million records A small table. Nice. I have one here with 8.5 billion rows. we have !300 inserts and !100 updates per minute Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute. All that running on stock hardware. Seriously. THough not totally ...


8

Why are you not getting minimal logging? I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I suspect that the grid is complex enough already that the author did not add a column Table Partitioning to break out differences in behavior depending on ...


7

I would say the term Database Administrator covers the full gamut of database duties: design, development, production support, performance tuning, report writing, OLAP, etc. A Database Architect is involved in the design and development of large or complicated database solutions. However, the Architect is usually not involved in the day-to-day operations of ...


6

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to ...


6

At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name. ...


5

I'm not much of a fan of storing images in the database. In a small app with a few users, it seems like an easy solution, but as you start to scale, it makes things more difficult. My preference is to start out storing images in a folder on the web server, but keep the path in an easily accessible configuration so that when I need to, I can quickly move ...


5

"Isn't it possible to scale database servers through virtualization, like forever?" Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...


5

The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational ...


5

As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities. If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be ...


5

I agree with mrdenny to have 1 database per Application. There are many good reasons to do that : Most importantly Log : SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online. Full Backups : Easy to manage per application and can be ...


5

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


4

These two terms have been used interchangeably by many. I look at them distinctly. DATABASE ADMINISTRATOR (DBA) The DBA does just what the name suggests...administrator of the database. Based on the specific RDBMS being managed, the role DBA involves implementing the following aspects: user authentication backup strategies DB Metrics (Recording and ...


4

Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances. There are no performance optimisations available with ...


4

Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion. Almost all lookup tables. You read city and state list already. Why go to database again. Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it. Slow queries ...


4

The raw data for that table is 1.4GB (54m * 28 bytes), the indexes will add some data but nowhere near a huge amount, let's say 5GB for the index. So whatever you do: why only 4GB ram? Make it 32GB for room for growth. MySQL on an SSD will be nicely fast. NoSQL is a buzzword that means nothing, so unless you have a specific product in mind, I will ignore ...


4

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


4

Having done this type of project before, here's some of the practical gotchas to look out for: "2. This master then does one-way slave propagation to up to 200 read-only servers via VPN." Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's ...


4

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ? Under no circumstances would it be justified. A column containing two different data elements as one value is a violation of first normal form. 1NF ...



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