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

[Thomas Kellerer]

tutilu...@tutanota.com schrieb am 25.09.2020 um 06:41:

All lowercase is good

That's your personal opinion -- not some kind of fact, and it
definitely goes against everything that I believe.

You have to accept that every programming environment has its own best practices
(in terms of coding style AND naming conventions). The recommendation to use all
lower-case unquoted names is not Gavin's personal opinion, it's the opinion
of the Postgres community.

In Postgres and many other relational databases - with SQL Server and MySQL 
being the (only?) exception -
using non-quoted identifiers is the recommended way. In Oracle, DB2 or Firebird 
this leads to all uppercase
names, in Postgres to all lowercase names.

If you don't follow the recommended best practices, you can't really blame 
those that do (e.g. the PostGIS project),
for your problems.



The fact that there are a lot of sloppy, US-centric people who refuse
to use correctly named identifiers and cannot understand how there
can be anything besides a-z in an alphabet, doesn't change reality
and doesn't make a bug "right".


I am not in the USA (and far from being US-centric as well) and I have been 
working
with relational databases for over thirty years. I never had problems using 
unquoted
ASCII names (using snake_case) for my database objects.

Would it be nice if I could use special characters like öäü in the names of 
tables and columns (without the hassle of quoting them)?
Yes, absolutely.

Does not using them, limit me in any way doing what I want to do?
No, it doesn't.


Always the same thing. The slightest criticism, no matter how
warranted, always results in: "Fine. Go somewhere else. Use something
else."
Never: "Oh, right. Sorry, but we always used lowercase ourselves and
therefore didn't consider this. In retrospect, it's an embarrassing
mistake! We'll fix it in the next release. Thanks for pointing that
out."


Changing this behaviour has been discussed on this list multiple times.

The problem is, that currently no one sees a way to change this without
breaking all (or most) existing code that relies on it (at least not with
the resources the project hast).

It's my understanding (as a user, not a developer) that the impact on the code 
base would be huge, and
the community doesn't indeed really see a benefit in doing so.

It has especially been discussed to implement a behaviour that complies
with the SQL standard which *requires* to fold non-quoted names to uppercase!

Would you be more happy with a standard compliant behaviour?
I guess not.

So, this is indeed one of the things that you either have to accept, or move on.

*Every* piece of software has some quirks (or bugs as you see it) which are 
annoying
and can't or won't be changed. If the number of quirks (or bugs) exceeds the 
benefit
you get from the software, then you should indeed start looking for something 
different.

But claiming that the people on this list are not open to criticism is simply 
wrong and
doesn't do them justice.
You might want to search the internet on the reaction to Uber's change from 
Postgres
to MySQL - many of the criticism from their side have resulted in changes to
Postgres. And there are many other examples (e.g. Windows port, replication, 
vacuum problems).

You are unlucky to be offended by the best practices when it comes to naming
database objects. I think having a "case preserving, case insensitive"
option in Postgres would be nice indeed, but it's not a _functional_ problem,
it's just an aesthetic one. Sticking to the recommended best practices
doesn't limit you in any way in exploiting all features of the software.



The answer, of course, is that you have zero interest in helping but
100% interest in making smug insults,

The only person who is insulting other people is you.

But you apparently have zero interest in accepting that different environments, 
need different approaches.
And what works in "System A" does not work the same in "System B".


Thomas

horizontal scaling

[Rita]

There was a lot of talk
about
horizontal scaling a few years ago. I was wondering if there have been any
new efforts on it.

-- 
--- Get your facts first, then you can distort them as you please.--

Re: horizontal scaling

[Thomas Kellerer]

I think currently the best option is something like Citus[1] if you want to 
stick with vanilla Postgres.

Otherwise Greenplum might be another option.

Thomas

[1] https://github.com/citusdata/citus

Rita schrieb am 25.09.2020 um 13:51:

There was a lot of talk
about
horizontal scaling a few years ago. I was wondering if there have
been any new efforts on it.

Re: horizontal scaling

[Thomas Kellerer]

I think currently the best option is something like Citus[1] if you want to 
stick with vanilla Postgres.

Otherwise Greenplum might be another option.

Thomas

[1] https://github.com/citusdata/citus

Rita schrieb am 25.09.2020 um 13:51:

There was a lot of talk
about
horizontal scaling a few years ago. I was wondering if there have
been any new efforts on it.

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

[Adrian Klaver]

On 9/25/20 1:04 AM, Thomas Kellerer wrote:




The fact that there are a lot of sloppy, US-centric people who refuse
to use correctly named identifiers and cannot understand how there
can be anything besides a-z in an alphabet, doesn't change reality
and doesn't make a bug "right".


I am not in the USA (and far from being US-centric as well) and I have 
been working
with relational databases for over thirty years. I never had problems 
using unquoted

ASCII names (using snake_case) for my database objects.

Would it be nice if I could use special characters like öäü in the names 
of tables and columns (without the hassle of quoting them)?

Yes, absolutely.



But you can use them without quoting:

select version();
  version 



 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
7.5.0, 64-bit


create table öäü (id int , fld_1 varchar);

