7 ms·

PostgreSQL Configuration for Humans

262 points by sharjeelsayed 6y ago

nix23 6y ago
Most of the time i just use that: https://pgtune.leopard.in.ua/#/ https://pgtune.leopard.in.ua/#/
- 2mol 6y ago
  That's awesome! Do you know something similar for sane security settings?
  - nix23 6y ago
    No sorry. But for SSL security this is really nice: https://ssl-config.mozilla.org/#server=postgresql&version=12.1&config=intermediate&openssl=1.1.1d&guideline=5.6 https://ssl-config.mozilla.org/#server=postgresql&version=12...
- ar-jan 6y ago
  Do you know if the differences in recommended settings between Web Application and Desktop Application are mainly about network-related aspects (latency etc.), or more about typical access patterns of web vs desktop? I'm thinking of the configuration for a remote PostgreSQL server, to be accessed from a local desktop application over the internet.
  - taffer 6y ago
    Desktop Application assumes that the server is running on the user's workstation and is very conservative in terms of memory usage to avoid interfering with other applications. Web Application, on the other hand, assumes that the server is running on a dedicated computer and has all memory for itself.
- pingiun 6y ago
  This seems pretty cool, but it would benefit from some extra information on what it considers. And maybe links to documentation about the options your setting so you can double check.
  - nix23 6y ago
    It's just a webified version of pgtune: https://linux.die.net/man/8/pgtune https://linux.die.net/man/8/pgtune https://github.com/gregs1104/pgtune https://github.com/gregs1104/pgtune
- ahachete 6y ago
  One of the main reasons https://postgresql.conf https://postgresql.conf exists is precisely to provide as much information as possible to understand how to tune PostgreSQL's configuration. If generating a config would be as simple as filling in a form, this wouldn't make sense. It's tempting to think it may be a good starting point. Unfortunately, it might as well be a bad starting point: form-based configurations may be totally counter-productive. For instance, max_connections is a parameter that may cause outages or very bad performance if not properly configured. And its value depends, if anything, on having or not a connection pool. work_mem heavily depends on the types of queries that you run. Plus I disagree with some of the other choices made by pgtune's default behaviour. All in all, I'd recommend to do a deeper study of the parameters, or seek some expert help, specially for production environments. Disclaimer: I'm part of the team behind postgresql.conf
  - nix23 6y ago
    Hy, Unfortunately the site is unavailable: https://postgresql.conf/ https://postgresql.conf/ I never wrote it's a brainless goto conf generator. >max_connections is a parameter that may cause outages or very bad performance if not properly configured. That's why you can define the "Number of Connections"
    - ahachete 6y ago
      But if you don't, it suggests you one. My point here is that some of the suggestions provided, if taken by a non expert user, may do more harm than good. And the reason is that there are many factors that need to be considered for most of the parameters. Something it's hard to capture from a form-based configuration generation tool. That's why my recommendation of not using form-based configuration generation tools. They can be counter-productive.
  - ahachete 6y ago
    Edit: url above is wrong, correct one is https://postgresqlco.nf https://postgresqlco.nf my brain tricked me ;)
daniellarusso 6y ago
I thought this was going to be a written word article. It is a 52 minute video. Shame on me for not looking at the domain. It is a conference.
- deleted 6y ago
  [deleted]
- kid_atticus 6y ago
  It is not. Look at the list on the left side menu. It gives recommendations and detailed descriptions for each tunable parameter in postgres.
  - framecowbird 6y ago
    Yeah, it's definitely a disappointing onboarding. It looks like the site is there to host a single video, but actually the real value is hidden on the left
    - tuatoru 6y ago
      And there's not much value there, because there's no structure apart from alphabetical in that list. Which are the most important knobs to twist? Which ones should you never, never touch without 20 years' experience and a very peculiar requirement? Give me something to read, please. "In one ear and out the other" is a saying for a very good reason.
      - ahachete 6y ago
        Thank you for the feedback, we take it seriously. There's an upcoming new version that will come with something similar to what you say: some "guide" on which parameters to tune, classified by categories, and with varying level of "expertise" (basic, medium, advanced). With specific guidance for those set of parameters. Would that be useful to you?
        6y ago
