6 ms·

My wish list for Postgres 9.5

41 points by craigkerstiens 12y ago

astrodust 12y ago
MySQL's non-standard ON DUPLICATE KEY feature is extremely useful, so any "upsert"-like feature would be a great thing to have.
- j_s 12y ago
  Hope you saw the discussion of 'UPSERTisms in Postgres' a while back: https://news.ycombinator.com/item?id=7620050 https://news.ycombinator.com/item?id=7620050
giulianob 12y ago
More functions to modify JSON columns in place without needing to retrieve it.
- rch 12y ago
  Does this thinking lead to feature parity with Redis EVAL?
  - ddorian43 12y ago
    It will lead to feature parity with Mongodb.
buckbova 12y ago
The OP mentions gui, but what is the best gui/code editor/ide for postgres for linux? I found this list. I'll go through them. https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreS...
- conradfr 12y ago
  As a developer the database tool in Jetbrains' IDE covers the vast majority of my needs, in addition to the golden autocompletion in the code.
- rch 12y ago
  I've found pgAdmin to be sufficient for clicking through things here and there, but my sense is that DBA types are expecting more from the GUI than I am. Any insight into what those things might be would probably be helpful.
- smilliken 12y ago
  I've gone through that list before, but never find anything compelling enough to pull me away from psql (the built-in CLI client). I highly recommend learning psql first. It doesn't have every feature you might want, but it gets the basics right and is easy to integrate with your editor and other tools. As far as I can tell, it's what most professionals use.
  - yangyang 12y ago
    I fully agree with this. I used pgAdmin for a while but have recently switched to psql and it's much more useful. In particular, learn the \ commands. It's extremely useful to be able to quickly look at the structure of a table, dump the source to a function and so on, from a command prompt rather than navigating trees with the mouse in pgAdmin. It uses readline where available so you get history, completion etc.
- booty 12y ago
  All I really want from a database IDE is awesome autocomplete for identifiers and keywords. (I consider SQL Server Management Studio's autocomplete to be great - if I could get that for Postgres, I'd be thrilled) You can get part of the way there by Sublime Text to run queries and browse results. In Sublime, it's easy - you can create a custom "build tool" with (essentially) just one line of config... you're basically just piping the current file's contents to command-line PSQL: https://www.youtube.com/watch?v=tPd4m3PLVqU https://www.youtube.com/watch?v=tPd4m3PLVqU Then I use the SublimeAllAutocomplete package. It works just like "regular" Sublime autocomplete except it gives you autocomplete suggestions from all open files, not just the current one - so if you have your DB schema dump in another window it will use that: https://github.com/alienhard/SublimeAllAutocomplete https://github.com/alienhard/SublimeAllAutocomplete Obviously, that doesn't really give you the smartest autocomplete ever but it's pretty productive for me. Tons of room for improvement though.
  - asnyder 12y ago
    Has auto-complete and everything: http://www.sqlmanager.net/products/postgresql/manager http://www.sqlmanager.net/products/postgresql/manager
- megaman821 12y ago
  It is not free (or even that cheap) but Navicat is really good. It was worth it for me as much as I interact with databases.
  - untog 12y ago
    Seconded. And the 'Essentials' version is only $9.99.
- dataminded 12y ago
  Aqua Data Studio is the best database IDE I have ever used.
- deleted 12y ago
  [deleted]
- A_COMPUTER 12y ago
  I use ToRA http://torasql.com/ http://torasql.com/ and/or Oracle SQLDeveloper. Both of them are Oracle DB tools that work fine with Postgres with the right drivers. pgadmin is intolerable.
