Category Archives: Databases

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.

How to start with google bigquery

Here is the list of thing I noticed when started to work with bigquery. First you need to create a project — https://cloud.google.com/bigquery/docs/quickstarts/quickstart-web-ui. That’s how you create a service key for bigquery — https://cloud.google.com/docs/authentication/getting-started. Select your project on the very top of the page, and then create JSON key. And don’t forget to set is …

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