Notes about postgres, redis mongo.

Postgres

json

Json arrays, Json functions

setting value in json field

UPDATE mytable
   SET metadata = (
        SELECT jsonb_set(metadata, --value to change
                        '{MapMetadata,zoom}', --path
                        JSONB '"value-to-set"', --value to set
                        TRUE -- ???
                        ) FROM mytable);

Generated field

-- Postgres 12+
-- https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql
CREATE TABLE tbl (int1 int , int2 int , product bigint GENERATED ALWAYS AS (int1 * int2) STORED)
-- or
CREATE TABLE tbl (int1 int , int2 int );

alter table tbl add column product bigint GENERATED ALWAYS AS (int1 * int2) STORED;

We cannot use CONCAT here, but COALLESCE(field,'') || other works

export to csv

  • \copy TABLE to 'FILENAME' csv header
  • Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

Get table structure

pg_dump -t '<schema>."<tableName>"' --schema-only <db_name> -h localhost -U username

Or just \d in CLI (pgcli / psql)

Check replication lag

-- on master
 select client_addr, replay_lag from pg_stat_replication;

Drop connections to a db

All connections

Leaves your current connection

SELECT
    pg_terminate_backend(pg_stat_activity.pid)
FROM
    pg_stat_activity
WHERE
    pg_stat_activity.datname = 'testdb'
    AND pid <> pg_backend_pid();

Long running sessions

Get sessions with long running queries:

SELECT
    pid,
    datname,
    now() - xact_start AS time_running
FROM
    pg_stat_activity
WHERE
    state IN ('idle in transaction', 'active')
    AND now() - xact_start > interval '2 minutes'
ORDER BY
    time_running DESC;

And kill them by:

SELECT pg_terminate_backend(< pidfromabove >);

You can even just put the pg_terminate_backend in select from abeove ;)

Multicolumn indexes

http://www.postgresql.org/docs/current/static/indexes-multicolumn.html

multicolumn B-tree index:

  • can be used with query conditions that involve any subset of the index’s columns,
  • the index is most efficient when there are equality constraint on the left and inequality on a last “not-equals” column

Example: https://youtube.com/watch?v=a-7LphAnb9w (Overtalked )

Redis

Icicle setup:

SET icicle-generator-logical-shard-id 123

Mongo

forEaching

db.conversations.find().snapshot().forEach(
    function (elem) {
        origId = elem._id
        elem._id = elem.inboxId + "_" + elem.conversationId
        db.conversations.insert(elem);
        db.conversations.remove({"_id": origId});
    }
);

Aggregating

Random example 🤷‍♂️

db.getCollection('ala').aggregate([
    {$match:{"ela":"ula"}},
    {$group: {"_id": {"size":"$xdCount","elo":"$elo"}, "id": {"$push":"$_id"} }},
    {$project: {"ajdik":  { $arrayElemAt: [ "$id", 0 ] }}}
    ]).forEach(x => print('"' + x.ajdik + '",' ))

Check Collection load ( performance )

use mongotop