![]() ![]() I'm hoping for some guidance about optimizing this. It will be essential that the queries can be fast. I know JSONB is supposed to be faster than JSON, but we're going to ultimately be talking about 10s if not 100s of millions of records in these tables. This would allow for building queries that can easily search both datasets, and by using JSONB a similar pattern to the current design could be preserved. Expanding this to allow for searching both core and extension data is something that is being asked for right now.Īs part of this, I am investigating the possibility of moving the extension data to Postgres, using the JSONB data type. concerned about a slow query on one of their JSON columns. Tips for improving performance by using EXPLAIN ANALYZE. As it is currently designed, the search API only allows for querying fields in the "core" data in Postgres, and then uses the resource ID to lookup the extension data, if it exists, from MongoDB. EnterpriseDB helps to identify PostgreSQL query optimization problems. The takeaway The slow Postgres query is gone. The new query in production A short code deploy later: And the database looks much happier. That’s a 100x speedup for a simple one-line change. Right now, the "core" data is in Postgres, because of its relational nature, and the extension data is in MongoDB because our collection there needs to be flexible enough to fit a variety of business domain needs.īecause of this, searching is the main pain point. The 2 major differences are on lines 3 and 14. The "core" data is common across all business domains, the "extension" data is where the domain-specific info goes. To ensure it is flexible enough, we are adopting a "core" vs "extension" model. My final query needs to access the string array values from 4 of these top-level jsonb paths, which slows the query right down to 5 seconds. However when I instead select a top-level path of the same jsonb column, the query slows down by 6x (3241ms). You can then store your JSON in Mongo / Postgres or scatter the data model into. When I SELECT the entire jsonb column, the query is fast (574 ms). We have an enterprise service that is being designed to provide a critical piece of data to a wide range of business domains. THis allows you to search through the data by running a SQL query. ![]() The key difference between them is that JSON stores data in a raw format and. Ok, so I've got a situation that I'm pursuing possible solutions to. PostgreSQL has two native data types to store JSON documents: JSON and JSONB. ![]()
0 Comments
Leave a Reply. |