Re: How to get good performance for very large lists/sets?

On 10/6/14, 3:02 AM, Richard Frith-Macdonald wrote:
> I'm wondering if anyone can help with advice on how to manage large lists/sets of items in a postgresql database.
>
> I have a database which uses multiple lists of items roughly like this:
>
> CREATE TABLE List (
> ID SERIAL,
> Name VARCHAR ....
> );
>
> and a table containing individual entries in the lists:
>
> CREATE TABLE ListEntry (
> ListID INT, /* Reference the List table */
> ItemID INT /* References an Item table */
> ) ;
> CREATE UNIQUE INDEX ListEntryIDX ON ListEntry(ListID, ItemID);
BTW, performance-wise, your best bet might be to forget about using a listentry table (BTW, I recommend not using CamelCase for database object naming) and instead put an array in the list table:

CREATE TABLE list(
list_id serial PRIMARY KEY
, list_name varchar NOT NULL UNIQUE
, list_items int[] NOT NULL||||

);

I think there's an extension/add-on that would let you enforce referrential integrity between list_items and the items table, but I can't find it now.
--
Jim Nasby, Data Architect, Blue Treble
Data in Trouble? Get it in Treble! http://BlueTreble.com