PostgreSQL Basics

1        What is PostgreSQL?

-        PostgreSQL is an object-relational DBMS

-        Free and the complete source code is available.

-        Developed by volunteer developers spread throughout the world and communicating via the Internet.

-        It is a community project and is not controlled by any company.

-        No central committee, or controlling company

-        Allows users to do anything they want with the code, including reselling binaries without the source code. The only restriction is that you not hold us legally liable for problems with the software. There is also the requirement that this copyright appear in all copies of the software.

-        It is fully ACID compliant

-        Has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

-        It includes most SQL: 2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

-        It also supports storage of binary large objects, including pictures, sounds, or video.

-        It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

-        An enterprise class database, PostgreSQL boasts sophisticated features such as

o   Multi-Version Concurrency Control (MVCC),

o   point in time recovery, tablespaces,

o   asynchronous replication,

o   nested transactions (savepoints),

o   online/hot backups,

o   a sophisticated query planner/optimizer,

o   write ahead logging for fault tolerance.

-        It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.

2       What platforms does PostgreSQL support?

-        It runs on all major operating systems, including

o   Linux

o   UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64)

o   Windows.

  

3        Where can I get support?

-        The PostgreSQL community provides assistance to many of its users via email. The main web site to subscribe to the email lists is http://www.postgresql.org/community/lists/.

4        How do I find out about known bugs or missing features?

-        PostgreSQL supports an extended subset of SQL:2008. See TODO list

( http://wiki.postgresql.org/wiki/Todo ) for known bugs, missing features, and future plans.

-        In practice, bugs don't last very long in the software, and bugs that affect a large number of users are fixed rapidly.

-        A feature request usually results in one of the following replies:

o   The feature is already on the TODO list

o   The feature is not desired because:

§  It duplicates existing functionality that already follows the SQL standard

§  The feature would increase code complexity but add little benefit

§  The feature would be insecure or unreliable

o   The new feature is added to the TODO list

5        What is Versioning policy?

-        Major release

-        Minor release

Major Release:  

o   include new features and occur roughly once every year

o   numbered by increasing either the first or second part of the version number, e.g. 8.2 to 8.3.

o   change the internal format of system tables and data files.

o   These changes are often complex, so we do not maintain backward compatibility of all stored data.

o   A dump/reload of the database or use of the pg_upgrade module is required for major upgrades.

o   may require changes to your app

Minor release:

o   numbered by increasing the third part of the version number, e.g. 8.1.5 to 8.1.6.

o   The PostgreSQL team only adds bug fixes to minor releases. All users should upgrade to the most recent minor release as soon as possible.

o   While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading.

o   does not require a dump and restore; merely stop the database server, install the updated binaries, and restart the server.

o   For some releases, manual changes may be required to complete the upgrade, so always read the release notes before upgrading.

  

A bug I'm encountering is fixed in a newer minor release of PostgreSQL, but I don't want to   upgrade. Can I get a patch for just this issue?

-        NO. No need to do that.

7    How does PostgreSQL use CPU resources?

-        The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session.

-        A single database session (connection) cannot utilize more than one CPU.

-        Of course, multiple sessions are automatically spread across all available CPUs by your operating system. Client applications can easily use threads and create multiple database connections from each thread.

-        A single complex and CPU-intensive query is unable to use more than one CPU to do the processing for the query. The OS may still be able to use others for disk I/O etc, but you won't see much benefit from more than one spare core.

Some general PostgreSQL limits are included in the table below.

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

8       

           What replication solutions are available?

-        Master/slave replication allows a single master to receive read/write queries, while slaves can only accept read/SELECT queries. The most popular freely available master-slave PostgreSQL replication solution is Slony-I.

-        Multi-master replication allows read/write queries to be sent to multiple replicated computers. This capability also has a severe impact on performance due to the need to synchronize changes between servers. PGCluster is the most popular such solution freely available for PostgreSQL.

  

9          Is possible to create a shared-storage postgresql server cluster?

-        PostgreSQL does not support clustering using shared storage on a SAN, SCSI backplane, iSCSI volume, or other shared media. Such "RAC-style" clustering isn't supported. Only replication-based clustering is currently supported.