cpursley 6y ago
Does anyone know if there's a way to pull secrets into PG from an environment variable or similar and access it within PG in a way that does not directly expose the value to the db user or to logs?
- nix23 6y ago
  With pgcrypto? https://x-team.com/blog/storing-secure-passwords-with-postgresql/ https://x-team.com/blog/storing-secure-passwords-with-postgr...
  - sk5t 6y ago
    Won't the advice in that cursory blog post expose the plaintext to query logs? IMHO doing the hashing and password comparison in application code has benefits in both shifting the compute workload to horizontally scalable non-db workers and keeping the plaintext off the wire/queries.
- ants_a 6y ago
  While environment variables are not the most bulletproof place to store secrets, a plpython function will be able to access environment variable via the os module.
RedShift1 6y ago
Any guides on how to combine SSD's, software RAID (Linux md) and ext4 for best performance? Plenty of guides for the individual components but not all of them together
technics256 6y ago
How much does something like RDS get us there regarding configuration tuning?
- twunde 6y ago
  RDS takes care of 1/3 to 1/2 of the configurations, particularly around hardware related options. However, since postgres uses MVCC, it has a lot of tuning options around autovacuum and replication that impact performance. This is even more true if you have access patterns that don't take into account postgres' MVCC (are you updating 10%+ of large tables daily, triggering autovacuums?) but which would be fine with mysql
- apavlo 6y ago
  We have seen up to a 2x improvement in Postgres' throughput after tuning it with OtterTune over the default Amazon configuration. Here is an overview of how OtterTune's algorithms work: https://aws.amazon.com/blogs/machine-learning/tuning-your-dbms-automatically-with-machine-learning/ https://aws.amazon.com/blogs/machine-learning/tuning-your-db... Hit us up to see if we can do the same for you: https://ottertune.com/demo.html https://ottertune.com/demo.html
spapas82 6y ago
For me, the greatest pain of postgresql is upgrading between major versions. You need to install both versions to the same db server at the same time and then Tthe data must be copied (using pg_upgrade) between the two clusters, something that leads to downtime (that is proportional to the size of the data ie how much time it needs to copy and run some maintenance commands). If you consider that our cluster hosts databases for like 15 apps and each needs to be changed to display some "maintenance mode" home page you'lln understand my frustration and fear of major upgrades... I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version, or using logical replication between the clusters and switching to the new version cluster. The problem is that since I am not a full time DBA (and my organisation doesn't have one) I don't trust myself with these techniques and rely to the trusted pg_upgrade method that will leave the old cluster as it was with all its data intact in case anything went wrong! The only good thing is that postgresql supports each major version for a lot of time (5 years) so such updates don't need to be very frequent :)
- saturn_vk 6y ago
  i wonder if you can attach a replica with a bigger major version, let it sync, and drop the old versions?
  - spapas82 6y ago
    Yes I think this is possible (at least there are some blog posts recommending that way for upgrading without downtime) however configuring (and testing) that mechanism is not something I want to do :|
- uhoh-itsmaciek 6y ago
  >that is proportional to the size of the data I agree upgrades are still a pain point, but pg_upgrade running time is not proportional to the size of the data.
  - spapas82 6y ago
    Maybe i didn't express myself clearly (english is not my primary language) but since with pg_upgrade the data will be copied from the old cluster to the new one, doing an upgrade to a 10GB database would need ~ 10 times more than upgrading a 1 GB database, just for the data copying. So the time will be proportional to the size of the data.
abledon 6y ago
Has anyone other than Amazon successfully migrated off oracle.. and ported stuff into Postgres... e.g. 1000s of PL/SQL procedures/packages moved to postgres
samokhvalov 6y ago
I use this almost daily. Last time it was an hour ago -- checked when hot_standby's default changed from off to on (it was in Postgres 10). Highly recommend it.
KoenDG 6y ago
The nice thing about this website it the extra explanation on most of the options, with considerations explained.

