折腾 Zhēteng

PostgreSQL Console Notes

Created: Modified: 5 min read

This note for PostgreSQL console commands.

Switch to expanded display

\x

List databases

\l

Show all the tables

\dt

Show all indexes

\di

Describe a specific table

\d your_table_name

List all views in psql

\dv

To check the definition of a view:

\d+ your_view_name

Example:

\d+ latest_metadata
                          View "public.latest_metadata"
     Column      |           Type           | Collation | Nullable | Default | Storage  | Description
-----------------+--------------------------+-----------+----------+---------+----------+-------------
 instance_id     | text                     |           |          |         | extended |
 launch_date     | timestamp with time zone |           |          |         | plain    |
 last_reboot     | timestamp with time zone |           |          |         | plain    |
 state           | text                     |           |          |         | extended |
 state_priority  | integer                  |           |          |         | plain    |
 last_event_uuid | uuid                     |           |          |         | plain    |
 metadata        | jsonb                    |           |          |         | extended |
View definition:
 SELECT latest_instance_state_metadata.instance_id,
    latest_instance_state_metadata.launch_date,
    latest_instance_state_metadata.last_reboot,
    latest_instance_state_metadata.state,
    latest_instance_state_metadata.state_priority,
    latest_instance_state_metadata.last_event_uuid,
    latest_instance_state_metadata.metadata,
   FROM latest_instance_state_metadata
  WHERE latest_instance_state_metadata.state <> 'EC2_STATE_CHANGE_SHUTTING_DOWN'::text;

Show schema

\d <table_name>

Select by ID

select * from entities where id='e0c275c8-77c6-4f49-9951-572a07642a7b';

Note that you should use single quote, not double quote.

Sample queries

SELECT tweets.*, users.* FROM tweets
  JOIN users ON tweets.sender_id = users.id
  JOIN follows ON follows.followee_id = users.id
  WHERE follows.follower_id = current_user
SELECT a.name FROM (SELECT name FROM agentinformation) a

Statistics:

SELECT schemaname, relname, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables
explain analyze select id, version, status, created_by, created_at, started_at, results_refreshed_at, terminated_by from entities where status='starting' OR status='running' limit 10;

Output:

                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..8.27 rows=2 width=158) (actual time=0.258..0.258 rows=0 loops=1)
   ->  Index Scan using entity_status_not_finished_idx on entities  (cost=0.25..8.27 rows=2 width=158) (actual time=0.257..0.257 rows=0 loops=1)
         Filter: ((status = 'starting'::entity_status) OR (status = 'running'::entity_status))
 Planning Time: 0.119 ms
 Execution Time: 0.283 ms
(5 rows)

Analyze table (writer instance only, read-only can’t do)

analyze (verbose true, skip_locked) <table_name>;
select relname, schemaname, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze from pg_stat_all_tables;

Create index

Assume you have a table: your_table_name and you have a column: metadata which is a jsonb, and in that jsonb, you have a string field keyed with azure-resource-name.

CREATE INDEX idx_azure_resource_name_metadata ON your_table_name USING BTREE ((metadata ->> 'azure-resource-name'::text));

If you need to use LOWER() function for the value in this metadata field:

CREATE INDEX idx_azure_resource_name_metadata_lower ON your_table_name USING BTREE (LOWER((metadata ->> 'azure-resource-name'::text)));

You can use “CREATE INDEX CONCURRENTLY” to create index concurrently.

CREATE INDEX CONCURRENTLY idx_azure_resource_name_metadata ON your_table_name USING BTREE ((metadata ->> 'azure-resource-name'::text));

NOTE

You can’t use CREATE INDEX CONCURRENTLY to create index concurrently in a transaction.

Drop index

Better to use with IF EXISTS

DROP INDEX IF  EXISTS index_name ;

Autoanalyze and Autovacuum

Table-Level settings

  SELECT relname, reloptions
  FROM pg_class
  WHERE relname = 'latest_instance_state';

Output:

        relname        |                                 reloptions
-----------------------+-----------------------------------------------------------------------------
 latest_instance_state | {autovacuum_analyze_scale_factor=0.005,autovacuum_vacuum_scale_factor=0.01}
(1 row)

NOTE

