1

I'm building a SAAS webcrawler. I would like to store information which URL was crawled by the user. I'm expecting this table to grow initially by 5-10 MB/month, later maybe up to 40 MB/month.

The DB will contain one single table:

user_id (INT)
url (VARCHAR)
timestamp (TIMESTAMP)
type_1 (BOOLEAN)
type_2 (BOOLEAN)
type_3 (BOOLEAN)
type_4 (BOOLEAN) 

The data is essentially for tracking which user has crawled which url. This data will be used for payment (STRIPE/PayPal) and also for analytics.

Which database is suitable for that?

  • 1
    Afraid the answer is: pretty much any database will do. If you want to keep/preserve data for 1y+, SQLite is probably out of discussion for performance reasons – but all "real DB engines" still can cope, be it MySQL, PostgreSQL – or bigger ones like Oracle. So for recommendations, additional restrictions/requirements are needed – e.g. budget (MySQL/PostgreSQL come for free, Oracle costs a lot), should the database be shared with other projects, etc. – Izzy Oct 27 '15 at 12:47
  • You are probably right - i just thought with mysql 12.5 mio rows a month will get unperformant pretty early – Fabian Lurz Oct 27 '15 at 13:10
  • Ever heard of indexes and partitions? :) Requiring/wanting partitioning for performance reasons could be one of the things to add to your question's text (which again would outrule SQLite, but almost none of the other databases). – Izzy Oct 27 '15 at 13:14
  • Alright. I have heard of it and i had myself a table with 100 Mio. rows. But i never partitioned one :) Will have a look into it. Ty!! – Fabian Lurz Oct 27 '15 at 14:57
  • Any time! For technical questions (e.g. on partitioning), you might wish to check with our dba sister site. In short: when partitioned, the DB only needs to scan those parts matching the "partition criteria"; in your case, if e.g. partitioned "by quarter of the year", you could query data from the current quarter without the DB having to scan "anything else" – which of course improves performance. You could as well easily "get rid" of old data by simply dropping the associated partition(s) (plopp! gone), instead of running a "huge DELETE for half an hour" :) – Izzy Oct 27 '15 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.