262 points by sharjeelsayed 6y ago

nix23 6y ago

Most of the time i just use that: https://pgtune.leopard.in.ua/#/ https://pgtune.leopard.in.ua/#/

2mol 6y ago
That's awesome! Do you know something similar for sane security settings?
- nix23 6y ago
  No sorry. But for SSL security this is really nice: https://ssl-config.mozilla.org/#server=postgresql&version=12.1&config=intermediate&openssl=1.1.1d&guideline=5.6 https://ssl-config.mozilla.org/#server=postgresql&version=12...
ar-jan 6y ago
Do you know if the differences in recommended settings between Web Application and Desktop Application are mainly about network-related aspects (latency etc.), or more about typical access patterns of web vs desktop? I'm thinking of the configuration for a remote PostgreSQL server, to be accessed from a local desktop application over the internet.
- taffer 6y ago
  Desktop Application assumes that the server is running on the user's workstation and is very conservative in terms of memory usage to avoid interfering with other applications. Web Application, on the other hand, assumes that the server is running on a dedicated computer and has all memory for itself.
pingiun 6y ago
This seems pretty cool, but it would benefit from some extra information on what it considers. And maybe links to documentation about the options your setting so you can double check.
- nix23 6y ago
  It's just a webified version of pgtune: https://linux.die.net/man/8/pgtune https://linux.die.net/man/8/pgtune https://github.com/gregs1104/pgtune https://github.com/gregs1104/pgtune
ahachete 6y ago
One of the main reasons https://postgresql.conf https://postgresql.conf exists is precisely to provide as much information as possible to understand how to tune PostgreSQL's configuration. If generating a config would be as simple as filling in a form, this wouldn't make sense. It's tempting to think it may be a good starting point. Unfortunately, it might as well be a bad starting point: form-based configurations may be totally counter-productive. For instance, max_connections is a parameter that may cause outages or very bad performance if not properly configured. And its value depends, if anything, on having or not a connection pool. work_mem heavily depends on the types of queries that you run. Plus I disagree with some of the other choices made by pgtune's default behaviour. All in all, I'd recommend to do a deeper study of the parameters, or seek some expert help, specially for production environments. Disclaimer: I'm part of the team behind postgresql.conf
- nix23 6y ago
  Hy, Unfortunately the site is unavailable: https://postgresql.conf/ https://postgresql.conf/ I never wrote it's a brainless goto conf generator. >max_connections is a parameter that may cause outages or very bad performance if not properly configured. That's why you can define the "Number of Connections"
  - ahachete 6y ago
    But if you don't, it suggests you one. My point here is that some of the suggestions provided, if taken by a non expert user, may do more harm than good. And the reason is that there are many factors that need to be considered for most of the parameters. Something it's hard to capture from a form-based configuration generation tool. That's why my recommendation of not using form-based configuration generation tools. They can be counter-productive.
- ahachete 6y ago
  Edit: url above is wrong, correct one is https://postgresqlco.nf https://postgresqlco.nf my brain tricked me ;)

daniellarusso 6y ago

I thought this was going to be a written word article. It is a 52 minute video. Shame on me for not looking at the domain. It is a conference.

deleted 6y ago
[deleted]
kid_atticus 6y ago
It is not. Look at the list on the left side menu. It gives recommendations and detailed descriptions for each tunable parameter in postgres.
- framecowbird 6y ago
  Yeah, it's definitely a disappointing onboarding. It looks like the site is there to host a single video, but actually the real value is hidden on the left
  - tuatoru 6y ago
    And there's not much value there, because there's no structure apart from alphabetical in that list. Which are the most important knobs to twist? Which ones should you never, never touch without 20 years' experience and a very peculiar requirement? Give me something to read, please. "In one ear and out the other" is a saying for a very good reason.
    - ahachete 6y ago
      Thank you for the feedback, we take it seriously. There's an upcoming new version that will come with something similar to what you say: some "guide" on which parameters to tune, classified by categories, and with varying level of "expertise" (basic, medium, advanced). With specific guidance for those set of parameters. Would that be useful to you?
      - 6y ago

