Tag Archives: PostgreSQL

Postgresql index naming convention

Use the table name as the prefix. Use the following suffixes: _pkey for a Primary Key constraint _key for a Unique constraint _excl for an Exclusion constraint _idx for any other kind of index _fkey for a Foreign key _check for a Check constraint https://stackoverflow.com/a/4108266/801426 Also implicitly mentioned here — https://www.postgresql.org/docs/current/sql-altertable.html

Postgresql exercises

Basic querying — https://pgexercises.com/questions/basic/. Joins — https://pgexercises.com/questions/joins/. Data modification — https://pgexercises.com/questions/updates/. Aggregations — https://pgexercises.com/questions/aggregates/. That could be useful to refresh your memory. Not really up-to-date, but still pretty useful.

Install postgresql 11 on CentOs7

Just what to do, no discussion.

Postgres in docker: random «unique violation» errors

While running postgres in docker (for development, of course), from time to time I just run into errors like «Duplicate Key Value Violates Unique Constraint». I found the solution (thanks to this answer, for sure — https://stackoverflow.com/a/47089825/801426): if you have a table called «[table]», just do this

Another cool article on using PostgreSQL+pgBouncer with go

Extremely interesting and very practical talk about problems occurring with go+pgBouncer (in Russian, sorry). https://habr.com/ru/company/oleg-bunin/blog/461935/ And a video on that: https://www.youtube.com/watch?v=Uojy57I-xP0

PgBouncer and prepared statements

In our system, we use connection pooler called PgBouncer as a proxy to PostgreSQL server. PgBouncer has two main modes Session pooling mode This mode is less performant (it’s a default mode). When a client connects, a server connection will be assigned to it for the whole duration it stays connected. So it does not …

Read more

PostgreSQL: TOP slow queries

Golang, PostgreSQL and array_agg

If you have SQL like this:

PostgreSQL: using indices on json fields

It’s obviously a really bad idea. But if you really need it, that’s what you can do.

Postgresql: show all locked queries