

PostgreSQL 9.2: Full-throttle Database - mace
http://www.pgcon.org/2012/schedule/attachments/230_92_grand_prix.pdf

======
sitharus
If my experience with other DBMS (mostly SQL Server) is anything to go by,
index only scans will do wonders for append-mostly workloads, and the new
transactional scaling looks really good.

I can't wait to try this out, alas I use debian so it's a couple of years out
:/

Also my inner grand prix follower is slightly irked by the varying years of
the graphics.

~~~
photon137
> index only scans will do wonders for append-mostly workloads

Won't updates be actually _slower_ once indexes are added to take advantage of
index-only scans?

Another issue that I can see comes not from having the index-only scan feature
(which is cool, though belated) but having a query planner intelligent enough
to figure out which index to hit - ie query optimization.

If the columns used in WHERE clause do not contain the appropriate columns
(particularly for non-clustered indexes), shouldn't index hints be supported
to help pick up the relevant index or would we end up with horrible execution
plans leading to tablescans?([1] - I see this as a case of devs overriding
users)

[1]: [http://it.toolbox.com/blogs/database-soup/why-postgresql-
doe...](http://it.toolbox.com/blogs/database-soup/why-postgresql-doesnt-have-
query-hints-44121)

~~~
DrJokepu
This is a horribly naive theory (I have no idea what I'm talking about) but if
you have foreign keys you need to get those indexed anyway (otherwise inserts
or updates will need to do a sequential scan), and with index-only scans, if
the referenced key exists in the index, the tuple won't have to be visited,
hence inserts and updates getting faster in tables with foreign keys?

~~~
fdr
I think that's a very keen insight. There may be a detail that gets in the
way, but nothing comes to mind at the moment. Did you know that PostgreSQL's
referential integrity checks are -- in the most literal sense -- implemented
as triggers that construct SQL, and then plan and execute them just like a
client program?

[http://doxygen.postgresql.org/ri__triggers_8c_source.html#l0...](http://doxygen.postgresql.org/ri__triggers_8c_source.html#l00401)

I don't think anyone has measured the effect, which seems it would be greatest
on tables with wide rows, whereby the wide rows would cause cache thrashing
but the smaller index would not.

------
craigkerstiens
Here's the corresponding video to these slides from a separate event that
gives an overview of features in 9.2: <http://vimeo.com/49337386>

------
ceyhunkazel
Although PostgreSQL is featured database enterprises still using mainly SQL
Server or Oracle. [http://blog.protegra.com/2012/06/22/sql-
server-2012-cheaper-...](http://blog.protegra.com/2012/06/22/sql-
server-2012-cheaper-than-open-source-database-options/) What can we do for
wider adoptation of PostgreSQL?

~~~
sandGorgon
The simple answer is to make better tools. There is still no single, awesome,
dedicated tool to migrate from MySQL to Postgresql. (side note: Did you know
that Postgres can actually act as a replication slave for SQL Server ? ).

Again, there is no official Mysql Workbench for Postgres, let alone things
like SequelPro. (although a member on the Indian HN - Hackerstreet - is
building one at <http://pgxplorer.com/>)

Second - documentation _as compared_ to other database vendors. No, this is
not flamebait - by itself, it is very good and adequate. But for people used
to MySQL, SQL Server - it feels...scary.

Check out <http://dev.mysql.com/doc/> (sidenote: has docs in epub format as
well) vs <http://www.postgresql.org/docs/>

~~~
fdr
I basically agree with you, _except_ in regard to documentation, of which I
find exceptional in PostgreSQL, even compared to other database systems.

Your toplevel comparison of the index of both manuals is not very informative
to me. What do you feel is missing from PostgreSQL's manual that you find in
other documentation sources?

~~~
sandGorgon
I think it is hard for me to explain, because actually -> I <\- prefer the
Postgres documentation. However, I realized that was because it is
fundamentally a man page. For someone who is exploring Postgres, or for
someone who comes from other databases, it is way too abridged.

A simple example is the getting started part
[http://dev.mysql.com/doc/refman/5.5/en/creating-
database.htm...](http://dev.mysql.com/doc/refman/5.5/en/creating-
database.html) vs [http://www.postgresql.org/docs/9.2/interactive/tutorial-
crea...](http://www.postgresql.org/docs/9.2/interactive/tutorial-
createdb.html) Notice that Postgres has switched to using system commands like
"createdb" while mysql sticks to SQL - there is a perfectly viable way to do
this in Postgres using SQL, but perhaps the author didnt want to go directly
to explaining template databases (which is simpler IMHO).

Go and ask someone who is new to both Mysql and Postgres to read these
sections. Based on the distro they are on, you have completely lost them
between the complexities of the OS vs the database Here is an example of the
kind of confusions that result - [http://superuser.com/questions/220771/how-
can-i-create-a-dat...](http://superuser.com/questions/220771/how-can-i-create-
a-database-in-postgresql-on-windows-7)

Check this section out - <http://dev.mysql.com/doc/refman/5.5/en/privilege-
system.html>. The reason why I am picking up client authentication example is
that this where 90% of beginner postgres problems occur: they happened to me
and they happen to every other first timer. Is postgres more complicated ? No,
but you need to explain it better. I find this to be a much more useful
tutorial to point first timers to, even if they are not on Ubuntu :
<https://help.ubuntu.com/10.04/serverguide/postgresql.html>

~~~
fdr
I would say that I have the exact opposite opinion of you: the manual is not
so great at narration because it is really extensive. Consider the example you
gave: the postgres tutorial makes no assumption that you can even connect to
the database or have the 'createdb' binary, and fairly exhaustively addresses
each error case, whereas the MySQL tutorial at this stage presume you have a
the mysql binaries and database session live already. In the stage of the
tutorial that is most similar to the 'createdb' step is more similar to this
page:

[http://dev.mysql.com/doc/refman/5.5/en/connecting-
disconnect...](http://dev.mysql.com/doc/refman/5.5/en/connecting-
disconnecting.html)

Whereby it's presumed that if you cannot get a connection you should talk to
your administrator and give up. That may not be bad advice: the exhaustive
covering of error cases by postgres tutorial may detract from rather than
enhance the exercise.

I agree that Postgres' default authentication system one uses for development
-- based on the account name -- probably deserves a different kind of
explanation. It's an approach considerably better that most other software
that it's probably worth keeping (especially for someone who hates more
passwords), in spite of figuring out a way to explain it.

All in all, I basically don't agree with the notion that it is "abridged." It
could be improved, but I think you are repeatedly using these words describing
the scantness of the documentation whereas I think it is borderline over-
detailed as it is. Perhaps a different kind of information presentation is
required, is what I think.

Although still nascent, this lack of narrative documentation is probably why
Craig started writing <http://postgresguide.com/>. This doesn't do a good job
of explaining how to install the software, but is a lot more conversational
than the Postgres documentation:

<http://postgresguide.com/SQL/select.html>

vs

<http://www.postgresql.org/docs/9.2/static/sql-select.html>

Clearly, one is more complete in some sense, but it's not going to help the
beginner.

------
wolfeidau
Great talk, love the commentary relating the the index contributions by the
Russian contributors really impressive work.

Read and write performance improvements are something that will directly
improve even current applications.

Talks like this provide valuable insight into what is going on in this amazing
project.

------
beggi
Does anyone have accompanying video of the talk?

~~~
mrweasel
<http://www.justin.tv/sfpug/b/331890663>