cpursley 6y ago

Does anyone know if there's a way to pull secrets into PG from an environment variable or similar and access it within PG in a way that does not directly expose the value to the db user or to logs?

nix23 6y ago
With pgcrypto? https://x-team.com/blog/storing-secure-passwords-with-postgresql/ https://x-team.com/blog/storing-secure-passwords-with-postgr...
- sk5t 6y ago
  Won't the advice in that cursory blog post expose the plaintext to query logs? IMHO doing the hashing and password comparison in application code has benefits in both shifting the compute workload to horizontally scalable non-db workers and keeping the plaintext off the wire/queries.
ants_a 6y ago
While environment variables are not the most bulletproof place to store secrets, a plpython function will be able to access environment variable via the os module.

RedShift1 6y ago

Any guides on how to combine SSD's, software RAID (Linux md) and ext4 for best performance? Plenty of guides for the individual components but not all of them together

technics256 6y ago

How much does something like RDS get us there regarding configuration tuning?

twunde 6y ago
RDS takes care of 1/3 to 1/2 of the configurations, particularly around hardware related options. However, since postgres uses MVCC, it has a lot of tuning options around autovacuum and replication that impact performance. This is even more true if you have access patterns that don't take into account postgres' MVCC (are you updating 10%+ of large tables daily, triggering autovacuums?) but which would be fine with mysql
apavlo 6y ago
We have seen up to a 2x improvement in Postgres' throughput after tuning it with OtterTune over the default Amazon configuration. Here is an overview of how OtterTune's algorithms work: https://aws.amazon.com/blogs/machine-learning/tuning-your-dbms-automatically-with-machine-learning/ https://aws.amazon.com/blogs/machine-learning/tuning-your-db... Hit us up to see if we can do the same for you: https://ottertune.com/demo.html https://ottertune.com/demo.html

spapas82 6y ago

For me, the greatest pain of postgresql is upgrading between major versions. You need to install both versions to the same db server at the same time and then Tthe data must be copied (using pg_upgrade) between the two clusters, something that leads to downtime (that is proportional to the size of the data ie how much time it needs to copy and run some maintenance commands). If you consider that our cluster hosts databases for like 15 apps and each needs to be changed to display some "maintenance mode" home page you'lln understand my frustration and fear of major upgrades... I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version, or using logical replication between the clusters and switching to the new version cluster. The problem is that since I am not a full time DBA (and my organisation doesn't have one) I don't trust myself with these techniques and rely to the trusted pg_upgrade method that will leave the old cluster as it was with all its data intact in case anything went wrong! The only good thing is that postgresql supports each major version for a lot of time (5 years) so such updates don't need to be very frequent :)

saturn_vk 6y ago
i wonder if you can attach a replica with a bigger major version, let it sync, and drop the old versions?
- spapas82 6y ago
  Yes I think this is possible (at least there are some blog posts recommending that way for upgrading without downtime) however configuring (and testing) that mechanism is not something I want to do :|
uhoh-itsmaciek 6y ago
>that is proportional to the size of the data I agree upgrades are still a pain point, but pg_upgrade running time is not proportional to the size of the data.
- spapas82 6y ago
  Maybe i didn't express myself clearly (english is not my primary language) but since with pg_upgrade the data will be copied from the old cluster to the new one, doing an upgrade to a 10GB database would need ~ 10 times more than upgrading a 1 GB database, just for the data copying. So the time will be proportional to the size of the data.

abledon 6y ago

Has anyone other than Amazon successfully migrated off oracle.. and ported stuff into Postgres... e.g. 1000s of PL/SQL procedures/packages moved to postgres

samokhvalov 6y ago

I use this almost daily. Last time it was an hour ago -- checked when hot_standby's default changed from off to on (it was in Postgres 10). Highly recommend it.

KoenDG 6y ago

The nice thing about this website it the extra explanation on most of the options, with considerations explained.