Postgres database growing (a lot) without any insert
So I'm a student and I'm used to create database, make queries to extract data from it or insert some and so on, but I don't have any knowledge about maintenance. I searched for some informations on the net, but it didn't answered my problem or was too difficult for me.
Here is the thing : The database I work on has millions of lines, and every time I run a SELECT query, it get bigger and way slower, so I need to find why in order to stop it because I'm running out of space and some really slow queries can take hours to finish.
It was about 25Go last week, already 33 now, but I think it shouldn't get bigger than 10Go for all the data it contains, and I really don't have any clues about what is happening.
Some threads talk about logs files but I don't think it could get that big that fast, some other about OID but even if I don't really know what this is they are set on "OFF" on this database so I'm quiet lost.
Thank you for your time.
centos database postgresql
asked Jan 29 at 12:53
Gary FGary F
12
|
show 1 more comment
So I'm a student and I'm used to create database, make queries to extract data from it or insert some and so on, but I don't have any knowledge about maintenance. I searched for some informations on the net, but it didn't answered my problem or was too difficult for me.
Here is the thing : The database I work on has millions of lines, and every time I run a SELECT query, it get bigger and way slower, so I need to find why in order to stop it because I'm running out of space and some really slow queries can take hours to finish.
It was about 25Go last week, already 33 now, but I think it shouldn't get bigger than 10Go for all the data it contains, and I really don't have any clues about what is happening.
Some threads talk about logs files but I don't think it could get that big that fast, some other about OID but even if I don't really know what this is they are set on "OFF" on this database so I'm quiet lost.
Thank you for your time.
centos database postgresql
asked Jan 29 at 12:53
Gary FGary F
12
What's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06
|
show 1 more comment
So I'm a student and I'm used to create database, make queries to extract data from it or insert some and so on, but I don't have any knowledge about maintenance. I searched for some informations on the net, but it didn't answered my problem or was too difficult for me.
Here is the thing : The database I work on has millions of lines, and every time I run a SELECT query, it get bigger and way slower, so I need to find why in order to stop it because I'm running out of space and some really slow queries can take hours to finish.
It was about 25Go last week, already 33 now, but I think it shouldn't get bigger than 10Go for all the data it contains, and I really don't have any clues about what is happening.
Some threads talk about logs files but I don't think it could get that big that fast, some other about OID but even if I don't really know what this is they are set on "OFF" on this database so I'm quiet lost.
Thank you for your time.
centos database postgresql
asked Jan 29 at 12:53
Gary FGary F
12
So I'm a student and I'm used to create database, make queries to extract data from it or insert some and so on, but I don't have any knowledge about maintenance. I searched for some informations on the net, but it didn't answered my problem or was too difficult for me.
Here is the thing : The database I work on has millions of lines, and every time I run a SELECT query, it get bigger and way slower, so I need to find why in order to stop it because I'm running out of space and some really slow queries can take hours to finish.
It was about 25Go last week, already 33 now, but I think it shouldn't get bigger than 10Go for all the data it contains, and I really don't have any clues about what is happening.
Some threads talk about logs files but I don't think it could get that big that fast, some other about OID but even if I don't really know what this is they are set on "OFF" on this database so I'm quiet lost.
Thank you for your time.
centos database postgresql
centos database postgresql
asked Jan 29 at 12:53
Gary FGary F
12
asked Jan 29 at 12:53
Gary FGary F
12
edited Jan 29 at 13:14
Gary F
asked Jan 29 at 12:53
Gary FGary F
12
asked Jan 29 at 12:53
Gary FGary F
12
asked Jan 29 at 12:53
Gary FGary F
12
12
What's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06
|
show 1 more comment
What's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06
What's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
What's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fsuperuser.com%2fquestions%2f1399637%2fpostgres-database-growing-a-lot-without-any-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1399637%2fpostgres-database-growing-a-lot-without-any-insert%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's the error when trying to view the pgsql directory?
– Seth
Jan 29 at 12:59
It says "no files or directory of this type"
– Gary F
Jan 29 at 13:06
Did you have a look at the latest change dates? Why do you expect it to not get bigger than 10 GB? What kind of queries are you running? Does it really increase with every query? If so, by how much?
– Seth
Jan 29 at 13:33
Well I don't really know, there are a lot of files in the folder, some are the same since 19 of November and maybe half of it was updated since last week. The weird thing is that it grows with SELECT queries, and like several GB per day ! I do not know if it is the same for every query, it is difficult to test as lot of them run for hour before failing (server disconnected as result)
– Gary F
Jan 29 at 13:42
Check what queries are being ran. Maybe you are create views, setting up locks that don't get released, temporary tables etc.
– Seth
Jan 29 at 14:06