RISCI_ATOM
/
synapse
mirror of https://github.com/matrix-org/synapse.git


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374
							<!DOCTYPE HTML>
<html lang="en" class="sidebar-visible no-js light">
    <head>
        <!-- Book generated using mdBook -->
        <meta charset="UTF-8">
        <title>Useful SQL for Admins - Synapse</title>
        <!-- Custom HTML head -->
        <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
        <meta name="description" content="">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="theme-color" content="#ffffff" />

        <link rel="icon" href="../../favicon.svg">
        <link rel="shortcut icon" href="../../favicon.png">
        <link rel="stylesheet" href="../../css/variables.css">
        <link rel="stylesheet" href="../../css/general.css">
        <link rel="stylesheet" href="../../css/chrome.css">
        <link rel="stylesheet" href="../../css/print.css" media="print">
        <!-- Fonts -->
        <link rel="stylesheet" href="../../FontAwesome/css/font-awesome.css">
        <link rel="stylesheet" href="../../fonts/fonts.css">
        <!-- Highlight.js Stylesheets -->
        <link rel="stylesheet" href="../../highlight.css">
        <link rel="stylesheet" href="../../tomorrow-night.css">
        <link rel="stylesheet" href="../../ayu-highlight.css">

        <!-- Custom theme stylesheets -->
        <link rel="stylesheet" href="../../docs/website_files/table-of-contents.css">
        <link rel="stylesheet" href="../../docs/website_files/remove-nav-buttons.css">
        <link rel="stylesheet" href="../../docs/website_files/indent-section-headers.css">
        <link rel="stylesheet" href="../../docs/website_files/version-picker.css">
    </head>
    <body>
        <!-- Provide site root to javascript -->
        <script type="text/javascript">
            var path_to_root = "../../";
            var default_theme = window.matchMedia("(prefers-color-scheme: dark)").matches ? "navy" : "light";
        </script>

        <!-- Work around some values being stored in localStorage wrapped in quotes -->
        <script type="text/javascript">
            try {
                var theme = localStorage.getItem('mdbook-theme');
                var sidebar = localStorage.getItem('mdbook-sidebar');
                if (theme.startsWith('"') && theme.endsWith('"')) {
                    localStorage.setItem('mdbook-theme', theme.slice(1, theme.length - 1));
                }
                if (sidebar.startsWith('"') && sidebar.endsWith('"')) {
                    localStorage.setItem('mdbook-sidebar', sidebar.slice(1, sidebar.length - 1));
                }
            } catch (e) { }
        </script>

        <!-- Set the theme before any content is loaded, prevents flash -->
        <script type="text/javascript">
            var theme;
            try { theme = localStorage.getItem('mdbook-theme'); } catch(e) { }
            if (theme === null || theme === undefined) { theme = default_theme; }
            var html = document.querySelector('html');
            html.classList.remove('no-js')
            html.classList.remove('light')
            html.classList.add(theme);
            html.classList.add('js');
        </script>

        <!-- Hide / unhide sidebar before it is displayed -->
        <script type="text/javascript">
            var html = document.querySelector('html');
            var sidebar = 'hidden';
            if (document.body.clientWidth >= 1080) {
                try { sidebar = localStorage.getItem('mdbook-sidebar'); } catch(e) { }
                sidebar = sidebar || 'visible';
            }
            html.classList.remove('sidebar-visible');
            html.classList.add("sidebar-" + sidebar);
        </script>

        <nav id="sidebar" class="sidebar" aria-label="Table of contents">
            <div class="sidebar-scrollbox">
                <ol class="chapter"><li class="chapter-item expanded affix "><li class="part-title">Introduction</li><li class="chapter-item expanded "><a href="../../welcome_and_overview.html">Welcome and Overview</a></li><li class="chapter-item expanded affix "><li class="part-title">Setup</li><li class="chapter-item expanded "><a href="../../setup/installation.html">Installation</a></li><li class="chapter-item expanded "><a href="../../postgres.html">Using Postgres</a></li><li class="chapter-item expanded "><a href="../../reverse_proxy.html">Configuring a Reverse Proxy</a></li><li class="chapter-item expanded "><a href="../../setup/forward_proxy.html">Configuring a Forward/Outbound Proxy</a></li><li class="chapter-item expanded "><a href="../../turn-howto.html">Configuring a Turn Server</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../setup/turn/coturn.html">coturn TURN server</a></li><li class="chapter-item expanded "><a href="../../setup/turn/eturnal.html">eturnal TURN server</a></li></ol></li><li class="chapter-item expanded "><a href="../../delegate.html">Delegation</a></li><li class="chapter-item expanded affix "><li class="part-title">Upgrading</li><li class="chapter-item expanded "><a href="../../upgrade.html">Upgrading between Synapse Versions</a></li><li class="chapter-item expanded affix "><li class="part-title">Usage</li><li class="chapter-item expanded "><a href="../../federate.html">Federation</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/index.html">Configuration</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../usage/configuration/config_documentation.html">Configuration Manual</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/homeserver_sample_config.html">Homeserver Sample Config File</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/logging_sample_config.html">Logging Sample Config File</a></li><li class="chapter-item expanded "><a href="../../structured_logging.html">Structured Logging</a></li><li class="chapter-item expanded "><a href="../../templates.html">Templates</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/user_authentication/index.html">User Authentication</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../usage/configuration/user_authentication/single_sign_on/index.html">Single-Sign On</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../openid.html">OpenID Connect</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/user_authentication/single_sign_on/saml.html">SAML</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/user_authentication/single_sign_on/cas.html">CAS</a></li><li class="chapter-item expanded "><a href="../../sso_mapping_providers.html">SSO Mapping Providers</a></li></ol></li><li class="chapter-item expanded "><a href="../../password_auth_providers.html">Password Auth Providers</a></li><li class="chapter-item expanded "><a href="../../jwt.html">JSON Web Tokens</a></li><li class="chapter-item expanded "><a href="../../usage/configuration/user_authentication/refresh_tokens.html">Refresh Tokens</a></li></ol></li><li class="chapter-item expanded "><a href="../../CAPTCHA_SETUP.html">Registration Captcha</a></li><li class="chapter-item expanded "><a href="../../application_services.html">Application Services</a></li><li class="chapter-item expanded "><a href="../../server_notices.html">Server Notices</a></li><li class="chapter-item expanded "><a href="../../consent_tracking.html">Consent Tracking</a></li><li class="chapter-item expanded "><a href="../../user_directory.html">User Directory</a></li><li class="chapter-item expanded "><a href="../../message_retention_policies.html">Message Retention Policies</a></li><li class="chapter-item expanded "><a href="../../modules/index.html">Pluggable Modules</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../modules/writing_a_module.html">Writing a module</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../modules/spam_checker_callbacks.html">Spam checker callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/third_party_rules_callbacks.html">Third-party rules callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/presence_router_callbacks.html">Presence router callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/account_validity_callbacks.html">Account validity callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/password_auth_provider_callbacks.html">Password auth provider callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/background_update_controller_callbacks.html">Background update controller callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/account_data_callbacks.html">Account data callbacks</a></li><li class="chapter-item expanded "><a href="../../modules/porting_legacy_module.html">Porting a legacy module to the new interface</a></li></ol></li></ol></li><li class="chapter-item expanded "><a href="../../workers.html">Workers</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../synctl_workers.html">Using synctl with Workers</a></li><li class="chapter-item expanded "><a href="../../systemd-with-workers/index.html">Systemd</a></li></ol></li></ol></li><li class="chapter-item expanded "><a href="../../usage/administration/index.html">Administration</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../usage/administration/admin_api/index.html">Admin API</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../admin_api/account_validity.html">Account Validity</a></li><li class="chapter-item expanded "><a href="../../usage/administration/admin_api/background_updates.html">Background Updates</a></li><li class="chapter-item expanded "><a href="../../admin_api/event_reports.html">Event Reports</a></li><li class="chapter-item expanded "><a href="../../admin_api/experimental_features.html">Experimental Features</a></li><li class="chapter-item expanded "><a href="../../admin_api/media_admin_api.html">Media</a></li><li class="chapter-item expanded "><a href="../../admin_api/purge_history_api.html">Purge History</a></li><li class="chapter-item expanded "><a href="../../admin_api/register_api.html">Register Users</a></li><li class="chapter-item expanded "><a href="../../usage/administration/admin_api/registration_tokens.html">Registration Tokens</a></li><li class="chapter-item expanded "><a href="../../admin_api/room_membership.html">Manipulate Room Membership</a></li><li class="chapter-item expanded "><a href="../../admin_api/rooms.html">Rooms</a></li><li class="chapter-item expanded "><a href="../../admin_api/server_notices.html">Server Notices</a></li><li class="chapter-item expanded "><a href="../../admin_api/statistics.html">Statistics</a></li><li class="chapter-item expanded "><a href="../../admin_api/user_admin_api.html">Users</a></li><li class="chapter-item expanded "><a href="../../admin_api/version_api.html">Server Version</a></li><li class="chapter-item expanded "><a href="../../usage/administration/admin_api/federation.html">Federation</a></li></ol></li><li class="chapter-item expanded "><a href="../../manhole.html">Manhole</a></li><li class="chapter-item expanded "><a href="../../metrics-howto.html">Monitoring</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../usage/administration/monitoring/reporting_homeserver_usage_statistics.html">Reporting Homeserver Usage Statistics</a></li></ol></li><li class="chapter-item expanded "><a href="../../usage/administration/monthly_active_users.html">Monthly Active Users</a></li><li class="chapter-item expanded "><a href="../../usage/administration/understanding_synapse_through_grafana_graphs.html">Understanding Synapse Through Grafana Graphs</a></li><li class="chapter-item expanded "><a href="../../usage/administration/useful_sql_for_admins.html" class="active">Useful SQL for Admins</a></li><li class="chapter-item expanded "><a href="../../usage/administration/database_maintenance_tools.html">Database Maintenance Tools</a></li><li class="chapter-item expanded "><a href="../../usage/administration/state_groups.html">State Groups</a></li><li class="chapter-item expanded "><a href="../../usage/administration/request_log.html">Request log format</a></li><li class="chapter-item expanded "><a href="../../usage/administration/admin_faq.html">Admin FAQ</a></li><li class="chapter-item expanded "><div>Scripts</div></li></ol></li><li class="chapter-item expanded "><li class="part-title">Development</li><li class="chapter-item expanded "><a href="../../development/contributing_guide.html">Contributing Guide</a></li><li class="chapter-item expanded "><a href="../../code_style.html">Code Style</a></li><li class="chapter-item expanded "><a href="../../development/reviews.html">Reviewing Code</a></li><li class="chapter-item expanded "><a href="../../development/releases.html">Release Cycle</a></li><li class="chapter-item expanded "><a href="../../development/git.html">Git Usage</a></li><li class="chapter-item expanded "><div>Testing</div></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../development/demo.html">Demo scripts</a></li></ol></li><li class="chapter-item expanded "><a href="../../opentracing.html">OpenTracing</a></li><li class="chapter-item expanded "><a href="../../development/database_schema.html">Database Schemas</a></li><li class="chapter-item expanded "><a href="../../development/experimental_features.html">Experimental features</a></li><li class="chapter-item expanded "><a href="../../development/dependencies.html">Dependency management</a></li><li class="chapter-item expanded "><div>Synapse Architecture</div></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../development/synapse_architecture/cancellation.html">Cancellation</a></li><li class="chapter-item expanded "><a href="../../log_contexts.html">Log Contexts</a></li><li class="chapter-item expanded "><a href="../../replication.html">Replication</a></li><li class="chapter-item expanded "><a href="../../development/synapse_architecture/streams.html">Streams</a></li><li class="chapter-item expanded "><a href="../../tcp_replication.html">TCP Replication</a></li><li class="chapter-item expanded "><a href="../../development/synapse_architecture/faster_joins.html">Faster remote joins</a></li></ol></li><li class="chapter-item expanded "><a href="../../development/internal_documentation/index.html">Internal Documentation</a></li><li><ol class="section"><li class="chapter-item expanded "><div>Single Sign-On</div></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../development/saml.html">SAML</a></li><li class="chapter-item expanded "><a href="../../development/cas.html">CAS</a></li></ol></li><li class="chapter-item expanded "><a href="../../development/room-dag-concepts.html">Room DAG concepts</a></li><li class="chapter-item expanded "><div>State Resolution</div></li><li><ol class="section"><li class="chapter-item expanded "><a href="../../auth_chain_difference_algorithm.html">The Auth Chain Difference Algorithm</a></li></ol></li><li class="chapter-item expanded "><a href="../../media_repository.html">Media Repository</a></li><li class="chapter-item expanded "><a href="../../room_and_user_statistics.html">Room and User Statistics</a></li></ol></li><li class="chapter-item expanded "><div>Scripts</div></li><li class="chapter-item expanded affix "><li class="part-title">Other</li><li class="chapter-item expanded "><a href="../../deprecation_policy.html">Dependency Deprecation Policy</a></li><li class="chapter-item expanded "><a href="../../other/running_synapse_on_single_board_computers.html">Running Synapse on a Single-Board Computer</a></li></ol>
            </div>
            <div id="sidebar-resize-handle" class="sidebar-resize-handle"></div>
        </nav>

        <div id="page-wrapper" class="page-wrapper">

            <div class="page">
                <div id="menu-bar-hover-placeholder"></div>
                <div id="menu-bar" class="menu-bar sticky bordered">
                    <div class="left-buttons">
                        <button id="sidebar-toggle" class="icon-button" type="button" title="Toggle Table of Contents" aria-label="Toggle Table of Contents" aria-controls="sidebar">
                            <i class="fa fa-bars"></i>
                        </button>
                        <button id="theme-toggle" class="icon-button" type="button" title="Change theme" aria-label="Change theme" aria-haspopup="true" aria-expanded="false" aria-controls="theme-list">
                            <i class="fa fa-paint-brush"></i>
                        </button>
                        <ul id="theme-list" class="theme-popup" aria-label="Themes" role="menu">
                            <li role="none"><button role="menuitem" class="theme" id="light">Light (default)</button></li>
                            <li role="none"><button role="menuitem" class="theme" id="rust">Rust</button></li>
                            <li role="none"><button role="menuitem" class="theme" id="coal">Coal</button></li>
                            <li role="none"><button role="menuitem" class="theme" id="navy">Navy</button></li>
                            <li role="none"><button role="menuitem" class="theme" id="ayu">Ayu</button></li>
                        </ul>
                        <button id="search-toggle" class="icon-button" type="button" title="Search. (Shortkey: s)" aria-label="Toggle Searchbar" aria-expanded="false" aria-keyshortcuts="S" aria-controls="searchbar">
                            <i class="fa fa-search"></i>
                        </button>
                        <div class="version-picker">
                            <div class="dropdown">
                                <div class="select">
                                    <span></span>
                                    <i class="fa fa-chevron-down"></i>
                                </div>
                                <input type="hidden" name="version">
                                <ul class="dropdown-menu">
                                    <!-- Versions will be added dynamically in version-picker.js -->
                                </ul>
                            </div>
                        </div>      
                    </div>

                    <h1 class="menu-title">Synapse</h1>

                    <div class="right-buttons">
                        <a href="../../print.html" title="Print this book" aria-label="Print this book">
                            <i id="print-button" class="fa fa-print"></i>
                        </a>
                        <a href="https://github.com/matrix-org/synapse" title="Git repository" aria-label="Git repository">
                            <i id="git-repository-button" class="fa fa-github"></i>
                        </a>
                        <a href="https://github.com/matrix-org/synapse/edit/develop/docs/usage/administration/useful_sql_for_admins.md" title="Suggest an edit" aria-label="Suggest an edit">
                            <i id="git-edit-button" class="fa fa-edit"></i>
                        </a>
                    </div>
                </div>

                <div id="search-wrapper" class="hidden">
                    <form id="searchbar-outer" class="searchbar-outer">
                        <input type="search" id="searchbar" name="searchbar" placeholder="Search this book ..." aria-controls="searchresults-outer" aria-describedby="searchresults-header">
                    </form>
                    <div id="searchresults-outer" class="searchresults-outer hidden">
                        <div id="searchresults-header" class="searchresults-header"></div>
                        <ul id="searchresults">
                        </ul>
                    </div>
                </div>
                <!-- Apply ARIA attributes after the sidebar and the sidebar toggle button are added to the DOM -->
                <script type="text/javascript">
                    document.getElementById('sidebar-toggle').setAttribute('aria-expanded', sidebar === 'visible');
                    document.getElementById('sidebar').setAttribute('aria-hidden', sidebar !== 'visible');
                    Array.from(document.querySelectorAll('#sidebar a')).forEach(function(link) {
                        link.setAttribute('tabIndex', sidebar === 'visible' ? 0 : -1);
                    });
                </script>

                <div id="content" class="content">
                    <main>
                        <!-- Page table of contents -->
                        <div class="sidetoc">
                            <nav class="pagetoc"></nav>
                        </div>

                        <h2 id="some-useful-sql-queries-for-synapse-admins"><a class="header" href="#some-useful-sql-queries-for-synapse-admins">Some useful SQL queries for Synapse Admins</a></h2>
