Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

is Sqlite db good enough to handle large data.

by Anonymous Monk
on Jul 21, 2019 at 16:49 UTC ( #11103105=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am writing Desktop application using Perl Tk. so my question is, will SQLite database be good enough to handle enough large data.
I am thinking to use SQLite as it is highly portable. please suggest.

Thank you

  • Comment on is Sqlite db good enough to handle large data.

Replies are listed 'Best First'.
Re: is Sqlite db good enough to handle large data.
by marto (Cardinal) on Jul 21, 2019 at 16:56 UTC

      Basically, I am writing desktop application for retailers which will manage buy and sales Information for them. so wondering if sqlite db is ideal for the same.

        SQLite is not ideal if you need multiple consumers of your application. What I mean is, if you have several retail locations trying to share the database, it's more difficult to do right. A serverless database is really just intended for in-process, on-system use, not for multi-process, many-system use. SQLite also is difficult to use in situations where you need master/replica configurations for redundancy or shared load, live backups, row locking, and so on.

        When you talk about whether SQLite is appropriate for big data you are only considering one of the factors that would go in to deciding if it is an appropriate choice. And you've got a vision of how it will fit into your big data plan that differs from what we may be thinking. It can certainly handle large amounts of data. And it may have a place in certain parts of big data system.

        But with the very limited information you're providing, I would suggest that no, it's not ideal. Why? Because one retail location could become two, or five, or twenty five, or five hundred. Why should each location have a local database? It could make sense for each location to cache information, or to keep track of information ephemerally, and in such a case SQLite might be fine. There are thousands of uses of SQLite. But the moment you start thinking in terms of a central repository for multiple applications or multiple instances or installations of an application to access, SQLite runs out of gas.

        On the other hand, perhaps you're standing up a service that multiple locations can access, and that service you're contemplating backing with SQLite for your data. Again, maybe not ideal; how will you handle running multiple instances of your application as traffic from multiple locations grows beyond what a single application worker can handle? How will you deal with live backups in a 24/7 world? How will you handle hot promotion of replicas to master should master ever go down? You wouldn't want mission critical data deployed alongside a single application instance on a single piece of hardware; 1990 is 29 years behind us, and we've learned a lot about scalability and redundancy since then.

        So while SQLite can handle tons of data, it may not provide flexibility and scalability in areas that have nothing to do with the size of the data set.

        I love SQLite, and recently used it in a system that comprises thousands of servers. But in this case the servers each do some job and store in local SQLite databases batches of information that later get posted up to a service that receives from each of those thousands of servers. The SQLite databases are ok, in this case, being single-server instances; there's no need for them to become a shared resource, and there's no competition from other processes to simultaneously write into SQLite. So it was a good choice; minimal, nothing to maintain, almost nothing to install.

        It sounds like the application you are contemplating has no current expectation of growing beyond a single instance. But in a retail world I can't imagine that would be a good plan for any business that may someday grow. Admittedly I know nothing about the application you're considering. But I do have some retail experience: the first half of my career was as a retail buyer for a chain of stores that was at one time a dozen locations, and later 80, and later hundreds. Solutions that would have worked for one store would never have worked for a dozen. And solutions that worked for a dozen would never have been able to scale to 80, let alone hundreds. Small business, mom and pop stores sometimes do grow beyond their single location, and if an application cannot support that, it is targeting the specific narrow niche of businesses that are content not to grow.


        Dave

Re: is Sqlite db good enough to handle large data.
by haukex (Bishop) on Jul 21, 2019 at 16:57 UTC
    large data

    What do you mean by this? Many rows, many columns, or both, what kinds of columns, e.g. large BLOB columns, and so on? When in doubt, benchmark: use a Perl script to generate data that is representative of the data you'll be working with, and test the speeds. See also https://www.sqlite.org/whentouse.html.

Re: is Sqlite db good enough to handle large data.
by erix (Prior) on Jul 22, 2019 at 20:33 UTC

    SQLite is wonderful, even for not-so-small data, as long as it is readonly / write-rarely.

    The SQLite people say (from whentouse.html):

    " SQLite is not directly comparable to client/server SQL database engines such as MySQL or PostgreSQL. SQLite does not compete with client/server databases. SQLite competes with fopen(). "

    If your "desktop application for retailers" just reads/writes to the local computer, one user at a time, SQLite might be all right.

    If it reads/writes, by possibly many users concurrently, to some central database SQLite is probably not so good. Then I'd use PostgreSQL.

    2019.07.23 - (edited slightly)

      Thank you for this information. I am going with SQLite as it fits the bill for me.

Re: is Sqlite db good enough to handle large data.
by holli (Abbot) on Jul 23, 2019 at 11:22 UTC
    When you're saying "Desktop application", are you saying "Windows" too? Because if that is the case, my advice - albeit unpopular here - would be to avoid Perl and especially Tk (*shudder*) alltogether and go for the .NET route (C# and WPF). The relevant toolchain and IDE is free and you can use .MDB as your data format. You don't even need a MS-Access license for that, even if it helps with debugging.


    holli

    You can lead your users to water, but alas, you cannot drown them.

      Thank you for the suggestion. but I don't know anything about dot net but with perl Tk it turns out to be well IMO.

Re: is Sqlite db good enough to handle large data.
by Anonymous Monk on Jul 22, 2019 at 06:18 UTC

    Thank you all for your inputs and suggestions. I have gone through the links provided, I would like to go with sqlite because mainly my data would reside on local machine and not on any server. Also this would be for one client, one application.

    Let me see how it goes.
    Thank you,

    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: is Sqlite db good enough to handle large data.
by Anonymous Monk on Jul 23, 2019 at 10:04 UTC

    While this application is still in progress. But if someone is interested in this by any chance. Please let me know on my email "pawark86@gmail.com".
    Please keep "MUNIMJEE Enquiry" in subject line.
    Though I am not sure if this is the right platform, sorry in advance for the same.
    Thank you

Re: is Sqlite db good enough to handle large data.
by sundialsvc4 (Abbot) on Jul 23, 2019 at 01:30 UTC

    The question as posted is whether or not SQLite is good enough to handle large amounts of data.   The superficial answer is of course Yes.   But, quite some time ago now, we moved away from "desktop database" topologies that were based on shared files an offshoot from files that existed only on a single non-shared computer and adopted the client/server model instead.   Since every computer you'll actually encounter fully supports multiprogramming ... as earlier systems did not ... it is now a trivial matter to support a client/server capable database on your own computer.   The advantage of this being that the topology requires no changes to become multi-user:   "it already is."

    SQLite just might be the penultimate advancement (IMHO) of "a filesystem-based database."   It really is a remarkable, best-of-breed tool for its appointed, narrow, niche.   But its technically-necessary design introduces issues that you might come to regret if your use-case grows in such a way that this requirement might very reasonably be expected to do.   Hence my recommendation to instead use a local, but multi-user capable, daemon.   Best to get off on the right foot, no matter what programming language/tool you are using.

      Fair points. The defining advantage of SQLite, for me, is that it does not require all the CREATE USER, GRANT PRIVILEGES and then debugging the localhost VS 127.0.0.1 and tens of other small problems (talking about MySQL here which is my main experience) arising when users try to install your application to different OS and with different privileges. SQLite can be run with default user and no networking which requires zero admin rights. I think?

      Thank you for this.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11103105]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2021-10-28 12:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My first memorable Perl project was:







    Results (96 votes). Check out past polls.

    Notices?