Thread: 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.
Miguel Angel.
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@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
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 ajs@crankycanuck.ca
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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-owner@postgresql.org [mailto:pgsql-general- > owner@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.
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 US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@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 supportedor partially supported. There is a fairly high barrier of entry into figuring out which tools to use, how to putthem 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 thoseDBA'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 ofdifferential backups used to be a huge pain when I had multi-terabyte datawarehouses to look after. Ditto for query progresswhen 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 CPUand IO utilization of particular queries or sets of queries is something I find very useful in the commercial DB spacethat I'd love to see in Postgres. Same goes for troubleshooting locking conflicts if you aren't looking at the systemwhen they are happening, and tracing the causes of those locks down to finer grained details (IE - am I waiting onbuffer 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 prettyhigh, can involves high volume logging and is often far more time consuming task than it could be. Brad.
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, asthose 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 CPUand IO utilization of particular queries or sets of queries is something I find very useful in the commercial DB spacethat I'd love to see in Postgres. Same goes for troubleshooting locking conflicts if you aren't looking at the systemwhen they are happening, and tracing the causes of those locks down to finer grained details (IE - am I waiting onbuffer 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 US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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 ajs@crankycanuck.ca
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
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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@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 goingto 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 performanceyou 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
On Mon, Apr 25, 2011 at 3:41 AM, Linos <info@linos.es> 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
On Tue, Apr 26, 2011 at 5:39 AM, Andrew Sullivan <ajs@crankycanuck.ca> 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.