Only the ‘factors’ are shown above, the ‘thresholds’ are not shown, because there is no table override on the global parameters.

Global/Server-Level Settings

  SELECT name, setting, unit, short_desc
  FROM pg_settings
  WHERE name LIKE '%autovacuum%' OR name LIKE '%vacuum%'
  ORDER BY name;

Quick summary of key settings

  SELECT name, setting
  FROM pg_settings
  WHERE name IN (
    'autovacuum',
    'autovacuum_analyze_threshold',
    'autovacuum_analyze_scale_factor',
    'autovacuum_vacuum_threshold',
    'autovacuum_vacuum_scale_factor',
    'autovacuum_naptime',
    'autovacuum_max_workers'
  );

Output:

              name               | setting
---------------------------------+---------
 autovacuum                      | on
 autovacuum_analyze_scale_factor | 0.05
 autovacuum_analyze_threshold    | 50
 autovacuum_max_workers          | 3
 autovacuum_naptime              | 5
 autovacuum_vacuum_scale_factor  | 0.1
 autovacuum_vacuum_threshold     | 50
(7 rows)

Effective settings for a specific table

  SELECT
    c.relname,
    c.reloptions,
    current_setting('autovacuum_analyze_threshold') as global_analyze_threshold,
    current_setting('autovacuum_analyze_scale_factor') as global_analyze_scale_factor,
    current_setting('autovacuum_vacuum_threshold') as global_vacuum_threshold,
    current_setting('autovacuum_vacuum_scale_factor') as global_vacuum_scale_factor
  FROM pg_class c
  WHERE c.relname = 'latest_instance_state';

Output:

relnamereloptionsglobal_analyze_thresholdglobal_analyze_scale_factorglobal_vacuum_thresholdglobal_vacuum_scale_factor
latest_instance_state{autovacuum_analyze_scale_factor=0.005,autovacuum_vacuum_scale_factor=0.01}500.05500.1
(1 row)

Analyze results

  1. Table size: SELECT count(*) FROM latest_instance_state;
  2. Current autoanalyze frequency (how often it’s running):
SELECT relname, last_autoanalyze, last_analyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'latest_instance_state';
  1. Dead tuple accumulation (indicates vacuum pressure):
SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'latest_instance_state';

If the database has been running for a while, you can estimate the average rate since stats were last reset

WARNING

This won’t work for ‘Reader’ instance on AWS PostgreSQL cluster.

  SELECT
    t.relname,
    t.n_tup_ins AS inserts,
    t.n_tup_upd AS updates,
    t.n_tup_del AS deletes,
    t.n_tup_ins + t.n_tup_upd + t.n_tup_del AS total_modifications,
    d.stats_reset,
    EXTRACT(EPOCH FROM (now() - d.stats_reset)) / 60 AS minutes_since_reset,
    ROUND(
      (t.n_tup_ins + t.n_tup_upd + t.n_tup_del)::numeric /
      NULLIF(EXTRACT(EPOCH FROM (now() - d.stats_reset)) / 60, 0)
    ) AS mods_per_minute
  FROM pg_stat_user_tables t
  CROSS JOIN pg_stat_database d
  WHERE t.relname = 'latest_instance_state'
    AND d.datname = current_database();

Columns:

  | Column              | Unit        | Description                          |
  |---------------------|-------------|--------------------------------------|
  | inserts             | rows        | Total INSERT count since stats reset |
  | updates             | rows        | Total UPDATE count since stats reset |
  | deletes             | rows        | Total DELETE count since stats reset |
  | total_modifications | rows        | Sum of inserts + updates + deletes   |
  | stats_reset         | timestamp   | When statistics were last reset      |
  | minutes_since_reset | minutes     | Time elapsed since stats reset       |
  | mods_per_minute     | rows/minute | Average modification rate            |

Example output:

        relname        |  inserts  |  updates  | deletes | total_modifications |          stats_reset          | minutes_since_reset | mods_per_minute
-----------------------+-----------+-----------+---------+---------------------+-------------------------------+---------------------+-----------------
 latest_instance_state | 185653961 | 557537323 |       0 |           743191284 | 2024-04-01 21:31:37.456427+00 | 910156.908485466667 |             817
(1 row)

So, modification per minute is 817.

Graph View

Backlinks