On 2014/04/16 03:39, Nick Eubank wrote:
I started in Postgres, but discovered that in Windows one cannot increase some of the per-query RAM memory caps above 2gb (I know -- I would love to switch operating systems, but it's beyond my control). So I'm thinking of moving to SQLite.
Firstly, Windows isn't limiting the memory you can use, you can use all 16gb installed memory, and in fact much more if needed (which will just cause a lot of drive-swapping and slowness, but no less...), the 2gb limit is in fact a 32bit limit and is self imposed by any 32 bit application (which I'm guessing postgress on your system is) or API or device context, etc, and even if it is compiled in 64bit guise it can still be self limiting by using 32bit UInts or whatever for the mapping. Also, the 32bit limit is in fact near 4gb, but the data transfer typically happen in unicode strings which in most cases are mapped to a 2-bytes-per-character memory which then translates to ~2gb. Another way some systems limit to 2gb is because they use Integer mappers (as opposed to UInt mappers) which has a ~2gb upper limit.
Anyway, none of that matters, it is only information - what does matter is that sqlite is not bogged down by this in 64bit guise and you can even compile it into your own program (just to be sure), or use it as stand-alone or one of the pre-compiled binaries.
That said, it is hard to imagine the actual query using >2gb memory to simply execute, but I guess it is possible. One can usually rethink queries that are too big or too slow with some other coded algorithms or options, which makes sqlite quite handy as it is real quick at supplying a query line by line using the API.
Also there is a lot of tweaking that can be done with page-sizes and the like to enable storage and querying of insanely large DBs (of which your's certainly qualify!) - Documentation abound re this on the SQLite site.
-- Is there any reason I should NOT use SQLite for manipulation of large datasets like this (for example, pulling out unique pairs of transaction participants, averages across users, etc.)?
I don't think SQLite supports quite the range of functions you may find in, say, PostGreSQL or MSSQL etc, but a lot of add-ons exist that can enable, for instance, Math functions, Statistical functions and RTree Relational structures etc. etc. - Might be worth seeing if the sort of thing you are exactly after is available (feel free to ask here).
The great thing with SQLite is that you can test all this in every detail free of charge and as much as you like to ensure you are on the right track, and you can ask here if anything is unclear.
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
