Amazon really missed the boat on Citus, a few more great acquisitions like this and Azure is going to look great (yes I know it can be still self installed on AWS).
AWS has Aurora and Redshift, along with plenty of other database tech.
Citus 10 brings columnar compression to Postgres
51–60 of 62 posts
Re: Citus 10 brings columnar compression to Postgres
#52Earlier quoted context omitted.
Again, my qualifier is "to PostgreSQL". I couldn't see such a connection on a brief skim of the A/S wikipedia entry.
I was the PM for the software side of Greenplum DB for a while. I also don't know of an earlier example of a separate column oriented storage option on top of a Postgres base. Vertica and ParAccel (which became Redshift) both also started from a Postgres base (though Vertica claims otherwise in marketing material) but were a little later. In any case, "Column oriented storage for Postgres" is almost 20 years old. Thi…
Vertica runs on top of Postgres - the grants, admin commands, etc. are postgres commands.
Source: was a Vertica DBA, received Vertica training.
Re: Citus 10 brings columnar compression to Postgres
#53Re: Citus 10 brings columnar compression to Postgres
#54Earlier quoted context omitted.
cstore_fdw had the same limitations, so even if they say these limitations may not persist forever, i am not very hopeful they really want to solve this problem. But they could solve it with just a little bit of work: * Create a hidden bitmap information to store whether the „row“ of the columnar table is still valid * When updating/deleting values only set the bitmap information to zero to indicate the value is no l…
Thank you for the suggestions! We are interested in UPDATE/DELETE. Can you describe you use case for columnar update/delete in a little more detail? Is it a few random updates, or bulk updates, or something else?
Not the previous poster, but my use case is storing site analytics (i.e. "Google Analytics"-like stuff) and allowing people to retain pageviews only for n amount of days.
Re: Citus 10 brings columnar compression to Postgres
#55Earlier quoted context omitted.
Thank you for the suggestions! We are interested in UPDATE/DELETE. Can you describe you use case for columnar update/delete in a little more detail? Is it a few random updates, or bulk updates, or something else?
Not the previous poster, but my use case is storing site analytics (i.e. "Google Analytics"-like stuff) and allowing people to retain pageviews only for n amount of days.
Can you elaborate on how you'd use update/delete?
It sounds like you might be able to do that using range partitioning instead, similar to the example in the blog post. Then just delete entire partitions when they are older than x days.
Re: Citus 10 brings columnar compression to Postgres
#56Earlier quoted context omitted.
cstore_fdw had the same limitations, so even if they say these limitations may not persist forever, i am not very hopeful they really want to solve this problem. But they could solve it with just a little bit of work: * Create a hidden bitmap information to store whether the „row“ of the columnar table is still valid * When updating/deleting values only set the bitmap information to zero to indicate the value is no l…
Thank you for the suggestions! We are interested in UPDATE/DELETE. Can you describe you use case for columnar update/delete in a little more detail? Is it a few random updates, or bulk updates, or something else?
I would need just random updates. In almost all cases i wouldn‘t update old records, but sometimes some data needs to be records needs to be rewritten or deleted because of gdpr requests.
Re: Citus 10 brings columnar compression to Postgres
#57One of the gotchas of columnar storage (coming from Redshift) is that you lose all of the compression benefits if you have just one column that’s fat or hard to compress. In Redshift columns are stored in blocks. You want to fit roughly the same number of column values per block across all your columns. But if you have one column where a small number of values can fit in a block, the rest of the columns end up leavin…
This caveat would apply for columns queried, not columns stored right?
Re: Citus 10 brings columnar compression to Postgres
#58Earlier quoted context omitted.
Correct, though it depends whether you are CPU-bound or I/O-bound. We see the latter a lot more often for large data sets. Columnar storage for PostgreSQL is especially relevant in cloud environments. Most database servers in the cloud use managed, network-attached disks because of durability, availability, and encryption-at-rest requirements. However, those do come with a performance penalty compared to local SSDs.…
That's exactly what we've seen. I don't know how Citus exactly stores data, but we found difference of 30x between gzipped Parquet and "generic Oracle table". There's a huge difference whether your analytical query is scanning full 30 GB or 1 GB (or maybe even half that or less if you need to scan just some columns).
Wouldn't it be possible to create a new type of index in Postgres (or maybe it already exists) which would take all the data of a column and simply lays it out in columnar format in memory, with all the benefit of compression ?
Re: Citus 10 brings columnar compression to Postgres
#59since we're talking postgresql: i recently started diving into postgresql and it seems to me that there is a patchwork of HA solutions (with patroni being the most feature-full) but no real multi-master solution for postgresql released under an open source license. There's BDR (bi-directional replication) but apparently 2ndquadrant pulled it back under a proprietary license, am i right? what's the current status of p…
Not multi-master, but pg_auto_failover looks to be a very nice solution for HA, and one that is operationally simple to manage: https://github.com/citusdata/pg_auto_failover
I just yesterday set up pg_auto_failover, and it was such a breeze compared to Patroni/PAF/repmgr. Less moving parts and a much simpler, but flexible setup. They did a really good job there! Also forced me to finally implement multihost support in my Postgres.js driver.
Re: Citus 10 brings columnar compression to Postgres
#60Memory, IO, Computation and DB connection concurrency all 4 have to be kept in the mind to achieve optimum latency across wide range of complex queries.
It has been my observation that if you follow 3NF design, table partition and carefully crafted Materialized Views works 8 out of 10 times when data size is huge.