News for hackers. Stuff that matters.
Re:Columnar Store for Analytics with Postgres by drej on Thursday October 12, 2017 @10:43AM (#15458309)
Has anyone achieved a dramatic speedup using this extension? Last time I tried it, I got barely a 30% speedup in a generic aggregation across a few fairly normalised tables with ~10M rows. I don't know if I'm doing something wrong or if this is all I'm getting. Thanks!
Re:Columnar Store for Analytics with Postgres by pjungwir on Thursday October 12, 2017 @11:54AM (#15458866)
I've tried it a couple times but didn't see as dramatic a speedup as I'd hoped either. What has worked for me is storing a full array in a single row of a regular Postgres table. Then if you have to load e.g. 1 million FLOATs, you can just slurp an 8MB chunk off disk instead of looking in 1 million different places. Postgres doesn't have a lot of functions for working on that kind of data though, so I wrote an extension to provide a few at [0]. I will probably add some more functions soon around masking out values so you can work "R/Pandas-style" on these arrays.

My approach breaks down if you are heavily updating your arrays though. On the mailing list Tom Lane suggested I store that kind of data outside the database.[1] But of course all software development is an attempt to have your cake and eat it too, so I wrote an extension that is a bit like cstore_fdw, but maybe more extreme: you can store/load/append a float array as an ordinary file.[2] It has similar operational disadvantages to cstore_fdw (no replication, no pg_dump, etc.) but performs faster. On the other hand it is definitely not battle-tested and still likely to change a little. (I need to improve the crash story and may add compression.) If you decide to use it, let me know!

Oh another limitation of keeping everything in an array (whether with floatfile or not) is that Postgres has a 1 GB limit on most RAM allocations. For example you could try running array_agg on a giant table and you might get a message about `Exception invalid memory alloc request size 1073741824`. I don't think cstore_fdw has that problem.

[0] https://github.com/pjungwir/aggs_for_arrays/

[1] http://www.postgresql-archive.org/Performance-appending-to-a...

[2] https://github.com/pjungwir/floatfile/

Re:Columnar Store for Analytics with Postgres by mtuncer on Thursday October 12, 2017 @12:36PM (#15459200)
replication support is recently added to cstore_fdw. You can replicate a cstore_fdw table to another server using streaming replication.
Re:Columnar Store for Analytics with Postgres by metdos on Thursday October 12, 2017 @10:52AM (#15458378)
This actually depends on your use case and type of your queries. There is a blog post (2014) about a benchmark: https://www.citusdata.com/blog/2014/06/14/columnar-store-ben...

I also believe that the main use case is for the compression of data more than the performance benefits.

Re:Columnar Store for Analytics with Postgres by metdos on Thursday October 12, 2017 @10:55AM (#15458405)
If you have a common filter, it is a good idea to sort your data on that field and load into cstore_fdw. This will help to use skip indexes. https://github.com/citusdata/cstore_fdw#using-skip-indexes