
Dear Postgres - Twisell
http://www.craigkerstiens.com/2017/10/12/dear-postgres/
======
randlet
Nice to see a "Dear <open source project>" that was a full blown love letter
instead of a critique!

~~~
dotdi
I was dreading the big "BUT + <arbitrary rant>", and indeed, it never came.

I share many of the views of the author. It's a great piece of software.

~~~
Finnucane
Yeah, I could quibble about how the XML support in Postgres is not really
equal to an XML database--it's still limited to xpath/xslt 1.0, and no xquery
--but the fact that is has any at all is still handy and useful. I mean, I
guess it would be too much to ask for it to do _everything_.

~~~
kuschku
And if you need that, you can easily write a function to do it in one of
dozens of languages – from pl/SQL to pl/V8, or even pl/C – or even directly
write an extension (and I think there are already a few for that purpose)

------
pmontra
> In the early years you had the standard format of index b-tree that most
> database engines leveraged. Then quietly but confidently you started adding
> more. Then came K-nearest neighbor, generalized inverted indexes (GIN), and
> generalized search-tree (GiST), only to be followed by space partitioned
> GiST and block range indexes (BRIN). Now the only question is which do I
> use?

I also wonder which index is appropriate for which case. I googled out this
[1] and obviously all the chapters from 62 to 65 at [2]. I'm putting them on
my reading list and posting them here just in case somebody else is asking the
same question.

[1] [https://devcenter.heroku.com/articles/postgresql-
indexes](https://devcenter.heroku.com/articles/postgresql-indexes)

[2]
[https://www.postgresql.org/docs/current/static/internals.htm...](https://www.postgresql.org/docs/current/static/internals.html)

~~~
Alex3917
B-tree for most ‘normal’ data. You mostly only need the other indexes for
cases where your data isn’t just simple values, like if your column contains
stemmed search terms or arrays or JSON.

If you’re using ORM, then your ORM documentation should be telling you which
index is the most appropriate if it isn’t a b-tree.

There are also some corner cases where you can see a speed up if you have tons
of data that’s being written in an append-only manner, but for the most part
b-trees are the way to go for chars, ints, bools, etc.

~~~
rjeli
I haven't found an explanation of when to use BRIN indexes that works with my
brain. anyone have a good link?

~~~
_wmd
I wrote [http://pythonsweetness.tumblr.com/post/119568339102/block-
ra...](http://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-
indexes-in-postgresql-95) because I had the same problem, maybe see if it
works for you

~~~
rjeli
Awesome, thank you

------
majewsky
At the top:

> I’ve always felt an affinity for you in my 9 years of working with you.

At the bottom:

> Postgres, I just want to say thank you for the past ten years together.

It's only logical to assume that it took him a year to write the letter. :)

~~~
dotdi
> It's only logical to assume that it took him a year to write the letter. :)

In German we say "gut Ding braucht Weile", i.e. "A good thing takes time".

------
mmartinson
And specifically, thank you Postgres contributors and maintainers for your
time and energy. You've done your community and peers an invaluable service.

------
zeveb
Postgres falls into the category of things which should be considered
defaults. Yes, sometimes an alternative choice is indeed the correct one, but
generally if a project isn't using Postgres it's wrong.

It really is excellent.

------
tapoxi
Not critiquing but more curious, why does Postgres seem to get so much love
while MariaDB is mostly ignored on HN?

~~~
ape4
I prefer Mysql/MariaDB's `show tables` vs Postgres' `\dt`. And other commands.
More like sql.

~~~
kuschku
Postgres has similar SQL commands.

Commands that start with \ are shortcuts in the psql client that are
translated into SQL commands.

You can always use SELECT * FROM pg_tables; and operate on those tables as if
it was a normal SQL relation (because it is).

Additionally, if you use psql -E, it shows you the SQL it runs for such
shortcuts. For \d (Show relations) it runs:

    
    
        SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','v','m','S','f','')
              AND n.nspname <> 'pg_catalog'
              AND n.nspname <> 'information_schema'
              AND n.nspname !~ '^pg_toast'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2;

~~~
ape4
Good thing there is a shortcut :)

Can you do `alter table` on pg_tables?

~~~
kuschku
pg_tables is a view, which only loads data from a few other internal tables,
but you can modify many of them, yes. (And many extensions do that).

Specifically, pg_tables is defined as

    
    
        CREATE VIEW pg_tables AS SELECT n.nspname AS schemaname,
            c.relname AS tablename,
            pg_get_userbyid(c.relowner) AS tableowner,
            t.spcname AS tablespace,
            c.relhasindex AS hasindexes,
            c.relhasrules AS hasrules,
            c.relhastriggers AS hastriggers,
            c.relrowsecurity AS rowsecurity
           FROM ((pg_class c
             LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
             LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
          WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]));

~~~
ape4
Cool. I like the Posgres is written in Postgres. Of course, the normal
commands for changing the schema are a better approach.

------
Chiba-City
Stonebreaker's Ingres Papers revenge. The Berkeley IT crowd was remarkably
generative of good and enduring outcomes. Their Free/Open Cathedral efforts
still anchor our frothier bazaars. Great read.

There are vast real world application domains globally where Postgres one stop
shopping for both infrastructure, dev targeting, training and MRO is the clear
win with negative returns on BFC better faster cheaper.

Good enough and comfortably familiar haptics is really important. The worldly
focus brings us worldly wins. We apply the applied math tools to human
concerns to find jobs solving problems and not get jobs to solve our problems.
Balancing those two sets of problem is the act.

------
ejlangev
+1 it's a great piece of software that delivers tremendous value to users and
continues to add amazing features every year. Cheers and thank you to all the
maintainers and contributors.

------
Wonnk13
any good resources for going deeper on the analytical capabilities of
postgres? ya know, beyond basic window functions. How can i leverage stored
procs etc?

~~~
cpburns2009
Look into PL/pgSQL [1]. PostgreSQL also offers a few other languages such as
Python and Perl [2]. Please note that the individual extensions have to be
installed.

[1]:
[https://www.postgresql.org/docs/current/static/plpgsql.html](https://www.postgresql.org/docs/current/static/plpgsql.html)

[2]: [https://www.postgresql.org/docs/current/static/server-
progra...](https://www.postgresql.org/docs/current/static/server-
programming.html)

