limits?

We are converting our system from using Sybase's SQL Anywhere 10 to PostGres 8.3. In SQL Anywhere there technically isn't any difference in how a char and varchar is stored. They are all an array of char[1]. So we always just defined everything as a char since right truncation is the default.

In PostGres though if we are using a character type column to search should we define that as a varchar or a char or does it make any difference?

Best Regards

Michael Gould
Intermodal Software Solutions, LLC.

On Jun 23, 2008, at 10:56 AM, Kynn Jones wrote:

How can I find the limits (if any) on things such as the maximum
number of tables, views, indices, columns-per-table, size of
database, etc.?

(At the moment I'm particularly interested any limits that my exist
on the numbers of tables and views that may exist in any one
database.)

http://www.postgresql.org/about/ has some of the theoretical limits.

In real use you're unlikely to hit any limits, theoretical or
practical, but if you start to use a silly number of tables and so on
you're likely to hit performance issues eventually. I'm not sure where
that threshold would be, but it's higher than "thousands".

Cheers,
Steve

http://www.postgresql.org/about/

Character will use more disk space than varchar so it does make a
difference.

http://www.postgresql.org/docs/8.3/interactive/datatype-character.html

Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values.

Jon

________________________________

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Gould
Sent: Monday, June 23, 2008 1:01 PM
To: pgsql-general General
Subject: [GENERAL] Data Types

We are converting our system from using Sybase's SQL Anywhere 10 to
PostGres 8.3. In SQL Anywhere there technically isn't any difference in
how a char and varchar is stored. They are all an array of char[1]. So
we always just defined everything as a char since right truncation is
the default.

In PostGres though if we are using a character type column to search
should we define that as a varchar or a char or does it make any
difference?

Best Regards

Michael Gould
Intermodal Software Solutions, LLC.

"Roberts, Jon" <Jon.Roberts@asurion.com> writes:

Character will use more disk space than varchar so it does make a
difference.

char also has very peculiar comparison semantics. Unless your strings
are really truly fixed-length, you should just about always use varchar.

regards, tom lane

On Mon, 2008-06-23 at 11:21 -0700, Steve Atkins wrote:

http://www.postgresql.org/about/ has some of the theoretical limits.

In real use you're unlikely to hit any limits, theoretical or
practical, but if you start to use a silly number of tables and so
on
you're likely to hit performance issues eventually. I'm not sure
where
that threshold would be, but it's higher than "thousands".

Cheers,
Steve

I'm just a developer (my DBA would have more details on the settings
that needed adjusting), but I will attest to it being reasonably easy to
hit database limits.

For us, it was related to having a reasonably small number of (large)
aggregate tables that are partitioned (inherited with check constraints)
by week.

The real problem wasn't the absolute limit of tables (IIRC) as much as
accessing the data in a parallel manner from the parent tables in bulk
update transactions (the parallel was probably what pushed it over the
top).

The limit is absurdly high, and by the time you hit it, you'll probably
have a really good idea of how to overcome it. Really, there's lots of
"challenges" you'll overcome before that time (IMO).

Of course all this is anecdotal, and you should take it with a grain of
salt. :)

-Mark

Thanks for all of the replies.

Best Regards,

Michael Gould, Manager Information Technology
All Coast Intermodal Services, Inc.
904-226-0978
_____

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
To: Roberts, Jon [mailto:Jon.Roberts@asurion.com]
Cc: mgould@allcoast.net, pgsql-general General [mailto:pgsql-general@postgresql.org]
Sent: Mon, 23 Jun 2008 15:00:05 -0400
Subject: Re: [GENERAL] Data Types

"Roberts, Jon" <Jon.Roberts@asurion.com> writes:

Character will use more disk space than varchar so it does make a
difference.

char also has very peculiar comparison semantics. Unless your strings
are really truly fixed-length, you should just about always use varchar.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 23, 2008 at 2:21 PM, Steve Atkins <steve@blighty.com> wrote:

In real use you're unlikely to hit any limits, theoretical or practical,
but if you start to use a silly number of tables and so on you're likely to
hit performance issues eventually. I'm not sure where that threshold would
be, but it's higher than "thousands".

Actually, the DB I have in mind would certainly be approaching "silly
territory." I'm looking at a schema with around 10 thousand tables (or
views). Unfortunately, as far as I can tell,
http://www.postgresql.org/about/ says nothing about maximum number of
tables. I suppose I could always find what this limit is "the hard way", by
writing a script that just keeps creating empty tables and see where that
goes, but I'd prefer not to do something like this...

Anyway, thanks! (And to Joshua too!)

Kynn

"Kynn Jones" <kynnjo@gmail.com> writes:

Actually, the DB I have in mind would certainly be approaching "silly
territory." I'm looking at a schema with around 10 thousand tables (or
views). Unfortunately, as far as I can tell,
http://www.postgresql.org/about/ says nothing about maximum number of
tables.

There is no hard limit (at least not till you hit the 16TB size limit on
pg_class or pg_attribute...). In practice this number tends to be
constrained by the operating system, not Postgres. How well does your
OS do with tens of thousands of entries in a single directory?

Generally, though, schema designs like this amount to manually replacing
leading columns of an index key with separate tables, and they're
usually bad style and a PITA to work with. If you have a desperate need
to partition the data then you might have to go that way, but you should
think twice.

regards, tom lane

On Mon, Jun 23, 2008 at 2:45 PM, Kynn Jones <kynnjo@gmail.com> wrote:

Actually, the DB I have in mind would certainly be approaching "silly
territory." I'm looking at a schema with around 10 thousand tables (or
views).

What kind of app would require such a schema? Just curious...

Steve Atkins wrote on 23.06.2008 20:21:

In real use you're unlikely to hit any limits, theoretical or practical,

I imagine that the 1GB column-value limit is something that could be reached
though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.

Thomas

Thomas Kellerer wrote:

Steve Atkins wrote on 23.06.2008 20:21:

In real use you're unlikely to hit any limits, theoretical or practical,

I imagine that the 1GB column-value limit is something that could be
reached though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.

No, since they are stored out of band (toast tables). For *lob there is
a lob type which stored the data completely separate, not to be confused
with bytea and text (varchar).

Tino.

On Monday 23 June 2008 15:45:22 Kynn Jones wrote:

On Mon, Jun 23, 2008 at 2:21 PM, Steve Atkins <steve@blighty.com> wrote:

In real use you're unlikely to hit any limits, theoretical or practical,
but if you start to use a silly number of tables and so on you're likely
to hit performance issues eventually. I'm not sure where that threshold
would be, but it's higher than "thousands".

Actually, the DB I have in mind would certainly be approaching "silly
territory." I'm looking at a schema with around 10 thousand tables (or
views). Unfortunately, as far as I can tell,
http://www.postgresql.org/about/ says nothing about maximum number of
tables. I suppose I could always find what this limit is "the hard way",
by writing a script that just keeps creating empty tables and see where
that goes, but I'd prefer not to do something like this...

http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL