Postgresql – Dynamic form builder database optimization Postgresql

database-designnosqlpostgresql

Hi I have a project that create forms dynamically I use a model like the following:

forms
-----
  id (PK)
  name
  (other fields)

form_fields
-------------
  id (PK)
  form_id (FK to forms.id)
  label (nome to the field)

form_submits (form inserted by someone user)
-------------
  id (PK)
  form_id (FK to forms.id)
  user_id (FK to users.id)

form_submits_fiels 
-------------
  id (PK)
  form_fields_id (FK to form_fields.id)
  form_submit_id (FK to form_submit.id)
  data 

Now I have seriously problem whit the performance in the production server because the form_element table have more than 1.500.000 rows; the number continue growing and the function that return all records of a form take more that 20 seconds, the question is how optimizer the model or the query to take less time

I have some ideas but I thing that not resolve the problem:

  • Paginate: if i could paginate the queries the performance will better so much but I thing that is not possible because the data was not in order.
  • MongoDB: If use a no relational model the performance maybe will better but I not sure so mach can ber better with Mongo and is a so expensive solution

Best Answer

If I had more reputation, I would add a comment asking you to describe your indexes. As it is, I'll assume you don't have indexes since you didn't mention them.

First, consider using EXPLAIN on your queries to analyze the decisions the planner makes. 1.5m rows shouldn't take 20 seconds with any sort of reasonable combination of hardware and indexing. See the documentation of the EXPLAIN modifier to get started with that. You should include (ANALYZE on) to see the cost breakdown and timing info from actually running the query, and more importantly the individual processes that go into each stage.

Second, a schema like this would benefit not only from indexes, but from some limited use of multi-column indexes. (See CREATE INDEX documentation.) Consider creating indexes on:

  • form_fields.formid, form_fields.id
  • form_submits.formid, form_submits.id

and others. It depends highly on what your code is doing. Duplicate the database to another server and create different configurations of indexes and run your code against it. (EXPLAIN (ANALYZE on) again here!) Multi-column indexes are great because they help the planner check multiple filtering/sorting criteria in one pass of the index.

Third, to address your last points, I'm not sure that pagination or moving to a non-relational model will help. The fact that you're using foreign keys actually points to a strongly relational set of data. You can, of course, try MongoDB for free and see what the performance looks like.