Re: [GENERAL] 10 missing features

2011-04-26 Thread Scott Marlowe
On Tue, Apr 26, 2011 at 5:39 AM, Andrew Sullivan  wrote:

> In commercial development, this is where product development managers
> live.  They identify the meaning of the feature request, and then
> identify how the actual need (rather than the requested feature) can
> be addressed.

But there are also plenty of examples where this falls down.  I know
for years oracle refused to release a proper package for the client
side libs.  sqlplus has no command line history.  Luckily there's
rlwrap for that, but that's an example where you need an external
package to make Oracle come close to what's built into pgsql.  Sadly a
LOT of what a product manager is tasked with getting added to the
database is determined solely by its ability to generate more cash
flow, so often basic features like command line editing never get
done.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Merlin Moncure
On Mon, Apr 25, 2011 at 3:41 AM, Linos  wrote:
> Hi all,
>        only want to link this blog post
> http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features ,
> i think he may have any good points.

my take:

1. Query progress
Seen a couple of near miss proposals -- good feature, will probably
see eventually

2. Index Only Scans
visibility map could support some flavor of this in the future.  it
remains to be seen if it will actually help...

3. Session Wait Data
sounds very nice.  seems complex to implement and has some overlap
with external tools

4. Session Tracing
ditto the above.  pgfouine is pretty good and already does this

5. Multiple Block I/O
"All disk I/O goes through the OS and reads a single block at a time."
incorrect.  the o/s can read more blocks if it feels it should --
also, there are some facilities for async i/o.  I'm skeptical there is
much benefit going further here for postgres -- the storage scene is
changing fast.

6. Tablespace Quotas
Trivially done with tablespaces, although a built in solution would be
nice and probably not too tough.

7. No Logging
Beyond what's already be done and maybe a couple of corner cases, I
don't think there's much more to be done here.  There was a pretty
serious proposal for unlogged tables a while back -- I wasn't a big
fan...better to work around the facilities we have than complicate WAL
and recovery.

8. Better Export/Import Tools
I think the SQL underpinnings to pg_dump should be server side.  That
would fix a lot of the issues listed. Some of the stuff asked for we
already have but could be improved (I like dump/restore progress
idea).

9. Query Hints
I am in anti-hint camp.  I grew up without them, so learned to operate
in a different way.  Per recent discussion it's probably better to
inject assumptions into the statistics environment.

10. More Backup/Restore Options
I think the current solutions are pretty good. HS/SR, pitr, etc etc.
It's possible to rig rsync style dumps as well if you're clever.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Nicholson, Brad (Toronto, ON, CA)
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Radoslaw Smogura
> Sent: Tuesday, April 26, 2011 9:55 AM
> To: Leif Biberg Kristensen
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 10 missing features
> 
> 
>  For example, having knowledge when particular query stuck may be great
>  advantage for administrators and developers as well. Ofcourse each
>  functionality gives some overhead, but from other hand, if you create
>  important systems (like financials) "stability" and how it's easy to
>  track errors is required.


For those types of systems - lack of a true audit log is probably a bigger 
barrier.  The biggest "missing feature" are going to depend on your problem 
space.

>  Form this what I was interested and saw:
>  * I think he good pointed that logging indices, may be unneeded, as
>  those can be recreated.

Whether this is acceptable depends on your system.  Yes they can be recreated 
with a number of caveats

-performance for many systems will be poor until some (or all) indexes are 
back.  If you have SLA's based around performance you can extend your outage 
until the indexes get rebuilt.
-Indexes are used to enforce primary keys.  Are you comfortable running 
temporarily without your primary keys?
-Some replication engines rely on primary keys or unique indexes.  Losing these 
could break replication for you.

I think if you could control this on a per-index basis though it could be a win.

Brad

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Radosław Smogura

On Tue, 26 Apr 2011 14:30:19 +0200, Leif Biberg Kristensen wrote:

On Monday 25 April 2011 10:41:36 Linos wrote:

Hi all,
only want to link this blog post

http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features 
,

i think he may have any good points.


Miguel Angel.


Maybe the best point is the one between the lines: That PostgreSQL is 
being
compared to Oracle on a feature-by-feature basis. I don't know if his 
views

are representative for Oracle DBAs, but it's an indication of
PostgreSQL being
considered in the same league as Oracle.

The «features» perceived to be missing from PostgreSQL by the Oracle 
DBA is

the icing on the cake. The cake itself (in terms of data integrity,
performance, scalability) is obviously regarded as totally edible.

regards, Leif