<h2 id="size-of-full-matrix-db"><a class="header" href="#size-of-full-matrix-db">Size of full matrix db</a></h2>
<pre><code class="language-sql">SELECT pg_size_pretty( pg_database_size( 'matrix' ) );
</code></pre>
<h3 id="result-example"><a class="header" href="#result-example">Result example:</a></h3>
<pre><code>pg_size_pretty 
----------------
 6420 MB
(1 row)
</code></pre>
<h2 id="show-top-20-larger-tables-by-row-count"><a class="header" href="#show-top-20-larger-tables-by-row-count">Show top 20 larger tables by row count</a></h2>
<pre><code class="language-sql">SELECT relname, n_live_tup AS &quot;rows&quot;
  FROM pg_stat_user_tables
  ORDER BY n_live_tup DESC
  LIMIT 20;
</code></pre>
<p>This query is quick, but may be very approximate, for exact number of rows use:</p>
<pre><code class="language-sql">SELECT COUNT(*) FROM &lt;table_name&gt;;
</code></pre>
<h3 id="result-example-1"><a class="header" href="#result-example-1">Result example:</a></h3>
<pre><code>state_groups_state - 161687170
event_auth - 8584785
event_edges - 6995633
event_json - 6585916
event_reference_hashes - 6580990
events - 6578879
received_transactions - 5713989
event_to_state_groups - 4873377
stream_ordering_to_exterm - 4136285
current_state_delta_stream - 3770972
event_search - 3670521
state_events - 2845082
room_memberships - 2785854
cache_invalidation_stream - 2448218
state_groups - 1255467
state_group_edges - 1229849
current_state_events - 1222905
users_in_public_rooms - 364059
device_lists_stream - 326903
user_directory_search - 316433
</code></pre>
<h2 id="show-top-20-larger-tables-by-storage-size"><a class="header" href="#show-top-20-larger-tables-by-storage-size">Show top 20 larger tables by storage size</a></h2>
<pre><code class="language-sql">SELECT nspname || '.' || relname AS &quot;relation&quot;,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS &quot;total_size&quot;
  FROM pg_class c
  LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND c.relkind &lt;&gt; 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(c.oid) DESC
  LIMIT 20;
</code></pre>
<h3 id="result-example-2"><a class="header" href="#result-example-2">Result example:</a></h3>
<pre><code>public.state_groups_state - 27 GB
public.event_json - 9855 MB
public.events - 3675 MB
public.event_edges - 3404 MB
public.received_transactions - 2745 MB
public.event_reference_hashes - 1864 MB
public.event_auth - 1775 MB
public.stream_ordering_to_exterm - 1663 MB
public.event_search - 1370 MB
public.room_memberships - 1050 MB
public.event_to_state_groups - 948 MB
public.current_state_delta_stream - 711 MB
public.state_events - 611 MB
public.presence_stream - 530 MB
public.current_state_events - 525 MB
public.cache_invalidation_stream - 466 MB
public.receipts_linearized - 279 MB
public.state_groups - 160 MB
public.device_lists_remote_cache - 124 MB
public.state_group_edges - 122 MB
</code></pre>
<h2 id="show-top-20-larger-rooms-by-state-events-count"><a class="header" href="#show-top-20-larger-rooms-by-state-events-count">Show top 20 larger rooms by state events count</a></h2>
<p>You get the same information when you use the
<a href="../../admin_api/rooms.html#list-room-api">admin API</a>
and set parameter <code>order_by=state_events</code>.</p>
<pre><code class="language-sql">SELECT r.name, s.room_id, s.current_state_events
  FROM room_stats_current s
  LEFT JOIN room_stats_state r USING (room_id)
  ORDER BY current_state_events DESC
  LIMIT 20;
</code></pre>
<p>and by state_group_events count:</p>
<pre><code class="language-sql">SELECT rss.name, s.room_id, COUNT(s.room_id)
  FROM state_groups_state s
  LEFT JOIN room_stats_state rss USING (room_id)
  GROUP BY s.room_id, rss.name
  ORDER BY COUNT(s.room_id) DESC
  LIMIT 20;
</code></pre>
<p>plus same, but with join removed for performance reasons:</p>
<pre><code class="language-sql">SELECT s.room_id, COUNT(s.room_id)
  FROM state_groups_state s
  GROUP BY s.room_id 
  ORDER BY COUNT(s.room_id) DESC
  LIMIT 20;
</code></pre>
<h2 id="show-top-20-rooms-by-new-events-count-in-last-1-day"><a class="header" href="#show-top-20-rooms-by-new-events-count-in-last-1-day">Show top 20 rooms by new events count in last 1 day:</a></h2>
<pre><code class="language-sql">SELECT e.room_id, r.name, COUNT(e.event_id) cnt
  FROM events e
  LEFT JOIN room_stats_state r USING (room_id)
  WHERE e.origin_server_ts &gt;= DATE_PART('epoch', NOW() - INTERVAL '1 day') * 1000
  GROUP BY e.room_id, r.name 
  ORDER BY cnt DESC
  LIMIT 20;
</code></pre>
<h2 id="show-top-20-users-on-homeserver-by-sent-events-messages-at-last-month"><a class="header" href="#show-top-20-users-on-homeserver-by-sent-events-messages-at-last-month">Show top 20 users on homeserver by sent events (messages) at last month:</a></h2>
<p>Caution. This query does not use any indexes, can be slow and create load on the database.</p>
<pre><code class="language-sql">SELECT COUNT(*), sender
  FROM events
  WHERE (type = 'm.room.encrypted' OR type = 'm.room.message')
    AND origin_server_ts &gt;= DATE_PART('epoch', NOW() - INTERVAL '1 month') * 1000
  GROUP BY sender
  ORDER BY COUNT(*) DESC
  LIMIT 20;
</code></pre>
<h2 id="show-last-100-messages-from-needed-user-with-room-names"><a class="header" href="#show-last-100-messages-from-needed-user-with-room-names">Show last 100 messages from needed user, with room names:</a></h2>
<pre><code class="language-sql">SELECT e.room_id, r.name, e.event_id, e.type, e.content, j.json
  FROM events e
  LEFT JOIN event_json j USING (room_id)
  LEFT JOIN room_stats_state r USING (room_id)
  WHERE sender = '@LOGIN:example.com'
    AND e.type = 'm.room.message'
  ORDER BY stream_ordering DESC
  LIMIT 100;
</code></pre>
<h2 id="show-rooms-with-names-sorted-by-events-in-this-rooms"><a class="header" href="#show-rooms-with-names-sorted-by-events-in-this-rooms">Show rooms with names, sorted by events in this rooms</a></h2>
<p><strong>Sort and order with bash</strong></p>
<pre><code class="language-bash">echo &quot;SELECT event_json.room_id, room_stats_state.name FROM event_json, room_stats_state \
WHERE room_stats_state.room_id = event_json.room_id&quot; | psql -d synapse -h localhost -U synapse_user -t \
| sort | uniq -c | sort -n
</code></pre>
<p>Documentation for <code>psql</code> command line parameters: https://www.postgresql.org/docs/current/app-psql.html</p>
<p><strong>Sort and order with SQL</strong></p>
<pre><code class="language-sql">SELECT COUNT(*), event_json.room_id, room_stats_state.name
  FROM event_json, room_stats_state
  WHERE room_stats_state.room_id = event_json.room_id
  GROUP BY event_json.room_id, room_stats_state.name
  ORDER BY COUNT(*) DESC
  LIMIT 50;
</code></pre>
<h3 id="result-example-3"><a class="header" href="#result-example-3">Result example:</a></h3>
<pre><code>   9459  !FPUfgzXYWTKgIrwKxW:matrix.org              | This Week in Matrix
   9459  !FPUfgzXYWTKgIrwKxW:matrix.org              | This Week in Matrix (TWIM)
  17799  !iDIOImbmXxwNngznsa:matrix.org              | Linux in Russian
  18739  !GnEEPYXUhoaHbkFBNX:matrix.org              | Riot Android
  23373  !QtykxKocfZaZOUrTwp:matrix.org              | Matrix HQ
  39504  !gTQfWzbYncrtNrvEkB:matrix.org              | ru.[matrix]
  43601  !iNmaIQExDMeqdITdHH:matrix.org              | Riot
  43601  !iNmaIQExDMeqdITdHH:matrix.org              | Riot Web/Desktop
</code></pre>
<h2 id="lookup-room-state-info-by-list-of-room_id"><a class="header" href="#lookup-room-state-info-by-list-of-room_id">Lookup room state info by list of room_id</a></h2>
<p>You get the same information when you use the
<a href="../../admin_api/rooms.html#room-details-api">admin API</a>.</p>
<pre><code class="language-sql">SELECT rss.room_id, rss.name, rss.canonical_alias, rss.topic, rss.encryption,
    rsc.joined_members, rsc.local_users_in_room, rss.join_rules
  FROM room_stats_state rss
  LEFT JOIN room_stats_current rsc USING (room_id)
  WHERE room_id IN ( WHERE room_id IN (
    '!OGEhHVWSdvArJzumhm:matrix.org',
    '!YTvKGNlinIzlkMTVRl:matrix.org' 
  );
</code></pre>
<h2 id="show-users-and-devices-that-have-not-been-online-for-a-while"><a class="header" href="#show-users-and-devices-that-have-not-been-online-for-a-while">Show users and devices that have not been online for a while</a></h2>
<pre><code class="language-sql">SELECT user_id, device_id, user_agent, TO_TIMESTAMP(last_seen / 1000) AS &quot;last_seen&quot;
  FROM devices
  WHERE last_seen &lt; DATE_PART('epoch', NOW() - INTERVAL '3 month') * 1000;
</code></pre>

                    </main>

                    <nav class="nav-wrapper" aria-label="Page navigation">
                        <!-- Mobile navigation buttons -->
                            <a rel="prev" href="../../usage/administration/understanding_synapse_through_grafana_graphs.html" class="mobile-nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
                                <i class="fa fa-angle-left"></i>
                            </a>
                            <a rel="next" href="../../usage/administration/database_maintenance_tools.html" class="mobile-nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
                                <i class="fa fa-angle-right"></i>
                            </a>
                        <div style="clear: both"></div>
                    </nav>
                </div>
            </div>

            <nav class="nav-wide-wrapper" aria-label="Page navigation">
                    <a rel="prev" href="../../usage/administration/understanding_synapse_through_grafana_graphs.html" class="nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
                        <i class="fa fa-angle-left"></i>
                    </a>
                    <a rel="next" href="../../usage/administration/database_maintenance_tools.html" class="nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
                        <i class="fa fa-angle-right"></i>
                    </a>
            </nav>

        </div>

        <script type="text/javascript">
            window.playground_copyable = true;
        </script>
        <script src="../../elasticlunr.min.js" type="text/javascript" charset="utf-8"></script>
        <script src="../../mark.min.js" type="text/javascript" charset="utf-8"></script>
        <script src="../../searcher.js" type="text/javascript" charset="utf-8"></script>
        <script src="../../clipboard.min.js" type="text/javascript" charset="utf-8"></script>
        <script src="../../highlight.js" type="text/javascript" charset="utf-8"></script>
        <script src="../../book.js" type="text/javascript" charset="utf-8"></script>

        <!-- Custom JS scripts -->
        <script type="text/javascript" src="../../docs/website_files/table-of-contents.js"></script>
        <script type="text/javascript" src="../../docs/website_files/version-picker.js"></script>
        <script type="text/javascript" src="../../docs/website_files/version.js"></script>
    </body>
</html>