Tips for a Healthier Postgres Database
blog.crunchydata.com
Tips for a Healthier Postgres Database
1–10 of 87 posts
Re: Tips for a Healthier Postgres Database
#2"Set a statement timeout"
I think I would recommend setting log_min_duration_statement first and watching the logs for some time before doing that. So that you know what's going to get whacked, have some opportunity to tune it, etc.
Edit: It is mentioned, so perhaps just talking about that prior to talking about setting the timeout.
Re: Tips for a Healthier Postgres Database
#3 There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys
Every single postgres primary key (and unique constraint) automatically gets an index. That's how the unique constraint is implemented. Primary keys being naturally unique.
Re: Tips for a Healthier Postgres Database
#4I kind of wish the title was "Healthier and Happier".
Despite this calamitous oversight (wink), articles such as this are a great source to be able to draw on others' experience and reap the benefits of others hindsight without experiencing the outages or time consuming problems yourself.
Re: Tips for a Healthier Postgres Database
#5I would have said, check your shared buffers memory and concurrency are configured right for the environment you are running on. The defaults are wrong for most production environments and the right values are highly server dependent. If you haven't specifically tuned them for your production server, chances are they are wrong and you are leaving substantial memory and / or concurrency on the table.
Re: Tips for a Healthier Postgres Database
#6There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys Not to be rude or anything but I hope he doesn't mean this. Every single postgres primary key (and unique constraint) automatically gets an index. That's how the unique constraint is implemented. Primary keys being naturally unique.
I read this as "maybe a few (which are the ones automatically created on primary keys)", as opposed to redundant indices created on primary keys.
Re: Tips for a Healthier Postgres Database
#7There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys Not to be rude or anything but I hope he doesn't mean this. Every single postgres primary key (and unique constraint) automatically gets an index. That's how the unique constraint is implemented. Primary keys being naturally unique.
I read this as "maybe a few (which are the ones automatically created on primary keys)", as opposed to redundant indices created on primary keys.
I would agree IFF there wasn't the 'maybe'. If that word wasn't there I would read it as having not many tables in the beginning and thus not many primary keys and thus not many primary key indices.
'maybe' changes that dramatically unfortunately.
Re: Tips for a Healthier Postgres Database
#8I would have said, check your shared buffers memory and concurrency are configured right for the environment you are running on. The defaults are wrong for most production environments and the right values are highly server dependent. If you haven't specifically tuned them for your production server, chances are they are wrong and you are leaving substantial memory and / or concurrency on the table.
How would one go about determining the right values for these settings?
Re: Tips for a Healthier Postgres Database
#9"Set a statement timeout" I think I would recommend setting log_min_duration_statement first and watching the logs for some time before doing that. So that you know what's going to get whacked, have some opportunity to tune it, etc. Edit: It is mentioned, so perhaps just talking about that prior to talking about setting the timeout.
Good catch, will definitely update or perhaps even re-order. I'm not sure either in isolation gives you everything you need, but point noted that some sense of what is getting cancelled is equally important.
Re: Tips for a Healthier Postgres Database
#10I kind of wish the title was "Healthier and Happier". Despite this calamitous oversight (wink), articles such as this are a great source to be able to draw on others' experience and reap the benefits of others hindsight without experiencing the outages or time consuming problems yourself.
:) Love this.