Обсуждение: Is there a way to bypass sql?
I have been digging into NoSQL of late. For navigational queries it would be great if there was a way to bypass SQL and directly pull from an identifier for a record or arbitrary byte stream. Does postgresql directly support such ability? What is the closest that you could come?
- samantha
- samantha
hello 2010/8/9 samantha <sjatkins@mac.com>: > I have been digging into NoSQL of late. For navigational queries it would > be great if there was a way to bypass SQL and directly pull from an > identifier for a record or arbitrary byte stream. Does postgresql directly > support such ability? What is the closest that you could come? no, there are nothing similar - you cannot to bypass SQL. Regards Pavel Stehule > > - samantha >
On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote: > I have been digging into NoSQL of late. For navigational queries it would > be great if there was a way to bypass SQL and directly pull from an > identifier for a record or arbitrary byte stream. Does postgresql directly > support such ability? What is the closest that you could come? You can get pretty close, depending on how you define 'bypass'. For example, it is possible to send rich data structures back and forth between the client and the server without constructing a SQL text string. Those structures still have to be strongly typed in the server unless you want to stuff everything into a bytea (which btw I think is a terrible idea for most cases). Could you describe in more detail what you'd like to do and what (if any) inefficiencies or restrictions SQL is imposing that you would like to bypass? merlin
On Mon, Aug 9, 2010 at 1:39 PM, samantha <sjatkins@mac.com> wrote: > I have been digging into NoSQL of late (...) Be wary of DBAs Running with Scissors... http://www.pgcon.org/2010/schedule/attachments/141_PostgreSQL-and-NoSQL.pdf
On Aug 9, 2010, at 11:57 AM, Merlin Moncure wrote: > On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote: >> I have been digging into NoSQL of late. For navigational queries it would >> be great if there was a way to bypass SQL and directly pull from an >> identifier for a record or arbitrary byte stream. Does postgresql directly >> support such ability? What is the closest that you could come? > > You can get pretty close, depending on how you define 'bypass'. For > example, it is possible to send rich data structures back and forth > between the client and the server without constructing a SQL text > string. Those structures still have to be strongly typed in the > server unless you want to stuff everything into a bytea (which btw I > think is a terrible idea for most cases). Could you describe in more > detail what you'd like to do and what (if any) inefficiencies or > restrictions SQL is imposing that you would like to bypass? In many OO projects the majority of the work on persistent objects is navigational and inserts with relatively few updates. Queries are usually mainly for initial working set in many such systems and little else. When retrieving an objectgiven a persistent oid it would be better if I didn't need to go through even a prepared statement and especially itwould be better if I did not need to translate column values or do subqueries to either construct my OO language objector construct my OO cache entry. One thought is that I could in many cases store the cache entry format directly ina KV store and save a bit. There is also the interesting case of dynamic OO languages where technically the object fields do not have a defined typeto start with. - samantha
On 08/10/10 10:31 PM, Samantha Atkins wrote: > In many OO projects the majority of the work on persistent objects is > navigational and inserts with relatively few updates. Queries are > usually mainly for initial working set in many such systems and little > else. When retrieving an object given a persistent oid it would be > better if I didn't need to go through even a prepared statement and > especially it would be better if I did not need to translate column > values or do subqueries to either construct my OO language object or > construct my OO cache entry. One thought is that I could in many cases > store the cache entry format directly in a KV store and save a bit. or just dump your data in a flatfile, in ASN.1 or XML or something. sheesh.
On Wed, Aug 11, 2010 at 1:31 AM, Samantha Atkins <sjatkins@mac.com> wrote: > > On Aug 9, 2010, at 11:57 AM, Merlin Moncure wrote: > >> On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote: >>> I have been digging into NoSQL of late. For navigational queries it would >>> be great if there was a way to bypass SQL and directly pull from an >>> identifier for a record or arbitrary byte stream. Does postgresql directly >>> support such ability? What is the closest that you could come? >> >> You can get pretty close, depending on how you define 'bypass'. For >> example, it is possible to send rich data structures back and forth >> between the client and the server without constructing a SQL text >> string. Those structures still have to be strongly typed in the >> server unless you want to stuff everything into a bytea (which btw I >> think is a terrible idea for most cases). Could you describe in more >> detail what you'd like to do and what (if any) inefficiencies or >> restrictions SQL is imposing that you would like to bypass? > > In many OO projects the majority of the work on persistent objects is navigational and inserts with relatively few updates. Queries are usually mainly for initial working set in many such systems and little else. When retrieving an objectgiven a persistent oid it would be better if I didn't need to go through even a prepared statement and especially itwould be better if I did not need to translate column values or do subqueries to either construct my OO language objector construct my OO cache entry. One thought is that I could in many cases store the cache entry format directly ina KV store and save a bit. you can certainly do that...you have a few options: *) EAV style table design *) XML or JSON column in the database (xml is currently better supported) *) hstore column type PostgreSQL also supports arrays of composite types that you can use to define nested complex structures and stuff them into a column. This is a hybrid approach between the classic way and the EAV style; it has a couple of annoying downsides (updating single values is a PITA) and is not commonly used. In my experience these type of designs are typically pursued when the actual data modeling and constraint checking is done in code. Using them will highly limit the ability of the database to support operations that are not exposed through your OO layer whereas a rich, strongly typed schema is more flexible, and typically easier to maintain and administrate at the cost of having to marshal your data coming in and out of hte database. Most people on this list will discourage EAV for general use (specific cases might be ok though). merlin
Samantha Atkins <sjatkins@mac.com> writes:
> In many OO projects the majority of the work on persistent objects is
> navigational and inserts with relatively few updates. Queries are
> usually mainly for initial working set in many such systems and little
> else. When retrieving an object given a persistent oid it would be
> better if I didn't need to go through even a prepared statement and
> especially it would be better if I did not need to translate column
> values or do subqueries to either construct my OO language object or
> construct my OO cache entry.
I think you are drastically overestimating the overhead of a prepared
query, and drastically underestimating the work involved in implementing
or maintaining a "bypass" solution. This really isn't going to be worth
your time.
If you think you don't need SQL at all anywhere, then go with something
like BDB.
regards, tom lane
On Wednesday 11 August 2010 07.31:24 Samantha Atkins wrote: > There is also the interesting case of dynamic OO languages where > technically the object fields do not have a defined type to start > with. I'm not sure what you want to say here. If you apply this to databases, my answer is: if you want to use persistent objects in such languages, don't use a relational database, since it's just the wrong tool. Storing stuff like XML snippets or Python pickled objects or whatever in tables in a postgres database is just like using a 40t lorry to commute to work: it's possible, but it's kind of silly. cheers -- vbi -- Protect your privacy - encrypt your email: http://fortytwo.ch/gpg/intro