
Postgres features and tips - craigkerstiens
http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/
======
adamzegelin
Why 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
CTE'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.

~~~
anonetal
This is an old discussion on the dev list: [http://www.postgresql.org/message-
id/201209191305.44674.db@k...](http://www.postgresql.org/message-
id/201209191305.44674.db@kavod.com)

.. 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).

------
netcraft
I'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
I 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
I 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/...](https://www.packtpub.com/big-data-and-business-
intelligence/postgresql-90-high-performance)

~~~
craigkerstiens
Greg 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.

~~~
edwinnathaniel
Definitely there's a market for postgresql books. Specifically, books with
laddering type of learning experience (e.g.: beginner, intermediate,
administration, developer, etc).

------
dizzystar
My 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."

------
ris
I 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.

------
blowski
Are 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.

~~~
craigkerstiens
Here'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...](https://itunes.apple.com/us/app/postico/id1031280567?ls=1&mt=12)

\- 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.

~~~
stevenjohns
+1 for PGAdmin. I've been using it for several weeks and it's been pretty
good, although it does have a couple of flaws. One of them appears to be an
OSX issue, though, where opening tables sometimes throws part of the window
(the top bar) outside of the screen (and the only way I've found to fix it is
to click on Window > Zoom).

------
kevinqi
As someone who always uses \x, having it become a default setting in .psqlrc
is a useful one.

~~~
craigkerstiens
Are 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
oh! yeah I use \x only in situations when it wraps, so \x auto fits the bill.

------
asherkin
This could really do with a definition of the several acronyms used.

~~~
netcraft
Which 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.

