7 ms·
Postgres features and tips
- netcraft 11y agoI'm changing jobs and finally getting to switch to postgres for my day job from mssql for years and years - things like array_agg, jsonb and range types have me salivating.
- mhd 11y agoI wish someone would do the advanced Postgres version of the standard RDBMS "library" example as a book, going beyond just joins and simple window functions to arrays, json, notifications etc.
- monksy 11y agoI would suggest reading the High Perofrmance Postgresql book. It's one of the rare good PacktPub books: https://www.packtpub.com/big-data-and-business-intelligence/postgresql-90-high-performance https://www.packtpub.com/big-data-and-business-intelligence/...
- craigkerstiens 11y agoGreg Smith, the author is a former colleague and friend. His book on the performance side is definitely one of the better ones out there, but it's a few years old now and definitely more about tuning disks and storage more so than advanced usage. Sadly, I don't have a better one to recommend for the advanced user.
- monksy 11y agoCan you tell him that I really enjoyed the book!
- edwinnathaniel 11y agoDefinitely there's a market for postgresql books. Specifically, books with laddering type of learning experience (e.g.: beginner, intermediate, administration, developer, etc).
- ChristianGeek 11y agoIt's also only $5 at the moment.
- adamzegelin 11y agoWhy is everyone pushing CTE's for "readability" for Postgres queries? I've seen this come up multiple times in the past few months. CTE's (WITH clauses) are optimisation barriers — the query planner in Postgres won't inline CTEs, but instead will materialise their result into a temporary table first, preventing qual (WHERE clause) push-down and other optimisations. I know this because it bit me, hard — I went nuts and wrote a lot of the queries in a new system to use WITH clauses, for readability. Performance was atrocious, and required refactoring to inline all the WITH clauses as sub-SELECTS. CTE's are useful, but should not be used to improve readability.
- netcraft 11y agoCTE's should be used to improve readability, they shouldn't be used if performance is a concern. There are many queries where the difference will be negligible if even noticeable. We should write code for humans first. That being said, does anyone know if there are any plans to address them being optimisation barriers, or what the challenges are? Seems like even a naive rewrite into subselects before even creating the plan would reap a lot of benefits.
- keeperofdakeys 11y agoI'm pretty sure that the optimisation barrier is a 'feature'. This can actually be handy, especially when replacing a complex subquery that the query planner turns into a nested loop.
- anonetal 11y agoThis is an old discussion on the dev list: http://www.postgresql.org/message-id/201209191305.44674.db@kavod.com http://www.postgresql.org/message-id/201209191305.44674.db@k... .. which indicates that reliance on them being optimization fences may be too ingrained to change. I had assumed that they were treated as sub-selects (given the generally advanced optimizer that PostgreSQL has), and was quite surprised to realize that they aren't. I think this is a major shortcoming of PostgreSQL, especially given the readability advantages of CTEs makes them quite attractive to use (I usually recommend heavy use of CTEs to beginning SQL-ers).
- asherkin 11y agoThis could really do with a definition of the several acronyms used.
- netcraft 11y agoWhich ones specifically, im sure commenters here could help. ETL = Extract Transform Load, a common pattern when transferring data from one system to another. GIS = Geographical Information System, having to do with positions in space, generally lat/lng and mapping concerns.
- blowski 11y agoAre there any good Postgres GUIs for OSX? SequelPro is incredible - easy to use, beautiful, reliable, powerful - but it only works with MySQL (or MariaDB). I've tried Toad but it's very buggy and ugly. The AppStore has SQLPro for Postgres, but I've not heard any reviews, so I'm reticent to spend $30. I've used a Navicat trial for MySQL and it was good but costs around $200.
- andlarry 11y agoI recommend and use sqlworkbench, which is great as long as you don't work for one of the govts disallowed by the license (which is apache otherwise) http://www.sql-workbench.net/ http://www.sql-workbench.net/
- dorfsmay 11y agoDBeaver, not just Postgres but Oracle, MySQL, SQLite etc... http://dbeaver.jkiss.org/ http://dbeaver.jkiss.org/
- reactor 11y agosecond this, great tool.
- nkantar 11y agoI don't know if I'd call it good, but pgAdmin is a free/Free option that, well, works: http://www.pgadmin.org/ http://www.pgadmin.org/
- craigkerstiens 11y agoHere's a comment from myself just a few days ago that highlights a few of them - https://news.ycombinator.com/item?id=10804931 https://news.ycombinator.com/item?id=10804931 And actually content copied as well to make it easier to digest: If you're looking for a list of other clients some of the others ones include: - Postico OSX - https://itunes.apple.com/us/app/postico/id1031280567?ls=1&mt=12 https://itunes.apple.com/us/app/postico/id1031280567?ls=1&mt... - JackDB (web based) - https://www.jackdb.com/ https://www.jackdb.com/ - SQL Pro for Postgres - http://www.hankinsoft.com/SQLProPostgres/ http://www.hankinsoft.com/SQLProPostgres/ - PGAdmin - Slightly outdated but still feature rich and fully cross platform - http://www.pgadmin.org/ http://www.pgadmin.org/ And of course there's always psql which is all CLI, but incredibly flexible.
- kevinqi 11y agoAs someone who always uses \x, having it become a default setting in .psqlrc is a useful one.
- craigkerstiens 11y agoAre you using \x or \x auto? \x auto will automatically format it to the size of your screen for when things wrap or don't. I've found it's more often than not what I actually want.
- kevinqi 11y agooh! yeah I use \x only in situations when it wraps, so \x auto fits the bill.
- ris 11y agoI especially second the "arrays" advice. Arrays of some datatypes (notably integers) have GIN and GiST index support for membership tests. This makes them good for denormalizing many-valued joins for fast lookups. Window functions can be a big win too.
- dizzystar 11y agoMy favorite feature is explain (analyze, buffers, verbose). It took some work to learn how to understand it, but I don't think anything else had such a large payoff. I also like the information and catalog schemas. Not something I use all the time, but all of the tables in each schema are worth exploring and seeing what information can be found. When they are needed, they can save a lot of time. Call me crazy, but I also like using PL/pgSQL. A helpful function I have takes two table or view name arguments and compares if they are the same result set. Total life-saver and a major time boost for redesigning queries and checking to make sure table transfers won't cause corruption. I love the error messages. They are so clear and always correct. I've found myself reading them and thinking "that can't be right" and sure enough, I was wrong. Configuration is a breeze. The hba and conf files are fully documented and simple to understand. The location of the files are sensible and easy to find. Seeing how other database systems deal with configuration is eye-opening to say the least. Last but not least, the documentation is just incredible. I've been asked about good PostgreSQL books, and I always say "read the docs; you'll see why there are few books around."