cstore_fdw, the column-store extension for PostgreSQL by CitusData, is a really good way to add compressed storage for archival data, and analytic data intended to be aggregated, to your application. Because it's a column store, though, cstore wants new data added in batches, the bigger the better. This means that you need to find some way to batch-up incoming data, preferably one able to accept a lot of new data rapidly.
This is where PipelineDB comes in. Since PipelineDB is open source now, and based on 9.4.4, I can add extensions to it, including cstore_fdw. I've done so with the PipelineDB 0.7.7a Docker container, so if you use Docker it's simply available.
As a demonstration of this, I'll set up some fake clickstream data, archived to a cstore table hourly. First, I wrote a quick python script to generate it continuously and push it to a Pipeline stream.
Then I created the stream and continuous view in PipelineDB:
CREATE STREAM seenstream ( user_id int, page_id int, ts timestamptz );
CREATE CONTINUOUS VIEW seenfeed as select user_id, page_id, ts
FROM seenstream
WHERE arrival_timestamp > ( clock_timestamp() - interval '90 minutes' );
Next, I created the cstore table:
CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
CREATE FOREIGN TABLE seen (
user_id int,
page_id int,
ts timestamp tz
)
SERVER cstore_server
OPTIONS (compression 'pglz');
Finally, I added a simple script which ran the following query once per hour:
INSERT INTO seen
SELECT user_id, page_id, ts
FROM seenfeed
WHERE ts >= ( now() - interval '1 hour' )
ORDER BY user_id, page_id, ts;
... and then I started everything in motion.
Now, for cstore the ORDER BY is vitally important; it determines how the blocks you create for the column store are organized. In this particular case, I knew that I would be doing more analysis by user. But most users would do ORDER BY ts instead.
After a few hours, I checked back, and now I can run some analytical queries on the cstore table. For example, user activity:
select user_id, count(distinct page_id), max(ts)
from seen group by user_id;
user_id | count | max
--------+-------+-------------------------------
1 | 92 | 2015-08-11 22:59:51.504777+00
2 | 86 | 2015-08-11 22:54:09.77318+00
3 | 89 | 2015-08-11 22:59:14.574697+00
page activity:
select page_id, count(distinct user_id) as duv, max(ts) as last
from seen group by page_id order by duv desc;
page_id | duv | last
--------+-----+-------------------------------
71 | 96 | 2015-08-11 22:59:38.690743+00
99 | 96 | 2015-08-11 22:58:43.004618+00
4 | 96 | 2015-08-11 22:57:45.95007+00
... and more.
Now, in a production environment, you'll want to do more than that. Depending on traffic, you might batch inserts daily instead of hourly. You might want to use several continuous views to store different summaries of the data instead of raw data. But the above should be enough to show you how well these two tools go together.
Showing posts with label big data. Show all posts
Showing posts with label big data. Show all posts
Wednesday, August 12, 2015
Friday, August 7, 2015
Understanding Unintuitive TABLESAMPLE Results
In 9.5 Alpha 2:
create table mil ( id int, val text );
insert into mil select i, i::text || '-val' from
generate_series(1,1000000) as gs(i);
analyze;
postgres=# select * from mil tablesample system ( 0.04 );
id | val
----+-----
(0 rows)
Huh, what?
So, what I didn't understand here is the way rows are selected for TABLESAMPLE SYSTEM. Since SYSTEM is page-based, I thought that we selected the requested % of pages, and then pick that many pages at random. Since this table had exactly 185 rows per page, it should return 370 rows every time (2 pages). But that's not what happened. In fact, running the following query I got a variety of counts:
SELECT count(*) FROM (select * from mil tablesample system ( 0.04 ) ) as a;
What's really happening is that pages for SYSTEM are selected a different way. Each page is checked against the probability once. This means that, while on average you'll get the number of pages you're expecting, the numbers will vary from request to request quite a bit.
This also means that SYSTEM is a bad choice for really small sample sizes, like 0.01%. BERNOULLI is better, because it'll be checking by row, and therefore the size of the return sample will be much more predictable. It will still have a bit of variation, though; in my testing, +/- 10% on a few hundred rows.
Gulcin Yildirim has a great explanation of this on the 2nd Quadrant blog.
So, what if you need TABLESAMPLE to get a very specific number of rows for you? Well, that's why Petr Jelinek wrote the optional (loadable) SYSTEM_ROWS sampling method. This can be loaded as the tsm_system_rows extension in 9.5.
Hopefully that helps folks be less confused than me about how TABLESAMPLE works.
create table mil ( id int, val text );
insert into mil select i, i::text || '-val' from
generate_series(1,1000000) as gs(i);
analyze;
postgres=# select * from mil tablesample system ( 0.04 );
id | val
----+-----
(0 rows)
Huh, what?
So, what I didn't understand here is the way rows are selected for TABLESAMPLE SYSTEM. Since SYSTEM is page-based, I thought that we selected the requested % of pages, and then pick that many pages at random. Since this table had exactly 185 rows per page, it should return 370 rows every time (2 pages). But that's not what happened. In fact, running the following query I got a variety of counts:
SELECT count(*) FROM (select * from mil tablesample system ( 0.04 ) ) as a;
370
370
370
555
555
185
0
925
925? 0? What the hey?
What's really happening is that pages for SYSTEM are selected a different way. Each page is checked against the probability once. This means that, while on average you'll get the number of pages you're expecting, the numbers will vary from request to request quite a bit.
This also means that SYSTEM is a bad choice for really small sample sizes, like 0.01%. BERNOULLI is better, because it'll be checking by row, and therefore the size of the return sample will be much more predictable. It will still have a bit of variation, though; in my testing, +/- 10% on a few hundred rows.
Gulcin Yildirim has a great explanation of this on the 2nd Quadrant blog.
So, what if you need TABLESAMPLE to get a very specific number of rows for you? Well, that's why Petr Jelinek wrote the optional (loadable) SYSTEM_ROWS sampling method. This can be loaded as the tsm_system_rows extension in 9.5.
Hopefully that helps folks be less confused than me about how TABLESAMPLE works.
Saturday, March 28, 2015
Crazy SQL Saturday: replacing SciPy with SQL
I have a data analytics project which produces multiple statistical metrics for a large volume of sensor data. This includes percentiles (like median and 90%) as well as average, min and max. Originally this worked using PL/R, which was pretty good except that some of the R modules were crashy, which was not so great for uptime.
This is why, two years ago, I ripped out all of the PL/R and replaced it with PL/Python and SciPy. I love SciPy because it gives me everything I liked about R, without most of the things I didn't like. But now, I've ripped out the SciPy as well. What am I replacing it with? Well, SQL.
In version 9.4, Andrew Gierth added support for percentiles to PostgreSQL via WITHIN GROUP aggregates. As far as I'm concerned, this is second only to JSONB in reasons to use 9.4.
Now, one of the more complicated uses I make of aggregates is doing "circular" aggregates, that is producing percentiles for a set of circular directions in an effort to determine the most common facings for certain devices. Here's the PL/Python function I wrote for this, which calculates circular aggregates using the "largest gap" method. This algorithm assumes that the heading measurements are essentially unordered, so to find the endpoints of the arc we look for two measurements which are the furthest apart on the circle. This means shifting the measurements to an imaginary coordinate system where the edge of this gap is the low measurement, calculating percentiles, and then shifting it back. Note that this method produces garbage if the device turned around a complete circle during the aggregate period.
Now, that SciPy function was pretty good and we used it for quite a while. But we were unhappy with two things: first, SciPy is rather painful as a dependency because the packaging for it is terrible; second, having PostgreSQL call out to SciPy for each iteration isn't all that efficient.
So, since 9.4 has percentiles now, I started writing a function based the built-in SQL percentiles. Initially I was thinking it would be a PL/pgSQL function, but was pleasantly surprised to find that I could write it entirely as a SQL function! Truly, Postgres's SQL dialect is turing-complete.
So here's the new all-SQL function, with some helper functions.
Then I performance tested it, and was pleasantly surprised again. The SciPy version took 2.6 seconds* to aggregate 100,000 sets of 20 measurements. The new SQL version takes 40 milleseconds, cutting response time by 98%. Wow!
And I've eliminated a hard-to-install dependency. So it's all win. Of course, if anyone has ideas on making it even faster, let me know.
Pushing the limits of SQL to the edge of insanity.
(* note: I expect that most of the extra time for the SciPy version is in calling out to Python through PL/Python, rather than in SciPy itself.)
This is why, two years ago, I ripped out all of the PL/R and replaced it with PL/Python and SciPy. I love SciPy because it gives me everything I liked about R, without most of the things I didn't like. But now, I've ripped out the SciPy as well. What am I replacing it with? Well, SQL.
In version 9.4, Andrew Gierth added support for percentiles to PostgreSQL via WITHIN GROUP aggregates. As far as I'm concerned, this is second only to JSONB in reasons to use 9.4.
Now, one of the more complicated uses I make of aggregates is doing "circular" aggregates, that is producing percentiles for a set of circular directions in an effort to determine the most common facings for certain devices. Here's the PL/Python function I wrote for this, which calculates circular aggregates using the "largest gap" method. This algorithm assumes that the heading measurements are essentially unordered, so to find the endpoints of the arc we look for two measurements which are the furthest apart on the circle. This means shifting the measurements to an imaginary coordinate system where the edge of this gap is the low measurement, calculating percentiles, and then shifting it back. Note that this method produces garbage if the device turned around a complete circle during the aggregate period.
Now, that SciPy function was pretty good and we used it for quite a while. But we were unhappy with two things: first, SciPy is rather painful as a dependency because the packaging for it is terrible; second, having PostgreSQL call out to SciPy for each iteration isn't all that efficient.
So, since 9.4 has percentiles now, I started writing a function based the built-in SQL percentiles. Initially I was thinking it would be a PL/pgSQL function, but was pleasantly surprised to find that I could write it entirely as a SQL function! Truly, Postgres's SQL dialect is turing-complete.
So here's the new all-SQL function, with some helper functions.
Then I performance tested it, and was pleasantly surprised again. The SciPy version took 2.6 seconds* to aggregate 100,000 sets of 20 measurements. The new SQL version takes 40 milleseconds, cutting response time by 98%. Wow!
And I've eliminated a hard-to-install dependency. So it's all win. Of course, if anyone has ideas on making it even faster, let me know.
Pushing the limits of SQL to the edge of insanity.
(* note: I expect that most of the extra time for the SciPy version is in calling out to Python through PL/Python, rather than in SciPy itself.)
Thursday, December 4, 2014
What's this pg_shard thing?
I've been working with CitusData for a while, and I'm excited that they've finally released pg_shard, a generic "sharding" extension for PostgreSQL databases. The press releases have all of the noise about this, so this blog post is for the nuts-and-bolts of pg_shard.
pg_shard takes one or more tables in your database and distributes them over multiple databases on multiple servers, or "shards". Rows are distributed based on a hash function on the primary key which you define. The hash function used is Postgres' own hash function for hash indexes.
A single node is the "master node" which contains templates for the distributed tables, as and transparently handles making sure that inserts, updates, and selects go to the right shards. To ensure redundancy, sharded tables can be defined as having multiple copies across multiple shards.
SELECTs are distributed based on comparing constants in your WHERE clause and other filter conditions against the hashed key, allowing you to query against only the shards which have compliant data. This should speed up queries for really large tables a great deal.
Where there are multiple copies of shards, pg_shard notices timeouts in contacting the shards and marks certain shards as offline.
All of this is done inside PostgreSQL, most of it using our defined APIs and hooks. The only thing the Citus team had to fork was ruleutils.c, and they're hoping to change that in 9.5 so forking that isn't required either.
First, since the rows are distributed using a hash function, only equality comparisons are currently supported. This means that if you query your table on ranges (such as BETWEEN two timestamps) it will scan all shards for that data. Adding range partitioning to pg_shard is planned.
Second, unlike Postgres-XC/Postgres-XL, transactions between shards and distributed JOINs are not supported. Those features are among the things which distinguish CitusData's proprietary product.
Third, currently only one master node is supported. That means that pg_shard isn't yet a solution for supporting applications which need massive numbers of connections. Supporting multiple query nodes is planned for the future.
Complex distribution rules, like requiring each shard to be copied to a specific subset of hosts in a different availability zone or data center, is also not supported. It's not clear when/if it will be.
A lot of web applications consist of "One Big Table" (OBT) which by size is 90% or more of the database. This might be a users table, or a messages table, a log table, or a pictures table. Years ago, this was exactly the problem we faced with Instagram.
pg_shard will allow users to distribute the OBT across many nodes in a public cloud, letting users scale to much larger sizes. Particularly, it makes it much easier to redistribute the OBT so that it fits in RAM on each cloud node, both decreasing overall cloud hosting cost and improving throughput and response times. And since there's shard redundancy, they are protected against certain kinds of cloud failures.
As Instagram and Wanelo have done, you can implement this kind of sharding on the application layer ... and when you get to their scale, you'll probably have to. But most PostgreSQL users aren't at that scale yet, and pg_shard can help them get over the "hump" of going from one server to several with a lot less pain.
First, over the next week I'll be building a "Postgre-NoSQL" database using PostgreSQL 9.4, pg_shard and JSONB, to demonstrate that you can replace some non-relational databases with PostgreSQL. Expect more on this blog.
If you're in the Bay Area, then join us for an SFPUG meeting next week, which will be all about pg_shard. If possible, we'll have live video, but no promises.
What does pg_shard do?
pg_shard takes one or more tables in your database and distributes them over multiple databases on multiple servers, or "shards". Rows are distributed based on a hash function on the primary key which you define. The hash function used is Postgres' own hash function for hash indexes.
A single node is the "master node" which contains templates for the distributed tables, as and transparently handles making sure that inserts, updates, and selects go to the right shards. To ensure redundancy, sharded tables can be defined as having multiple copies across multiple shards.
SELECTs are distributed based on comparing constants in your WHERE clause and other filter conditions against the hashed key, allowing you to query against only the shards which have compliant data. This should speed up queries for really large tables a great deal.
Where there are multiple copies of shards, pg_shard notices timeouts in contacting the shards and marks certain shards as offline.
All of this is done inside PostgreSQL, most of it using our defined APIs and hooks. The only thing the Citus team had to fork was ruleutils.c, and they're hoping to change that in 9.5 so forking that isn't required either.
What does pg_shard not do?
First, since the rows are distributed using a hash function, only equality comparisons are currently supported. This means that if you query your table on ranges (such as BETWEEN two timestamps) it will scan all shards for that data. Adding range partitioning to pg_shard is planned.
Second, unlike Postgres-XC/Postgres-XL, transactions between shards and distributed JOINs are not supported. Those features are among the things which distinguish CitusData's proprietary product.
Third, currently only one master node is supported. That means that pg_shard isn't yet a solution for supporting applications which need massive numbers of connections. Supporting multiple query nodes is planned for the future.
Complex distribution rules, like requiring each shard to be copied to a specific subset of hosts in a different availability zone or data center, is also not supported. It's not clear when/if it will be.
So what is pg_shard useful for right now?
A lot of web applications consist of "One Big Table" (OBT) which by size is 90% or more of the database. This might be a users table, or a messages table, a log table, or a pictures table. Years ago, this was exactly the problem we faced with Instagram.
pg_shard will allow users to distribute the OBT across many nodes in a public cloud, letting users scale to much larger sizes. Particularly, it makes it much easier to redistribute the OBT so that it fits in RAM on each cloud node, both decreasing overall cloud hosting cost and improving throughput and response times. And since there's shard redundancy, they are protected against certain kinds of cloud failures.
As Instagram and Wanelo have done, you can implement this kind of sharding on the application layer ... and when you get to their scale, you'll probably have to. But most PostgreSQL users aren't at that scale yet, and pg_shard can help them get over the "hump" of going from one server to several with a lot less pain.
Can you give some examples of pg_sharding a database?
First, over the next week I'll be building a "Postgre-NoSQL" database using PostgreSQL 9.4, pg_shard and JSONB, to demonstrate that you can replace some non-relational databases with PostgreSQL. Expect more on this blog.
If you're in the Bay Area, then join us for an SFPUG meeting next week, which will be all about pg_shard. If possible, we'll have live video, but no promises.
Tuesday, August 12, 2014
SFPUG Video on YouTube
TL;DR: SFPUG video available on YouTube, including streaming video tonight. I need help converting old videos and making new ones.
First, we have a few SFPUG videos available on YouTube:
We will have streaming video for tonight's SFPUG, which will be on Postgres-XL. Join the Google Event to get a link to the streaming video once it starts; expected start time is 7:15PM PST.
This brings me to the third thing, which is that I could use some help with video, in two areas:
First, we have a few SFPUG videos available on YouTube:
- Wanelo.com presented "12-Step Program for Database Application Performance"
- CitusData presented "cstore_FDW for Big Data"
We will have streaming video for tonight's SFPUG, which will be on Postgres-XL. Join the Google Event to get a link to the streaming video once it starts; expected start time is 7:15PM PST.
This brings me to the third thing, which is that I could use some help with video, in two areas:
- I desperately need someone to help take better-quality videos of SFPUG meetings, with better focus and sound. Right now I'm using a Logitech webcam, and it's just not cutting it. Video will need to be compatible with Google Hangouts, unless you have another plan for broadcasting.
- I have 7 or 8 videos of SFPUG meetings from 2013, including Tom Lane explaining the query planner, in FLV format. I need someone to transcode these to a YouTube format and upload them.
Thursday, May 15, 2014
9.4 Beta, Postgres-XL, and pgCon Events
So, in case you somehow missed it, the PostgreSQL 9.4 Beta 1 is out. Yaay! Here's what I have to say about that:
libdata=# select title,
bookdata #> '{"publication_info", 0, "isbn"}' as isbn
from booksdata
where bookdata @> '{ "publication_info" : [{"publisher": "Avon"} ] }'::jsonb
order by bookdata #> '{"publication_info", 0, "price"}' DESC;
title | isbn
--------------------------------------+-----------------
The Bewitched Viking | "0-06-201900-7"
When a Scot Loves a Lady | "0-06-213120-6"
Eternal Prey | "0-06-201895-7"
My Irresistible Earl | "0-06-173396-2"
...
Download the beta now and test it out! Break it! Tell us how you broke it! It's a beta, and it's up to you to make sure that the final release is as robust as possible.
Speaking of betas, there's a new new open source big data option on the block: Postgres-XL. This is a fork of PostgresXC, which supposedly resolves the blockers which have kept PostgresXC from being ready for production use. I look forward to trying it out when I get a chance.
Finally, I wanted to remind everyone about the Clustering Summit, the PostgresXC Pizza Demo, and the Unconference at pgCon next week. Especially, I still need two assistants to help me with the unconference. Email me at josh-at-postgresql.org if you're available to help with setup at the unconference.
libdata=# select title,
bookdata #> '{"publication_info", 0, "isbn"}' as isbn
from booksdata
where bookdata @> '{ "publication_info" : [{"publisher": "Avon"} ] }'::jsonb
order by bookdata #> '{"publication_info", 0, "price"}' DESC;
title | isbn
--------------------------------------+-----------------
The Bewitched Viking | "0-06-201900-7"
When a Scot Loves a Lady | "0-06-213120-6"
Eternal Prey | "0-06-201895-7"
My Irresistible Earl | "0-06-173396-2"
...
Download the beta now and test it out! Break it! Tell us how you broke it! It's a beta, and it's up to you to make sure that the final release is as robust as possible.
Speaking of betas, there's a new new open source big data option on the block: Postgres-XL. This is a fork of PostgresXC, which supposedly resolves the blockers which have kept PostgresXC from being ready for production use. I look forward to trying it out when I get a chance.
Finally, I wanted to remind everyone about the Clustering Summit, the PostgresXC Pizza Demo, and the Unconference at pgCon next week. Especially, I still need two assistants to help me with the unconference. Email me at josh-at-postgresql.org if you're available to help with setup at the unconference.
Monday, May 12, 2014
cstore_fdw and big data
About a month ago, PostgreSQL fork vendor and Data Warehousing company CitusDB announced the availability of the open-source cstore_fdw. This foreign data wrapper creates an external table with highly compressed data, which allows you to keep large amounts of archival data on your PostgreSQL server.
You can find out more if you want to tune in tommorrow night, May 13th, around 7:15PM PDT. Tomorrow night's even will be sponsored by CitusDB and hosted by Rackspace.
First, the good stuff: compression:
phc=# select pg_size_pretty(pg_total_relation_size('postgres_log'));
pg_size_pretty
----------------
28 GB
ls -lh $PGDATA/base
-rw------- 1 postgres postgres 3.2G May 12 13:37 pglog.cstore
-rw------- 1 postgres postgres 12K May 12 13:37 pglog.cstore.footer
So, the "postgres_log" table from this Performance Health Check database, which has 15 million records, takes up 28GB in postgres, and 3.2GB as a cstore table ... a space savings of about 89%. Not bad. Especially if you consider that the cstore table already has skip indexes on all indexable columns.
Now, where this space savings becomes a real benefit is if the cstore table fits in memory and the Postgres table doesn't. I don't have a case like that, although the cstore still does show performance benefits if you have a wide table and you don't need all columns:
phc=# select count(1) from postgres_log where command_tag = 'UPDATE';
count
--------
986390
(1 row)
Time: 23746.476 ms
phc=# select count(1) from c_pglog where command_tag = 'UPDATE';
count
--------
986390
(1 row)
Time: 14059.405 ms
And even better if you can apply a relatively restrictive filter:
phc=# select count(1) from postgres_log where command_tag = 'UPDATE' and log_time BETWEEN '2014-04-16 07:15:00' and '2014-04-16 07:20:00';
count
-------
84982
(1 row)
Time: 19653.746 ms
phc=# select count(1) from c_pglog where command_tag = 'UPDATE' and log_time BETWEEN '2014-04-16 07:15:00' and '2014-04-16 07:20:00';
count
-------
84982
(1 row)
Time: 2260.891 ms
One limitation is that currently, with FDWs not able to cleanly push down aggregation to the foreign data wrapper, the actual aggregation is still done on the postgres side. This means that large aggregates are about the same speed on cstore_fdw as they are for PostgreSQL tables:
phc=# select round((sum(duration)/1000)::numeric,2) from statements where command_tag = 'UPDATE'; round
--------
444.94
(1 row)
Time: 2920.640 ms
phc=# select round((sum(duration)/1000)::numeric,2) from c_statements where command_tag = 'UPDATE';
round
--------
444.94
(1 row)
Time: 3232.986 ms
The project plans to fix this, but until then, cstore_fdw is useful mainly for searches across really large/wide tables. Or for seldom-touched archive tables where you want to save yourself GB or TB of disk space.
There are a bunch of other features, and a bunch of other limitations; tune in to the SFPUG event to learn more.
You can find out more if you want to tune in tommorrow night, May 13th, around 7:15PM PDT. Tomorrow night's even will be sponsored by CitusDB and hosted by Rackspace.
First, the good stuff: compression:
phc=# select pg_size_pretty(pg_total_relation_size('postgres_log'));
pg_size_pretty
----------------
28 GB
ls -lh $PGDATA/base
-rw------- 1 postgres postgres 3.2G May 12 13:37 pglog.cstore
-rw------- 1 postgres postgres 12K May 12 13:37 pglog.cstore.footer
So, the "postgres_log" table from this Performance Health Check database, which has 15 million records, takes up 28GB in postgres, and 3.2GB as a cstore table ... a space savings of about 89%. Not bad. Especially if you consider that the cstore table already has skip indexes on all indexable columns.
Now, where this space savings becomes a real benefit is if the cstore table fits in memory and the Postgres table doesn't. I don't have a case like that, although the cstore still does show performance benefits if you have a wide table and you don't need all columns:
phc=# select count(1) from postgres_log where command_tag = 'UPDATE';
count
--------
986390
(1 row)
Time: 23746.476 ms
phc=# select count(1) from c_pglog where command_tag = 'UPDATE';
count
--------
986390
(1 row)
Time: 14059.405 ms
And even better if you can apply a relatively restrictive filter:
phc=# select count(1) from postgres_log where command_tag = 'UPDATE' and log_time BETWEEN '2014-04-16 07:15:00' and '2014-04-16 07:20:00';
count
-------
84982
(1 row)
Time: 19653.746 ms
phc=# select count(1) from c_pglog where command_tag = 'UPDATE' and log_time BETWEEN '2014-04-16 07:15:00' and '2014-04-16 07:20:00';
count
-------
84982
(1 row)
Time: 2260.891 ms
One limitation is that currently, with FDWs not able to cleanly push down aggregation to the foreign data wrapper, the actual aggregation is still done on the postgres side. This means that large aggregates are about the same speed on cstore_fdw as they are for PostgreSQL tables:
phc=# select round((sum(duration)/1000)::numeric,2) from statements where command_tag = 'UPDATE'; round
--------
444.94
(1 row)
Time: 2920.640 ms
phc=# select round((sum(duration)/1000)::numeric,2) from c_statements where command_tag = 'UPDATE';
round
--------
444.94
(1 row)
Time: 3232.986 ms
The project plans to fix this, but until then, cstore_fdw is useful mainly for searches across really large/wide tables. Or for seldom-touched archive tables where you want to save yourself GB or TB of disk space.
There are a bunch of other features, and a bunch of other limitations; tune in to the SFPUG event to learn more.
Friday, August 23, 2013
PostgreSQL plus Vertica on Tuesday: SFPUG Live Video
This upcoming Tuesday, the 27th, SFPUG will have live streaming video of Chris Bohn from Etsy talking about how he uses PostgreSQL and Vertica together to do data analysis of Etsy's hundreds of gigabytes of customer traffic. barring technical difficulties with the video or internet, of course.
The video will be on the usual SFPUG Video Channel. It is likely to start around 7:15PM PDT. Questions from the internet will be taken on the attached chat channel.
For those in San Francisco, this event will be held at Etsy's new downtown SF offices, and Etsy is sponsoring a Tacolicious taco bar. Of course, the event is already full up, but you can always join the waiting list.
In other, related events, sfPython will be talking about PostgreSQL performance, and DjangoSF will be talking about multicolumn joins, both on Wednesday the 28th. I'll be at DjangoSF, doing my "5 ways to Crash Postgres" lightning talk.
The video will be on the usual SFPUG Video Channel. It is likely to start around 7:15PM PDT. Questions from the internet will be taken on the attached chat channel.
For those in San Francisco, this event will be held at Etsy's new downtown SF offices, and Etsy is sponsoring a Tacolicious taco bar. Of course, the event is already full up, but you can always join the waiting list.
In other, related events, sfPython will be talking about PostgreSQL performance, and DjangoSF will be talking about multicolumn joins, both on Wednesday the 28th. I'll be at DjangoSF, doing my "5 ways to Crash Postgres" lightning talk.
Monday, July 9, 2012
SFPUG July: MADLib
Tommorrow Hitoshi Harada of Greenplum will be presenting about MADlib, the analytics and machine learning library for PostgreSQL at the offices of SwitchFly in San Francisco. The meetup is already full, so if you can't make it, you can tune in on live streaming video.
This time, we'll make sure the video stays up so that folks and watch it later.
This time, we'll make sure the video stays up so that folks and watch it later.
Subscribe to:
Posts (Atom)