News for hackers. Stuff that matters.

I have 6.000.000 RSS articles in a DB. What can I do?

I created a website a couple of years ago (thatstoday.com) where members can add RSS feeds and read them in our "news reader". The years went by and we've indexed all feed articles in our db (and in Lucene).

Now I'm not sure what to do with all the data since the db it's getting large and difficult to handle.

Should I delete it or create a new service? What do you guys suggest?

Thanks, Rob

( 37 comments | 30 points | on HN )
Re:I have 6.000.000 RSS articles in a DB. What can I do? by gbog on Thursday June 21, 2012 @06:39AM (#4141289)
> the db is getting large and difficult to handle.

Did your store the raw content in the database? If so, you might consider writing files instead. These blobs, like pictures, are better stored as files. In your database, you should keep relational data: url, time, adder, etc, properly indexed (probably by adder, time and maybe keywords). Then a 6M rows table is quite a small thing for any RDBMS (if your SELECTs are filtering on indexed columns).

Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:46AM (#4141306)
Good idea! When i'm importing the raw data I add it to lucene to make similar-articles-matching, but after that it could be dumped to text.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by bulte-rs on Thursday June 21, 2012 @06:25AM (#4141262)
You've got the basis for a "you-like-this-so-perhaps-you-will-want-to-read-this" recommendation engine. Perform some - mentioned in another reply - n-gram analysis on the corpus; do some basic cosine-similarity analysis with the feeds people subscribe to and see what pops up. Try other techniques (from e.g. ICWSM[1]) (last time I did something like this is April 2007); iterate; analyse results; publish.

At least you'll have fun (YMMV)...

[1] http://www.icwsm.org/[2007-2011]

Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:45AM (#4141303)
I already use "find similar articles" techniques using Lucene. But I'm thinking it might be more interesting in another more data-centered website?
Re:I have 6.000.000 RSS articles in a DB. What can I do? by dodo53 on Thursday June 21, 2012 @06:56AM (#4141333)
Does your reader record which items in a feed were read? I'm sure lots of feed providers would be interested in metrics on which of their articles were frequently read (and possibly some clustering of interests based on what else readers subscribe to)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @07:04AM (#4141350)
Yes it does! The problem is the low activity on the site. If the traffic grew the meta data would explode ;)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by xSwag on Thursday June 21, 2012 @05:43AM (#4141179)
You should dump a copy of the database somewhere so we can all take a look at it and perhaps analyse it.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:52AM (#4141323)
I'd love to do that, but how can I finance storage and bandwidth? Paypal donations?

Hmm.. I know a few peeps at MS/Azure.. maybe they can sponsor..I'll get back on this one.

Re:I have 6.000.000 RSS articles in a DB. What can I do? by kip_ on Thursday June 21, 2012 @07:50AM (#4141497)
Torrent that. Exactly the kind of thing that BitTorrent is for.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by vladiim on Thursday June 21, 2012 @07:08PM (#4144617)
+1
Re:I have 6.000.000 RSS articles in a DB. What can I do? by Terretta on Thursday June 21, 2012 @05:48AM (#4141192)
Do want. Please. :-)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by rcknight on Thursday June 21, 2012 @05:55AM (#4141204)
Would also love this!
Re:I have 6.000.000 RSS articles in a DB. What can I do? by bbayer on Thursday June 21, 2012 @06:52AM (#4141320)
that is what I thought
Re:I have 6.000.000 RSS articles in a DB. What can I do? by drewcrawford on Thursday June 21, 2012 @05:06AM (#4141103)
There's no e-mail address in your profile. Please contact me at the e-mail address in mine.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @10:09AM (#4142021)
I have an encrypted version there now ;)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by huragok on Thursday June 21, 2012 @05:01AM (#4141094)
Sell it or lease it. I'm sure there's some value in the aggregation of feeds (though I can't imagine what besides user habits).
Re:I have 6.000.000 RSS articles in a DB. What can I do? by haddr on Thursday June 21, 2012 @07:46AM (#4141482)
For God's sake don't delete it! First of all dump it to files instead of DB. (Or use some NoSQL Document Storage, such as MongoDB. The structure of RSS is actually non-relational, I suppose) Second of all: is your data clean? If not then you might need to clean it from any boilerplate (such as HTML code) Then you can process it with some tools. There are some good NLP tools available such as Gate. You may have a look at them. You can do great deal of things there: - detect some entities (companies? products?) and do some classification of documents - you can detect some events (iPhone announcements, etc) - if you have time & date (hope you have) you can do some trending topics analysis (what was hot in June 2010) - probably you can't sell the data as the content of articles is not yours, but you may sell some derived data (analysis, etc)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by raverbashing on Thursday June 21, 2012 @05:34AM (#4141161)
Natural Language processing n-gram studies Or just some kind of webservice where people can look this up
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:51AM (#4141316)
Thought of that, but I need to add it to a 50GB+ cloud SQL storage which cost a few bucks... :)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by spobo on Thursday June 21, 2012 @06:00AM (#4141213)
Write an algorithm to match articles from different sources that are about the same story. That way you can autohide news that you've supposedly already read from another source. Clustering news from different sources would be a killer feature for me :)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by waxjar on Thursday June 21, 2012 @07:54PM (#4144735)
Depending on how far back the data goes, you could try to spot language trends trough time or make a pretty graph of the average article length trough time. I'm not that inspiring.

I hope this'll get a follow up, I'm sure someone can think of something awesome to do with it.

If you decide to open source it (copyrights?), use Bittorrent: it's the perfect tool for this job.

Re:I have 6.000.000 RSS articles in a DB. What can I do? by kngspook on Thursday June 21, 2012 @05:10AM (#4141110)
How big is the DB dump?

I know I would be interested in downloading it, and just poking it for interesting stats...

Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:53AM (#4141327)
50-60 GB
Re:I have 6.000.000 RSS articles in a DB. What can I do? by ramigb on Thursday June 21, 2012 @06:10AM (#4141233)
I suggest you change your privacy policy (i am sure you have one) and tell the users that you will open the database for researchers, and make the open database a donation based service, this way you can pay to host it and might get some good extra money.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:53AM (#4141325)
Sounds tempting!
Re:I have 6.000.000 RSS articles in a DB. What can I do? by bromagosa on Thursday June 21, 2012 @05:10AM (#4141112)
Are they all open? If so, I'd contact wikimedia and see what use can they give them.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by rocky1138 on Thursday June 21, 2012 @08:46AM (#4141694)
Do you have the rights to distribute the articles in your database?
Re:I have 6.000.000 RSS articles in a DB. What can I do? by evanwolf on Tuesday July 03, 2012 @09:06PM (#4197090)
Donate it to http://Archive.org. They can reconstruct disappeared web sites from it, preserving the web's past.
Re:I have 6.000.000 RSS articles in a DB. What can I do? by tzaman on Thursday June 21, 2012 @05:00AM (#4141092)
If you think it could be useful to someone, try to sell it
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @06:54AM (#4141329)
How much do you offer? ;)
Re:I have 6.000.000 RSS articles in a DB. What can I do? by tzaman on Thursday June 21, 2012 @08:28AM (#4141629)
I'm not that someone :D
Re:I have 6.000.000 RSS articles in a DB. What can I do? by ahmedaly on Thursday June 21, 2012 @09:12AM (#4141783)
Maybe I can offer you hosting for free if this is your problem.. pls email me on ahmed(at)svwebdev.com
Re:I have 6.000.000 RSS articles in a DB. What can I do? by gauravvijay on Thursday June 21, 2012 @07:09AM (#4141363)
I can sponsor the S3 storage but with limited IO
Re:I have 6.000.000 RSS articles in a DB. What can I do? by robertpohl on Thursday June 21, 2012 @07:55AM (#4141521)
I guess it would need more that just storage, but also an API so you can query it. Maybe a JQuery/JSONP layer ontop of a Lucene index?
Re:I have 6.000.000 RSS articles in a DB. What can I do? by aw4y on Thursday June 21, 2012 @05:14AM (#4141121)
make it open!
Re:I have 6.000.000 RSS articles in a DB. What can I do? by lucamartinetti on Thursday June 21, 2012 @06:25AM (#4141261)
a bunch of interesting things. It is a nice NLP corpus. Put a dump on S3 and make it public
Re:I have 6.000.000 RSS articles in a DB. What can I do? by openmosix on Thursday June 21, 2012 @07:22AM (#4141406)
love it...