7 ms·

PostgreSQL Best Practices

nakovet 2y ago
The access control portion is not clear to me: `current_user_organization_id()` where is this reading from? We tried using `set_config` https://www.postgresql.org/docs/current/functions-admin.html https://www.postgresql.org/docs/current/functions-admin.html and it was incredibly slow, it was not designed for how we were using which was included connection pooling and each query ran a difference "current_user_id" set_config.
- shreddit 2y ago
  After asking myself the same (i guess) it seems like setting a “session” beforehand is the key https://www.postgresql.org/docs/current/sql-set.html https://www.postgresql.org/docs/current/sql-set.html
cube2222 2y ago
Regarding streaming replication, these days you might want to also consider a “disaggregated storage-compute” setup, like AWS Aurora, or Neon (which is open-source I believe). Re partial indexes - those are great, though you have to be careful to keep them updated as your queries evolve. E.g. if you have a partial index on a set of enum values, you should make sure to check and potentially update it whenever you add a new enum variant.
tefkah 2y ago
shallow AI ass article, i’m betting i’d get the exact same if i’d ask an llm to write me an article about postgres best practices
- IgorPartola 2y ago
  Wonder if there is a way to use that kind of technique as a filter.
  - Zandikar 2y ago
    General AI on non-objective ("best" is undefined here and for what usecase/priorities?) broadly covered topics like this instance is mostly just a regression to the mean with bias bleed in from other knowledge graphs (eg, trying to use correct grammar/tense (linguistics) in place of endianness(compsci)). As we traverse further into the depths of the dead internet theory, and more AI slop pollutes the internet (and in turn/tandem, poorly curated synthetic datasets), there is some inevitable Ouroboros style reinforcement here too. So a simple filter in the sense of "omit anything too similar to X" would just omit the mean result within your given deviation. It's effectively asking, "What are some truly insane ways to use PostgreSQL", which is an interesting thought experiment, though if it actually produces useful results then you've basically just written a unit-test (Domain test?) for when AI slop evolves into full on AI jumping the shark. If you're doing it based on cross-linking (source-citing), you're basically doing Page-Rank for AI. If you time gate familiarity to posts only up to the NLP/General AI explosion in 2022 or so, well that might still be useful today, but for how long? If you were to write a "Smart" filter, you're basically just writing the "PostgreSQL Best Practices" article yourself, but writing it for machines instead of humans. And I don't know what to make of that, but frankly I was lead to believe that if nothing else, the robopocalypse would be more interesting than this.
tfeldmann 2y ago
I’m struggling naming my views. Any recommendations? Same naming as tables?
- sbuttgereit 2y ago
  For what it's worth. I like to call out views in the naming. I'll typically use a "vw_" prefix prior to a name which would be similar to table naming. "v_" is also an option, but I also write a lot of DB functions and stored procedures and with all the types, tables, and column names that get used, indicating variables and parameters can be helpful... so "v_" indicates a variable name in the function context which is why views get "vw_".
  - cwbriscoe 2y ago
    I do this but at the end: some_table_vw
  - Kwpolska 2y ago
    1980 called, they want their Hungarian Notation back.
    - richbell 2y ago
      That's an easy swipe to make. What would you suggest instead?
      - Kwpolska 2y ago
        A prefix/suffix for views is maaybe acceptable. But definitely not within database functions and procedures.
        richbell 2y ago
        > But definitely not within database functions and procedures. I generally find Hungarian notation silly, however, there's a compelling argument to have them in functions and procedures — perhaps more than for views. The type of the variable matters just as much as where/how it was declared, such as in/out params. That's the 'steelman' argument for them, as I understand it.
nick_ 2y ago
Why is snake_case recommended? What advantage(s) does it provide?
- Boxxed 2y ago
  Postgres stores and refers to your tables in lowercase (even though you can reference them case insensitively) so naming conventions that involve case don't really work.
  - Kwpolska 2y ago
    Some ORMs will use a "camelCaseNameInQuotes", in which case PostgreSQL will preserve the case (as will other databases), and manual querying will require quotes everywhere. Alternatively, you can use camelCase without quotes in your SQL, and ignore the fact that Postgres will lowercase it.
  - levkk 2y ago
    You just need to quote your identifiers with double quotes, e.g.: SELECT * FROM "MyTable" You can even use reserved SQL keywords in table/column names as long as they are double-quoted.
    - Izkata 2y ago
      With quotes you can even use spaces in table and column names.
