begriffs open source - ai-pg/blob - full-docs/src/sgml/html/locking-indexes.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>13.7. Locking and Indexes</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="mvcc-caveats.html" title="13.6. Caveats" /><link rel="next" href="performance-tips.html" title="Chapter 14. Performance Tips" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">13.7. Locking and Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="mvcc-caveats.html" title="13.6. Caveats">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="mvcc.html" title="Chapter 13. Concurrency Control">Up</a></td><th width="60%" align="center">Chapter 13. Concurrency Control</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="performance-tips.html" title="Chapter 14. Performance Tips">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOCKING-INDEXES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">13.7. Locking and Indexes <a href="#LOCKING-INDEXES" class="id_link">#</a></h2></div></div></div><a id="id-1.5.12.10.2" class="indexterm"></a><p>
   3     Though <span class="productname">PostgreSQL</span>
   4     provides nonblocking read/write access to table
   5     data, nonblocking read/write access is not currently offered for every
   6     index access method implemented
   7     in <span class="productname">PostgreSQL</span>.
   8     The various index types are handled as follows:
   9
  10     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">
  11        B-tree, <acronym class="acronym">GiST</acronym> and <acronym class="acronym">SP-GiST</acronym> indexes
  12       </span></dt><dd><p>
  13         Short-term share/exclusive page-level locks are used for
  14         read/write access. Locks are released immediately after each
  15         index row is fetched or inserted.  These index types provide
  16         the highest concurrency without deadlock conditions.
  17        </p></dd><dt><span class="term">
  18        Hash indexes
  19       </span></dt><dd><p>
  20         Share/exclusive hash-bucket-level locks are used for read/write
  21         access.  Locks are released after the whole bucket is processed.
  22         Bucket-level locks provide better concurrency than index-level
  23         ones, but deadlock is possible since the locks are held longer
  24         than one index operation.
  25        </p></dd><dt><span class="term">
  26        <acronym class="acronym">GIN</acronym> indexes
  27       </span></dt><dd><p>
  28         Short-term share/exclusive page-level locks are used for
  29         read/write access. Locks are released immediately after each
  30         index row is fetched or inserted. But note that insertion of a
  31         GIN-indexed value usually produces several index key insertions
  32         per row, so GIN might do substantial work for a single value's
  33         insertion.
  34        </p></dd></dl></div><p>
  35    </p><p>
  36     Currently, B-tree indexes offer the best performance for concurrent
  37     applications; since they also have more features than hash
  38     indexes, they are the recommended index type for concurrent
  39     applications that need to index scalar data. When dealing with
  40     non-scalar data, B-trees are not useful, and GiST, SP-GiST or GIN
  41     indexes should be used instead.
  42    </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="mvcc-caveats.html" title="13.6. Caveats">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="mvcc.html" title="Chapter 13. Concurrency Control">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="performance-tips.html" title="Chapter 14. Performance Tips">Next</a></td></tr><tr><td width="40%" align="left" valign="top">13.6. Caveats </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 14. Performance Tips</td></tr></table></div></body></html>