3 ms·
Could you tell me the specific advantages that this may have over say, json data in a text field ? Is it faster to: - Convert an array into json and push it i
by ashray 14y ago
Could you tell me the specific advantages that this may have over say, json data in a text field ?
Is it faster to:
- Convert an array into json and push it into a text field
- Retrieve it the same way
Or use this ?
Also, are there any distinct full-text indexing advantages ? I know more MySQL than Postgres but am eager to learn about it since it looks way more powerful! :)
- bdr 14y agoOne advantage is filtering query results at the DB layer. For example, you could compare elements within an array, test a particular index of the array, or see if a value is present anywhere in the array. See http://www.postgresql.org/docs/9.2/static/arrays.html http://www.postgresql.org/docs/9.2/static/arrays.html.
- ashray 14y agoThanks! On further thinking about my question I realized that anything at the DB layer (Postgres) will probably be faster than doing it in Python because it's written in C. (re: the json load and dump part of my question..)
- dagw 14y agoI don't know if the default json library in python is written C or not (never had a problem where json decoding was even close to a bottleneck), but there are at least a couple of very high performance json libraries for python written in C, so that shouldn't be much of a problem.
- the_cat_kittles 14y agothat is pretty cool. I'm sure there are many use cases for that, i just can't think of any off the top of my head.
- fdr 14y agoOne-dimensional arrays can be useful for indexing. See: GIN, and here's an example: http://stackoverflow.com/a/4946888 http://stackoverflow.com/a/4946888 This uses the same underlying indexing method often used with Full Text Search. Values in arrays can be awkward if you are frequently pulling them out for joins, et al. However, if you frequently are dealing with operators over the entire collection they are quite handy, and I have enjoyed using them for this purpose. An example that parallels something I've recently done for Real Work: Suppose you are writing a piece of software that aggregates some blogs of various vendors and twitter feeds. You want to take a chunk of text (the contents of the post) and then be able to easily search for text that has a certain textual tag ascribed by their author. Sometimes, one's requirements are nicely met if one puts tags into an array and writes queries like this: SELECT contents FROM bucket_o_text WHERE tags && '{go,golang,gccgo}' The "&&" is read "overlaps", so now you will get posts that seem to be about the programming language Go. If you want to find things about both golang and erlang, one might say: SELECT contents FROM bucket_o_text WHERE tags && '{go,golang,gccgo}' AND tags @> '{erlang}'; Where "@>" is read "contains". If you were confident the tags seem reasonably normalized, you might even write: SELECT contents FROM bucket_o_text WHERE tags @> '{erlang,golang}'; I would stop using this approach if I started having higher-order data about the tags 'erlang' and 'golang'. Said another way, data about tags, or metadata on tags, which are themselves metadata of the post. In this case I'd be inclined to break out 'erlang' and 'golang' into rows of a table so one could enumerate the concepts in the database easily and write constraints.