PostgreSQL 9.0 High Performance

What Hacker News says

1. psMar 2019
   The point is that the pgtune could and should force you to check the config options in documentation and it actually addresses your current setup. RTFM.

   Values in the post are pretty arbitrary and useless as their comments in postgresql.conf used to be.

   I am using PostgreSQL in production since 7.4 and the best source of performance tuning tips for me has been PostgreSQL 9.0 High Performance by Gregory Smith. These days PostgreSQL wiki is pretty good as well.

   Show morethread
2. lastofusMar 2018
   PostgreSQL is hands down the way to go when you need a relational DB (which is 99% of the time).

   Some features to read up on:

     * COALESCE, CASE
     * Aggregate functions + GROUP BY + HAVING
     * Subqueries
     * The LEFT/RIGHT/INNER JOINs, and things you can achieve with JOIN conditions
     * Different index types and how to use them
     * Different constraint types and how they will save your ass
     * Using temporary tables
     * Views + Materialized Views
     * CTEs, including recursive
     * Full text search
     * Trigger functions
     * Window function
     * DISTINCT ON vs DISTINCT & GROUP BY (a PG extension - why it's useful)
     * Partial/functional indexes
     * Schemas - especially useful for multi-tenancy
     * COPY to load in datasets fast 
     * Transaction isolation levels
     * Manual locking
     * EXPLAIN + EXPLAIN ANALYZE
   

   The above is just scratching the surface, but it's what I've found most useful doing web dev. The PG manual covers all these things well, along with supplemental stuff that can be googled. If you read the PG manual cover to cover, your mind you be blown by what the DB can accomplish.

   For learning about performance and what's going on under the hood, check out "PostgreSQL 9.0 High Performance".

   Most of the really complex queries I've written consist of writing several CTEs/sub-queries/views/temp tables which create a number of intermediate tables containing transformed data, which in turn are available to the final top level query. There's a lot of overlap with functional programming here: build small queries, and use them as reusable building blocks.

   If you find yourself needing to dynamically generate complicated queries, instead of trying to construct them w/ string concatenation in your app, try building temporary views, and then referencing those views in later queries, as opposed to doing it all in one go. I usually have one function/method per view in the app, and I can call the needed functions to create the foundation for a later query.

   Show morethread
3. adamnemecekJan 2017
   You should try to understand how databases in general work, it will help you with your query writing.

   One thing you have to realize is that once you get a little advanced, you have to get to the details of the single SQL implementations, it's not about SQL but about Postgres.

   I've found these books really valuable

   # SQL Performance Explained Everything Developers Need to Know about SQL Performance

   https://www.amazon.com/Performance-Explained-Everything-Deve...

   This book fundamentally talks about how to effectively use and leverage the SQL indices. Talks about all the important implementations (Postgres, MySQL, Oracle, SQL Server).

   # Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems

   https://www.amazon.com/Designing-Data-Intensive-Applications...

   This book gets mentioned a bunch around here and for a good reason. There aren't too many concrete resources on making your systems "webscale" and this one is really good.

   # PostgreSQL 9.0 High Performance

   https://www.amazon.com/PostgreSQL-High-Performance-Gregory-S...

   Discusses all the different settings and tweaks you can do in Postgres. It's crazy how much of a perf gain you can get just by twiddling the parameters of the database, i.e. all the tricks you can do when the single instances are bottle necks.

   There's a similar book for MySQL https://www.amazon.com/High-Performance-MySQL-Optimization-R...

   # PostgreSQL 9 High Availability Cookbook

   https://www.amazon.com/PostgreSQL-9-High-Availability-Cookbo...

   Discusses how do you go from 1 Postgres instance to 1+ instance. Talks about replication, monitoring, cluster management, avoiding downtime etc i.e. all the tricks you can do to manage multiple instances. Again there's a similar book for MySQL https://www.amazon.com/MySQL-High-Availability-Building-Cent...

   Last but not least check out the postgres documentation, people consider it a standard of what good documentation looks like https://www.postgresql.org/docs/9.6/static/index.html

   Also last but not least, read up on relational algebra (the foundation of SQL) https://en.wikipedia.org/wiki/Relational_algebra. I've always found SQL to be extremely verbose (the syntax reminds me of idk COBOL or smth) but there's another query language called Datalog, that's for our purposes similar to SQL but the syntax is much more legible.

   E.g. check out these snippets from these slides (page 29) (and check out the whole class too)

   https://pages.iai.uni-bonn.de/manthey_rainer/IIS_1617/IIS201...

   Datalog:

   s(X) <- p(X,Y).

   s(X) <- r(Y,X).

   t(X,Y,Z) <- p(X,Y), r(Y,Z).

   w(X) <- s(X), not q(X).

   SQL:

   CREATE VIEW s AS (SELECT a FROM p)

   UNION

   (SELECT b FROM r);

   CREATE VIEW t AS

   SELECT a, b, c

   FROM p, r

   WHERE p.b = r.a,

   CREATE VIEW w AS (TABLE s)

   MINUS (TABLE q);

   Show morethread
4. jpitzDec 2016
   That is a great resource.

   Here are some I've used:

   Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL Querying https://books.google.com/books?id=FZlCAwAAQBAJ&printsec=fron...

   Good resource for designing high performing queries and investigating performance problems.

   PostgreSQL 9.0: High Performance https://books.google.com/books?id=OWOAu0GcsqoC&printsec=fron...

   Greg lays out a great foundation for doing performance analysis that is largely applicable to any rdbms platform.

   Optimizing Oracle Performance https://books.google.com/books?id=D7ElCwAAQBAJ&printsec=fron...

   Another great book that explains the process of approaching and solving performance problems.

   Joe Celko's SQL For Smarties https://books.google.com/books?id=i-19BAAAQBAJ&printsec=fron...

   Everyone writing SQL should read Joe.

   Show morethread
5. rchJul 2016
   I also like PostgreSQL 9.0 High Performance by Gregory Smith.
   thread
6. akurilinMay 2014
   The PostgreSQL 9.0 High Performance book has been great for us, goes in depth into most interest variables that you can tweak on your instance to make sure you're making the most out of your machine's resources: http://www.packtpub.com/postgresql-90-high-performance/book

   A bit of a lengthy read, but worth it.

   thread