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

tutiluren@tutanota.com writes:
> 1. All non-ANSI characters are turned into "?"s for application_name.

Yeah, that's hard to do much with unfortunately.  We cannot assume that
all databases in an installation share the same encoding, so for globally
visible strings like application_name, the only safe solution is to
restrict them to ASCII.

On the other hand, the very same thing could be said of database names
and role names, yet we have never worried much about whether those were
encoding-safe when viewed from databases with different encodings, nor
have there been many complaints about the theoretical unsafety.  So maybe
this is just overly anal-retentive and we should drop the restriction,
or at least pass through data that doesn't appear to be invalidly
encoded.

> 2. pg_dump misinterprets non-ANSI values for the "--exclude-*" options
> (at least the --exclude-table-data one, which is the one I've tested) on
> Windows, resulting in it being impossible to make more "sophisticated"
> backups of PostgreSQL databases; it's either all or nothing.

TBH, I'm going to throw that back on you as probably pilot error.  There's
no reason for such cases not to work if you're running the terminal window
in the same encoding that pg_dump thinks it's using.

> I spent a lot of time and efforts experimenting with and asking about
> this, but eventually gave up and concluded that it was yet another bug
> in an open source project "only" on Windows with no real/pressing
> interest in fixing it.

Yeah, I remember that discussion.  It being open source cuts two ways:
problems get fixed by people who have the motivation to find a workable
fix.  Not being a Windows user, I don't particularly care about whether
this case is broken or not, and even if I cared more, I do not have
the resources to figure it out myself.  There are other people around the
project who do Windows, but you evidently have not managed to persuade
them that this is something they should spend time on, either.

> 3. The ability to embed PG to run in an automatic, quiet manner as part
> of something else.

That would be a packaging problem for the "something else" to figure out,
no?  There are enough platform-specific issues about "automatic" that
I don't see it as very practical for the core Postgres project to take
on, in any case.  I'm aware of some packagings, such as Postgres.app for
macOS (https://postgresapp.com), that might be closer to what you are
looking for than the core project ever could be.

> 4. There is no built-in means to have PG manage (or even suggest)
> indexes on its own.

Indeed, and that does not seem to me like something that ought to be
built in.  There is at least one "index advisor" plug-in floating
around, which perhaps could help you.

> 5. Ever since my early days with PG in the mid-2000s, I've tried
> numerous times to read the manual, wikis and comments for the
> configuration files, specifically the performance directives, and asked
> many, many times for help about that, yet never been able to figure out
> what they want me to enter for all the numerous options. At this point,
> it isn't me being lazy/stupid; it's objectively very difficult to
> understand all of that.

We do the best we can as far as the documentation goes; if you have
concrete suggestions about how to improve that, we'll surely listen.

> The practical end result of this is that I've always gone back to using
> the untouched default configuration file (except for the logging-related
> options), which, especially in the past on FreeBSD, *severely* crippled
> my PG database to not even come close to taking advantage of the full
> power of the hardware.

You seem to be wishing that the default configuration was designed to
try to commandeer the whole machine.  It's deliberate project policy
that that not be so --- and given that a couple of bullet points up,
you were arguing for Postgres to be easily usable as an invisible
part of some other app, it doesn't seem like you really want it to
be so either.

There is a lot of info out there about Postgres tuning, and yes some
of it is contradictory, because one size doesn't fit all.  People
have different goals about how they want the system to act, and the
proper settings may vary across platforms or PG versions, and there's a
fair amount of just plain different opinions.  But I think you may be
overcomplicating it.  IMO there's not that much you have to adjust to
start with --- maybe just increase shared_buffers and possibly work_mem
--- and then tweak other settings only when you find yourself running into
that limit.  If you can figure out our logging settings then you can
figure out the rest of this, too.

            regards, tom lane