Top products from r/PostgreSQL

You never "need" to use a graph database. Relational databases are capable of storing graph data in them. The real question is which is more advantageous for your use case. Since you haven't specified anything about your use case it's hard to give any advice, but here are some things to think about:

1. Graph databases are often not ACID compliant.
   
2. Graph databases are not as well supported and generally have smaller communities, they aren't bad communities mind you, just smaller.
   
3. Graph databases often have less tooling. (UI, backup tools, Replication Tools.)
   
4. Graph databases don't have built in support in Cloud Environments, so you would have to manage your own boxes.
   
5. Graph databases are good at Graphs, but can't do normal relational stuff well or at all.
   
   If you have a graph with millions or billions of nodes in it, a Graph database will be faster doing most "graph" things, however if you have mixed use data. where you want to treat it like a graph sometimes and just rows of data other times you will have a hard time with a graph database.
   
   There are many great ways to do graphs in an RDBMS. The book by Joe Selko https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/0123877334 is probably the best book on the subject, it covers all the different ways you can store a graph in a SQL database. Thanks to Postgres Recursive CTEs usually an adjacency table is the easiest and best option but sometimes you will want something else for a specific use case. Also for very simple trees you can often use the ltree extension. Ltree stores the path through the tree to the node in the label and has fast search and other functions thanks to Postgres' GIST indexes.
   
   One last thing, If you are implementing graphs in Postgres and your recursive CTE's are slowing down one thing you can do to really speed things up is build a materialized view of your graph with the data denormalized.

By now already dated but a good top-to-bottom introduction into Postgres in the real world is PostgreSQL 9.0 High Performance.

Most of the things Postgres does is exposed via system tables & views - for example pg_stat_activity & pg_locks.

The rest of the documentation is great as well, give it a read.

If you are new to system administration & architecture, you may want to put Designing data intensive applications on your shopping list as well to broaden your horizon.

If you have Postgres-specific questions you can ask them here or reach out to the community.

edit: fixed links

The Database Hackers Handbook is not specific to PostgreSQL, but it's quite nice at introducing several databases from a security standpoint.

I've only read that book and the PostgreSQL documentation.

I have this book, it's pretty thorough: http://www.amazon.co.uk/gp/product/0672327562/

Ofcourse you should also study the Postgres official docs, at http://www.postgresql.org/docs/

I don't know any books on database design or theory, does anyone have good tips?

I think this book gives the best overview of core functionality:-

https://www.amazon.co.uk/PostgreSQL-Running-Regina-O-Obe/dp/1449373194/ref=sr_1_1?ie=UTF8&qid=1479283169&sr=8-1&keywords=postgresql

And this is "reasonably" useful:-

https://www.amazon.co.uk/PostgreSQL-Development-Essentials-Manpreet-Kaur/dp/1783989009/ref=sr_1_3?ie=UTF8&qid=1479283169&sr=8-3&keywords=postgresql

I dunno about the best, but PostgreSQL - up and running is decent. Though I second the advice to learn database & SQL generally first, then worry about the specifics of PG - most of what you will be doing in the early stages will be pretty generic across databases

Probably picking up a copy of Greg Smith's book is a good place to start in that case. Keep in mind how things have changed with ssd's since that was published, otherwise it's all very pertinent.

SQL for Smarties by Joe Celko

PostgreSQL Admin Cookbook followed by PostgreSQL 9.0 High Performance

The first chapter of PostgreSQL High Availability Cookbook has a couple sections on picking out hardware, with example spreadsheets to calculate everything. Unfortunately the sizing makes the assumption that you know roughly how many concurrent users you might have, the number of queries that will be running, your expected database size, and so on.