Skip to content

Commit ceec779

Browse files
bmomjianbmomjian
committed
Update FAQ.
1 parent 3576820 commit ceec779

File tree

2 files changed

+54
-71
lines changed

2 files changed

+54
-71
lines changed

‎doc/FAQ

Lines changed: 27 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated: Tue Feb 12 12:18:09 EST 2002
4+
Last updated: Thu Feb 14 12:14:47 EST 2002
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -48,13 +48,11 @@
4848
Why?
4949
3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors.
5050
Why?
51-
3.5) How do I prevent other hosts from accessing my PostgreSQL
52-
database?
53-
3.6) Why can't I connect to my database from another machine?
54-
3.7) How do I tune the database engine for better performance?
55-
3.8) What debugging features are available?
56-
3.9) Why do I get "Sorry, too many clients" when trying to connect?
57-
3.10) What are the pg_sorttempNNN.NN files in my database directory?
51+
3.5) How do I control connections from other hosts?
52+
3.6) How do I tune the database engine for better performance?
53+
3.7) What debugging features are available?
54+
3.8) Why do I get "Sorry, too many clients" when trying to connect?
55+
3.9) What are the pg_sorttempNNN.NN files in my database directory?
5856

5957
Operational Questions
6058

@@ -484,22 +482,15 @@
484482
Administrator's Guide for more detailed information about shared
485483
memory and semaphores.
486484

487-
3.5) How do I prevent other hosts from accessing my PostgreSQL database?
485+
3.5) How do I control connections from other hosts?
488486

489487
By default, PostgreSQL only allows connections from the local machine
490488
using Unix domain sockets. Other machines will not be able to connect
491489
unless you add the -i flag to postmaster, and enable host-based
492490
authentication by modifying the file $PGDATA/pg_hba.conf accordingly.
493491
This will allow TCP/IP connections.
494492

495-
3.6) Why can't I connect to my database from another machine?
496-
497-
The default configuration allows only Unix domain socket connections
498-
from the local machine. To enable TCP/IP connections, make sure
499-
postmaster has been started with the -i option, and add an appropriate
500-
host entry to the file pgsql/data/pg_hba.conf.
501-
502-
3.7) How do I tune the database engine for better performance?
493+
3.6) How do I tune the database engine for better performance?
503494

504495
Certainly, indexes can speed up queries. The EXPLAIN command allows
505496
you to see how PostgreSQL is interpreting your query, and which
@@ -530,7 +521,7 @@
530521
You can also use the CLUSTER command to group data in tables to match
531522
an index. See the CLUSTER manual page for more details.
532523

533-
3.8) What debugging features are available?
524+
3.7) What debugging features are available?
534525

535526
PostgreSQL has several features that report status information that
536527
can be valuable for debugging purposes.
@@ -577,7 +568,7 @@
577568
pgsql/data/base/dbname directory. The client profile file will be put
578569
in the client's current directory.
579570

580-
3.9) Why do I get "Sorry, too many clients" when trying to connect?
571+
3.8) Why do I get "Sorry, too many clients" when trying to connect?
581572

582573
You need to increase postmaster's limit on how many concurrent backend
583574
processes it can start.
@@ -601,7 +592,7 @@
601592
was 64, and changing it required a rebuild after altering the
602593
MaxBackendId constant in include/storage/sinvaladt.h.
603594

604-
3.10) What are the pg_sorttempNNN.NN files in my database directory?
595+
3.9) What are the pg_sorttempNNN.NN files in my database directory?
605596

606597
They are temporary files generated by the query executor. For example,
607598
if a sort needs to be done to satisfy an ORDER BY, and the sort
@@ -671,29 +662,31 @@
671662
4.6) How much database disk space is required to store data from a typical
672663
text file?
673664

