Thread: Simplified (a-la [G|N]DBM) DB access
Good day, excuse me, if my question is lame, but is it possible to get some simplified access to the PostgreSQL? What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. For example, let me have some textual data in PostgreSQL, and let me wish to have an inverted index for some statistical analyses purpose (for example, search, but without using tsearch2). For now, to make any operations with all that data, I have to use an SQL, which makes such thing really slow (take a look at sql-mode mnogosearch and others). Yes, I can store all that data outside the db, but I will have to reinvent all the features, which realized great in PostgreSQL (for example, recovery, transactions) and I will get harder administrative support, backup and so on. Thank you in advance, Regards, /Alexandre.
Alexandre <Xlex0x835@rambler.ru> writes:
> What I mean is: currently to get/put/delete/edit any data I have to
> compose an SQL query, which should be parsed, compiled, optimized and
> so on. While, in some tasks simple interface a-la [G|N]DBM should be
> more than enough, but it will be more preferable to store all data in
> one database, which support concurrent access, transactions, etc.
I seem to recall that someone has written an ISAM-style interface
library, which might be more or less what you are asking for. Check
the archives, and/or look at gborg and pgfoundry.
regards, tom lane
Lane, thank you, but it is not: PostISAM "generates SQL statements on the fly from traditional ISAM (read, write, start) statements", so it just add overhead, and is not what I'm looking for. Anyway, thank you for the information. Regards, /Alexandre. On Apr 19, 2005, at 18:16, Tom Lane wrote: > Alexandre <Xlex0x835@rambler.ru> writes: >> What I mean is: currently to get/put/delete/edit any data I have to >> compose an SQL query, which should be parsed, compiled, optimized and >> so on. While, in some tasks simple interface a-la [G|N]DBM should be >> more than enough, but it will be more preferable to store all data in >> one database, which support concurrent access, transactions, etc. > > I seem to recall that someone has written an ISAM-style interface > library, which might be more or less what you are asking for. Check > the archives, and/or look at gborg and pgfoundry. > > regards, tom lane >
Alexandre <Xlex0x835@rambler.ru> writes:
> Lane, thank you, but it is not: PostISAM "generates SQL statements on
> the fly from traditional ISAM (read, write, start) statements", so it
> just add overhead, and is not what I'm looking for.
Well, if you don't want any SQL capability at all, I think you are
looking for something more like Berkeley DB ...
regards, tom lane
Have you actually seen any problem in simple get/put/delete/update situations? It's a shame when people spend a great deal of effort to invent a cure for a disease that does not exist. If you have problems with any of these things, there are usually good solutions. Using the copy API, you can insert like a raving madman. PostgreSQL has a prepared insert. Safe, transacted inserts will be hard to do much faster than that. You can also bracket bunches of operations in a single transaction if you like. In real life, how fast can you update records? If you have a titanic pile of users all trying to update, then the MVCC model is probably close to optimal anyway. Do you have some measurement that shows PostgreSQL is not performing up to a real business case time requirement? Sometimes, we can be guilty of 1980's batch oriented mind-set, if we have been doing data processing for a long time. The old paradigms no longer apply for the most part. Recommended reading: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2 005-39 -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexandre Sent: Tuesday, April 19, 2005 10:41 AM To: Tom Lane Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access Lane, thank you, but it is not: PostISAM "generates SQL statements on the fly from traditional ISAM (read, write, start) statements", so it just add overhead, and is not what I'm looking for. Anyway, thank you for the information. Regards, /Alexandre. On Apr 19, 2005, at 18:16, Tom Lane wrote: > Alexandre <Xlex0x835@rambler.ru> writes: >> What I mean is: currently to get/put/delete/edit any data I have to >> compose an SQL query, which should be parsed, compiled, optimized and >> so on. While, in some tasks simple interface a-la [G|N]DBM should be >> more than enough, but it will be more preferable to store all data in >> one database, which support concurrent access, transactions, etc. > > I seem to recall that someone has written an ISAM-style interface > library, which might be more or less what you are asking for. Check > the archives, and/or look at gborg and pgfoundry. > > regards, tom lane > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
At 9:40 PM +0400 4/19/05, Alexandre wrote:
>Lane, thank you, but it is not: PostISAM "generates SQL statements
>on the fly from traditional ISAM (read, write, start) statements",
>so it just add overhead, and is not what I'm looking for.
Speaking from experience, as I have a system which hides Postgres
behind an ISAM interface (though not PostISAM -- I rolled my own DB
library) as part of a legacy 4GL migration, the overhead's ignorable.
Dismissing it for that reason's not a good idea.
>On Apr 19, 2005, at 18:16, Tom Lane wrote:
>
>>Alexandre <Xlex0x835@rambler.ru> writes:
>>>What I mean is: currently to get/put/delete/edit any data I have to
>>>compose an SQL query, which should be parsed, compiled, optimized and
>>>so on. While, in some tasks simple interface a-la [G|N]DBM should be
>>>more than enough, but it will be more preferable to store all data in
>>>one database, which support concurrent access, transactions, etc.
>>
>>I seem to recall that someone has written an ISAM-style interface
>>library, which might be more or less what you are asking for. Check
>>the archives, and/or look at gborg and pgfoundry.
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk
Tom, yes, something like Berkeley DB, but inside PostgreSQL... It may sounds crazy, but some data just need not all SQL functionality and overhead, and at the same time I need not second db, so the best possible solution (in my opinion): two ways of the data access inside one db. Regards, /Alexandre. On Apr 19, 2005, at 22:37, Tom Lane wrote: > Alexandre <Xlex0x835@rambler.ru> writes: >> Lane, thank you, but it is not: PostISAM "generates SQL statements on >> the fly from traditional ISAM (read, write, start) statements", so it >> just add overhead, and is not what I'm looking for. > > Well, if you don't want any SQL capability at all, I think you are > looking for something more like Berkeley DB ... > > regards, tom lane >
On Apr 19, 2005, at 23:24, Dan Sugalski wrote: > At 9:40 PM +0400 4/19/05, Alexandre wrote: >> Lane, thank you, but it is not: PostISAM "generates SQL statements on >> the fly from traditional ISAM (read, write, start) statements", so it >> just add overhead, and is not what I'm looking for. > > Speaking from experience, as I have a system which hides Postgres > behind an ISAM interface (though not PostISAM -- I rolled my own DB > library) as part of a legacy 4GL migration, the overhead's ignorable. > Dismissing it for that reason's not a good idea. Does your own ISAM interface also convert ISAM calls to the SQL queries? If so, then it is also add overhead: make a call to the some wrapper, which will generate SQL query and send it to the sever, which will parse, plan and compile it and execute only after that. Desirable: make a call, which will connect to the server and get row. But if you write ISAM interface which didn't use SQL, can you tell more about, please? I didn't have any legacy application, which I need to move to the new DB, I just need more simple and fast access. Regards, /Alexandre.
On Apr 19, 2005, at 22:56, Dann Corbit wrote: > Have you actually seen any problem in simple get/put/delete/update > situations? Not sure, that I understand your question. > It's a shame when people spend a great deal of effort to invent a cure > for a disease that does not exist. > > If you have problems with any of these things, there are usually good > solutions. Not always. Please, keep in mind, that not all problems need so complicated querys. > Using the copy API, you can insert like a raving madman. PostgreSQL > has > a prepared insert. Yes, but it will be rest "prepared" till the end of the session. > Safe, transacted inserts will be hard to do much > faster than that. INSERT is not the main problem, SELECT - is. Why I need complicated SELECT * FROM words WHERE id = 21 to just make simple key/value search? > You can also bracket bunches of operations in a > single transaction if you like. > In real life, how fast can you update records? If you have a titanic > pile of users all trying to update, then the MVCC model is probably > close to optimal anyway. Again, INSERT and UPDATE is not a main problem. > Do you have some measurement that shows PostgreSQL is not performing up > to a real business case time requirement? And again, business can be different. > Sometimes, we can be guilty of 1980's batch oriented mind-set, if we > have been doing data processing for a long time. The old paradigms no > longer apply for the most part. > > Recommended reading: > http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR- > TR-2 > 005-39 Thank you for the reading, but it is not the case for me. First of all, I will repeat, in some situations, you need not so complicated solution, and nothing comes for free. In some situation you may need faster access with more complicated API, in some, of course, SQL is more suitable. As Tom said absolutely correct, in such situations Berkeley DB is something that more suitable, but it will add another one database, so ISAM-like (native, which does not translate it's calls to the SQL) access to the PostgreSQL will be just fine. Regards, /Alexandre.
At 10:05 AM +0400 4/20/05, Alexandre wrote:
>On Apr 19, 2005, at 23:24, Dan Sugalski wrote:
>
>>At 9:40 PM +0400 4/19/05, Alexandre wrote:
>>>Lane, thank you, but it is not: PostISAM "generates SQL statements
>>>on the fly from traditional ISAM (read, write, start) statements",
>>>so it just add overhead, and is not what I'm looking for.
>>
>>Speaking from experience, as I have a system which hides Postgres
>>behind an ISAM interface (though not PostISAM -- I rolled my own DB
>>library) as part of a legacy 4GL migration, the overhead's
>>ignorable. Dismissing it for that reason's not a good idea.
>
>Does your own ISAM interface also convert ISAM calls to the SQL queries?
Yes of course -- it has to, there's no other way to access Postgres.
>If so, then it is also add overhead: make a call to the some
>wrapper, which will generate SQL query and send it to the sever,
>which will parse, plan and compile it and execute only after that.
>Desirable: make a call, which will connect to the server and get row.
Right. And, as I said, the overhead is ignorable. It just doesn't matter.
>I didn't have any legacy application, which I need to move to the
>new DB, I just need more simple and fast access.
Then either use PostISAM or write your own wrapper code to present an
ISAM interface.
You really need to understand that there's *already* a lot of
overhead involved in Postgres, or any other relational database. ACID
guarantees aren't free. Compared to the costs involved in Postgres,
as well as in any app that's using the ISAM interface, the extra
costs in the wrapping are negligible.
Bluntly, you're worrying about the wrong stuff. Just write the
wrappers, prepare the SQL statements (hell, pregenerate them if you
want -- they don't have to be created on the fly), and ignore the
library until it ends up being worth worrying about. You'll be
ignoring it for a very long time.
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk
On Apr 20, 2005, at 17:09, Dan Sugalski wrote: >> Does your own ISAM interface also convert ISAM calls to the SQL >> queries? > > Yes of course -- it has to, there's no other way to access Postgres. Some server specific API? >> If so, then it is also add overhead: make a call to the some wrapper, >> which will generate SQL query and send it to the sever, which will >> parse, plan and compile it and execute only after that. Desirable: >> make a call, which will connect to the server and get row. > > Right. And, as I said, the overhead is ignorable. It just doesn't > matter. > >> I didn't have any legacy application, which I need to move to the new >> DB, I just need more simple and fast access. > > Then either use PostISAM or write your own wrapper code to present an > ISAM interface. > > You really need to understand that there's *already* a lot of overhead > involved in Postgres, or any other relational database. ACID > guarantees aren't free. Compared to the costs involved in Postgres, as > well as in any app that's using the ISAM interface, the extra costs in > the wrapping are negligible. > > Bluntly, you're worrying about the wrong stuff. Just write the > wrappers, prepare the SQL statements (hell, pregenerate them if you > want -- they don't have to be created on the fly), and ignore the > library until it ends up being worth worrying about. You'll be > ignoring it for a very long time. I see no reason to write another ISAM wrapper if it will not invent anything new. I understand, that there is already a lot of overhead, but Berkeley DB also "provides strict ACID transaction semantics". Would you like to say, that PostgreSQL will be faster on simple operations than Berkeley DB? Regards, /Alexandre.
Xlex0x835@rambler.ru (Alexandre) writes:
> On Apr 19, 2005, at 23:24, Dan Sugalski wrote:
>
>> At 9:40 PM +0400 4/19/05, Alexandre wrote:
>>> Lane, thank you, but it is not: PostISAM "generates SQL statements
>>> on the fly from traditional ISAM (read, write, start) statements",
>>> so it just add overhead, and is not what I'm looking for.
>>
>> Speaking from experience, as I have a system which hides Postgres
>> behind an ISAM interface (though not PostISAM -- I rolled my own DB
>> library) as part of a legacy 4GL migration, the overhead's
>> ignorable. Dismissing it for that reason's not a good idea.
>
> Does your own ISAM interface also convert ISAM calls to the SQL
> queries? If so, then it is also add overhead: make a call to the some
> wrapper, which will generate SQL query and send it to the sever, which
> will parse, plan and compile it and execute only after
> that. Desirable: make a call, which will connect to the server and get
> row.
I think you're missing two points:
1. With careful design, the ISAM "wrapper" can _avoid_ most of the
costs you suggest.
For instance, one might set up a prepared query which would only
parse, plan, and compile the query _once_.
Further, I'd expect that most of the behaviour could be
hidden in stored procedures which would further hide the need to
parse, plan, and compile things. The ISAM-congruent abstraction
would presumably make it easier to use, to boot.
2. Dan Sugalski indicated that he actually found the overhead to be
ignorable.
As a datapoint, that's pretty useful. He actually went thru
the effort of building the ISAM wrapper, and discovered that
the overhead wasn't material.
You ought to consider the possibility that perhaps he is right,
and that perhaps you are trying to optimize something that does
not need to be optimized.
Remember Michael Jackson's _First Rule of Software Optimization_,
which is expressed in one word:
Don't.
(And then there's his second rule, for experts: "Don't do it yet.")
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>
Chris (and others), thank you for the good explanation! =) I will try to use database as you recommend, thank you again for the advices! Regards, /Alexandre. On Apr 20, 2005, at 17:39, Chris Browne wrote: > I think you're missing two points: > > 1. With careful design, the ISAM "wrapper" can _avoid_ most of the > costs you suggest. > > For instance, one might set up a prepared query which would only > parse, plan, and compile the query _once_. > > Further, I'd expect that most of the behaviour could be > hidden in stored procedures which would further hide the need to > parse, plan, and compile things. The ISAM-congruent abstraction > would presumably make it easier to use, to boot. > > 2. Dan Sugalski indicated that he actually found the overhead to be > ignorable. > > As a datapoint, that's pretty useful. He actually went thru > the effort of building the ISAM wrapper, and discovered that > the overhead wasn't material. > > You ought to consider the possibility that perhaps he is right, > and that perhaps you are trying to optimize something that does > not need to be optimized. > > Remember Michael Jackson's _First Rule of Software Optimization_, > which is expressed in one word: > > Don't. > > (And then there's his second rule, for experts: "Don't do it yet.")