Re: (another ;-)) PostgreSQL-derived project ...

On Sat, Sep 24, 2011 at 11:11 PM, Albretch Mueller <lbrtchx@gmail.com> wrote:

>> I cannot imagine you would benefit that much by removing these capabilities compared to simply ignoring them.
>> Plus, by having it in the DB I avoid considerable considerable overhead
> ~
>  Can you or do you know of anyone who has made those kinds of
> imaginations falsifiable?
> ~

My own experience here is that while it is generally possible to
create additional overhead by mis-use of advanced features, *in
general* you save more overhead and get clearer code by pushing what
you can into the database within reason.

>> ... and can now use those features within my SQL statements/queries.
> ~
>  For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2)
> word in modern hardware) more efficient than comparing sequences of
> string characters?
> ~
>> simply guessing that in simply being feature rich PostgreSQL has sub-optimal performance
> ~
>  I never said that
> ~
>> ... you might want to look at SQLite.  It provides a number of compile-time options where you can exclude various
featuresyou don't want from the binary 
> ~
>  I couldn't find the compile options you mentioned: sqlite.org/
> {faq.html, custombuild.html, docs.html}
> ~
>> ... you're looking for a non-sql compliant SQL database where a lot of the data integrity is actually coded in the
application:-) 
> ~
>  First past of your statement I acknowledged, but how is it exactly
> that "lot of the data integrity is actually coded in the application"

I can give you a good example.  Some years ago, I was working on an
accounting application someone else wrote which stored all monetary
values as double-precision floats and then handled arbitrary precision
math in the front-end of the application.  This meant:

1)  To detect if an invoice was closed, it would retrieve all gl lines
associated with the invoice and an AR/AP account and see if these
totalled to 0 in the middleware.  This performed ok for a small
database, but for a large one, it didn't work so well.......  Had the
application used NUMERIC types, this could have been more easily done
with HAVING clause, and this could have been done far more efficiently
on the db server.

2)  It made the application relatively sensitive to rounding errors---
sum() with group by would return different numbers with different
groupings in sufficiently large databases.

So here you get a case where the application was made less robust and
performed quite a bit worse by not using arbitrary math capabilities
of PostgreSQL.

> ~
>> That approach strips down on application complexity. My apps don't have to do any post-processing
> of the data - I query the records I need and the app merely displays them.
> ~
>  Again have you actually tested those assumptions?

In general my experience is that it is far easier to tune performance
of an app as is described here (where all presentation is done in db)
than it is an app where a lot of it is done in middle-ware or
front-end.

For example, consider the following:  I need to determine all of the
years that have dates in a database table with, say, 50M records.  If
I have a database query which does this all at once, when it performs
badly, I can tune it, and there are fewer tradeoffs I have to make.
> ~
>> My point being: postgresql does what it does very reliably
> ~
>  I never said otherwise

I'd add it performs remarkably well IMHO as well as reliably.

 Best Wishes,
Chris Travers