Hight Performance Postgresql for Railsを読んでく

rideshare_development=> SELECT * FROM pg_stat_activity
rideshare_development-> WHERE pid = (SELECT PG_BACKEND_PID());
-[ RECORD 1 ]----+---------------------------------------
datid            | 16396
datname          | rideshare_development
pid              | 22568
leader_pid       | 
usesysid         | 16390
usename          | owner
application_name | psql
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 55625
backend_start    | 2024-01-29 22:58:56.428045+09
xact_start       | 2024-01-29 23:06:52.481908+09
query_start      | 2024-01-29 23:06:52.481908+09
state_change     | 2024-01-29 23:06:52.48191+09
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 863
query_id         | 
query            | SELECT * FROM pg_stat_activity        +
                 | WHERE pid = (SELECT PG_BACKEND_PID());
backend_type     | client backend

SELECT PG_SIZE_PRETTY( 
  PG_TOTAL_RELATION_SIZE('rideshare.users')
);

// 結果
 pg_size_pretty 
----------------
 2629 MB
(1 row)

// データめっちゃいれた
SELECT COUNT(*) From users;
  count   
----------
 10020210

//userテーブルの統計対象データ数をset
ALTER TABLE users
ALTER COLUMN first_name SET STATISTICS 5_000;

//分析開始
ANALYZE users;

//分析結果出力
SELECT
  attname,
  n_distinct,
  most_common_vals
FROM pg_stats
WHERE schemaname = 'rideshare' AND tablename = 'users'
AND attname = 'first_name';

rideshare_development=> SELECT
rideshare_development-> conrelid::regclass AS table_name, conname AS foreign_key, PG_GET_CONSTRAINTDEF(oid)
rideshare_development-> FROM pg_constraint
rideshare_development-> WHERE contype = 'f'
rideshare_development-> AND connamespace = 'rideshare'::regnamespace ORDER BY conrelid::regclass::text, contype DESC;
      table_name      |     foreign_key     |                    pg_get_constraintdef                    
----------------------+---------------------+------------------------------------------------------------
 trip_positions       | fk_rails_9688ac8706 | FOREIGN KEY (trip_id) REFERENCES trips(id)
 trip_requests        | fk_rails_fa2679b626 | FOREIGN KEY (start_location_id) REFERENCES locations(id)
 trip_requests        | fk_rails_c17a139554 | FOREIGN KEY (rider_id) REFERENCES users(id)
 trip_requests        | fk_rails_3fdebbfaca | FOREIGN KEY (end_location_id) REFERENCES locations(id)
 trips                | fk_rails_6d92acb430 | FOREIGN KEY (trip_request_id) REFERENCES trip_requests(id)
 trips                | fk_rails_e7560abc33 | FOREIGN KEY (driver_id) REFERENCES users(id)
 vehicle_reservations | fk_rails_7edc8e666a | FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
 vehicle_reservations | fk_rails_59996232fc | FOREIGN KEY (trip_request_id) REFERENCES trip_requests(id)

rideshare_development=> CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION
rideshare_development=> \dx

仮想列というのがposgresで使えるらしい。postgresでは生成列と呼ばれている

```rb
create_table 'temp.customers' do |t| t.string :email
t.virtual :email_downcased,
type: :string,
as: 'LOWER(email)', stored: true
end

create_enum :vehicle_status, [ VehicleStatus::DRAFT, VehicleStatus::PUBLISHED]
add_column :vehicles, :status, :enum, enum_type: :vehicle_status, default: VehicleStatus::DRAFT, null: false

~/Desktop/rideshare main !3 ?7 ❯ bin/rake active_record_doctor                                                           Ruby 3.2.2
trip_requests.end_location_id is a foreign key of type integer and references locations.id of type bigint - foreign keys should be of the same type as the referenced column
trip_requests.start_location_id is a foreign key of type integer and references locations.id of type bigint - foreign keys should be of the same type as the referenced column
vehicle_reservations.trip_request_id is a foreign key of type integer and references trip_requests.id of type bigint - foreign keys should be of the same type as the referenced column
vehicle_reservations.vehicle_id is a foreign key of type integer and references vehicles.id of type bigint - foreign keys should be of the same type as the referenced column
use `dependent: :delete_all` or similar on Vehicle.vehicle_reservations - associated model VehicleReservation has no callbacks and can be deleted in bulk
add an index on vehicle_reservations(trip_request_id) - foreign keys are often used in database lookups and should be indexed for performance reasons
add an index on trip_positions(trip_id) - foreign keys are often used in database lookups and should be indexed for performance reasons
SearchResult references a non-existent table or view named search_results
FastSearchResult references a non-existent table or view named fast_search_results
the schema limits locations.state to 2 characters but there's no length validator on Location.state - remove the database limit or add the validator
add a unique index on users(drivers_license_number) - validating uniqueness in Driver without an index can lead to duplicates
add a unique index on trips(trip_request_id) - using `has_one` in TripRequest without an index can lead to duplicates
create a foreign key on trip_requests.rider_id - looks like an association without a foreign key constraint
create a foreign key on trips.driver_id - looks like an association without a foreign key constraint
add a `presence` validator to VehicleReservation.canceled - it's NOT NULL but lacks a validator

// 早い
ALTER TABLE temp.users ADD COLUMN city_id INTEGER
DEFAULT 1;

// 遅い
ALTER TABLE temp.users ADD COLUMN city_id INTEGER
DEFAULT 1 + FLOOR(RANDOM() * 25);

// ほかセッションで実行したらtimeoutになった
rideshare_development=> SELECT * FROM temp.users LIMIT 1;
ERROR:  canceling statement due to lock timeout
LINE 1: SELECT * FROM temp.users LIMIT 1;

N+1 queries detected:
  SELECT COUNT(*) FROM "vehicle_reservations" WHERE "vehicle_reservations"."vehicle_id" = 1 /*application='Rideshare'*/
  SELECT COUNT(*) FROM "vehicle_reservations" WHERE "vehicle_reservations"."vehicle_id" = 2 /*application='Rideshare'*/
  SELECT COUNT(*) FROM "vehicle_reservations" WHERE "vehicle_reservations"."vehicle_id" = 3 /*application='Rideshare'*/
  SELECT COUNT(*) FROM "vehicle_reservations" WHERE "vehicle_reservations"."vehicle_id" = 4 /*application='Rideshare'*/
Call stack:
  (irb):9:in `block (2 levels) in <top (required)>'
  (irb):8:in `block in <top (required)>'
  (irb):8:in `<top (required)>'

irb(main):002> User.fast_count
   (38.1ms)  SELECT fast_count('users', 100000) /*application='Rideshare'*/
=> 19949796
irb(main):003> User.count
[sql.active_record] 14.766931 SELECT COUNT(*) FROM "users" /*application='Rideshare'*/
  User Count (14767.0ms)  SELECT COUNT(*) FROM "users" /*application='Rideshare'*/
=> 20040420
irb(main):004> 

rideshare_development=> EXPLAIN (ANALYZE, BUFFERS)                                                                                    SELECT * FROM temp.users                                                                                                              WHERE last_name IN ('lname10000', 'lname100000', 'lname1000000');
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using users_last_name_idx on users  (cost=0.43..19.97 rows=3 width=73) (actual time=0.038..0.053 rows=3 loops=1)
   Index Cond: (last_name = ANY ('{lname10000,lname100000,lname1000000}'::text[]))
   Buffers: shared hit=9 read=6
 Planning:
   Buffers: shared hit=15 read=1 dirtied=2
 Planning Time: 0.278 ms
 Execution Time: 0.077 ms
(7 rows)

SELECT * FROM trips
WHERE data @> '{"ride_details":{"water_offered": true}}';

rideshare_development=*> SELECT PG_SIZE_PRETTY( PG_RELATION_SIZE('trip_positions_created_at_btree'));
 pg_size_pretty 
----------------
 113 MB
(1 row)

rideshare_development=*> SELECT PG_SIZE_PRETTY( PG_RELATION_SIZE('trip_positions_created_at_brin'));
 pg_size_pretty 
----------------
 32 kB
(1 row)

rideshare_development=> SELECT ctid,id FROM users WHERE id = 1;
    ctid    | id 
------------+----
 (41443,11) |  1

rideshare_development=> SELECT
rideshare_development->  schemaname,
rideshare_development->   relname,
rideshare_development->   last_autoanalyze,
rideshare_development->   last_analyze
rideshare_development-> FROM pg_stat_all_tables WHERE relname = 'vehicles';
 schemaname | relname  | last_autoanalyze |         last_analyze          
------------+----------+------------------+-------------------------------
 rideshare  | vehicles |                  | 2024-02-03 01:06:07.150831+09
(1 row)

-- Use \watch to continually monitor results
SELECT
pid,
datname,
usename, application_name, client_hostname, client_port, backend_start, query_start, query,
state
FROM pg_stat_activity

~ ❯ psql -p 6432 -U owner pgbouncer                                                                                                6s
Password for user owner: 
psql (16.1, server 1.22.1/bouncer)
WARNING: psql major version 16, server major version 1.22.
         Some psql features might not work.
Type "help" for help.

pgbouncer=# SHOW HELP;
NOTICE:  Console usage
DETAIL:  
	SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
	SHOW PEERS|PEER_POOLS
	SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
	SHOW DNS_HOSTS|DNS_ZONES
	SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
	SET key = arg
	RELOAD
	PAUSE [<db>]
	RESUME [<db>]
	DISABLE <db>
	ENABLE <db>
	RECONNECT [<db>]
	KILL <db>
	SUSPEND
	SHUTDOWN
	WAIT_CLOSE [<db>]
SHOW
pgbouncer=# 
pgbouncer=# SHOW DATABASES;
pgbouncer=# SHOW CLIENTS;
pgbouncer=#