Database for storing large sets of data from web scraping

Question

I am designing a scraping system that will scrape large set of data from websites. The system is expected to collect data from websites and store it to database.

I am storing structured data parsed from HTML tags.

For example: If it's scraping a hotel listing, the data would contain the hotel id, hotel name, location, price, reviews, ratings, URL, etc.

This database is expected to have over 20-50 million records. The data set can contain information in different languages. The data will be populated periodically (more or less a million every week).

The database needs to be optimized for filtering and search. Also it should be capable of managing the other language characters (for example Chinese). I need fast execution and retrieval of data along with character support.

I was considering using MySQL, but I don't know if there are any better options. If MySQL is the best option, which storage engine, collation, and charset should be used?

Edited

I have a DB design plan to store data for each week store in separate tables, which could be later combined (if needed) as per the requirements.

Does Chinese characters cause any troubles while JOINing the queries?

Does storing the data in multiple tables create any performance issue while retrieval. (I planned of multiple tables since an indexed big table could make data insertion slower)

Answer 1

Postgres JSONB

If you are scraping different kinds of data from different sources to be stored all together but varied enough or changing often enough that you don't want to define specific tables for each source, and the structured data you derive is simple enough to be represented as JSON, then I suggest considering Postgres.

The latest versions of Postgres have a new built-in data type called JSONB, where the B means binary. When you submit a JSON value, Postgres parses it into the various fields and stores those parts intelligently in an internally-defined binary format. This process gives Postgres the ability to index the JSON fields. That means wicked fast performance that can out-perform the various “NO-SQL” systems. And unlike those other systems, you get ACID-compliant reliability and data-integrity with Postgres.

This JSONB type supplants Postgres original JSON support as well as its older HSTORE type.

For more info, see:

• Explanation of JSONB introduced by PostgreSQL
• What's new in Postgres 9.4
• What's new in Postgres 9.5
• NoSQL with PostgreSQL 9.4 and JSONB
• JSONB type performance in PostgreSQL 9.4
• PostgreSQL 9.4's secret weapon? YES, IT'S A NEW FILE FORMAT!

Chinese text

Postgres can handle Chinese text, with full support of Unicode. Be sure when creating the database/catalog to specify UTF-8 encoding.

Of course, your database driver and client app must also support UTF-8.

Data volume

Postgres can easily handle tens of millions of rows, and inserts or updates of a million a week.

If loading many rows at once, look at alternatives to calling INSERT one row at a time, such as the COPY command.

Postgres by default is configured conservatively to not negatively impact the host machine. If you know you have additional memory and CPU available, explore the various Postgres settings.

Answer 2

Any modern database system with unicode support will be able to handle this easily, performance gains will be made in the optimization of indexes and a proper database schema. Breaking things into multiple tables/databases using natural clusters will also help with performance.

Its not just about response time requesting data but also updating. For a mass load of multiple items, I generaly found it much better to disable indexing until finished loading, then re-index..and even better to go to smaller sub-tables with logical clusters.

Try your hardest to avoid using strings/varchar/guid values as a key in an index. Use an int or int64. I know that uuid's are "statistically unique" and seem like a great thing to use for a unique id...but collisions happen, and you have to be prepared for them and the cost of checking for one. If you need to index on something common, better to put the common thing in a separate table and link to the record using an integer foreign key, and index the foreign key field.

Answer 3

Why not a NOSQL database?

I think that there may be better response times on a document-based no-sql database like MongoDB. It allows to free-form (imagine that there are hotels that haven't all the fields you look for, for example) the documents and replication is really easy, which adds an integrity layer, being possible to have replica sets in different servers that are transparent for the programmer.

Said so, you have to be really careful with the data structure, because there are no structure checks, nor PKs or FKs.