Postgres OnLine Journal

Coming from other database systems I found this intriguing, and did some googling. Turns out there are historical reasons that PG has functionally overlapping text and varchar(n) datatypes, where text was the original still the best datatype as detailed in this thread, whereas varchar was added for ANSI compatibility, and does not enjoy the same system support (changing length is a pain compared to other engines).

PG is going to be the engine of choice for my next project, so I'm happy to learn about this quirk compared to other engines. Which doesn't mean I'm all that happy about it. I'm really in agreement with this post from 2010 (still current AFAICT) that wishes the support for varchar(n) would be better, for readability and cross-engine compatibility nothing else [0]

[0] https://www.postgresonline.com/journal/archives/154-In-Defense-of-varcharx.html

Good point, and a good reason to learn how your DBMS of choice does common operations. Most query planners will explicitly tell you what yours is doing. I remember even back a long time ago MSSQL 7 had a nice GUI for its "query explain" and pgAdmin also gives a really nice graphical view of query plans, which really helps visualize when it is looping, doing hash lookups, etc. I suppose that would be part of the recommendation for learning: use a graphical query plan view if you can find a client that supports it :)

http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html (example pictures if anyone reading this hasn't seen it before), it is pretty cool.

http://www.heidisql.com/forum.php?t=7025

It looks like the author is entertaining the idea of postgres support (if you read further down the thread). A timely donation might make it happen.

Everyone has their own preferences for administration tools, so it's hard to find a one-to-one replacement. You might find something at one of these places though:

http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
http://www.postgresonline.com/journal/index.php?/archives/133-Database-Administration,-Reporting,-and-Light-application-development.html

My favorite tool for this kind of analysis was pgAdmin3, which had a very nice diagramming output for EXPLAIN ANALYZE which you can see here [0]. Hovering over the various images in the diagram would display their time, rows, and which statement caused them.

There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance [1].

[0] http://www.postgresonline.com/images/journal/explain_plan_5.png

[1] http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html

Quite a few:

http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

http://www.postgresonline.com/journal/index.php?/archives/133-Database-Administration,-Reporting,-and-Light-application-development.html

Navicat is a good one if you don't mind paying $80-$250 for the personal version. http://www.navicat.com/