insert into öäü values (1, 'test');

select * from öäü;
 id | fld_1
+---
  1 | test




Thomas





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Adrian Klaver]

On 9/24/20 10:40 PM, tutilu...@tutanota.com wrote:


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

It's funny that you should bring that up, considering how it was one of 
my points... See the point about pg_dump's bug on Windows.


Yes, I read your bug 
report(https://www.postgresql.org/message-id/mck38uv--...@tutanota.com) 
and you where just as resistant to advice there as here.




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.

Why does nobody understand that it was an *example* and not some kind of 
full PostGIS review?


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.

It sure is great to have such choices... I can't take it seriously when 
people say things like this. It's similar to "it's open source so you 
can easily vet it yourself". It's not taking reality into consideration 
at all.


As for doing without it, that would make it impossible to deal with GPS 
coordinates/maps. So it's not really a choice at all.


Read this issue from PostGIS:

https://trac.osgeo.org/postgis/ticket/3496

" Then we can use the variable @extschema@ in lieu of the actual schema 
name. This will still allow users to do:


CREATE EXTENSION postgis SCHEMA whereever_I_damn_want_you_to_be;
"



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.

If it changes that much, it's far, far worse than I even thought, and it 
sounds like it will be pointless to even *try* to learn it as it keeps 
changing between versions/OSes/other stuff.


Life is not static. If you want to stay current you have to keep learning.



I can't help but feel as if people just don't want to answer this and 
other concerns I have. As if there's some silent agreement along the 
lines of "securing PG DBAs' jobs".


The reason people have not answered is you have not provided the 
information necessary to formulate an answer. For instance, OS & 
version, Postgres version, size of data, database use case, number of 
users.




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.

I don't mean this to sound rude, but it's like talking to a wall... What 
I mean is that there are obviously technical means for software to know 
whether they are exhausting the system they are running on or not, and 
expecting people to understand all these intricate internal parameters 
is just... bizarre. There ought to be some kind of "abstract" setting 
for those of us who aren't able to (or even *wish* to) comprehend all 
the PG internals, and just want an efficient database using (roughly) as 
much of our machine as we want.


This is not the first time I feel like I'm repeating myself over and 
over in different ways but never getting through. It could be that you 
are so familiar with PG's internals that it all is obvious to you, but 
it could just as well be that you don't want to hear about this.


No I am not all that familiar with the Postgres internals. I'm an end 
user for what qualifies as small databases. The configuration as is 
works for me, in that any impediments it might impose are hidden by 
other parts of the stack. I just know, from years on this list, that 
there are folks who would help you with configuration given some 
starting point information.




--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Thomas Kellerer]

Adrian Klaver schrieb am 25.09.2020 um 17:02:

Would it be nice if I could use special characters like öäü in the names of 
tables and columns (without the hassle of quoting them)?
Yes, absolutely.


But you can use them without quoting:

select version();
   version

  PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 
64-bit

create table öäü (id int , fld_1 varchar);

insert into öäü values (1, 'test');

select * from öäü;
  id | fld_1
+---
   1 | test


Ah cool ;)

I didn't know that, thanks.

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

[Johannes Graën]

On 9/25/20 7:40 AM, tutilu...@tutanota.com wrote:
> I don't mean this to sound rude, but it's like talking to a wall... What
> I mean is that there are obviously technical means for software to know
> whether they are exhausting the system they are running on or not, and
> expecting people to understand all these intricate internal parameters
> is just... bizarre. There ought to be some kind of "abstract" setting
> for those of us who aren't able to (or even *wish* to) comprehend all
> the PG internals, and just want an efficient database using (roughly) as
> much of our machine as we want.

I found pgtune [1] to be a very good start for tuning PG. There's also a
tool available online [2].

[1] https://github.com/gregs1104/pgtune
[2] https://pgtune.leopard.in.ua/

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

[Bruce Momjian]

On Thu, Sep 24, 2020 at 11:47:10PM -0500, Ron wrote:
> On 9/24/20 6:20 PM, Bruce Momjian wrote:
> > On Wed, Sep 23, 2020 at 12:28:14AM +0200, tutilu...@tutanota.com wrote:
> > > Sep 21, 2020, 7:53 PM by j...@commandprompt.com:
> > >  See my comment about Google. The information is out there and easy 
> > > to find.
> > > 
> > > I guess I'm the worst idiot in the world, then, who can't DuckDuckGo 
> > > (Google is
> > > evil) it even after 15 years.
> > > 
> > > Seriously, I didn't type my feedback "for fun". It may be difficult for 
> > > very
> > > intelligent people to understand (as often is the case, because you 
> > > operate on
> > > a whole different level), but the performance-related PostgreSQL 
> > > configuration
> > > options are a *nightmare* to me and many others. I spent *forever* reading
> > > about them and couldn't make any sense of it all. Each time I tried, I 
> > > would
> > > give up, frustrated and angry, with no real clue what "magic numbers" it
> > > wanted.
> > > 
> > > It's quite baffling to me how this can be so difficult for you all to
> > > understand. Even if we disregard the sheer intelligence factor, it's 
> > > clear that
> > > users of PG don't have the same intimate knowledge of PG's internals as 
> > > the PG
> > > developers, nor could possibly be expected to.
> > > 
> > > As mentioned, I kept going back to the default configuration over and over
> > > again. Anyone who doesn't is either a genius or pretends/thinks that they
> > > understand it. (Or I'm extremely dumb.)
> > I think there is a clear dependency that people reading the docs,
> > particularly for performance purposes, must have an existing knowledge
> > of a lot of low-level things --- this could be the cause of your
> > frustration.
> 
> And that's a serious problem with the documentation. (Not that I know how to
> fix it in an OSS project.)

