Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.
The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!
How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.
postgresql postgresql-performance
asked Apr 25 '16 at 16:13
user3000172user3000172
162
bumped to the homepage by Community♦ 1 hour ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.
The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!
How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.
postgresql postgresql-performance
asked Apr 25 '16 at 16:13
user3000172user3000172
162
bumped to the homepage by Community♦ 1 hour ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are bykey, why do you includevaluein the index? Also, if you use PostgreSQL 9.5 and yourkeyfield is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.
– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22
|
show 1 more comment
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.
The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!
How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.
postgresql postgresql-performance
asked Apr 25 '16 at 16:13
user3000172user3000172
162
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.
The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!
How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.
postgresql postgresql-performance
postgresql postgresql-performance
asked Apr 25 '16 at 16:13
user3000172user3000172
162
asked Apr 25 '16 at 16:13
user3000172user3000172
162
asked Apr 25 '16 at 16:13
user3000172user3000172
162
asked Apr 25 '16 at 16:13
user3000172user3000172
162
asked Apr 25 '16 at 16:13
user3000172user3000172
162
162
bumped to the homepage by Community♦ 1 hour ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 hour ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are bykey, why do you includevaluein the index? Also, if you use PostgreSQL 9.5 and yourkeyfield is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.
– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22
|
show 1 more comment
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are bykey, why do you includevaluein the index? Also, if you use PostgreSQL 9.5 and yourkeyfield is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.
– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are by
key, why do you include value in the index? Also, if you use PostgreSQL 9.5 and your key field is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are by
key, why do you include value in the index? Also, if you use PostgreSQL 9.5 and your key field is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22
|
show 1 more comment
1 Answer
1
active
oldest
votes
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f136485%2fpostgresql-with-just-index-storage%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
add a comment |
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
add a comment |
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
answered Oct 18 '18 at 5:44
QuickJoeQuickJoe
513
513
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f136485%2fpostgresql-with-just-index-storage%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
– a_horse_with_no_name
Apr 25 '16 at 16:18
Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
– user3000172
Apr 25 '16 at 18:12
That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
– a_horse_with_no_name
Apr 25 '16 at 19:31
@a_horse_with_no_name Thanks a bunch!
– user3000172
Apr 25 '16 at 20:24
Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are by
key, why do you includevaluein the index? Also, if you use PostgreSQL 9.5 and yourkeyfield is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.– Ziggy Crueltyfree Zeitgeister
Apr 26 '16 at 2:22