REINDEX vs VACUUM
What is the difference between reindex and vacuum's impact on index file? I deleted an indexed row. Running either vacuum or reindex shows the index entry for the row is removed from the index page. I was under the impression that only reindex will remove dangling index entries. I am guessing that vacuum will not shrink the index file and will only add deleted index entries in the free space file for the index? But REINDEX is recreating the index file from scratch so it is like vacuum full for index? Thanks
Re: REINDEX vs VACUUM
+ pgsql-admin Would appreciate any insights. Thanks On Sat, Dec 31, 2022 at 1:04 PM Hao Zhang wrote: > What is the difference between reindex and vacuum's impact on index file? > I deleted an indexed row. Running either vacuum or reindex shows the index > entry for the row is removed from the index page. I was under the > impression that only reindex will remove dangling index entries. I am > guessing that vacuum will not shrink the index file and will only add > deleted index entries in the free space file for the index? But REINDEX is > recreating the index file from scratch so it is like vacuum full for index? > > Thanks >
Re: Read write performance check
Veem You should also be familiar with Aurora Postgres's storage architecture, which is very different from regular Postgres (see https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.html ) Aurora is remote storage, which means if your read workload can't fit into the PG's shared buffers, it will have a very different performance than if the storage is a local SSD. On write, it writes six copies to three different availability zones for high durability and availablity. So having enough network bandwidth is a factor as well. Ken On Tue, Dec 26, 2023 at 11:30 PM Kirk Wolak wrote: > On Thu, Dec 21, 2023 at 8:31 AM veem v wrote: > >> Can someone please guide me, if any standard scripting is available for >> doing such read/write performance test? Or point me to any available docs? >> >> >> ... > > > Veem, first things first... "Top Posting" is when you reply at the top of > the email... Notice how I replied at the bottom (and I deleted context, > clearly). > This is the style we prefer here. > > Second, since you are new to postgreSQL... Let me recommend some reading. > Cybertec has articles on performance (Tom Kyte style). > Also, read the "Don't Do That" wiki, and finally, have a look at pgbench > and psql documentation. And specifically look at GENERATE_SERIES(), > but the Cybertec articles will touch on that. Regardless... Reading the > docs is insightful. > > Links: > https://www.cybertec-postgresql.com/en/postgresql-hash-index-performance/ > https://wiki.postgresql.org/wiki/Don't_Do_This > https://www.postgresql.org/docs/current/app-psql.html > https://www.postgresql.org/docs/current/pgbench.html > https://www.postgresql.org/docs/16/functions-srf.html > > HTH, > > Kirk Out! > > >
psql sslmode behavior and trace_connection_negotiation in PG17
Hi I tried to connect with psql + client sslmode = require + server requiring ssl with PG17 and trace_connection_negotiation = "on". So "SSLRequest accepted" is logged twice with two different PID. I believe the PID 15553 is psql and 15554 is the PG backend. How do you explain the two connections with SSLRequest? From the log, it seems psql made a connection to itself with SSLRequest and proxied that to Postgres server with a full SSL negotiation. I never saw a log on 15553's connection being closed when I closed the psql process. Does this behavior match what was talked about in the below hacker thread on additional connection? 2024-07-17 03:06:54.492 PDT [15553] LOG: connection received: host=127.0.0.1 port=54002 2024-07-17 03:06:54.492 PDT [15553] LOG: SSLRequest accepted 2024-07-17 03:06:59.982 PDT [15554] LOG: connection received: host=127.0.0.1 port=54004 2024-07-17 03:06:59.982 PDT [15554] LOG: SSLRequest accepted 2024-07-17 03:06:59.994 PDT [15554] LOG: connection authenticated: identity="postgres" method=md5 (/usr/local/pgsql/data/pg_hba.conf:18) 2024-07-17 03:06:59.994 PDT [15554] LOG: connection authorized: user=postgres database=postgres application_name=psql SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256) https://www.postgresql.org/message-id/flat/CAM-w4HOEAzxyY01ZKOj-iq=M4-VDk=vzqgusuqitfjfdzae...@mail.gmail.com
