Optimal database for large number of columns or rows
PostgreSQL


  @Dobob everything can be normalized :) If it's not very secret, what sort of data it is? (Asking as a geneticist-turned-DBA.)


This is generally true. And you should start at looking how you can normalize. Clearly you're averse to the idea, and you can't always normalize. For instance, a raster is often a collection of readings from a scientific instrument, and you can't really denormalize that. They're taken at the same time, represent the same sample and frequently have 1,000s of data points. (Though now you can store them in PostGIS).

Solution for Problem 1

PostgreSQL does not support 16,383 columns on a table (or more than 1,600+ for that matter), but it does supports SQL Arrays. You can probably make use of that,

CREATE TABLE foo AS
  SELECT
    r AS id,
    array_agg(c) AS genetic_stuff
  FROM generate_series(1,567)
    AS r
  CROSS JOIN LATERAL generate_series(1,16382)
    AS c
  GROUP BY r;


Now you have a table that meets the first criteria but isn't insane.

          Table "public.foo"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 id            | integer   | 
 genetic_stuff | integer[] | 


Finding average is simple..

SELECT id, avg(x)
FROM foo
CROSS JOIN LATERAL unnest(genetic_stuff)
  AS t(x)
GROUP BY id;


And for that whole operation Execution time: 3865.308 ms! Can complain about that. Feel free to use a MATERIALIZED VIEW if you want to cache that average.

For Standard Deviation just use the appropriate aggregate function in the place of avg(). Execution times are about the same.

I'm not sure how you're querying this here, but without an index (worst case situation) is pretty simple and fast (considering it's a seq scan) scanning through an array with 16,000 items isn't slow either for a modern a CPU, and on PostgreSQL 9.6 this seq scan should even run in parallel (afaik).

SELECT * FROM foo
WHERE genetic_stuff @&gt; ARRAY[5];


Can you use an index? Sure. From the intarray module,


  Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.


Let's give it a shot...

CREATE INDEX ON foo
  USING gist(genetic_stuff gist__intbig_ops);
VACUUM FULL foo;

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_genetic_stuff_idx on foo  (cost=0.14..8.16 rows=1 width=22) (actual time=0.119..47.846 rows=567 loops=1)
   Index Cond: (genetic_stuff @&gt; '{5}'::integer[])
 Planning time: 0.072 ms
 Execution time: 47.948 ms
(4 rows)


Not sure what more you want than index scan on that. Probably be more useful if not every row had an element of 5, but you know it's only sample data.

Anyway have at it.

Solution for Problem 2

Shy of that, the other thing isn't a problem at all a bit of overhead because Pg rows are fat, but you're not talking many rows and ultimately it shouldn't matter this below table meeting that criteria is only 5088 kB on my system. Hardly worth concerning yourself with.

CREATE TABLE baz
AS
  SELECT
    x AS id,
    x*2 AS x2,
    x*3 AS x3,
    x*4 AS x4
  FROM generate_series(1,117493)
    AS t(x);