Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

On 9/23/20 11:51 AM, tutiluren@tutanota.com wrote:

>     It is not deprecated or legacy and it is still used by many. The
>     issue is that people try to do 'partial' dumps without reading the
>     docs on what those switches actually do and the potential conflicts
>     therein. For large installations there it is often better to use
>     some form of replication(binary and/or logical) to maintain a
>     continuous backup. pg_dump can take a long time on very big
>     databases, so it may not keep up.
> 
> Well, I've never done partial backups, frankly because I don't trust it 
> to actually work. Full backups may be more wasteful with disk space, but 
> at least "makes sense" in some abstract way. (Also, I try to keep as 
> little data as possible, so they are never *gigantic*.)

Well not partial as in incremental. Instead dump only some portion of 
the schema with or without its associated data.


>     This comes down to what your definition of embedded is? A matter of
>     determining whether we are talking apples or oranges.
> 
> Just what I said in my original question. Something which is bundled and 
> invisible to the user.

That is going to be difficult with Postgres as it is its own process. It 
would take a good bit of tooling to hide that from the user.


>     Huh? A schema is just a name space, why does it matter how the
>     extension chooses to define it? I mean you could have number of
>     permutations of postgis.
> 
> I'm saying that PostGIS has a bug due to incorrectly constructed 
> internal queries which makes it impossible to properly name the schema 
> where PostGIS is to reside, causing my database to look very ugly when 
> it has to say "postgis" instead of "PostGIS" for PostGIS's schema. And 
> that was an example of how sloppy/bad third-party things always are, and 
> is one reason why I don't like it when I have to rely on "extensions".

If that is the sum of your issues with PostGIS then I really don't have 
much sympathy. They are extensions so you aren't required to use them 
and rely on their way of doing things. You have the choice of writing 
your own code/extension or do without completely. I personally find 
compromise on my part wrt to extensions greatly reduces the amount of 
coding/effort I have to do to move forward. Honestly, I don't see how it 
is different from the compromises that arise out of using the core 
project itself.



>     That would entail building an AI into the code that would deal with
>     all the possible OS(versions), Postgres(versions), hardware
>     permutations.
> 
> I... guess. If "AI" means "a series of ifs". Which is what software... 
> is? I doubt that people who can make the world's most advanced open 
> source database cannot check the amount of RAM and see how fast the 
> CPU/disk is.

It is more then that. It would have to take into account the behavior 
changes that happen in Postgres between major versions. It also would 
have to account for OS specific parameters and the changes that happen 
there between OS versions. It also would need to 'know' how the database 
was going to be used; readonly, heavy writes, etc. Also how the database 
should play with other programs on the same machine. Add to the mix 
containers, cloud instances and so on and you are outrunning the ability 
of 'ifs' to handle it.

> 
>     Does your server runs to your satisfaction with the default settings?
> 
> Right now, yes, but that says very little as I'm the only user of it. 
> I've had many nightmares in the past, however, where even determining 
> whether the changes in the config did anything (good or bad) has been 
> impossible. I fundamentally don't like the idea that the config is so 
> "conservative" (crippled) with no obvious/easy way to "set a different 
> general mode". If you honestly think that the numerous 
> performance-related options are easy to understand, I don't know what to 
> say.

The thing is 'general mode' is going to mean something different to 
someone running a database in the MB-low GB range vs. high GB vs. TB vs. 
PB.

As to monitoring see:

https://www.postgresql.org/docs/12/monitoring-stats.html

https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

I know it is an extension, still it is useful:

https://www.postgresql.org/docs/12/pgstatstatements.html

-- 
Adrian Klaver
adrian.klaver@aklaver.com