jfb 12y ago
A better language than SQL in front of Postgres' careful and mature data storage engine?
- collyw 12y ago
  SQL syntax isn't great, but it is very powerful and good for what it does. What would you expect from a language improvement point of view? Its a declarative language so you can't expect a load of object oriented features. I found that once I learned to think in sets, the language doesn't bother me. There is a certain simplicity to it, that is fairly elegant.
  - jfb 12y ago
    I would like a language more like Tutorial D; strictly set based (none of SQL's bags); without SQL's terrible, awful, no-good very bad 4GL syntax (I'd like something terser and more mathematical). It would be closed over composition, for instance, and would map more directly onto the relational calculus.
    - Shorel 12y ago
      Do you have any documents or RFC links about this language? I'm sure an specification is gladly appreciated.
      - dragonwriter 12y ago
        "Tutorial D", a particular pedagogical instance of a defined class of languages, is defined here: http://www.dcs.warwick.ac.uk/~hugh/TTM/Tutorial%20D-2013-05-23.pdf http://www.dcs.warwick.ac.uk/~hugh/TTM/Tutorial%20D-2013-05-... The requirements list for any "D" (that is, the definition of membership in the class of languages) is here in The Third Manifesto: http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-2013-02-07.pdf http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-2013-02-07.pdf
        Shorel 12y ago
doxcf434 12y ago
Automatic failover that doesn't require a 3rd party HA tool would be great.
- Qantourisc 12y ago
  You'd still have to deal with the complexity, but somewhere else ...
saosebastiao 12y ago
I was really excited about the Citus column-store FDW...but it took me about an hour of using it before I realized it wasn't going to solve any of my problems any time soon. Queries with data larger than memory were slower than native tables. Compression didn't come anywhere close to the 2-4x improvement that was claimed. It doesn't support insert, update, or delete commands, let alone the insert from select that I was hoping to use. In order to convert a native table into a cstore table, you have to output to a file and then import using COPY, or hack it with a pipe to a COPY command. After a while, I realized that some of this was just the maturity of the extension, but a huge amount was the limitations of FDWs as a platform. And not only that, it seems that every other 3rd party FDW extension is either abandoned or ignored. Postgres is quite possibly the highest quality piece of software I use. I rate it higher than the Linux Kernel. But Postgres' historical approaches to extensibility have left a lot to be desired. Reading over this comment, I realize I probably sound like an entitled jackass, but I'll post it anyway. I really do wish I could contribute to Postgres, but learning C well enough to contribute is way out of reach for me at the moment. But I guess all I can say is that my (admittedly vague) wishlist for Postgres 9.5 is to enhance any and all features to support extensibility, including FDWs and Extensions.
- buckbova 12y ago
  > I really do wish I could contribute to Postgres, but learning C well enough to contribute is way out of reach for me at the moment. I'm sure they accept money in lieu of C skills. http://www.postgresql.org/about/donate/ http://www.postgresql.org/about/donate/
  - saosebastiao 12y ago
    Good call...now I feel like even more of a jackass for not considering that :)
- ozgune 12y ago
  (Ozgun from Citus Data here) We're actively working on improving the cstore_fdw extension and your feedback is valuable to us. We're keeping a list of open issues on GitHub, and prioritize them for upcoming releases based on your comments. If you have any requests, please give us your feedback here: https://github.com/citusdata/cstore_fdw/issues https://github.com/citusdata/cstore_fdw/issues For performance and compression related questions, we'd also love to hear more from you and help if we can in any way: https://groups.google.com/forum/#!forum/cstore-users https://groups.google.com/forum/#!forum/cstore-users As PostgreSQL covers a huge feature set, we know that it will take time on our end to get there. Some of that will involve new foreign table features from PostgreSQL core, such as foreign indexes and foreign table inheritance. I just wanted to let you know that we are working hard in that journey to get there.
asnyder 12y ago
I don't think an "official" GUI is necessary. Rather it would simply hurt the community of paid/free tools that have built up around it. My favorite PostgreSQL GUI is: http://www.sqlmanager.net/products/postgresql/manager http://www.sqlmanager.net/products/postgresql/manager, it's excellent overall.
- terhechte 12y ago
  Excuse me, but I think this only runs on Windows. Many developers use Mac OS X or Linux. A wonderful cross platform solution would certainly be great. I'm using PGAdmin on a weekly basis, and while it offers more features than any other free solution, it fails at usability, native integration and also lacks support for the more advanced features of PostgreSQL.
  - asnyder 12y ago
    It supports most of the advanced features of PostgreSQL, including functions, triggers, composite types, JSON, and most anything one can throw at it. Not to mention it obviously has a great SQL Editor with auto-complete for when you want to do drafts by hand, as I usually do. As far as it being windows only, I thought that was no longer an issue thanks to great tools like Parallels and VMWare Fusion. Can't you just open the app in that while still living in your OS? I honestly don't see much of an issue.
    - yangyang 12y ago
      Windows licences are not cheap.
      - asnyder 12y ago
        You can find genuine windows licenses pretty easily for about $50, and the windows store sells them for as low $69.99, but it's true, you do need a license. Though for all the time it's saved me over the years it's definitely worth it IMO. It would be nice if they made a cross platform version, I remember they used to support Linux somehow.
nasalgoat 12y ago
I'd sure like to have replication to one database while still allowing writes on another. As it stands now, if you're replicating from a master, you can't do anything else. This is something that MySQL could do over a decade ago.
jpitz 12y ago
My long-standing feature request is for index-organized-tables : http://www.postgresql.org/message-id/ac116f9a0907151917o6d87d1a8wf3ff2afb088eb47c@mail.gmail.com http://www.postgresql.org/message-id/ac116f9a0907151917o6d87...
- buckbova 12y ago
  Same as this todo item? Automatically maintain clustering on a table http://wiki.postgresql.org/wiki/Todo#CLUSTER http://wiki.postgresql.org/wiki/Todo#CLUSTER
  - jpitz 12y ago
    In my view, periodic re-clustering isn't the same thing, though it may be good enough for most use cases. I'd label it "transactionally maintain clustering on a table"
Lennie 12y ago
On my list, adding even better support for fast full text search: https://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf https://wiki.postgresql.org/images/2/25/Full-text_search_in_... Many of the changes already went into 9.4 but for PostgreSQL to be a 'killer' of external tools would be ridiculously awesome.
gerry_shaw 12y ago
It might be simple or maybe it's OCD but I sure wish there was a way to reorder table columns in the table schema without having rebuild the columns.
- pbz 12y ago
  I came across this limitation within minutes of playing with pg. I couldn't believe that such a powerful tool would be missing something so basic.
swasheck 12y ago
parallelism is a big omission from pg for me. for all of the language features (and they're awesome), the thing that is the most glaring omission is parallelism. i understand it's been and on-again off-again project that seems difficult to implement. i still love pg, though so it's not a deal-breaker for me.

MySQL's non-standard ON DUPLICATE KEY feature is extremely useful, so any "upsert"-like feature would be a great thing to have.

j_s 12y ago
Hope you saw the discussion of 'UPSERTisms in Postgres' a while back: https://news.ycombinator.com/item?id=7620050 https://news.ycombinator.com/item?id=7620050

giulianob 12y ago

More functions to modify JSON columns in place without needing to retrieve it.

rch 12y ago
Does this thinking lead to feature parity with Redis EVAL?
- ddorian43 12y ago
  It will lead to feature parity with Mongodb.

buckbova 12y ago

The OP mentions gui, but what is the best gui/code editor/ide for postgres for linux? I found this list. I'll go through them. https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreS...

conradfr 12y ago
As a developer the database tool in Jetbrains' IDE covers the vast majority of my needs, in addition to the golden autocompletion in the code.
rch 12y ago
I've found pgAdmin to be sufficient for clicking through things here and there, but my sense is that DBA types are expecting more from the GUI than I am. Any insight into what those things might be would probably be helpful.
smilliken 12y ago
I've gone through that list before, but never find anything compelling enough to pull me away from psql (the built-in CLI client). I highly recommend learning psql first. It doesn't have every feature you might want, but it gets the basics right and is easy to integrate with your editor and other tools. As far as I can tell, it's what most professionals use.
- yangyang 12y ago
  I fully agree with this. I used pgAdmin for a while but have recently switched to psql and it's much more useful. In particular, learn the \ commands. It's extremely useful to be able to quickly look at the structure of a table, dump the source to a function and so on, from a command prompt rather than navigating trees with the mouse in pgAdmin. It uses readline where available so you get history, completion etc.
booty 12y ago
All I really want from a database IDE is awesome autocomplete for identifiers and keywords. (I consider SQL Server Management Studio's autocomplete to be great - if I could get that for Postgres, I'd be thrilled) You can get part of the way there by Sublime Text to run queries and browse results. In Sublime, it's easy - you can create a custom "build tool" with (essentially) just one line of config... you're basically just piping the current file's contents to command-line PSQL: https://www.youtube.com/watch?v=tPd4m3PLVqU https://www.youtube.com/watch?v=tPd4m3PLVqU Then I use the SublimeAllAutocomplete package. It works just like "regular" Sublime autocomplete except it gives you autocomplete suggestions from all open files, not just the current one - so if you have your DB schema dump in another window it will use that: https://github.com/alienhard/SublimeAllAutocomplete https://github.com/alienhard/SublimeAllAutocomplete Obviously, that doesn't really give you the smartest autocomplete ever but it's pretty productive for me. Tons of room for improvement though.
- asnyder 12y ago
  Has auto-complete and everything: http://www.sqlmanager.net/products/postgresql/manager http://www.sqlmanager.net/products/postgresql/manager
megaman821 12y ago
It is not free (or even that cheap) but Navicat is really good. It was worth it for me as much as I interact with databases.
- untog 12y ago
  Seconded. And the 'Essentials' version is only $9.99.
dataminded 12y ago
Aqua Data Studio is the best database IDE I have ever used.
deleted 12y ago
[deleted]
A_COMPUTER 12y ago
I use ToRA http://torasql.com/ http://torasql.com/ and/or Oracle SQLDeveloper. Both of them are Oracle DB tools that work fine with Postgres with the right drivers. pgadmin is intolerable.

jfb 12y ago

A better language than SQL in front of Postgres' careful and mature data storage engine?

collyw 12y ago
SQL syntax isn't great, but it is very powerful and good for what it does. What would you expect from a language improvement point of view? Its a declarative language so you can't expect a load of object oriented features. I found that once I learned to think in sets, the language doesn't bother me. There is a certain simplicity to it, that is fairly elegant.
- jfb 12y ago
  I would like a language more like Tutorial D; strictly set based (none of SQL's bags); without SQL's terrible, awful, no-good very bad 4GL syntax (I'd like something terser and more mathematical). It would be closed over composition, for instance, and would map more directly onto the relational calculus.
  - Shorel 12y ago
    Do you have any documents or RFC links about this language? I'm sure an specification is gladly appreciated.
    - dragonwriter 12y ago
      "Tutorial D", a particular pedagogical instance of a defined class of languages, is defined here: http://www.dcs.warwick.ac.uk/~hugh/TTM/Tutorial%20D-2013-05-23.pdf http://www.dcs.warwick.ac.uk/~hugh/TTM/Tutorial%20D-2013-05-... The requirements list for any "D" (that is, the definition of membership in the class of languages) is here in The Third Manifesto: http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-2013-02-07.pdf http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-2013-02-07.pdf
      - Shorel 12y ago

doxcf434 12y ago

Automatic failover that doesn't require a 3rd party HA tool would be great.

Qantourisc 12y ago
You'd still have to deal with the complexity, but somewhere else ...

saosebastiao 12y ago

I was really excited about the Citus column-store FDW...but it took me about an hour of using it before I realized it wasn't going to solve any of my problems any time soon. Queries with data larger than memory were slower than native tables. Compression didn't come anywhere close to the 2-4x improvement that was claimed. It doesn't support insert, update, or delete commands, let alone the insert from select that I was hoping to use. In order to convert a native table into a cstore table, you have to output to a file and then import using COPY, or hack it with a pipe to a COPY command. After a while, I realized that some of this was just the maturity of the extension, but a huge amount was the limitations of FDWs as a platform. And not only that, it seems that every other 3rd party FDW extension is either abandoned or ignored. Postgres is quite possibly the highest quality piece of software I use. I rate it higher than the Linux Kernel. But Postgres' historical approaches to extensibility have left a lot to be desired. Reading over this comment, I realize I probably sound like an entitled jackass, but I'll post it anyway. I really do wish I could contribute to Postgres, but learning C well enough to contribute is way out of reach for me at the moment. But I guess all I can say is that my (admittedly vague) wishlist for Postgres 9.5 is to enhance any and all features to support extensibility, including FDWs and Extensions.

buckbova 12y ago
> I really do wish I could contribute to Postgres, but learning C well enough to contribute is way out of reach for me at the moment. I'm sure they accept money in lieu of C skills. http://www.postgresql.org/about/donate/ http://www.postgresql.org/about/donate/
- saosebastiao 12y ago
  Good call...now I feel like even more of a jackass for not considering that :)
ozgune 12y ago
(Ozgun from Citus Data here) We're actively working on improving the cstore_fdw extension and your feedback is valuable to us. We're keeping a list of open issues on GitHub, and prioritize them for upcoming releases based on your comments. If you have any requests, please give us your feedback here: https://github.com/citusdata/cstore_fdw/issues https://github.com/citusdata/cstore_fdw/issues For performance and compression related questions, we'd also love to hear more from you and help if we can in any way: https://groups.google.com/forum/#!forum/cstore-users https://groups.google.com/forum/#!forum/cstore-users As PostgreSQL covers a huge feature set, we know that it will take time on our end to get there. Some of that will involve new foreign table features from PostgreSQL core, such as foreign indexes and foreign table inheritance. I just wanted to let you know that we are working hard in that journey to get there.

asnyder 12y ago

I don't think an "official" GUI is necessary. Rather it would simply hurt the community of paid/free tools that have built up around it. My favorite PostgreSQL GUI is: http://www.sqlmanager.net/products/postgresql/manager http://www.sqlmanager.net/products/postgresql/manager, it's excellent overall.

terhechte 12y ago
Excuse me, but I think this only runs on Windows. Many developers use Mac OS X or Linux. A wonderful cross platform solution would certainly be great. I'm using PGAdmin on a weekly basis, and while it offers more features than any other free solution, it fails at usability, native integration and also lacks support for the more advanced features of PostgreSQL.
- asnyder 12y ago
  It supports most of the advanced features of PostgreSQL, including functions, triggers, composite types, JSON, and most anything one can throw at it. Not to mention it obviously has a great SQL Editor with auto-complete for when you want to do drafts by hand, as I usually do. As far as it being windows only, I thought that was no longer an issue thanks to great tools like Parallels and VMWare Fusion. Can't you just open the app in that while still living in your OS? I honestly don't see much of an issue.
  - yangyang 12y ago
    Windows licences are not cheap.
    - asnyder 12y ago
      You can find genuine windows licenses pretty easily for about $50, and the windows store sells them for as low $69.99, but it's true, you do need a license. Though for all the time it's saved me over the years it's definitely worth it IMO. It would be nice if they made a cross platform version, I remember they used to support Linux somehow.

nasalgoat 12y ago

I'd sure like to have replication to one database while still allowing writes on another. As it stands now, if you're replicating from a master, you can't do anything else. This is something that MySQL could do over a decade ago.

jpitz 12y ago

My long-standing feature request is for index-organized-tables : http://www.postgresql.org/message-id/ac116f9a0907151917o6d87d1a8wf3ff2afb088eb47c@mail.gmail.com http://www.postgresql.org/message-id/ac116f9a0907151917o6d87...

buckbova 12y ago
Same as this todo item? Automatically maintain clustering on a table http://wiki.postgresql.org/wiki/Todo#CLUSTER http://wiki.postgresql.org/wiki/Todo#CLUSTER
- jpitz 12y ago
  In my view, periodic re-clustering isn't the same thing, though it may be good enough for most use cases. I'd label it "transactionally maintain clustering on a table"

Lennie 12y ago

On my list, adding even better support for fast full text search: https://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf https://wiki.postgresql.org/images/2/25/Full-text_search_in_... Many of the changes already went into 9.4 but for PostgreSQL to be a 'killer' of external tools would be ridiculously awesome.

gerry_shaw 12y ago

It might be simple or maybe it's OCD but I sure wish there was a way to reorder table columns in the table schema without having rebuild the columns.

pbz 12y ago
I came across this limitation within minutes of playing with pg. I couldn't believe that such a powerful tool would be missing something so basic.

swasheck 12y ago

parallelism is a big omission from pg for me. for all of the language features (and they're awesome), the thing that is the most glaring omission is parallelism. i understand it's been and on-again off-again project that seems difficult to implement. i still love pg, though so it's not a deal-breaker for me.