![]() Now consider the same data stored as json in PostgresĬreating a new table with JSON data type CREATE TABLE orders ( However, the larger your database, the more reduced the performance will be when querying huge datasets. In essence, this is okay for small or medium size databases. Support for JSON/JSONb which in essence gives us best of the bothĬonsider this table orders stored as SQL array in Postgres id | first_name | last_name | product | qty Most of us are used to Relational Database Management Systems (RDBMS) where each data is mostly separated by columns. The short answer is Performance and Flexibility The right question to ask here would not be 'When' but 'Why' use json instead of standard relational SQL queries. I was about to give up when finally I found someone who gave me the answer I was looking for. Still none of them gave me a satisfying answer. I read so many posts and so many different articles from so many different people. My suggestion here: as long as you need one dimension, use normal arrays.Īs a new PostgresQL user, I searched the internet for answers regarding the same question you're asking. It is also possible to use postgresql arrays to store json/jsonb data, and approach is very similar. We also tried it with jsonb and jsonb arrays, but our approach is not final yet and jsonb arrays are more complex in handling. Our optimized tables consume more storage space, but they are much faster to query and way easier to understand by analysts. The normal lookup tables are just written into fields, bridge tables are aggregated into arrays first and then put into single fields. Thus we decided to leverage triggers to get rid of most lookup tables and some of the bridge tables, to increase human readability and decrease the amount of lookups and code complexity for analysts. Proper queries with additional external data would be non-manageable and waay to long lasting. you want to analyze the fluctuance of your income compared to economic data). Taken together analytic queries had >20 joins for some major business objects, without adding data (e.x. ![]() Basically if you open the table as a human, great part of the table is not readable.Īdditionally there are many-many bridge tables with 1:n:1 relation, which can be simplified to simple arrays. The data comes from external data source, which heavily uses artificial keys. ![]() ![]() We are using postgresql arrays and tried jsonb arrays. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |