Search Postgresql Archives

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where the only difference is the receiver column.

 

|---------------------|------------------|---------------------|------------------|

|      Receiver       |       Event      |         Date        |      Location    |

|---------------------|------------------|---------------------|------------------|

|       Alpha         |         3        |          12         |         USA      |

|---------------------|------------------|---------------------|------------------|

|       Bravo         |         3        |          12         |         USA      |

|---------------------|------------------|---------------------|------------------|

|       Charlie       |         3        |          12         |         USA      |

|---------------------|------------------|---------------------|------------------|

 

While redesigning the database, I have considered using an array with a GIN index instead of the current B-Tree index on receiver. My proposed new table would look like this:

|-------------------------------|--------------|------------|-------------------|

|           Receivers           |     Event    |    Date    |     Location      |

|-------------------------------|--------------|------------|-------------------|

| ["Alpha", "Bravo", "Charlie"] |       3      |     12     |         USA       |

|-------------------------------|--------------|------------|-------------------|

 

More Information:

·         Receiver names are of the type (a-z, 1-5, .)

·         95% of all queries currently look like this: SELECT * FROM table WHERE Receiver = Alpha, with the new format this would be SELECT * FROM table WHERE receivers @> '"Alpha"'::jsonb;

·         The table currently contains over 4 billion rows (with duplication) and the new proposed schema would cut it down to under 2 billion rows.

·          

Question:

1.      Does it make more sense to use Postgres Native Text Array?

2.      Would a jsonb_path_ops GIN index on receivers make sense here?

3.      Which option is more efficient? Which is faster?

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux