|
Server : Apache System : Linux cs317.bluehost.com 4.19.286-203.ELK.el7.x86_64 #1 SMP Wed Jun 14 04:33:55 CDT 2023 x86_64 User : andertr9 ( 1047) PHP Version : 8.2.18 Disable Function : NONE Directory : /usr/share/doc/postgresql-9.2.24/html/ |
Upload File : |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Locking and Indexes</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.2.24 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Concurrency Control"
HREF="mvcc.html"><LINK
REL="PREVIOUS"
TITLE="Caveats"
HREF="mvcc-caveats.html"><LINK
REL="NEXT"
TITLE="Performance Tips"
HREF="performance-tips.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-06T22:43:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.2.24 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Caveats"
HREF="mvcc-caveats.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 13. Concurrency Control</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Performance Tips"
HREF="performance-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="LOCKING-INDEXES"
>13.6. Locking and Indexes</A
></H1
><P
> Though <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
provides nonblocking read/write access to table
data, nonblocking read/write access is not currently offered for every
index access method implemented
in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
The various index types are handled as follows:
<P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>B-tree, <ACRONYM
CLASS="ACRONYM"
>GiST</ACRONYM
> and <ACRONYM
CLASS="ACRONYM"
>SP-GiST</ACRONYM
> indexes</DT
><DD
><P
> Short-term share/exclusive page-level locks are used for
read/write access. Locks are released immediately after each
index row is fetched or inserted. These index types provide
the highest concurrency without deadlock conditions.
</P
></DD
><DT
>Hash indexes</DT
><DD
><P
> Share/exclusive hash-bucket-level locks are used for read/write
access. Locks are released after the whole bucket is processed.
Bucket-level locks provide better concurrency than index-level
ones, but deadlock is possible since the locks are held longer
than one index operation.
</P
></DD
><DT
><ACRONYM
CLASS="ACRONYM"
>GIN</ACRONYM
> indexes</DT
><DD
><P
> Short-term share/exclusive page-level locks are used for
read/write access. Locks are released immediately after each
index row is fetched or inserted. But note that insertion of a
GIN-indexed value usually produces several index key insertions
per row, so GIN might do substantial work for a single value's
insertion.
</P
></DD
></DL
></DIV
><P>
</P
><P
> Currently, B-tree indexes offer the best performance for concurrent
applications; since they also have more features than hash
indexes, they are the recommended index type for concurrent
applications that need to index scalar data. When dealing with
non-scalar data, B-trees are not useful, and GiST, SP-GiST or GIN
indexes should be used instead.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="mvcc-caveats.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="performance-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Caveats</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Performance Tips</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>