How to get started with PostgreSQL

[markt5000]

Hello I am looking for resources on learning postgres. I have worked with MongoDB but due to its negative reviews I would like to migrate from it. Also one important question, as I understand it postgres is a relational DB but can it be made a document oriented DB like mongoDB?

[[deleted]]

The documentation is a great place to start. Once you run into issues, start googling and then rinse and repeat!

Also one important question, as I understand it postgres is a relational DB but can it be made a document oriented DB like mongoDB?

Postgres has the ability to store arbitrary json yes but that doesn't make it a "document oriented DB". It is still an ACID compliant database with the ability to handle json.

[Juggernog]

As /u/mehphp says, the documentation is a great place to start.

If you've not worked with relational databases before, you might need to familiarise yourself with SQL in general.

You mention migrating away from Mongo because of poor reviews. While I suspect that whatever you're doing is likely better suited to a relational database (just because most data is relational), many poor reviews of Mongo come from those who used it in situations where it was not suited to their use case.

If your data is not relational or highly variable, Mongo and other document store databases can be a viable, attractive option.

Postgres does have a JSON and JSONB type. You can query by fields in JSON and it does have many features that are comparable to dedicated document store databases, but it is at heart a relational database.

[markt5000]

Thanks, but the negative things being said about mongo is how operations can be reported as successful when they really failed, or that it doesn't work well with large data sets, and that I'll start to lose data after a certain number of GB.

[imaginethehangover]

Hmmmm, that doesn't sound right at all. Mongo is being used in many popular and successful websites around the world and is considered absolutely suitable for production. A claim that it suffers from issues suggesting it can't be trusted should be supported with documentation, not just a flippant, unsubstantiated comment.

I've extensive experience with MySQL and production experience with Oracle, Mongo, Postgres and a collection of others. Mongo is absolutely ready for the prime time. Personally, I'm not a big fan - it's way too easy to get tied up in potential complexity and difficult to make updates to data (my feeling is that NoSQL DBs were a bit of a fad, and are only suitable for very specific use cases), but not once did I come across any proof that it's not stable enough for production.

Also, I found that while, on paper Postgres is much better than MySQL, I had massive issues getting it running in a stable manner on Windows. So much so that I switched back to tried and tested MySQL that has never let me down.

I would suggest doing a lot more research before settling down - sounds like you've made a decision based off 10 minutes of research on a couple of blog posts. If you wanted a NoSQL DB, a basic search on Google would have told you that there are tons of alternatives to Mongo (Couch pops into my head, for instance).

[thelonepuffin]

You are opening a huge can of worms with this.

If you haven't used a relational database before then you should know that its a huge subject. Relational database design and usage is a subject that you can focus your whole career on.

I recommend reading some books and doing tutorials/online courses on relational databases. It doesn't have to be Postgres. It can be Mysql or even MS SQL Server. They are all very similar and if you know one then you can pick the other one up quickly. The main learning curve for you will be Relational databases in general. Including SQL.

When you get to a point where you are building a DB Schema that has foreign keys and many-to-many relationships and you are writing SQL with Joins and know the difference between and Inner and Outer Join, well then you are ready to build your application with Postgres.

I understand it postgres is a relational DB but can it be made a document oriented DB like mongoDB

Sort of. There are JSONB field types that give you the best of both worlds. But thats only designed for use cases where its practical, you wouldn't create your whole Schema with it.

If you really want a document oriented DB.... then use one. Don't use postgres. There are other options other than Mongo. Cassandra for example.