674-
A PostgreSQL database may need six-and-a-half times the disk space
675-
required to store the data in a flat file.
676-
677-
Consider a file of 300,000 lines with two integers on each line. The
678-
flat file is 2.4 MB. The size of the PostgreSQL database file
679-
containing this data can be estimated at 14 MB:
665+
A PostgreSQL database may require up to five times the disk space to
666+
store data from a text file.
667+
668+
As an example, consider a file of 100,000 lines with an integer and
669+
text description on each line. Suppose the text string avergages
670+
twenty characters in length. The flat file would be 2.8 MB. The size
671+
of the PostgreSQL database file containing this data can be estimated
672+
as 6.6 MB:
680673
36 bytes: each row header (approximate)
681-
+ 8 bytes: two int fields @ 4 bytes each
674+
26 bytes: two int fields @ 4 bytes each
682675
+ 4 bytes: pointer on page to tuple
683676
----------------------------------------
684-
48 bytes per row
677+
66 bytes per row
685678

686679
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
687680

688681
8192 bytes per page
689-
------------------- = 171 rows per database page (rounded up)
690-
48 bytes per row
682+
------------------- = 124 rows per database page (rounded down)
683+
66 bytes per row
691684

692-
300000 data rows
693-
-------------------- = 1755 database pages
694-
171 rows per page
685+
100000 data rows
686+
-------------------- = 807 database pages (rounded up)
687+
124 rows per page
695688

696-
1755 database pages * 8192 bytes per page = 14,376,960 bytes (14 MB)
689+
807 database pages * 8192 bytes per page = 6,610,944 bytes (6.6 MB)
697690

698691
Indexes do not require as much overhead, but do contain the data that
699692
is being indexed, so they can be large also.

‎doc/src/FAQ/FAQ.html

Lines changed: 27 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
alink="#0000ff">
1515
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1616

17-
<P>Last updated: Tue Feb 12 12:18:09 EST 2002</P>
17+
<P>Last updated: Thu Feb 14 12:14:47 EST 2002</P>
1818

1919
<P>Current maintainer: Bruce Momjian (<A href=
2020
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
@@ -72,16 +72,13 @@ <H2 align="center">Administrative Questions</H2>
7272
get <I>IpcMemoryCreate</I> errors. Why?<BR>
7373
<A href="#3.4">3.4</A>) When I try to start <I>postmaster</I>, I
7474
get <I>IpcSemaphoreCreate</I> errors. Why?<BR>
75-
<A href="#3.5">3.5</A>) How do I prevent other hosts from
76-
accessing my PostgreSQL database?<BR>
77-
<A href="#3.6">3.6</A>) Why can't I connect to my database from
78-
another machine?<BR>
79-
<A href="#3.7">3.7</A>) How do I tune the database engine for
75+
<A href="#3.5">3.5</A>) How do I control connections from other hosts?<BR>
76+
<A href="#3.6">3.6</A>) How do I tune the database engine for
8077
better performance?<BR>
81-
<A href="#3.8">3.8</A>) What debugging features are available?<BR>
82-
<A href="#3.9">3.9</A>) Why do I get <I>"Sorry, too many
78+
<A href="#3.7">3.7</A>) What debugging features are available?<BR>
79+
<A href="#3.8">3.8</A>) Why do I get <I>"Sorry, too many
8380
clients"</I> when trying to connect?<BR>
84-
<A href="#3.10">3.10</A>) What are the <I>pg_sorttempNNN.NN</I>
81+
<A href="#3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I>
8582
files in my database directory?<BR>
8683

8784

@@ -633,8 +630,8 @@ <H4><A name="3.4">3.4</A>) When I try to start <I>postmaster</I>, I
633630
PostgreSQL Administrator's Guide for more detailed information
634631
about shared memory and semaphores.</P>
635632

636-
<H4><A name="3.5">3.5</A>) How do I prevent other hosts from
637-
accessing my PostgreSQL database?</H4>
633+
<H4><A name="3.5">3.5</A>) How do I control connections from other
634+
hosts?</H4>
638635

639636
<P>By default, PostgreSQL only allows connections from the local
640637
machine using Unix domain sockets. Other machines will not be able
@@ -643,16 +640,7 @@ <H4><A name="3.5">3.5</A>) How do I prevent other hosts from
643640
<I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP
644641
connections.</P>
645642