We added a glossary in PG 13, so we could certainly have some kind of
hardware terms primer which explains various OS features that affect
Postgres.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

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

[Bruce Momjian]

On Fri, Sep 25, 2020 at 10:04:53AM +0200, Thomas Kellerer wrote:
> I am not in the USA (and far from being US-centric as well) and I have been 
> working
> with relational databases for over thirty years. I never had problems using 
> unquoted
> ASCII names (using snake_case) for my database objects.
> 
> Would it be nice if I could use special characters like öäü in the names of 
> tables and columns (without the hassle of quoting them)?
> Yes, absolutely.
> 
> Does not using them, limit me in any way doing what I want to do?
> No, it doesn't.
> 
> > Always the same thing. The slightest criticism, no matter how
> > warranted, always results in: "Fine. Go somewhere else. Use something
> > else."
> > Never: "Oh, right. Sorry, but we always used lowercase ourselves and
> > therefore didn't consider this. In retrospect, it's an embarrassing
> > mistake! We'll fix it in the next release. Thanks for pointing that
> > out."
> 
> Changing this behaviour has been discussed on this list multiple times.
> 
> The problem is, that currently no one sees a way to change this without
> breaking all (or most) existing code that relies on it (at least not with
> the resources the project hast).
> 
> It's my understanding (as a user, not a developer) that the impact on the 
> code base would be huge, and
> the community doesn't indeed really see a benefit in doing so.
> 
> It has especially been discussed to implement a behaviour that complies
> with the SQL standard which *requires* to fold non-quoted names to uppercase!

I did write a blog entry about case folding:

https://momjian.us/main/blogs/pgblog/2020.html#June_26_2020

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

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

[Sam Gendler]

On Thu, Sep 24, 2020 at 10:40 PM  wrote:

>
> Well not partial as in incremental. Instead dump only some portion of the
> schema with or without its associated data.
>
> It's funny that you should bring that up, considering how it was one of my
> points... See the point about pg_dump's bug on Windows.
>

And you seem to have ignored the fact that one of the core developers
pointed out that it likely isn't a pg_dump bug - if your terminal is using
the same locale as the database, it should have no difficulty dealing with
the characters you are having trouble with.  It seems likely that you
simply need to learn how to get your terminal set up correctly for it to
work.


> 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".
>
> 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.
>
> It sure is great to have such choices... I can't take it seriously when
> people say things like this. It's similar to "it's open source so you can
> easily vet it yourself". It's not taking reality into consideration at all.
>
> As for doing without it, that would make it impossible to deal with GPS
> coordinates/maps. So it's not really a choice at all.
>

Never mind that your tone in your emails is remarkably rude for someone who
is doing nothing but complain about perceived shortfalls in a product that
is entirely free and of which you appear to be fairly far from an expert
user, has it occurred to you that YOUR issue is that you have absolutely no
understanding of the variety of uses that people put a database like
postgres to out in the world? Imagine if the core database server was
subject to the development schedule of every extension that you happen to
think ought to be included in the core product - or vice versa.  Someone
finds a bug in PostGIS and it can't be fixed until the next major or minor
release of the core postgres server?  That would result in a terrible user
experience and force development of all extensions to move in lockstep with
the core server.  The vast majority of users do not ever use GIS extensions
and have absolutely no use for their presence in the core product. The fact
that you need it is your one individual use case.  I've been using Postgres
professionally since 2005 and I've only ever used postgis for a single
project. Who are you to say what should and should not be included in a
product to which you contribute absolutely nothing? And in the same breath,
you complain about the quality of that extension.  If it is so lacking in
quality, why would you want it embedded in the core service where it could
potentially impact the quality of every user's experience?  Maybe consider
that the dev team has good reasons for keeping non-essential functionality
in extensions instead of the core codebase.

> 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.
>
> If it changes that much, it's far, far worse than I even thought, and it
> sounds like it will be pointless to even *try* to learn it as it keeps
> changing between versions/OSes/other stuff.
>

> I can't help but feel as if people just don't want to answer this and
> other concerns I have. As if there's some silent agreement along the lines
> of "securing PG DBAs' jobs".
>

You really come off as something of an ass with this commentary. DBAs have
plenty of far more interesting things to do with their time than
performance tune a server config and certainly don't need that in order to
be secure in their role. What is clear is that you have absolutely no
understanding of the variety of ways in which a database server might be
configured depending on the way the database is being used, the quantity of
data