Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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 [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
[EMAIL PROTECTED] (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 [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Alexandre [EMAIL PROTECTED] 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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 [EMAIL PROTECTED] 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
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Alexandre [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
