PostgreSQL Open Source Database for the Enterprise

The term “scalability” gets injected into database conversations often, but scalability discussions often end without a clear consensus. I believe this is because “scalability”, like “replication”, actually means different things to different people.

There are two dimensions to database scalability; the first dimension specifies if additional servers are involved:

• vertical: The hardware capabilities of a single database server are increased to increase throughput
• horizontal: The number of a database servers is increased to increase throughput

Continue Reading »

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

As enterprises embark down the path of cloud computing we?re realizing that with many new advancements in technology, the promise of the technology is often more grand than the reality. It usually takes more time than we think it will …

Continue reading »

While database software can be the cause of outages, for Postgres, it is often not the software but the hardware that causes failures — and storage is often the failing component. Magnetic disk is one of the few moving parts on a computer, and hence prone to breakage, and solid-state drives (SSDs) have a finite write limit.

While waiting for storage to start making loud noises or fail is an option, a better option is to use some type of monitoring that warns of storage failure before it occurs, e.g. enter SMART. SMART is a system developed by storage vendors that allows the operating system to query diagnostics on the drive and warn of unusual storage behavior before failure occurs. While read/write failures are reported by the kernel, SMART parameters often warn of danger before failure occurs. Below is the SMART output from a Western Digital (WDC) WD20EARX magnetic disk drive:

Continue Reading »

Postgres consists of roughly 1.1 million lines of C code, which is compiled into an executable with millions of CPU instructions. Of the many CPU machine-language instructions in the Postgres server executable, which one is the most important? That might seem like an odd question, and one that is hard to answer, but I think I know the answer.

You might wonder, “If Postgres is written in C, how would we find the most important machine-language instruction?” Well, there is a trick to that. Postgres is not completely written in C. There is a very small file (1000 lines) with C code that adds specific assembly-language CPU instructions into the executable. This file is called s_lock.h. It is an include file that is referenced in various parts of the server code that allows very fast locking operations. The C language doesn’t supply fast-locking infrastructure, so Postgres is required to supply its own locking instructions for all twelve supported CPU architectures. (Operating system kernels do supply locking instructions, but they are much too slow to be used for Postgres.)

Continue Reading »

The new server is 2-10 times faster than my old 2003 server, but that 10x speedup is only possible for applications that:

• Do lots of random I/O, thanks to the SSDs. Postgres already supports tablespace-specific random_page_cost settings, but it would be interesting to see if there are cases that can be optimized for low random pages costs. This is probably not an immediate requirement because the in-memory algorithms already assume a low random page cost.
• Can be highly parallelized. See my previous blog entry regarding parallelism. The 16 virtual cores in this server certainly offer more parallelism opportunities than my old two-core system.

Other observations:

• It takes serious money to do the job right, roughly USD $4k — hopefully increased productivity and reliability will pay back this investment.
• I actually started the upgrade two years ago by adjusting my scripts to be more portable; this made the migration go much smoother. The same method can be used for migrations to Postgres by rewriting SQL queries to be more portable before the migration. Reliable hardware is often the best way to ensure Postgres reliability.
• My hot-swappable SATA-2 drive bays allow for a flexible hard-drive-based backup solution (no more magnetic tapes). File system snapshots allow similar backups for Postgres tablespaces, but it would be good if this were more flexible. It would also be cool if you could move a drive containing Postgres tablespaces from one server to another (perhaps after freezing the rows).

Continue Reading »

A few weeks ago, I finally replaced my eight-year-old home server. The age of my server, and its operating system, (BSD/OS, last officially updated in 2002) were a frequent source of amusement among Postgres community members. The new server is:

Super Micro 7046A-T 4U Tower Workstation
2 x Intel Xeon E5620 2.4GHz Quad-Core Processors
Crucial 24GB Dual-Rank PC3-10600 DDR3 SDRAM
Intel 160GB 320 Series SSD Drive
4 x Western Digital Caviar Green 2TB Hard Drives

Continue Reading »

As a followup to my previous blog entry, I want to show queries that allow users to analyze TOAST tables. First, we find the TOAST details about the test heap table:

SELECT oid, relname, reltoastrelid, reltoastidxid FROM pg_class where relname = 'test';
  oid  | relname | reltoastrelid | reltoastidxid
-------+---------+---------------+---------------
 17172 | test    |         17175 |             0

Continue Reading »

Many things are better toasted: cheese sandwiches, nuts, marshmallows. Even some of your Postgres data is better toasted — let me explain.

Postgres typically uses an eight-kilobyte block size — you can verify this by running pg_controldata:

Continue Reading »

As part of my server upgrade, I migrated to a newer version of LyX and LaTeX, but most significantly, to a more modern and powerful LaTeX document class, Beamer. All 1300 slides in my presentations have been updated. If you see something that needs improvement, no matter how minor, please let please know via email, chat, or blog comment.