Postgres ordering table by element in large data set

Question

I have a tricky problem trying to find an efficient way of ordering a set of objects (~1000 rows) that contain a large (~5 million) number of indexed data points. In my case I need a query that allows me to order the table by a specific datapoint. Each datapoint is a 16-bit unsigned integer.

I am currently solving this problem by using an large array:

Object Table:

  id serial NOT NULL,
  category_id integer,
  description text,
  name character varying(255),
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  data integer[],

GIST index:

  CREATE INDEX object_rdtree_idx
  ON object
  USING gist
  (data gist__intbig_ops)  

This index is not currently being used when I do a select query, and I am not certain it would help anyway.

Each day the array field is updated with a new set of ~5 million values

I have a webserver that needs to list all objects ordered by the value of a particular data point:

Example Query:

SELECT name, data[3916863] as weight FROM object ORDER BY weight DESC

Currently, it takes about 2.5 Seconds to perform this query.

Question: Is there a better approach? I am happy for the insertion side to be slow as it happens in the background, but I need the select query to be as fast as possible. In saying this, there is a limit to how long the insertion can take.

I have considered creating a lookup table where every value has it's own row - but I'm not sure how the insertion/lookup time would be affected by this approach and I suspect entering 1000+ records with ~5 million data points as individual rows would be too slow.

Currently inserting a row takes ~30 seconds which is acceptable for now.

Ultimately I am still on the hunt for a scalable solution to the base problem, but for now I need this solution to work, so this solution doesn't need to scale up any further.

Update: I was wrong to dismiss having a giant table instead of an array, while insertion time massively increased, query time is reduced to just a few milliseconds.

I am now altering my generation algorithm to only save a datum if it non-zero and changed from previous update. This has reduced insertions to just a few hundred thousands values which only takes a few seconds.

New Table:

CREATE TABLE data
(
  object_id integer,
  data_index integer,
  value integer,
)

CREATE INDEX index_data_on_data_index
  ON data
  USING btree
  ("data_index");

New Query:

SELECT name, coalesce(value,0) as weight FROM objects LEFT OUTER JOIN data on data.object_id = objects.id AND data_index = 7731363 ORDER BY weight DESC

Insertion Time: 15,000 records/second

Query Time: 17ms

Answer 1

First of all, do you really need a relational database for this? You do not seem to be relating some data to some other data. You might be much better off with a flat-file format.

Secondly, your index on data is useless for the query you showed. You are querying for a datum (a position in your array) while the index is built on the values in the array. Dropping the index will make the inserts considerably faster.

If you have to stay with PostgreSQL for other reasons (bigger data model, MVCC, security) then I suggest you change your data model and ALTER COLUMN data SET TYPE bytea STORAGE external. Since the data column is about 4 x 5 million = 20MB it will be stored out-of-line anyway, but if you explicitly set it, then you know exactly what you have.

Then create a custom function in C that fetches your data value "directly" using the PG_GETARG_BYTEA_P_SLICE() macro and that would look somewhat like this (I am not a very accomplished PG C programmer so forgive me any errors, but this should help you on your way):

// Function get_data_value() -- Get a 4-byte value from a bytea
// Arg 0: bytea* The data
// Arg 1: int32  The position of the element in the data, 1-based

PG_FUNCTION_INFO_V1(get_data_value);
Datum
get_data_value(PG_FUNCTION_ARGS)
{
    int32 element = PG_GETARG_INT32_P(1) - 1;     // second argument, make 0-based
    bytea *data = PG_GETARG_BYTEA_P_SLICE(0,      // first argument
                     element * sizeof(int32),     // offset into data
                     sizeof(int32));              // get just the required 4 bytes
    PG_RETURN_INT32_P((int32*)data);
}

The PG_GETARG_BYTEA_P_SLICE() macro retrieves only a slice of data from the disk and is therefore very efficient.

There are some samples of creating custom C functions in the docs.

Your query now becomes:

SELECT name, get_data_value(data, 3916863) AS weight FROM object ORDER BY weight DESC;