Andreas Scherbaum: Relational and Non-relational Data: PGSQL Phriday #005

Ryan Lambert asks in this month's PGSQL Phriday: What non-relational data do you store in Postgres and how do you use it? Have you attempted non-relational uses of Postgres that did not work well? What was the problem? What are the biggest challenges with your data, whatever its structure? Bonus: How do you define non-relational data? Looking over the many databases we operate at work, I can say that we are mostly a "relational shop". Almost every database we have uses relational structures, and does not attempt to go in on "full NoSQL", or "store non-relational data". Many of the databases are on the larger size, often in the hundreds of GB or even several TBs. We have good people in our dev teams who understand both PostgreSQL and SQL, and can develop a proper schema for the data, and write performant queries. There are a few exceptions though, but at least for us it's only a small number. Whenever we retrieve or get data which comes in from web requests, we may store this as JSON, and then we use JSONb. Quite often however it's rather "storage as JSON", not "querying the JSON in the database". It's just more convenient to keep the data in JSON format all the way, instead of transforming it several times.   Continue reading "Relational and Non-relational Data: PGSQL Phriday #005"