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 SELECT setval(‘[table]_id_seq’, (SELECT MAX(id) FROM [table]));
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 …
PostgreSQL: TOP slow queries
SELECT rolname, calls, total_time, mean_time, max_time, stddev_time, rows, query, regexp_replace(query, ‘[ \t\n]+’, ‘ ‘, ‘g’) AS query_oneline FROM pg_stat_statements JOIN pg_roles r ON r.oid = userid WHERE calls > 100 AND rolname NOT LIKE ‘%backup’ ORDER BY mean_time DESC LIMIT 15;
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
WITH RECURSIVE l AS ( SELECT pid, locktype, mode, granted, ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj FROM pg_locks ), pairs AS ( SELECT w.pid waiter, l.pid locker, l.obj, l.mode FROM l w JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted WHERE NOT w.granted ), tree AS ( SELECT l.locker pid, l.locker …