0

I have some data in a Postgres database that does not fit into a static schema. There is a table "content" and each piece of content has a user-defined type. Each type can have different user-defined fields. Within a single type those fields are always the same though. You can assume that a typical database has ~5-10 types with each having ~5-25 fields.

My original approach to store this was to use JSONB and store the data like this:

{
  "my_field": "foo",
  "another_field": "bar",
  "some_number_field": 5,
  "a_bool_field": true
}

So as key/value pairs where each field has a string id used as the key and the value stored as the type of the field. So of course you have to know if the specific field you are querying is a number of a string, but that information is stored in the DB elsewhere for all content types and their fields.

This is indexed with a GIN index using jsonb_path_ops and then can be queried using the @> containment operator. This works pretty nicely for checking equality, but doesn't support any other cases.

The problem is that I need to also support more advanced queries here, specifically some that require support for > and < operators. Part of this is because I'm adding timestamps as a type for the fields, and queries that restrict the range based on a timestamp are a very common use case there.

As far as I understand this is not possible to do in a generic way using a JSONB column, there is no index similar to the GIN index that would allow these kinds of queries. So I see only two ways to handle this:

  • dynamically create the right indexes for specific fields
  • store the data in a EAV pattern with columns for different data types like timestamp, int, ...

The first option to create the indexes in the running application based on user input is a bit unorthodox, though I think it would be possible to do this reasonably safely in this particular case. The tenants are separated by schemas, and I'd use partial indexes that are created concurrently to avoid locking the tables.

The second option with an entity attribute value system feels icky, but it does seem to be a valid solution for this particular requirement. But everyone seems to strongly advise against EAV every time this comes up, so I'm not entirely sure how problematic this solution would be.

Am I missing any solutions here? What are my options here to store this kind of flexible data in a way that still allows fast comparison queries on types like timestamps and numbers?

1

1 Answer 1

0

But everyone seems to strongly advise against EAV every time this comes up

A key question here is whether this is (1) a real business application, or whether it is (2) just a hobby project (or intended for a fantasy start-up business)?

And if it is (1) a business application, then is it (A) a typical database application with a potentially significant number of concurrent read-write users, or is it (B) something that happens to use SQL for storage and querying but has few or no concurrent read-writes?

Typical business context

In real businesses with databases that handle typical workloads, the definition of the storage schema (including indexing) is an expert job which sits on the "developer" side of the "developer/user" distinction. The design of that schema is context-specific, and often requires real professional judgment, prototyping of possibilities, or iterative refinement based on experience.

In particular, some database designs that work fine in a single-threaded context, fall down quickly under concurrent loads (or certain profiles of concurrent load, it often being difficult to anticipate what those dysfunctional profiles are).

Mainstream client-server SQL database environments offer good facilities for developers to specify and manage the storage schema, including the ability to make "dynamic" changes whilst the database is online and in use.

Nevertheless, changes to a storage schema often require collective planning and evaluation, testing, publication of changes to users, re-documentation, re-testing and re-integration with other peripheral computer applications, and bespoke transitional arrangements. This means that changes often cannot be made lightly.

Another thing to note in real businesses with typical database applications, is that database machinery is supervised in an ongoing way by various kinds of staff including administrators and developers, and database designs are often subject to incremental development which continues over the long-term and through multiple generations of development staff.

The needs of supervisory staff are not necessarily the same as the usual needs of the end-users, and the needs of an individual developer working with their own creations are are not necessarily the same as the needs of a multi-person, multi-generational development team.

There are also security and resilience concerns in commercial contexts, including controlling access, avoiding or coping with accidental damage, and detecting and limiting malicious use or "poisonous" inputs.

Reputation of EAV

As you correctly identify, EAV has an extremely poor reputation amongst all kinds of practitioners who work with databases.

That is because most practitioners work with databases in the typical business context described above.

In that typical context, EAV proves itself to be opaque, awkward, complicated, and poorly performing.

These problems arise primarily because EAV does encode a fixed database schema through its system of entities and attributes, but this schema becomes latent and largely invisible to most standard management tools that developers use, and also invisible to the database engine (and therefore outside the realm of the optimisations and controls it typically offers).

The promises of EAV, which are often only vaguely understood, are also often mirages based on a lack of awareness of what facilities DBMS products already provide as standard, or based on various kinds of radical misapprehension (such as what level of expertise is necessary to design a database schema that works under typical circumstances, or what kind of problems a "dynamic" storage schema actually implies).

Where EAV works

Needless to say, if you're engaging in hobby development, then EAV is likely to work fine whatever you do, because hobby development is entirely a short-lived, one-man circus with no real-world performance requirements.

In atypical database applications, EAV may also work fine. It's most likely to work fine with applications of limited scope where the developer and end-user is the same person, or where any end-user who isn't the developer is nevertheless closely overseen by the developer (who provides appropriate advice and control over things like altering the EAV schema).

One reason EAV is more likely to work in these atypical circumstances, is because the lack of schema visibility as seen through the management tooling, is less important for a sole developer/creator/maintainer than for a team of staff.

The sole creator retains a detailed mental model of the latent EAV schema they have personally created, and therefore they can often refer to information that is already embodied in their own minds.

For teams of staff where no one person has created everything, individuals have to learn and derive information about the schema from interacting with the database management tooling, and that is where it becomes essential that the schema is visible and explicit.

Conclusion

What are my options here to store this kind of flexible data in a way that still allows fast comparison queries

The speed and efficiency of data processing is always a function of its rigidity - speed comes either from making all kinds of assumptions about the data, or by performing consistent preparations based on the data like indexing.

It's impossible to reconcile all three of arbitrary structuring of data, high volumes, and good performance.

I've explained some of why EAV has a poor reputation elsewhere, but also how context is crucial to interpreting that reputation.

One thing the industry knows by now is that EAV is a siren voice which regularly draws unwary developers onto the rocks, for various reasons that defy simple summary.

The only approach that works reliably in a broad range of circumstances is to analyse your inputs and design a storage schema (as expressed in a standard way) that copes with all known variations of the input. If a new variation then arises in future, you maintain the schema by altering it, using developer tools to do so in the standard way.

Sometimes developers intuitively grasp that this ongoing maintenance is going to be a hassle. And it is. But you ain't seen nothing yet, compared to the fate of some EAV solutions.

You will ultimately have to decide on these design questions based on your own judgment, because only you are fully abreast of your particular circumstances.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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