- sbuttgereit 2y ago
  It's pretty typical to signal word boundaries in some way. Thinking about the most general norms across database vendors, consider a table name for User Posts: You could write that as UserPosts, but in a lot of databases you're going to end up with userposts, since typically SQL and identifiers are naturally case insensitive; you can preserve case by quoting that "UserPosts", but now you have extra characters just to get the case sensitivity. Finally, you can use snake case: user_posts... true you still end up with extra characters, but ultimately I think a little less shifting. Snake case also keeps you in line with much historical precedence in writing names for the database as well.
VWWHFSfQ 2y ago
You will find major performance improvements by using `include` with your indexes. For instance, on something trivial like counting votes on a blog post: create table post_vote ( post_id bigint not null references posts (id), user_id bigint not null references users (id), amount smallint not null, primary key (post_id, user_id) include (amount) ); select sum(amount) from post_vote where post_id = :'post_id'; Upvotes are 1, downvotes are -1. This will be a nearly instantaneous index-only scan summing the amount values.
- levkk 2y ago
  TIL. Thanks!
- cwbriscoe 2y ago
  Downside being that updates to amount are more costly and more storage required for the index. In your example though, it is probably a good optimization since how often do you change your up/down votes. I just wanted to point out that you really have to think about these types of optimizations because they might make one query faster but slow everything else down to the point where you are spending more time overall and using more storage.
  - VWWHFSfQ 2y ago
    > Downside being that updates to amount are more costly PG always does a delete + create for updates. It will have no additional overhead at all
    - cwbriscoe 2y ago
      You are updating both the data and the index when updating amount instead of just the data. That is twice the number of IO's.
      - VWWHFSfQ 2y ago
        It's the same IOs. PG does it regardless. Both the data and index
Kwpolska 2y ago
> Here are recommended naming practices: Recommended by whom, based on what? Everything in this article seems to be blogspam, with zero links, sources, or rationales for the decisions provided.
1a527dd5 2y ago
I'm amazed that this has reached the almost-top of HN. It's a very confused article that (IMO) is AI Slop. 1. Naming conventions is a weird one to start with, but okay. For the most part you don't need to worry about this with PKs, FKs, Indices etc. Those PG will automatically generate for you with the correct syntax. 2. Performance optimization, yes indices are create. But don't name them manually let PG name them for you. Also, where possible _always_ create the index concurrently. This does not lock the table. Important if you have any kind of scale. 3. Security bit of a weird jump as you've gone from app tier concern to PG management concerns. But I would say RLS isn't worth it. And the best security is going to be tightly control what can read and write. Point your reads to a read only replica. 4. pg_dump was never meant to be a backup method; see https://www.postgresql.org/message-id/flat/70b48475-7706-4268-990d-fd522b038d96%40eisentraut.org https://www.postgresql.org/message-id/flat/70b48475-7706-426... 5. You don't need to schedule VACUUM or ANALYZE. PG has the AUTO version of both. 6. Jesus, never use a DEFAULT value on a large table that causes a table rewrite which will cause you downtime. Don't use varchar(n) https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... This is an awful article.
- VWWHFSfQ 2y ago
  > don't name them manually let PG name them for you If you care about catching stuff like unique constraint violations in your code then you should know what the indexes are named. Always name your indexes and constraints. They're part of your code. How would you catch an exception that you don't know the name of?
  - 1a527dd5 2y ago
    You don't need to know the name; I prefer to go by the error code PG spits back at you. In this case 23505. https://www.postgresql.org/docs/current/errcodes-appendix.html https://www.postgresql.org/docs/current/errcodes-appendix.ht... That is much more flexible than hard coding the name.
    - VWWHFSfQ 2y ago
