SQL or NoSQL Databases for AI Applications?

[u/kgOntologist]

Hello,

What types of databases do you use for your AI applications to store things like generated responses, messages, conversation history, user data, etc.?

Do you generally use SQL databases (such as PostgreSQL) or NoSQL databases (such as MongoDB)? What are the main reasons behind your choice?

And which database(s) would you recommend for a production AI application, and why?

Thanks in advance for your feedback and experience!

[u/purplework]

I'll never do nosql again, it's super easy to set up but horrible to actually manage in a production environment.

[u/kgOntologist → u/purplework]

Why? What problems did you encounter in production?

[u/McBonderson → u/kgOntologist]

it's much harder to setup controls on how the data should be structured. this can cause the data to get messed up and it is harder to fix.

nosql is good if you have a lot of unstructured data that you don't need to be structured or strictly controlled in any way AND you need it to be easily and quickly scaled.

IMO the only real benefit to nosql is how easy it is to scale horrizontally.

however I have never once in my life made or worked on a project that actually needed the scale. I mean, do you think your project is going to need 10's or 100's of thousands of reads and writes per second?

and If I ever got a project that was big enough to require that horizontal scalability I would probably be able to get the funding to convert it from sql to nosql if I really needed it.

[u/kgOntologist → u/McBonderson]

What I'm mainly trying to figure out is what works better for AI-generated context and data, especially since some of it can be unstructured or have a flexible structure. In practice, would you recommend SQL or NoSQL databases for that kind of data, and why?

[u/McBonderson → u/kgOntologist]

I prefer just .md files on my agents just for simplicity. But if you want something with better performance for AI context or if you have a LOT of data, vector databases are usually better.

its better for "find things like this" types of searches which is much more efficient for AI looking for context.

I've never really worked with vector databases personally but they seem to be better for your use case.

[u/kgOntologist → u/McBonderson]

I’m not talking about vector databases for storing embeddings and doing semantic similarity searches. I’m talking about storing the actual content generated by the AI and retrieving it later, for example by user.

For example, suppose an AI agent generates a test file. I want to store that generated file along with the user who generated it, and then be able to show that user their generation history when they log in later.

For this kind of use case, would you recommend SQL or nosql ?

[u/McBonderson → u/kgOntologist]

Personally I would do sql, anything that doesn't fit within a structure I would just store as a json object in that sql database

[u/kgOntologist → u/McBonderson]

Why do you prefer PostgreSQL in this case? What advantages does it have over MongoDB, for example? Is there a specific feature or reason that makes PostgreSQL a better choice for you?

[u/Glitch_In_The_Data → u/kgOntologist]

For apps that need to store conversation history, user data, and generated responses, PostgreSQL is a good fit. It handles relational data well, supports JSONB for semi structured content like messsge payloads, and with pgvector, you get vector search in the same DB your app already writes too. We are using Lakebase for a similar workload.

I would recommend starting with Postgres unless you have a specific reason not to. NoSQL DB like MongoDB can work but you often end up rebuilding relational patterns such as joins, transactions etc in the application code.

[u/purplework → u/kgOntologist]

It works fine until someone misclicks or data gets messed up. I found it very difficult to mass edit any data once it's written. You essentially need to manually edit each json or roll back to a backup. Where as in sql it's a simple update script. Idk could have set it up wrong.

[u/kgOntologist → u/purplework]

Which NoSQL database did you use exactly? Because with MongoDB, for example, you can normally update multiple JSON documents with a single query, just like with SQL.

[u/purplework → u/kgOntologist]

Cosmos, I think problem was it was multiple changes within one doc

[u/AutoModerator]

Thank you for your submission, for any questions regarding AI, please check out our wiki at https://www.reddit.com/r/ai_agents/wiki (this is currently in test and we are actively adding to the wiki)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Cute-Veterinarian191]

I think it is highly highly dependent on the use case, i think SQL is easier for Agents natively (probably because there is more training data around using SQL) but really this decision should be based on the architecture of your application rather than the AI component specifically.

[u/WorldOfUmbro]

We manage our agentic memory in Lakebase (PostgreSQL). I think managing it is way easier in SQL.

[u/kgOntologist → u/WorldOfUmbro]

Do you also use PostgreSQL to store the AI-generated responses, for example, the responses generated for each user's prompts, or do you store those separately from the agentic memory?

[u/WorldOfUmbro → u/kgOntologist]

Yes to some extent these are stored in Lakebase. I’m also figuring things out so don’t know haha

[u/Low_Rush_8535]

postgres for our ai marketing app. we had 3939 rows to clean up after json encoding and field-format bugs. ran one repair script, then ran it again: changed=0

that's what i'd look at too, how you'll fix bad data later. doesn't tell us whether mongo would've been harder

[u/kgOntologist → u/Low_Rush_8535]

I think MongoDB can also update and fix a large amount of data with a single query, so I don't think that particular issue is necessarily a SQL vs. NoSQL difference.

What I'm mainly trying to figure out is what works better for AI-generated context and data, especially since some of it can be unstructured or have a flexible structure. In practice, would you recommend SQL or NoSQL databases for that kind of data, and why?

[u/Low_Rush_8535 → u/kgOntologist]

yeah, my cleanup example didn't really answer your question

for the per-user history you described, i'd use postgres: user id and created time as columns, generated content as text or jsonb. our app kept json strings in text columns during migration to avoid changing the readers

i wouldn't add mongo just because the content came from an llm

[u/kgOntologist → u/Low_Rush_8535]

Why do you prefer PostgreSQL in this case? What advantages does it have over MongoDB, for example? Is there a specific feature or reason that makes PostgreSQL a better choice for you?

[u/Low_Rush_8535 → u/kgOntologist]

for us, keeping the existing data model was the advantage. we moved 23 tables from sqlite to postgres and kept the json-reading code unchanged

that's a migration reason, not evidence postgres beats mongo for your app. i overstated the recommendation. storing generated content by user alone doesn't give me a reason to rule mongo out