All this small features creates good database in terms of database 
system, which is administrative and developer friendly.


For example, having knowledge when particular query stuck may be great 
advantage for administrators and developers as well. Ofcourse each 
functionality gives some overhead, but from other hand, if you create 
important systems (like financials) "stability" and how it's easy to 
track errors is required.


Form this what I was interested and saw:
* I think he good pointed that logging indices, may be unneeded, as 
those can be recreated.
* Approach for I/O - I have constant feeling that ShMem is wasted for 
storing copy of system cache, but I don't know better solution, as well 
I saw in bufmgr.c method for preloading data, but I don't know if it's 
used somewhere.

* Some operations may fill cache with "trash".

Regards,
Radek

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Leif Biberg Kristensen
On Monday 25 April 2011 10:41:36 Linos wrote:
> Hi all,
>   only want to link this blog post
> http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features ,
> i think he may have any good points.
> 
> 
> Miguel Angel.

Maybe the best point is the one between the lines: That PostgreSQL is being 
compared to Oracle on a feature-by-feature basis. I don't know if his views 
are representative for Oracle DBAs, but it's an indication of PostgreSQL being 
considered in the same league as Oracle.

The «features» perceived to be missing from PostgreSQL by the Oracle DBA is 
the icing on the cake. The cake itself (in terms of data integrity, 
performance, scalability) is obviously regarded as totally edible.

regards, Leif

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Andrew Sullivan
On Tue, Apr 26, 2011 at 06:21:21AM -0400, Greg Smith wrote:

> addressed doesn't start with "how can PostgreSQL duplicate the
> Oracle solution to this problem", which is how many of these
> incoming requests for features start.  The alternate question of
> "how do you provide something with the same feature set for
> PostgreSQL?" is the more useful one, and it doesn't lead to the same
> solutions.  That disconnect is important to address.

But the problem there is a really fundamental one, in that it goes to
the core of how free software gets developed: someone has a problem
that s/he wants fixed.

"The problem" in many of these cases is really, "I know how to do _x_.
How do I do that in Postgres?"  If the answer is, "You don't," it
sounds like a missing feature.

In commercial development, this is where product development managers
live.  They identify the meaning of the feature request, and then
identify how the actual need (rather than the requested feature) can
be addressed.[1] But in the free software world, of course, that won't
work unless you also have the developer hours to contribute to make
the feature.  Moreover, there is a strong preference toward 80/20
answers, because the code will have to be maintained and unless
someone who is pushing for a feature has a record of hanging around to
maintain it, there's good reason to wonder whether the feature will be
supported.  

The Postgres community is much better than most at this sort of thing,
of course, but it's hard to compete with commercial development on
refining user experience.

[1] Um, ok, the good ones do this.  The bad ones say, "Oh, people want
an animated paper clip," or, "Oh, people want us to extend PDF so that
it's an excellent vector for viruses," or, "Oh, people want high
uptime, so we'll make this multi-node failover thing that blows away
your system if you sneeze wrong," or something like that.

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-26 Thread Greg Smith

On 04/25/2011 04:54 PM, Nicholson, Brad (Toronto, ON, CA) wrote:
The problem is that there is a lot of noise in the add-on space. There 
are lots of things out there that are no longer supported or partially 
supported. There is a fairly high barrier of entry into figuring out 
which tools to use, how to put them together and what you can and 
can't do with them.




Right.  This is why I advise people to start on it as soon as possible, 
to make it part of the initial testing of PostgreSQL.  You have to start 
early on figuring which of the additional packages make sense for you, 
because in some environments you can't easily introduce them later if 
they weren't part of earlier QA.  I tried to work on the barrier to 
entry part in my book, there's a lot of specific recommendations for 
add-ons in there and context about what they are and aren't good for.



If (and I stress the word if) the target is winning over DBA's from the 
commercial crowd this is an important point, as those DBA's are going to be 
used to getting most of what they need in one package along with the DB.
   


Unfortunately that whole line of thinking is fundamentally incompatible 
with how open source databases are built and packaged.  What some people 
don't seem to get is that the "one package" here is "one operating 
system distribution with a good package manager".  It's not like you 
have to build all this stuff from source or anything; in many cases the 
packages are available to add with a quick search and a few clicks.



I do think the areas that are lacking in PG though do come to finer grain 
profiling of tasks.  The ability to isolate CPU and IO utilization of 
particular queries or sets of queries is something I find very useful in the 
commercial DB space that I'd love to see in Postgres.  Same goes for 
troubleshooting locking conflicts if you aren't looking at the system when they 
are happening, and tracing the causes of those locks down to finer grained 
details (IE - am I waiting on buffer eviction or xlog writes).
   