jitl 2y ago
Plural table names are silly, I will die on this hill.
- blargthorwars 2y ago
  hills

The access control portion is not clear to me: `current_user_organization_id()` where is this reading from? We tried using `set_config` https://www.postgresql.org/docs/current/functions-admin.html https://www.postgresql.org/docs/current/functions-admin.html and it was incredibly slow, it was not designed for how we were using which was included connection pooling and each query ran a difference "current_user_id" set_config.

shreddit 2y ago
After asking myself the same (i guess) it seems like setting a “session” beforehand is the key https://www.postgresql.org/docs/current/sql-set.html https://www.postgresql.org/docs/current/sql-set.html

cube2222 2y ago

Regarding streaming replication, these days you might want to also consider a “disaggregated storage-compute” setup, like AWS Aurora, or Neon (which is open-source I believe). Re partial indexes - those are great, though you have to be careful to keep them updated as your queries evolve. E.g. if you have a partial index on a set of enum values, you should make sure to check and potentially update it whenever you add a new enum variant.

tefkah 2y ago

shallow AI ass article, i’m betting i’d get the exact same if i’d ask an llm to write me an article about postgres best practices

IgorPartola 2y ago
Wonder if there is a way to use that kind of technique as a filter.
- Zandikar 2y ago
  General AI on non-objective ("best" is undefined here and for what usecase/priorities?) broadly covered topics like this instance is mostly just a regression to the mean with bias bleed in from other knowledge graphs (eg, trying to use correct grammar/tense (linguistics) in place of endianness(compsci)). As we traverse further into the depths of the dead internet theory, and more AI slop pollutes the internet (and in turn/tandem, poorly curated synthetic datasets), there is some inevitable Ouroboros style reinforcement here too. So a simple filter in the sense of "omit anything too similar to X" would just omit the mean result within your given deviation. It's effectively asking, "What are some truly insane ways to use PostgreSQL", which is an interesting thought experiment, though if it actually produces useful results then you've basically just written a unit-test (Domain test?) for when AI slop evolves into full on AI jumping the shark. If you're doing it based on cross-linking (source-citing), you're basically doing Page-Rank for AI. If you time gate familiarity to posts only up to the NLP/General AI explosion in 2022 or so, well that might still be useful today, but for how long? If you were to write a "Smart" filter, you're basically just writing the "PostgreSQL Best Practices" article yourself, but writing it for machines instead of humans. And I don't know what to make of that, but frankly I was lead to believe that if nothing else, the robopocalypse would be more interesting than this.

tfeldmann 2y ago

I’m struggling naming my views. Any recommendations? Same naming as tables?

sbuttgereit 2y ago
For what it's worth. I like to call out views in the naming. I'll typically use a "vw_" prefix prior to a name which would be similar to table naming. "v_" is also an option, but I also write a lot of DB functions and stored procedures and with all the types, tables, and column names that get used, indicating variables and parameters can be helpful... so "v_" indicates a variable name in the function context which is why views get "vw_".
- cwbriscoe 2y ago
  I do this but at the end: some_table_vw
- Kwpolska 2y ago
  1980 called, they want their Hungarian Notation back.
  - richbell 2y ago
    That's an easy swipe to make. What would you suggest instead?
    - Kwpolska 2y ago
      A prefix/suffix for views is maaybe acceptable. But definitely not within database functions and procedures.
      - richbell 2y ago
        > But definitely not within database functions and procedures. I generally find Hungarian notation silly, however, there's a compelling argument to have them in functions and procedures — perhaps more than for views. The type of the variable matters just as much as where/how it was declared, such as in/out params. That's the 'steelman' argument for them, as I understand it.

nick_ 2y ago

Why is snake_case recommended? What advantage(s) does it provide?

Boxxed 2y ago
Postgres stores and refers to your tables in lowercase (even though you can reference them case insensitively) so naming conventions that involve case don't really work.
- Kwpolska 2y ago
  Some ORMs will use a "camelCaseNameInQuotes", in which case PostgreSQL will preserve the case (as will other databases), and manual querying will require quotes everywhere. Alternatively, you can use camelCase without quotes in your SQL, and ignore the fact that Postgres will lowercase it.
- levkk 2y ago
  You just need to quote your identifiers with double quotes, e.g.: SELECT * FROM "MyTable" You can even use reserved SQL keywords in table/column names as long as they are double-quoted.
  - Izkata 2y ago
    With quotes you can even use spaces in table and column names.
sbuttgereit 2y ago
It's pretty typical to signal word boundaries in some way. Thinking about the most general norms across database vendors, consider a table name for User Posts: You could write that as UserPosts, but in a lot of databases you're going to end up with userposts, since typically SQL and identifiers are naturally case insensitive; you can preserve case by quoting that "UserPosts", but now you have extra characters just to get the case sensitivity. Finally, you can use snake case: user_posts... true you still end up with extra characters, but ultimately I think a little less shifting. Snake case also keeps you in line with much historical precedence in writing names for the database as well.

VWWHFSfQ 2y ago

You will find major performance improvements by using `include` with your indexes. For instance, on something trivial like counting votes on a blog post: create table post_vote ( post_id bigint not null references posts (id), user_id bigint not null references users (id), amount smallint not null, primary key (post_id, user_id) include (amount) ); select sum(amount) from post_vote where post_id = :'post_id'; Upvotes are 1, downvotes are -1. This will be a nearly instantaneous index-only scan summing the amount values.

levkk 2y ago
TIL. Thanks!
cwbriscoe 2y ago
Downside being that updates to amount are more costly and more storage required for the index. In your example though, it is probably a good optimization since how often do you change your up/down votes. I just wanted to point out that you really have to think about these types of optimizations because they might make one query faster but slow everything else down to the point where you are spending more time overall and using more storage.
- VWWHFSfQ 2y ago
  > Downside being that updates to amount are more costly PG always does a delete + create for updates. It will have no additional overhead at all
  - cwbriscoe 2y ago
    You are updating both the data and the index when updating amount instead of just the data. That is twice the number of IO's.
    - VWWHFSfQ 2y ago
      It's the same IOs. PG does it regardless. Both the data and index

Kwpolska 2y ago

> Here are recommended naming practices: Recommended by whom, based on what? Everything in this article seems to be blogspam, with zero links, sources, or rationales for the decisions provided.

1a527dd5 2y ago

I'm amazed that this has reached the almost-top of HN. It's a very confused article that (IMO) is AI Slop. 1. Naming conventions is a weird one to start with, but okay. For the most part you don't need to worry about this with PKs, FKs, Indices etc. Those PG will automatically generate for you with the correct syntax. 2. Performance optimization, yes indices are create. But don't name them manually let PG name them for you. Also, where possible _always_ create the index concurrently. This does not lock the table. Important if you have any kind of scale. 3. Security bit of a weird jump as you've gone from app tier concern to PG management concerns. But I would say RLS isn't worth it. And the best security is going to be tightly control what can read and write. Point your reads to a read only replica. 4. pg_dump was never meant to be a backup method; see https://www.postgresql.org/message-id/flat/70b48475-7706-4268-990d-fd522b038d96%40eisentraut.org https://www.postgresql.org/message-id/flat/70b48475-7706-426... 5. You don't need to schedule VACUUM or ANALYZE. PG has the AUTO version of both. 6. Jesus, never use a DEFAULT value on a large table that causes a table rewrite which will cause you downtime. Don't use varchar(n) https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... This is an awful article.

VWWHFSfQ 2y ago
> don't name them manually let PG name them for you If you care about catching stuff like unique constraint violations in your code then you should know what the indexes are named. Always name your indexes and constraints. They're part of your code. How would you catch an exception that you don't know the name of?
- 1a527dd5 2y ago
  You don't need to know the name; I prefer to go by the error code PG spits back at you. In this case 23505. https://www.postgresql.org/docs/current/errcodes-appendix.html https://www.postgresql.org/docs/current/errcodes-appendix.ht... That is much more flexible than hard coding the name.
  - VWWHFSfQ 2y ago

jitl 2y ago

Plural table names are silly, I will die on this hill.

blargthorwars 2y ago
hills