646-
<H4><A name="3.6">3.6</A>) Why can't I connect to my database from
647-
another machine?</H4>
648-
649-
<P>The default configuration allows only Unix domain socket
650-
connections from the local machine. To enable TCP/IP connections,
651-
make sure <I>postmaster</I> has been started with the <I>-i</I>
652-
option, and add an appropriate host entry to the file
653-
<I>pgsql/data/pg_hba.conf</I>.</P>
654-
655-
<H4><A name="3.7">3.7</A>) How do I tune the database engine for
643+
<H4><A name="3.6">3.6</A>) How do I tune the database engine for
656644
better performance?</H4>
657645

658646
<P>Certainly, indexes can speed up queries. The
@@ -689,7 +677,7 @@ <H4><A name="3.7">3.7</A>) How do I tune the database engine for
689677
data in tables to match an index. See the <SMALL>CLUSTER</SMALL>
690678
manual page for more details.</P>
691679

692-
<H4><A name="3.8">3.8</A>) What debugging features are
680+
<H4><A name="3.7">3.7</A>) What debugging features are
693681
available?</H4>
694682

695683
<P>PostgreSQL has several features that report status information
@@ -745,7 +733,7 @@ <H4><A name="3.8">3.8</A>) What debugging features are
745733
in the <I>pgsql/data/base/dbname</I> directory. The client profile
746734
file will be put in the client's current directory.</P>
747735

748-
<H4><A name="3.9">3.9</A>) Why do I get <I>"Sorry, too many
736+
<H4><A name="3.8">3.8</A>) Why do I get <I>"Sorry, too many
749737
clients"</I> when trying to connect?</H4>
750738

751739
<P>You need to increase <I>postmaster</I>'s limit on how many
@@ -775,7 +763,7 @@ <H4><A name="3.9">3.9</A>) Why do I get <I>"Sorry, too many
775763
the MaxBackendId constant in
776764
<I>include/storage/sinvaladt.h</I>.</P>
777765

778-
<H4><A name="3.10">3.10</A>) What are the <I>pg_sorttempNNN.NN</I>
766+
<H4><A name="3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I>
779767
files in my database directory?</H4>
780768

781769
<P>They are temporary files generated by the query executor. For
@@ -862,30 +850,32 @@ <H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
862850
<H4><A name="4.6">4.6</A>) How much database disk space is required
863851
to store data from a typical text file?</H4>
864852

865-
<P>A PostgreSQL database may need six-and-a-half times the disk
866-
space required to store the data in a flat file.</P>
853+
<P>A PostgreSQL database may require up to five times the disk space
854+
to store data from a text file.</P>
867855

868-
<P>Consider a file of 300,000 lines with two integers on each line.
869-
The flat file is 2.4 MB. The size of the PostgreSQL database file
870-
containing this data can be estimated at 14 MB:</P>
856+
<P>As an example, consider a file of 100,000 lines with an integer
857+
and text description on each line. Suppose the text string avergages
858+
twenty characters in length. The flat file would be 2.8 MB. The size
859+
of the PostgreSQL database file containing this data can be
860+
estimated as 6.6 MB:</P>
871861
<PRE>
872862
36 bytes: each row header (approximate)
873-
+ 8 bytes: two int fields @ 4 bytes each
863+
26 bytes: two int fields @ 4 bytes each
874864
+ 4 bytes: pointer on page to tuple
875865
----------------------------------------
876-
48 bytes per row
866+
66 bytes per row
877867

878868
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
879869

880870
8192 bytes per page
881-
------------------- = 171 rows per database page (rounded up)
882-
48 bytes per row
871+
------------------- = 124 rows per database page (rounded down)
872+
66 bytes per row
883873

884-
300000 data rows
885-
-------------------- = 1755 database pages
886-
171 rows per page
874+
100000 data rows
875+
-------------------- = 807 database pages (rounded up)
876+
124 rows per page
887877

888-
1755 database pages * 8192 bytes per page = 14,376,960 bytes (14 MB)
878+
807 database pages * 8192 bytes per page = 6,610,944 bytes (6.6 MB)
889879
</PRE>
890880

891881
<P>Indexes do not require as much overhead, but do contain the data

0 commit comments

Comments
 (0)