This is all true.  Unfortunately the way I expect this areas to be 
addressed doesn't start with "how can PostgreSQL duplicate the Oracle 
solution to this problem", which is how many of these incoming requests 
for features start.  The alternate question of "how do you provide 
something with the same feature set for PostgreSQL?" is the more useful 
one, and it doesn't lead to the same solutions.  That disconnect is 
important to address.  If people are only willing to work toward or fund 
solving a familiar problem, they may not recognize an alternate solution 
that is just as useful--just not as familiar--that is available or being 
built.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-25 Thread Nicholson, Brad (Toronto, ON, CA)
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Greg Smith
> Sent: Monday, April 25, 2011 4:23 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 10 missing features
> 
> On 04/25/2011 10:48 AM, Andrew Sullivan wrote:
> > You can see this in certain items in the top 10. Three, four, five,
> > seven, maybe 8 eight, and ten all seemed to me to be things I've
> > actually done before, but not using something directly inside
> > Postgres.
> >
> 
> The idea that something must ship in the database to be useful is
> really
> engrained in some people.  I do this talk nowadays about common
> mistakes
> people make when deploying PostgreSQL, and one of the top items I put
> on
> there is not actively investigating external tools.


The problem is that there is a lot of noise in the add-on space.  There are 
lots of things out there that are no longer supported or partially supported.  
There is a fairly high barrier of entry into figuring out which tools to use, 
how to put them together and what you can and can't do with them.

If (and I stress the word if) the target is winning over DBA's from the 
commercial crowd this is an important point, as those DBA's are going to be 
used to getting most of what they need in one package along with the DB.

> None of the items on this list would be on my own top list of missing
> things in PostgreSQL.  I see "Better fragmentation management" as a
> footnote and there's an intro discussion to that on the blog at
> http://blog.kimiensoftware.com/2011/04/compacting-postgresql-tables/
> Apparently the struggles required to sort out a little 25GB table
> apparently didn't make enough of an impression to put that into its
> proper place, which is way ahead of every item listed on the suggested
> missing feature set.  Query progress is #1?  It's annoying, yes, but so
> not even close to pole position to me.  From reading the blog a bit, it
> sounds like the author is managing lots of smallish (to me) databases,
> so putting so much emphasis on making each individual one easier to
> troubleshoot makes more sense.

I think you touch on this here - but a lot of what the "most needed" things are 
will depend on your problem set.  Lack of differential backups used to be a 
huge pain when I had multi-terabyte datawarehouses to look after.  Ditto for 
query progress when I had managers asking me when ad-hoc OLAP style queries 
would complete.

I do think the areas that are lacking in PG though do come to finer grain 
profiling of tasks.  The ability to isolate CPU and IO utilization of 
particular queries or sets of queries is something I find very useful in the 
commercial DB space that I'd love to see in Postgres.  Same goes for 
troubleshooting locking conflicts if you aren't looking at the system when they 
are happening, and tracing the causes of those locks down to finer grained 
details (IE - am I waiting on buffer eviction or xlog writes).

I do realize that there are ways to get at some of this stuff or work around it 
- but the barrier of entry is often pretty high, can involves high volume 
logging and is often far more time consuming task than it could be.


Brad.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-25 Thread Greg Smith

On 04/25/2011 10:48 AM, Andrew Sullivan wrote:

You can see this in certain items in the top 10. Three, four, five,
seven, maybe 8 eight, and ten all seemed to me to be things I've
actually done before, but not using something directly inside
Postgres.
   


The idea that something must ship in the database to be useful is really 
engrained in some people.  I do this talk nowadays about common mistakes 
people make when deploying PostgreSQL, and one of the top items I put on 
there is not actively investigating external tools.


None of the items on this list would be on my own top list of missing 
things in PostgreSQL.  I see "Better fragmentation management" as a 
footnote and there's an intro discussion to that on the blog at 
http://blog.kimiensoftware.com/2011/04/compacting-postgresql-tables/   
Apparently the struggles required to sort out a little 25GB table 
apparently didn't make enough of an impression to put that into its 
proper place, which is way ahead of every item listed on the suggested 
missing feature set.  Query progress is #1?  It's annoying, yes, but so 
not even close to pole position to me.  From reading the blog a bit, it 
sounds like the author is managing lots of smallish (to me) databases, 
so putting so much emphasis on making each individual one easier to 
troubleshoot makes more sense.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-25 Thread David Johnston


