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