Category Archives: Databases

Pros and cons of DynamoDB

What the author suggests is to use it in cases when there are not so many writes to DB and where eventual consistency is fine for the app. The writes are pretty expensive after all and could lead to problems with eventual consistency. And the main issue from my side is that DynamoDB is a …

How to choose a partition key for DynamoDB

Here’s an article about how to do it right: https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/. The main ideas — partition key should have a good cardinality, but at the same time it should be queried easily as all the queries to DynamoDB should start with PartitionKey=…, as they are hashes.

Checking restart problems with mysql on centos

You probable installed MySQL with a help of article like this — https://phoenixnap.com/kb/how-to-install-mysql-on-centos-7 So your mysql is controlled by systemctl. Check current status — sudo systemctl status mysqld. Start service — sudo systemctl start mysqld Logs for systemctl are here — sudo journalctl -xe Logs for mysql can be found here — /var/log/mysqld.log. Configs are …

DynamoDB videos list

AWS decided to start a series of videos on DynamoDB usage. First 5 episodes were published on Youtube, and I made a playlist for them: https://www.youtube.com/playlist?list=PLfgkuLYEOvGN95-dbdS_mT4bktQjkw2aT

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]));

1	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 …

ORM for go

https://github.com/Masterminds/squirrel Never used it as we always write SQL queries manually. But looks okay for ORM (well, actually a query-builder).

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;

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;