Thread: Some advanced database features, are they present in PostgreSQL
Hi,
I have been using PostgreSQL a bit and I have been surprised by the good
quality, the features, and now the Addison-Wesley book which is excellent.
However, I have some questions about the implementation of a few
additional features.
I suppose that PostgreSQL hasn't any ability to do the following yet:
- log all transactions to a special log file, that can be used for
backup purposes: ie you dump the database every day, but you keep
the transaction log on a separate disk. Should the database disk
crash, you won't have any data loss if you restore the backup and
replay the transaction log.
- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.
- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.
- ability to have databases bigger than the host's maximum file size
if this is true, can someone tell me why it hasn't been implemented yet
(there might be very good reasons), if it will be, and if the task seems
complicated.
thank you for your time.
On Tue, 10 Oct 2000, Marc SCHAEFER wrote: > - ability to have databases bigger than the host's maximum file size This has been in there since the old postgres days (although we did find it have a problem with Linux and files exactly 2Gig). PostgreSQL now splits tables at the 1Gb level, so tables are only really limited to the disk size, not the file size (normally 2^32 bytes = 2Gig) Peter -- Peter T Mount peter@retep.org.uk http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Marc SCHAEFER writes: > - log all transactions to a special log file, that can be used for > backup purposes: Will be in 7.1. > - hard transactions: cutting the power to a PostgreSQL server > may cause data loss and/or data corruption. Some databases use > sophisticated techniques to ensure serialization of operation > through journaling, redoing some of the transactions at > bootup time if required. This is really the same as above in implementation. So same answer. > - the ability to synchronize two database servers, with only the > changes being exchanged, live. Or the ability to have many > servers in a load-balancing or data scattering pool. Something like this has recently been announced as add-on from PostgreSQL, Inc. (www.pgsql.com) Haven't seen it used, though. > - ability to have databases bigger than the host's maximum file size That has been available for quite a while. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
>> - hard transactions: cutting the power to a PostgreSQL server
>> may cause data loss and/or data corruption.
That is false; Postgres is secure now against power failures,
at least if you run in the default mode with lots of fsync()s.
The WAL feature planned for 7.1 should make performance better,
but it won't make any fundamental change in reliability for power
failures.
The existing server does not offer any protection against disk hardware
failure, however. RAID disks might be an adequate answer to that.
regards, tom lane
On Tue, 10 Oct 2000, Peter Eisentraut wrote: > Will be in 7.1. [ ... ] > Something like this has recently been announced as add-on from PostgreSQL, [ ... ] > That has been available for quite a while. [ ... ] So, those are very good news. Thanks, and keep the good work.
hello. i am faced with a situation where i must store a potentially large ascii string (several thousand characters?). i am looking for some insight beyond what the documentation offers... is there a limit on the upper limit of a VARCHAR? i cannot find one in the documentation. is it true that a TEXT field can be any size? what is the best way to manage memory? for example, if i declare it as VARCHAR(10000), does that mean that memory for 10,000 characters will be allocated whether i use it or not, or is it dynamic? how about the TEXT type. thanks chris
I'm looking for info about the synching and I don't see any. Is there a more specific URL and is there a time frame for this. Also, has segmentation been discussed? -----Original Message----- From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On Behalf Of Peter Eisentraut Sent: Tuesday, October 10, 2000 1:08 PM To: Marc SCHAEFER Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Some advanced database features, are they present in PostgreSQL Marc SCHAEFER writes: > - log all transactions to a special log file, that can be used for > backup purposes: Will be in 7.1. > - hard transactions: cutting the power to a PostgreSQL server > may cause data loss and/or data corruption. Some databases use > sophisticated techniques to ensure serialization of operation > through journaling, redoing some of the transactions at > bootup time if required. This is really the same as above in implementation. So same answer. > - the ability to synchronize two database servers, with only the > changes being exchanged, live. Or the ability to have many > servers in a load-balancing or data scattering pool. Something like this has recently been announced as add-on from PostgreSQL, Inc. (www.pgsql.com) Haven't seen it used, though. > - ability to have databases bigger than the host's maximum file size That has been available for quite a while. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> - hard transactions: cutting the power to a PostgreSQL server > may cause data loss and/or data corruption. Some databases use > sophisticated techniques to ensure serialization of operation > through journaling, redoing some of the transactions at > bootup time if required. > > - ability to have databases bigger than the host's maximum file size Neither of these are problems in PostgreSQL. Tom Lane already answered the transaction one, but I'll answer the size one. PostgreSQL stores each table in a separate file, and will store a table in multiple files if it becomes larger than about 1 gig. I've had (for testing, not production) databases with 30 GB of data on Linux where the file limit was 2 GB. As for your other two concerns, I'd like to see them fixed as well. I handle those issues currently at the application level, but I'm considering writing a small change to the core to provide that functionality (as soon as I finish my other add on projects). -- Adam Ruth InterCation, Inc. www.intercation.com "Marc SCHAEFER" <schaefer@alphanet.ch> wrote in message news:Pine.LNX.3.96.1001010155021.1563A-100000@defian.alphanet.ch... > Hi, > > I have been using PostgreSQL a bit and I have been surprised by the good > quality, the features, and now the Addison-Wesley book which is excellent. > However, I have some questions about the implementation of a few > additional features. > > I suppose that PostgreSQL hasn't any ability to do the following yet: > > - log all transactions to a special log file, that can be used for > backup purposes: ie you dump the database every day, but you keep > the transaction log on a separate disk. Should the database disk > crash, you won't have any data loss if you restore the backup and > replay the transaction log. > > - hard transactions: cutting the power to a PostgreSQL server > may cause data loss and/or data corruption. Some databases use > sophisticated techniques to ensure serialization of operation > through journaling, redoing some of the transactions at > bootup time if required. > > - the ability to synchronize two database servers, with only the > changes being exchanged, live. Or the ability to have many > servers in a load-balancing or data scattering pool. > > - ability to have databases bigger than the host's maximum file size > > if this is true, can someone tell me why it hasn't been implemented yet > (there might be very good reasons), if it will be, and if the task seems > complicated. > > thank you for your time. > > >
"chris markiewicz" <cmarkiew@commnav.com> writes:
> is there a limit on the upper limit of a VARCHAR? i cannot find one in the
> documentation.
The physical limit is circa 1Gb under TOAST. There's a purely arbitrary
limit at 10Mb, which I put in on the theory that "varchar(100000000)"
is probably a typo and certainly pretty silly. (If anyone wants to
argue that decision, feel free --- I just did it on the spur of the
moment while changing the old code that checked for declared size <
BLCKSZ.)
> is it true that a TEXT field can be any size?
TEXT also has a limit at 1Gb. There's really no difference between TEXT
and VARCHAR as far as storage goes. My advice is use VARCHAR(n) if
there is some reason *in the semantics of your application* why the
field should never exceed n characters. If there's not an application-
derived reason for a specific upper limit, declare your field as TEXT
to document that there's no particular limit on it.
> what is the best way to manage memory? for example, if i declare it as
> VARCHAR(10000), does that mean that memory for 10,000 characters will be
> allocated whether i use it or not, or is it dynamic? how about the TEXT
> type.
Either one stores however many characters there are, and no more. Think
of the VARCHAR limit as a constraint check ("length(field) <= n"),
not a storage property.
This is quite unlike CHAR(n), where you get truncation or blank padding
to exactly n characters, so the limit is a storage property as well as
a constraint.
regards, tom lane
Hello Tom, On Tuesday, October 10, 2000 à 11:34:49 PM, you said : TL> "chris markiewicz" <cmarkiew@commnav.com> writes: >> is there a limit on the upper limit of a VARCHAR? i cannot find one in the >> documentation. TL> The physical limit is circa 1Gb under TOAST. There's a purely arbitrary TL> limit at 10Mb, which I put in on the theory that "varchar(100000000)" TL> is probably a typo and certainly pretty silly. (If anyone wants to TL> argue that decision, feel free --- I just did it on the spur of the TL> moment while changing the old code that checked for declared size < TL> BLCKSZ.) Excuse me, what is the 8kb-per-record size limit if we can have so big fields ? -- Jean-Christophe Boggio cat@thefreecat.org Independant Consultant and Developer Delphi, Linux, Oracle, Perl
Jean-Christophe Boggio <cat@thefreecat.org> writes:
>>> is there a limit on the upper limit of a VARCHAR? i cannot find one in the
>>> documentation.
TL> The physical limit is circa 1Gb under TOAST.
> Excuse me, what is the 8kb-per-record size limit if we can have so
> big fields ?
Sorry --- TOAST is 7.1. In existing releases, TEXT, VARCHAR, and CHAR
are all constrained by the BLCKSZ limit on total row size. But still,
that offers no reason for choosing TEXT over VARCHAR or vice versa.
regards, tom lane
There is another issue with TEXT vs VARCHAR. A TEXT field is not handled well
by ODBC and MS Access. If there is an index on the TEXT field, the ODBC link
will fail, saying something like "Can Not Index a OLE field." OLE fields are Access's
attempt at a BLOB field, and they are not indexable.
If you are using ODBC, I would not use a TEXT field until you test it in your enviroment.
Have Fun,
Dave Huttleston Jr
On Tue, 10 Oct 2000 17:50:25 -0400, you wrote:
> Jean-Christophe Boggio <cat@thefreecat.org> writes:
> >>> is there a limit on the upper limit of a VARCHAR? i cannot find one in the
> >>> documentation.
>
> TL> The physical limit is circa 1Gb under TOAST.
>
> > Excuse me, what is the 8kb-per-record size limit if we can have so
> > big fields ?
>
> Sorry --- TOAST is 7.1. In existing releases, TEXT, VARCHAR, and CHAR
> are all constrained by the BLCKSZ limit on total row size. But still,
> that offers no reason for choosing TEXT over VARCHAR or vice versa.
>
> regards, tom lane
>
>
right now, we are working with our partners internally on this, before we
release it publicly, but more information can be found at:
http://www.erserver.com/
On Tue, 10 Oct 2000, Rob Hutton wrote:
> I'm looking for info about the synching and I don't see any. Is there a
> more specific URL and is there a time frame for this. Also, has
> segmentation been discussed?
>
> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Peter Eisentraut
> Sent: Tuesday, October 10, 2000 1:08 PM
> To: Marc SCHAEFER
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Some advanced database features, are they present
> in PostgreSQL
>
>
> Marc SCHAEFER writes:
>
> > - log all transactions to a special log file, that can be used for
> > backup purposes:
>
> Will be in 7.1.
>
> > - hard transactions: cutting the power to a PostgreSQL server
> > may cause data loss and/or data corruption. Some databases use
> > sophisticated techniques to ensure serialization of operation
> > through journaling, redoing some of the transactions at
> > bootup time if required.
>
> This is really the same as above in implementation. So same answer.
>
> > - the ability to synchronize two database servers, with only the
> > changes being exchanged, live. Or the ability to have many
> > servers in a load-balancing or data scattering pool.
>
> Something like this has recently been announced as add-on from PostgreSQL,
> Inc. (www.pgsql.com) Haven't seen it used, though.
>
> > - ability to have databases bigger than the host's maximum file size
>
> That has been available for quite a while.
>
> --
> Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
>
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
David Huttleston Jr <dhjr@hddesign.com> writes:
> There is another issue with TEXT vs VARCHAR. A TEXT field is not
> handled well by ODBC and MS Access. If there is an index on the TEXT
> field, the ODBC link will fail, saying something like "Can Not Index a
> OLE field." OLE fields are Access's attempt at a BLOB field, and they
> are not indexable.
Hmm ... sounds like our ODBC driver is falling down on the job when it
comes to representing TEXT columns in ODBC-speak. There's surely no
reason for a TEXT column to behave worse than VARCHAR(n).
I think this points up the comment I made earlier today on
pgsql-interfaces, that our ODBC driver is badly in need of attention
from a committed maintainer. There's a lot of minor stuff that needs
done, and no one seems to want to do it. There's gotta be someone
out there to pick up this ball and run with it...
regards, tom lane
I have to tell you guys that his is the major limiting factor that has
kept us away from PGSQL for major projects. This looks great. Now, two
questions.
How much of what is on the page is what it will eventually do and how much
will there actually be in the beta.
Also, the site talks about data hierarchy replication. Is this similar to
segmentation where each site sees the information that is of interest to
them but not the info from all other sites? We have kind of the hub and
spoke situation where the corporate office gets everything from all the
sites, but the sites only get info from the home office that is of interest
to them. We were going to do this by segmenting the database into site
specific segments and only replicating that segment to the appropriate
site...
Thanks,
Rob
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of The Hermit Hacker
Sent: Tuesday, October 10, 2000 8:34 PM
To: Rob Hutton
Cc: 'Peter Eisentraut'; 'Marc SCHAEFER'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Some advanced database features, are they present
in PostgreSQL
right now, we are working with our partners internally on this, before we
release it publicly, but more information can be found at:
http://www.erserver.com/
On Tue, 10 Oct 2000, Rob Hutton wrote:
> I'm looking for info about the synching and I don't see any. Is there a
> more specific URL and is there a time frame for this. Also, has
> segmentation been discussed?
>
> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Peter Eisentraut
> Sent: Tuesday, October 10, 2000 1:08 PM
> To: Marc SCHAEFER
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Some advanced database features, are they present
> in PostgreSQL
>
>
> Marc SCHAEFER writes:
>
> > - log all transactions to a special log file, that can be used for
> > backup purposes:
>
> Will be in 7.1.
>
> > - hard transactions: cutting the power to a PostgreSQL server
> > may cause data loss and/or data corruption. Some databases use
> > sophisticated techniques to ensure serialization of operation
> > through journaling, redoing some of the transactions at
> > bootup time if required.
>
> This is really the same as above in implementation. So same answer.
>
> > - the ability to synchronize two database servers, with only the
> > changes being exchanged, live. Or the ability to have many
> > servers in a load-balancing or data scattering pool.
>
> Something like this has recently been announced as add-on from PostgreSQL,
> Inc. (www.pgsql.com) Haven't seen it used, though.
>
> > - ability to have databases bigger than the host's maximum file size
>
> That has been available for quite a while.
>
> --
> Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
>
>
>
Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary:
scrappy@{freebsd|postgresql}.org
Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL. Anyone can give me the title and the author or the way to find it? Here in italy i can't find nothing !! Thanks -----Messaggio Originale----- Da: "Marc SCHAEFER" <schaefer@alphanet.ch> A: <pgsql-general@postgresql.org> Data invio: Tuesday, October 10, 2000 03:51 PM Oggetto: [GENERAL] Some advanced database features, are they present in PostgreSQL > Hi, > > I have been using PostgreSQL a bit and I have been surprised by the good > quality, the features, and now the Addison-Wesley book which is excellent. > However, I have some questions about the implementation of a few > additional features. > > I suppose that PostgreSQL hasn't any ability to do the following yet: > > - log all transactions to a special log file, that can be used for > backup purposes: ie you dump the database every day, but you keep > the transaction log on a separate disk. Should the database disk > crash, you won't have any data loss if you restore the backup and > replay the transaction log. > > - hard transactions: cutting the power to a PostgreSQL server > may cause data loss and/or data corruption. Some databases use > sophisticated techniques to ensure serialization of operation > through journaling, redoing some of the transactions at > bootup time if required. > > - the ability to synchronize two database servers, with only the > changes being exchanged, live. Or the ability to have many > servers in a load-balancing or data scattering pool. > > - ability to have databases bigger than the host's maximum file size > > if this is true, can someone tell me why it hasn't been implemented yet > (there might be very good reasons), if it will be, and if the task seems > complicated. > > thank you for your time. > > > >
Hasn't been released for print yet... you can download a PDF version of it at www.postgresql.org Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Giorgio Ponza" <giorgio@opla.it> To: "Marc SCHAEFER" <schaefer@alphanet.ch>; <pgsql-general@postgresql.org> Sent: Wednesday, October 11, 2000 11:37 AM Subject: R: [GENERAL] PostgreSQL book > Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL. > Anyone can give me the title and the author or the way to find it? Here in > italy i can't find nothing !! > Thanks > > -----Messaggio Originale----- > Da: "Marc SCHAEFER" <schaefer@alphanet.ch> > A: <pgsql-general@postgresql.org> > Data invio: Tuesday, October 10, 2000 03:51 PM > Oggetto: [GENERAL] Some advanced database features, are they present in > PostgreSQL > > > > Hi, > > > > I have been using PostgreSQL a bit and I have been surprised by the good > > quality, the features, and now the Addison-Wesley book which is excellent. > > However, I have some questions about the implementation of a few > > additional features. > > > > I suppose that PostgreSQL hasn't any ability to do the following yet: > > > > - log all transactions to a special log file, that can be used for > > backup purposes: ie you dump the database every day, but you keep > > the transaction log on a separate disk. Should the database disk > > crash, you won't have any data loss if you restore the backup and > > replay the transaction log. > > > > - hard transactions: cutting the power to a PostgreSQL server > > may cause data loss and/or data corruption. Some databases use > > sophisticated techniques to ensure serialization of operation > > through journaling, redoing some of the transactions at > > bootup time if required. > > > > - the ability to synchronize two database servers, with only the > > changes being exchanged, live. Or the ability to have many > > servers in a load-balancing or data scattering pool. > > > > - ability to have databases bigger than the host's maximum file size > > > > if this is true, can someone tell me why it hasn't been implemented yet > > (there might be very good reasons), if it will be, and if the task seems > > complicated. > > > > thank you for your time. > > > > > > > >
Download it from http://www.postgresql.org/docs/aw_pgsql_book/aw_pgsql_book.pdf ----- Original Message ----- From: "Giorgio Ponza" <giorgio@opla.it> To: "Marc SCHAEFER" <schaefer@alphanet.ch>; <pgsql-general@postgresql.org> Sent: Wednesday, October 11, 2000 11:37 AM Subject: R: [GENERAL] PostgreSQL book > Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL. > Anyone can give me the title and the author or the way to find it? Here in > italy i can't find nothing !! > Thanks > > -----Messaggio Originale----- > Da: "Marc SCHAEFER" <schaefer@alphanet.ch> > A: <pgsql-general@postgresql.org> > Data invio: Tuesday, October 10, 2000 03:51 PM > Oggetto: [GENERAL] Some advanced database features, are they present in > PostgreSQL > > > > Hi, > > > > I have been using PostgreSQL a bit and I have been surprised by the good > > quality, the features, and now the Addison-Wesley book which is excellent. > > However, I have some questions about the implementation of a few > > additional features. > > > > I suppose that PostgreSQL hasn't any ability to do the following yet: > > > > - log all transactions to a special log file, that can be used for > > backup purposes: ie you dump the database every day, but you keep > > the transaction log on a separate disk. Should the database disk > > crash, you won't have any data loss if you restore the backup and > > replay the transaction log. > > > > - hard transactions: cutting the power to a PostgreSQL server > > may cause data loss and/or data corruption. Some databases use > > sophisticated techniques to ensure serialization of operation > > through journaling, redoing some of the transactions at > > bootup time if required. > > > > - the ability to synchronize two database servers, with only the > > changes being exchanged, live. Or the ability to have many > > servers in a load-balancing or data scattering pool. > > > > - ability to have databases bigger than the host's maximum file size > > > > if this is true, can someone tell me why it hasn't been implemented yet > > (there might be very good reasons), if it will be, and if the task seems > > complicated. > > > > thank you for your time. > > > > > > > > > >