Python: Array vs Database for storage of key/value

Question

Q: Which is quicker for this scenario?

My scenario: my application will be storing either in either an array or postgresql db a list of links, so it might look like:

1) mysite.com
   a)    /users/login
   b)    /users/registration/
   c)    /contact/
   d)    /locate/search
   e)    /priv/admin-login

The above entries under 1) - I will be doing string searches on these urls to find for example any path that contains:

'login'

for example.

The above letters a) through e) could maybe have anywhere from 5-100 more entries for a given domain.

*The usage: * This data structure can change potentially as much as everyday, but only once per day. Some key/values will be removed, others will be modified. An individual set like:

dict2 = { 'thesite.com': 123, 98.6: 37 };

Each key will represent 1 and only 1 domain.

I've tried searching a bit on this, but cannot seem to find a real good answer to : when should an array be used and when should a db like postgresql be used?

I've always used a db to handle data (using mysql, not postgresql), but I'm now trying to do it better from now on, so I wondered if an array or other data structure would work better within a loop, and while trying tomatch a given string while looping.

As always, thank you!

Answer 1

A full SQL database would probably be overkill. If you can fit everything in memory, put it all in a dict and then use the pickle module to serialize it and write it to the disk.

Another good option would be to use one of the dbm modules (dbm/dbm.ndbm, gdbm or anydbm) to store the data in a disk-bound hash table. It will have O(1) lookup times without the need to connect and form a query like in a bigger database.

edit: If you have multiple values per key and you don't want a full-blown database, SQLite would be a good choice. There is already a built-in module for it, sqlite3 (as mentioned in the comments)

Answer 2

1. Test it. It's your dataset, your hardware, your available disk and network IO, your usage pattern. There's no one true answer here. We don't even know how many queries are you planning - are we talking about one per minute or thousands per second?
2. If your data fits nicely in memory and doesn't take a massive amount of time to load the first time, sticking it into a dictionary in memory will probably be faster.
3. If you're always looking for full words (like in the login case), you will gain some speed too from splitting the url into parts and indexing those separately.