Dynamic form builder database optimization Postgresql
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.