From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: George Neuner <gneuner2@comcast.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Use Postgres as a column store by creating one table per column
Date: Fri, 24 May 2019 20:06:29 +0200
Message-ID: <20190524180629.42d3u4m7a3jgh5hq@development> (raw)
In-Reply-To: <d871e346-bd77-1081-42b8-af31bc8ed6ed@comcast.net>
References: <d871e346-bd77-1081-42b8-af31bc8ed6ed@comcast.net>
On Thu, May 23, 2019 at 01:08:42AM -0400, George Neuner wrote:
>On Tue, 21 May 2019 21:28:07 -0700, Lev Kokotov
><lev.kokotov@gmail.com> wrote:
>
>>Is it efficient to use Postgres as a column store by creating one
>table per
>>column?
>>
>>I would query it with something like `[...] UNION SELECT value AS <table>
>>FROM <table> WHERE value = <value> UNION [...]` to build a row.
>
>I think you mean JOIN.
>
>You'd need more than that: Postgresql uses MVCC for concurrency, so
>whenever you update any row in a table, the ordering of the rows
>within the table changes. And the JOIN operation inherently is
>unordered - you need to sort the result deliberately to control
>ordering.
>
>To emulate a column-store, at the very least you need a way to
>associate values from different "columns" that belong to the same
>"row" of the virtual table. IOW, every value in every "column" needs
>an explicit "row" identifier. E.g.,
>
> col1 = { rowid, value1 }, col2 = { rowid, value2 }, ...
>
>For performance you would need to have indexes on at least the rowid
>in each of the "column" tables.
>
>This is a bare minimum and can only work if the columns of your
>virtual table and the queries against it are application controlled or
>statically known. If you want to do something more flexible that will
>support ad hoc table modifications, elastically sized values (strings,
>bytes, arrays, JSON, XML), etc. this example is not suffice and the
>implementation can get very complicated very quickly
>
>
>Justin Pryzby was not joking when he said the performance could be
>awful ... at least as compared to a more normal row-oriented
>structure. Performance of a query that involves more than a handful
>of "columns", in general, will be horrible. It is up to you to decide
>whether some (maybe little) increase in performance in processing
>*single* columns will offset likely MASSIVE loss of performance in
>processing multiple columns.
>
Maybe take a look at this paper:
http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf
which essentially compares this approach to a "real" column store.
It certainly won't give you performance comparable to column store, it
adds quite a bit of overhead (disk space because of row headers, CPU
because of extra joins, etc.).
And it can't give you the column-store benefits - compression and/or
more efficient execution.
>
>>I'm thinking since Postgres stores tables in continuous blocks of
>16MB each
>>(I think that's the default page size?)
>
>Default page size is 8 KB. You'd have to recompile to change that,
>and it might break something - a whole lot of code depends on the
>knowing the size of storage pages.
>
>
Right. And the largest page size is 64kB. But 8kB is a pretty good
trade-off, in most cases.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, gneuner2@comcast.net
Subject: Re: Use Postgres as a column store by creating one table per column
In-Reply-To: <20190524180629.42d3u4m7a3jgh5hq@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox