4 ms·
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,
by 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 agoYou 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 agoIf you have multiple unique constraints in the same table then it will spit back the same error code. I want to know the name of the specific constraint that was violated. Always name your indexes. Always name your constraints.
- 1a527dd5 2y agoI mean if you have that many violations that points to other problems, but sure if you are in that situation I can see how catching by name might be helpful. It's not a panacea.
- VWWHFSfQ 2y agoIt's not that hard to just name the things you care about instead of relying on auto-generated stuff from the database. PG lets you do this easily. > if you have that many violations that points to other problems No, it doesn't. This is a sophisticated design choice. You might be out of your depth here.
- remram 2y agoAlso useful for migrations, especially if you support multiple DB backends.
- richbell 2y ago> I'm amazed that this has reached the almost-top of HN. The article is objectively bad; the subject is clearly something a lot of people think about, myself included.
- Izkata 2y ago> 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... Those are very recent emails that don't say anything about what it was originally meant for, just that nowadays there's better options. Though really the part they quote called it a backup tool, that does imply it was originally meant to be a backup method.