How to build an efficient EAV model over JSONB in Postgresql

Question

Context

Hello, we have a project we integrate with a ton of different CRMs. That means that when a new client comes in, we get all their data from their CRM and store it in our own postgresql database. This means that having fixed schemas for our application is pretty hard.

Requirements

Our app adds geographical capabilities to all the CRMs we integrate with by adding a map. Over that map, we then built some features on top. The base needs of these features are:

• Text search: common full text search.
• Filtering / colorising: filtering by range (numeric, datetime), specific values of picklist, location on map (we have a tile server for this), etc. We also support filtering by null or filled values. For example, show me all records where field field is null or not.
• Insert speed is kind of important, because we have to make imports of hundred of thousands of records when integrating a new company. But these are less and less frequent because we are building an event-based update mechanism to be always up to date with the CRMs data.

Current solution

We chose some time ago to fully implement an entity-atribute-value (EAV) data model. This seemed the best idea given the shape of our data (more or less shapeless, due to how many different objects you have in the CRMs). The structure is more or less the usual in an EAV model:

• One table to store some core fields of the entity (id, creator, created date and so on)
• 5 tables for the 5 different built-in types we support (number, string, picklist, datetime, address)^[1].

This seemed great some time ago, but now we are having some difficulties when adding new features to the system. For instance:

• Full text search: this is somewhat hard to implement for some of the field types that we support (because we need to make some data redundant).
• Adding new field types: putting all the information of a complex field type into one field only forces us to create some pretty weird code.
• The tile server we have built to implement the filtering, needs to run some pretty big and prone to error queries to combine filters by several fields.
• Model hard to understand by new joiners to the project.

Possible new solution

We were thinking about reducing the complexity of the project by combining Schemas and JSONB. Basically just store all records in just one table:

• Some fixed columns in the table with the most used values.
• Json b columns with all the fields coming from the CRM, stored there to be queried by the filter/colorising functionality. We would use GIN index to support full text search.

I have some concerns about the filtering speed. Customers are used to that feature working pretty fast. I'm unsure about how slow will it get if we store everything in a JSONB format. Right now we don't have a crazy amount of records, so I was thinking that this solution could get us to support more clients, and once we have more clients, move the filtering part to a Reversed Index solution like Elastic Search, while leaving all the other functionalities working with Postgresql.

What would you recommend in this case? Is everything clear? Please let me know if I need to clarify something!

---

^[1] Right now we have these five fields, but they could be reduced by:

• Combining picklist table into the string one.
• Casting all datetimes to strings (we store everything in UTC and we only care about range filters, which given that all is in UTC, then alphabetical comparisons can be used to implement the datetime range filter).

Answer 1

You're basically in the standard world of hurt that EAV causes. There's no such thing as an efficient EAV model.

SQL databases have schemas in order to improve the efficiency of storage and processing (compared to storing data structured arbitrarily), and to make that schema explicit to the developers so that they can design algorithms that correctly process the data in an automatic way.

You're getting trouble on both fronts. You propose arbitrary structuring but worry about the effect on performance. And you worry about how new developers will get anything done when trying to work with the unorganised menagerie of arbitrary data structures.

The doctor's solution is very simple: you don't do that.

Your system is obviously going to require constant oversight and readaptation as each of the source CRMs themselves are readapted by their development teams - except they control their own cadence, whereas you will be at the constant mercy of them all.

This will require your team to be relatively overstaffed compared to theirs. And it will massively reduce the availability of your system (or parts of it) compared to their systems individually, whenever you encounter a disrupting change for which you need time to design a readaptation.

You'll also have to be at the mercy of each source development team making design decisions that are mutually inconsistent and irreconcilable, or at least mutually inconvenient, which will either break your application fundamentally or add extreme complexity to both the design of its processing and the interpretation of its outputs. Extreme complexity could mean significant and unpredictable downtime whilst you readapt your system to work properly again, and it'll mean your users require information, training, and intellect above the norm that would be required for interpreting the outputs of a standard software application.

Unfortunately you don't just sew software designs together, any more so than you sew arbitrary animals together and expect them to function, because the source designs already reflect a complicated integration which required costly human labour to achieve.

The source developers have usually started from (and continue to retain) almost complete design freedom, whereas you are hobbled by trying to integrate several of these already difficult self-integrated designs, and you don't have an ounce of power to force any of the parts to be adjusted to the needs of your over-arching design or to be mutually rationalised.

It's like trying to design a car engine, but you have no power to tell the foundry what size (or even shape) pistons they make or stop them altering the output at will. No manufacturer would try working under those conditions.

You can try using a partly standardised schema with a JSON column to store all the residues, and periodically review the residues to decide whether they need to be incorporated into the standardised part of the schema.

It's impossible to predict the performance until you try it under your specific conditions, and performance may be highly sensitive to aspects of design where there is normally a large amount of freedom without performance implications. Your work will consist of searching and examining the possibilities.