Comments (8)
Inheritance support for foreign tables has been under serious discussion for some time but is not yet supported by PostgreSQL. The most recent CommitFest even had a patch to add support but it was not committed.
When supported by PostgreSQL, it's possible cstore_fdw
could be modified to support the new functionality, but we're unlikely to put together our own inheritance solution before that point.
The main purpose of inheritance in partitioning is to enable PostgreSQL to use constraint exclusion when planning queries. Since foreign tables also do not support CHECK
constraints that's not of any use here, so there's really no reason you can't do your own partitioning manually using a view defined with UNION ALL
.
from cstore_fdw.
Is there any other federation approach that can be used with FDWs, cstore_fdw specifically, to manage disk usage? The lack of DELETE support is at odds with the typical approach of avoiding partitioning with column stores.
I assume a UNION ALL view could be used, but would that introduce unwanted scans and impact SELECT performance?
from cstore_fdw.
Another option is using CitusDB. In CitusDB you can create a distributed cstore_fdw table, and once in a while use \stage command to add more shards to the table.
CitusDB will store the min/max values for each shard in its metadata catalogs, so for a query the cstore_fdw shards that aren't related are never scanned.
Once you don't need a shard anymore, you can drop it and clean up it's resources.
from cstore_fdw.
Noting from jasonmp85 that inherited tables are not currently by the Postgres FDW mechanism, closing this issue, and looking at CitusDB as a potential solution.
from cstore_fdw.
We'd love for you to give CitusDB a try and see if it fits your needs.
For completeness, cstore_fdw has built-in skip indexes. If your data inherently has a time dimension to it, and if your queries also filter on time, cstore_fdw will automatically skip over unrelated "blocks."
The number of rows skipped (in each comparison) is defined by block_row_count. This setting defaults to 10,000 and increasing it may help with your use-case.
from cstore_fdw.
CitusDB is on my short list, and initial performance tests with cstore_fdw are promising (especially on ZFS/lz4).
In this issue however, diskspace management is the interest. cstore_fdw without DELETE support means my time-series data grows rapidly (500GB/day) without a "convenient" way to trim older data. COPY only support (no INSERT/UPDATE) is not a significant concern, as append-only works for time-series metrics (provided all buckets are already aggregated), but DELETE would make things much easier.
I will be looking into the suggestion @pykello made about about CitusDB shards.
from cstore_fdw.
@pykello "In CitusDB you can create a distributed cstore_fdw table, and once in a while use \stage command to add more shards to the table."
Perhaps a FAQ or post on how to do this for CitusDB newbies (like me)? Specifically, how are shards managed and dropped?
Creation of shards is touched on in the examples with \stage, and http://www.citusdata.com/docs/before-production states "If disk space becomes an issue, users need to manually clean up the old shards in the database", but I cannot find documentation as to the process.
from cstore_fdw.
For completeness, it looks like inheritance support for foreign tables has been part of 9.4's commit fest.
https://commitfest.postgresql.org/action/patch_view?id=1386
If this change goes in, cstore_fdw will automatically pick up inheritance support.
from cstore_fdw.
Related Issues (20)
- select count(1) from table where datecolum = '2020-02-01' slow HOT 4
- Missing dependencies in makefile targets
- Cant ./configure in CentOS 07
- How to make use of parallelization with cstore_fdw? HOT 2
- Tests not running on Travis, and missing CircleCI configs
- Support for Heroku Postgres / Citus Data addon? HOT 1
- Is cstore_fdw still supported by developers? HOT 3
- segfault during copy into foreign table HOT 2
- hll build error against v13 HOT 5
- Does cstore_fdw Support Parallel COPY into the Same Table?
- Integer overflow in cstore_clean_table_resources()
- How to insert load / into cstore foreign tables in order HOT 1
- Impact of number of partitions on cstore tables
- Skip indexes are not in use HOT 2
- make installcheck failed, "ERROR: could not open file ... for writing: Permission denied" HOT 1
- Support for PostgreSQL15 HOT 1
- v1.7.0 download files does not support PostgreSQL 13 HOT 1
- 目录不存在
- Unable to upgrade from cstore_fdw to citus
- pg14 is support?
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from cstore_fdw.