P
Postgres OnLine Journal
postgresonline.com · Company blog · on HN since 2008
A company journal of PostgreSQL tips, tutorials, and PostGIS articles, covering features like LTree, arrays, and query plans.
What HN saysHN commenters link it for its guide to reading pgAdmin graphical explain plans, which one calls amazing yet rarely talked about.
29HN people shared it
12linked it in comments
17stories submitted
89story points
2 years agolast shared
2008people per year2026
Most-shared posts
- 127 Reading PgAdmin Graphical Explain Plans3 people · first shared 2013
- 2Index2 people · first shared 2013
- 3PostGIS 2.2 leveraging power of PostgreSQL 9.51 people · 47 points · first shared 2015 · 1 comments on HN
- 4Using LTree to Represent and Query Hierarchy and Tree Structures1 people · 10 points · first shared 2017 · discussion on HN
- 5The Pure Relational Database is dead1 people · 5 points · first shared 2011 · discussion on HN
- 6Schemas vs. Schemaless structures and The PostgreSQL Type Farm1 people · 4 points · first shared 2012 · discussion on HN
- 7Cross Compare of SQL Server, MySQL, and PostgreSQL1 people · 3 points · first shared 2008 · 1 comments on HN
- 8What is new in PostgreSQL 9.01 people · 3 points · first shared 2010 · discussion on HN
- 9PostGIS 1.3.6 is out and new upcoming PostGIS book1 people · 2 points · first shared 2009 · discussion on HN
- 10Biggest Obstacle to PostgreSQL Adoption: It is not Database X1 people · 2 points · first shared 2012 · discussion on HN
- 11What can PostgreSQL learn from MySQL?1 people · 2 points · first shared 2008 · discussion on HN
- 12POSTGRESQL ARRAY: THE ANY AND CONTAINS TRICK1 people · 2 points · first shared 2011 · discussion on HN
- 13First Look at pgAdmin 41 people · 2 points · first shared 2016 · discussion on HN
- 14Some of My Favorite PostgreSQLisms1 people · 2 points · first shared 2024 · discussion on HN
- 15Free and Open Source Software for Geospatial - FOSS4G 2011- PostGIS galore1 people · 1 points · first shared 2011 · discussion on HN
What HN people wrote when they linked it
stareatgoats · 2019-05-03
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-varc…
Dangeranger · 2019-02-21
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-Grap…
bitexploder · 2018-04-15
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-Grap… (example pictures if anyone reading this hasn't seen it before), it is pretty cool.
No1 · 2013-01-18
Some good resources there.
Don't forget to mention PgAdmin III has a built-in graphical explain analyze, which is absolutely amazing yet rarely talked about.
http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Grap…
sciurus · 2012-09-28
I've had some great experiences, e.g. Gregory Smith's PostgreSQL 9.0 High Performance. When considering purchasing a book on a niche topic like Magneto or PostgreSQL, I think it's worth waiting for prominent community members to get review copies and write about their impression, e.g. http://www.postgresonline.com/journal/archives/192-PostgreSQL-9-High-P…
robe2 · 2012-07-22
Our site is dedicated to that:
http://www.postgresonline.com/
Also check out our new book which came out this month.
http://shop.oreilly.com/product/0636920025061.do
Its primary target audience are people coming from other relational databases.
kaveh_h · 2023-07-13
Nice article, although the part on fuzzy search not being supported by PostgreSQL is partly incorrect, by using the pg_trgm extension and an GIN trigram index on a text column/expression we can do fuzzy search like the example in this article:
https://www.postgresonline.com/article_pfriendly/169.html
jimktrains2 · 2015-11-11
The raw data comes in a fixed-width plain-text table.
http://www.postgresonline.com/journal/archives/157-Import-fixed-width-…
bshimmin · 2015-06-18
Here's a nice one-page cheat sheet, of which about 75% you will probably never need to use: http://www.postgresonline.com/downloads/special_feature/postgresql83_p…
SkyMarshal · 2013-10-03
Quite a few:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tool…
http://www.postgresonline.com/journal/index.php?/archives/133-Database…
Navicat is a good one if you don't mind paying $80-$250 for the personal version. http://www.navicat.com/
jeffdavis · 2013-09-09
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_Tool…
http://www.postgresonline.com/journal/index.php?/archives/133-Database…
pella · 2013-08-18
see comments:
http://www.postgresonline.com/journal/archives/303-Which-PostGIS-shoul…
problems:
- non-compatible licenses
- different release and upgrade cycle