Relational and Non-relational Data: PGSQL Phriday #005

Welcome to the 5th installment of the #PGSQLPhriday blogging series. I am thrilled to be this month's host! The topic posts should be published by Friday February 3rd.

When Ryan Booz proposed the idea for #PGSQLPhriday I was immediately excited about it. Other than our first names, Ryan and I have a other few things in common. One of these common points is we both started our database careers in the world of MS SQL Server and later found our way to Postgres. My move to Postgres, and why I discovered Postgres, is at the heart of this month's topic for PGSQL Phriday 005.

Is your data relational?

The entire reason I discovered and started using Postgres was PostGIS. I needed PostGIS because I had a project in 2011 that could benefit from the OpenStreetMap data. The project still needed rock solid support for relational data and the SQL Standard, which Postgres also provides. However, it was the spatial support of PostGIS that pulled me into the world of Postgres.

Through working with OpenStreetMap data I became intimately familiar with the HSTORE extension, a simple key-value data type in Postgres. As time has passed, my use of hstore has turned into using Postgres' JSONB data type. I use it with the OpenStreetMap data to handle its flexible tagging structure, and also have audit tables and API data stored using JSONB too.

The challenge

How are you using Postgres? For relational data only, a mix of relational and non-relational, or primarily non-relational data?

• 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?

How to participate in #PGSQLPhriday

The official rules are summarized here.

• Publish your blog post about “Relational and Non-relational Data in Postgres” by Friday, February 3rd, 2023
• Include “PGSQL Phriday #005” in the title or first paragraph of the blog posting
• Link back to this blog post

Announce your blog post in one or any of these places:

• Twitter (or Mastodon) using the #PGSQLPhriday hashtag
• PostgreSQL Slack in the #PGSQLPhriday channel
• Interact with other blog posts about this topic