Postgres features and tips | Hacker News Reader

Postgres features and tips

craigkerstiens.com

190 pointscraigkerstiens37 comments

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.

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.

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

This is an old discussion on the dev list: 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).

I've used a CTE before specifically for its optimisation barrier. The query planner wasn't generating the query I knew would be faster, so I re-jiggered it a bit with a CTE and it dropped from 100ms to 1ms, as I expected. I know there's ways to fiddle with the query planner but sometimes it's just easier to tell postgres: "No, do it this way".

I've used them to replace multiple similar sub-selects to great performance advantage. That was a very awkward query though.

I've used CTE's in DB2 for hierarchical data using recursive SQL (8 ? years ago) and the performace was much better than using subselects.

I don't know much about Postgres, but for some things CTEs are a good tool.

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.

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.

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

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.

Can you tell him that I really enjoyed the book!

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

It's also only $5 at the moment.

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

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.

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.

I don't know if I'd call it good, but pgAdmin is a free/Free option that, well, works: http://www.pgadmin.org/

Here's a comment from myself just a few days ago that highlights a few of them - 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...

- JackDB (web based) - https://www.jackdb.com/

- SQL Pro for Postgres - http://www.hankinsoft.com/SQLProPostgres/

- PGAdmin - Slightly outdated but still feature rich and fully cross platform - http://www.pgadmin.org/

And of course there's always psql which is all CLI, but incredibly flexible.

http://wagonhq.com, which is in beta, is phenomenal

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

I'm waiting for SequelPro to add Postgres support before I make the switch. It's an amazing product.

Any idea when that will happen?

I recently bought a license to Postico (https://eggerapps.at/postico/) which is the successor to PG Commander. Switched from pgAdmin which is functional, but Postico's UI is nicer.

I'm experimenting with https://eggerapps.at/postico/ currently and it's pretty nice. Fairly similar to Sequel Pro in a lot of ways (which I miss too when in Postgres-world).

Navicat Essentials for Postgres on OSX costs $40 and is a robust if somewhat stripped down alternative.

JetBrains' DataGrip (or any of their IDE's if you have them) is really nice, I've been using it in tandem with PgAdmin (for general maintenance tasks since it has a GUI for some common stuff like permissions).

I'll chime in with another nod to Postico. I've been using it the last few months and really like it.

https://eggerapps.at/postico/

I 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/

DBeaver, not just Postgres but Oracle, MySQL, SQLite etc...

http://dbeaver.jkiss.org/

second this, great tool.

There are lots. The best is obviously the client I'm working on, Postico.

I've tried to compile a list of OS X Postgres clients in the docs for Postgres.app: http://postgresapp.com/documentation/gui-tools.html

If you know of any other, let me know.

There's also the list of GUI tools in the wiki: https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreS...

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

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.

oh! yeah I use \x only in situations when it wraps, so \x auto fits the bill.

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

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.

Reply on news.ycombinator.com