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:
Show quoted text
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?
Import Notes
Resolved by subject fallback
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:
Show quoted text
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
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/>
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.
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:
Show quoted text
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.")