Forum OpenACS Q&A: How large a Data Set can OpenACS with Postgres deal with?
# Select * from my_table_name where job_id = '16892234' ;I donÂt think I would ever want to pull out more than 500-1000 rows at a time. They tell me that the data will grow at a rate of about 100,000 entries per month. I was also wondering if anybody uses OpenACS as an accounting program, or if one can buy a pretty front end software program that will query the Postgres Database and pull out data as needed, and then allow the financial people to analyze the data? They would need similar functionality to Quickbooks - just as almost all businesses need. If not, is that because this would be something that is prohibitively difficult to build?
Thanks for the help.
I'm sure others can give you better stats on that.
SQLLedger is a Postgres based accounting system. It is a Perl system that I
have successfully installed with AOLServer. However we haven't started
using it so I can't tell you how good it is.
I am working with a system with 8 million rows in the acs_objects table. It is Oracle, so that might not say much about PG. For running reports, you should check out my cronjob package. This is relatively nice for manager types. It sends an html formatted email of the sql query, or for more heavy duty action, you can run any script or source a tcl file as well. It is in the OACS4.5 release. One nice thing is that it can run this whenever you want, or you can schedule it like cron, for daily, weekly, day of the week, or monthly reports, down to the minute.
And after it falls apart you'll remember to create the proper indexes next time! :)
Rows out ... PG builds the entire resultset at once, rather than step through the query and build a row at a time once the plan's set up. Then AOLserver pulls these out a row at a time and brings them back to the client program.
However ... the db_multirow builds the entire result set in your thread's space, so it can be made available to the template engine.
But 50-500 rows at a time is no big deal. The db_multirow code assumes that you're building pages of reasonable length and aren't munging out a bazillion rows at a time. Not only would that take a lot of memory but shoving it back up the socket to the poor user and her modem would take a long time, right, so it is reasonable to assume that you'll be pulling a reasonable amount of stuff out at a time.
As it happens we are buolding a few apps for internal functions. We've started on a sales/marketing app and my colleague Helen is looking into creating a payroll/vat/expenses app...
We've just started this, but I'm sure Helen would be interested in what your doing.
You may want to mail her at mailto:email@example.com as I don;t think she's currently subscribed to this forum.
I tried unsuccessfuly to lookup a ballpark cost to post here (from http://www.brio.com/). I have used BrioQuery for years, but Brio Inc seems to have buried the BrioQuery tool into their enterprise offering these days and a search for the term BrioQuery on the Brio website got me no results. Interestingly, a Google search brings up scores of universities that post their own local guides to using BrioQuery, so it seems to be alive and well out there in the wild.
But Brio is a rabbit across the path here, my real message is that ODBC access to Postgres was easy to setup and worked well for me (one user, small scale) which opens the door for numerous end user access options.