PostgreSQL

Common Commands

# Create new user
CREATE ROLE pathfinder_mono;
ALTER ROLE pathfinder_mono WITH LOGIN;
ALTER ROLE pathfinder_mono WITH SUPERUSER;
ALTER ROLE pathfinder_mono WITH PASSWORD 'pathfinder_mono';

# Show/modify max connections
SHOW max_connections;
SELECT COUNT(*) FROM pg_stat_activity;
SELECT pid, usename, application_name, backend_start, state_change, state FROM pg_stat_activity where state='idle';
SELECT pg_terminate_backend(PID);
ALTER system SET max_connections = 1000;

# Find slow queries
SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

# Alter idle in transaction session timeout
SET SESSION idle_in_transaction_session_timeout = '5min';
ALTER DATABASE SET idle_in_transaction_session_timeout = '5min'

Useful queries for monitoring

Tuning

Indexing

Important considerations when creating index:

  1. Understand the characteristics of index data structure that we are using. For example in Postgres, there are: B-tree (default), GIN, GIST and others.
  2. Index ordering matters, depending on the query it might be a good idea to put more specific column at the front so that we get a flatter b-tree structure.
  3. Use CONCURRENTLY to avoid table lock during index creation (but the craetion process will be slower).

Additional references:

Partitioning

Autovacuum

Autovacuum should be turned off for high volume DB in postgres (by default it’s on).

Autovacuum works well when configured correctly. However its default configuration is appropriate for databases of a few hundred megabytes in size, and is not aggressive enough for larger databases. In production environments it starts to fall behind.

References:
- https://www.citusdata.com/blog/2016/11/04/autovacuum-not-the-enemy/

References