-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of mark
Sent: Monday, April 25, 2011 10:30 AM
To: 'Linos'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 10 missing features



> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- 
> ow...@postgresql.org] On Behalf Of Linos
> Sent: Monday, April 25, 2011 2:42 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 10 missing features
> 
> Hi all,
>   only want to link this blog post
> http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-
> features , i
> think he may have any good points.
> 

I think this is flame bait for rules based / query hints stuff all over
again.


Did anyone else notice how most of his top then comes back to "bad plans".
Either the planner is really bad all the time and I never knew it or we are
way overblowing things.


One or two of his points are on my list as well, but as far as a TOP 10
missing features that PG "needs" his probably aren't anywhere close to what
the majority of people are in need of. 

>>>>>>>>>>>>>>>>>>

At first glance I disagree.  What I see is a desire for advanced diagnostic
features that aid in making complex queries (and probably queries written by
others) perform better by seeing exactly what the engine is doing and also
telling the engine to behave in a certain way.

PostgreSQL already implicitly recognizes the fact that some queries cannot
be perfectly planned - in a reasonable amount of time - due to having too
many joins (from_collapse_limit, join_collapse_limit).

When you make a statement regarding a "majority of people" you need to at
least implicitly define your domain.  The majority of people in the world do
not even know WHAT PostgreSQL is.  The author of the article belongs to a
sub-set of database administrators that have used Oracle heavily.  I would
offer that only a very small number of current PostGreSQL users have that
experience set.  Furthermore, the profile of the typical Oracle installation
and support is likely quite different than the typical profile for
PostgreSQL.  What is being hinted to by the author is that for PostgreSQL to
handle installations more like the typical Oracle installation it would be
highly desirable to have the features mentioned.  

Summarizing the article from my POV:

Oracle gives the DBA the ability to de-shroud the database Black-Box and
take more direct control (and monitoring) compared to PostgreSQL.  Top 10
percentile of Oracle DBAs find being forced to treat the PostgreSQL engine
as a Black-Box undesirable.  The Top 10 percentile of PostgreSQL DBAs may
share the same feelings but other benefits outweigh the loss of capability.
In any case the majority of DBAs in both camps likely do not find such
functionality necessary enough to commit to learning those tools - even if
they were present.

I think there is value in de-shrouding the database engine and giving the
DBA more control, if desired, but I also understand that it is not a simple
undertaking.  However, simply attempting to decrease the value proposition
is not going to sit well with those who already do value that ability.  Bad
plans are going to occur, and even good plans can often be improved.  It is
like giving a painter a canvas and some paint but not telling them the
materials they are being made out of.  They can likely paint you a decent
scene but could likely do much better given the proper knowledge of how
their materials are working. 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-25 Thread Andrew Sullivan
On Mon, Apr 25, 2011 at 08:29:43AM -0600, mark wrote:
> One or two of his points are on my list as well, but as far as a TOP 10
> missing features that PG "needs" his probably aren't anywhere close to what
> the majority of people are in need of. 

When I had to hire Postgres DBAs, I hated hiring people coming from an
Oracle background, because most Oracle DBAs are so used to doing
things how you do stuff in Oracle that learning how to do things with
a different mindset took a lot of time.

The biggest problem, I found, was the difference in approach to the
interaction with the operating sytem.  Oracle is basically designed to
take over the host system, and Oracle DBAs then start looking in
Postgres for features that, from Postgres's point of view, are
features that the OS should be offering.

You can see this in certain items in the top 10.  Three, four, five,
seven, maybe 8 eight, and ten all seemed to me to be things I've
actually done before, but not using something directly inside
Postgres.

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] 10 missing features

2011-04-25 Thread mark


> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Linos
> Sent: Monday, April 25, 2011 2:42 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 10 missing features
> 
> Hi all,
>   only want to link this blog post
> http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-
> features , i
> think he may have any good points.
> 

I think this is flame bait for rules based / query hints stuff all over
again.


Did anyone else notice how most of his top then comes back to "bad plans".
Either the planner is really bad all the time and I never knew it or we are
way overblowing things.


One or two of his points are on my list as well, but as far as a TOP 10
missing features that PG "needs" his probably aren't anywhere close to what
the majority of people are in need of. 


-Mark




> 
> Miguel Angel.
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] 10 missing features

2011-04-25 Thread Linos

Hi all,
	only want to link this blog post 
http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , i 
think he may have any good points.



Miguel Angel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general