-1

I am working on some sort of a bug tracking product.

We're using Postgres as our only database. So far we are only using normal tables and no jsonb.

Our basic data hierarchy looks like this:

Organization -> Project -> Tickets

Each "Ticket" can have multiple "Comments" or "Attachements". Our most common querys are for all "Tickets" of a "Project" or for a single "Ticket". In each of these we would have to do 1:N to also fetch the corresponding "Comments" and "Attachements". In order to make this query faster i am thinking of putting the a"Comments" and "Attachements" from their own tables into a jsonb column inside the "Tickets".

Since I don't have any experience with using jsonb I was wondering if this really is a good idea? The logic is that instead of querying "Tickets" + "Comments" + "Attachements" we would just query "Tickets" instead.

For reference this is how a "ticket" looks like (ORM is sequelize):

const Ticket = sequelize.define('ticket', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  reporterId: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  assigneeId: {
    type: Sequelize.INTEGER,
    allowNull: true,
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false
  },
  description: {
    type: Sequelize.TEXT,
    allowNull: true
  },
  statusId: {
    type: Sequelize.INTEGER,
    allowNull: true
  },
  priorityId: {
    type: Sequelize.INTEGER,
    allowNull: true
  },
  imageUrl: {
    type: Sequelize.STRING,
    allowNull: true
  },
},
  {  
  indexes: [
      {        
        fields: ['organizationId']
      }
    ]  
})

This is how a "comment" looks like:

const Comment = sequelize.define('comment', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  authorId: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  text: {
    type: Sequelize.TEXT,
    allowNull: false
  }  
},{
  indexes: [
    {      
      fields: ['organizationId']
    }
  ]
})

They are joined via

Ticket.hasMany(Comment)
Comment.belongsTo(Ticket)

Here's one of the query's mentioned:

const tickets = await Ticket.findAll({ 
        include: [ Comment ],
        where: {
          organizationId: req.user.organizationId,
          projectId: req.params.projectId
        },        
        limit: 100
      })

closed as too broad by mustaccio, John Eisbrener, kevinsky, Colin 't Hart, hot2use May 15 at 7:17

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    At this point, the question sounds like it's heavily opinion based due to the lack of overall information. Is there anyway you can quickly spec out a Minimal, Complete, and Verifiable Example? This would allow for some actual analysis regarding your specific problem. – John Eisbrener May 10 at 13:49
1

I can only give a very general answer; my personal rule of thumb:

  1. If the comments or attachments are almost always retrieved together, using a structured data type like jsonb is fine.

  2. If you regularly need to retrieve individual comments, or you want constraints on these comments, or you do any other kind of more intense processing on comments inside the database, you are probably better off with keeping them in an extra table and joining the tables.

Small joins are pretty efficient when the join condition is indexed.

Not the answer you're looking for? Browse other questions tagged or ask your own question.