We store tags on our items like this like this:
Tag.ID INT NOT NULL PRIMARY KEY
Tag.Value TEXT LCASE NOT NULL UNIQUE
Item.ID INT NOT NULL PRIMARY KEY
ItemTagBinding.ItemID INT NOT NULL REFERENCES Item.ID
ItemTagBinding.TagID INT NOT NULL REFERENCES Tag.ID
ItemTagBinding.ItemID + ItemTagBinding.
On Wed, 2007-07-18 at 14:26 -0700, [EMAIL PROTECTED] wrote:
> I am planning to add a tags (as in the "web 2.0" thing) feature to my web
> based application. I would like some feedback from the experts here on
> what the best database design for that would be.
>
> The possibilities I have come up
I am planning to add a tags (as in the "web 2.0" thing) feature to my web
based application. I would like some feedback from the experts here on
what the best database design for that would be.
The possibilities I have come up with are:
* A tags table containing the tag and id number of what it li
Adriaan van Os wrote:
> So, how does one (temporarily) disable WAL logging ? Or, for example,
> disable WAL logging for a temporary table ?
Operations on temporary tables are never WAL logged. Operations on other
tables are, and there's no way to disable it.
--
Heikki Linnakangas
EnterpriseD
Michael Stone wrote:
On Tue, Jul 17, 2007 at 10:58:01PM +0200, Thomas Finneid wrote:
I am not sure I understand you correctly here, are you saying that
SELECT INTO in 8.1 disables WAL logging and uses just a single fsync
at the end? in that case it means that I could disable WAL as well and
ac
Kalle Hallivuori wrote:
Hi.
2007/7/8, Thomas Finneid <[EMAIL PROTECTED]>:
Kalle Hallivuori wrote:
> COPY is plentitudes faster than INSERT:
> http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
> If you can't just push the data straight into the final table with
> COPY, push
Michael Stone wrote:
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar
to what happens in there.
No, because it only works on file to db or vice versa not table to table.
I don'
Michael Glaesemann wrote:
As they're individual inserts, I think what you're seeing is overhead
from calling this statement 100,000 times, not just on the server but
also the overhead through JDBC. For comparison, try
CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;
INSERT INTO ciu_data_
PFC wrote:
Unfortunately its not fast enough, it needs to be done in no more than
1-2 seconds, ( and in production it will be maybe 20-50 columns of
data, perhaps divided over 5-10 tables.)
Additionally it needs to scale to perhaps three times as many columns
and perhaps 2 - 3 times as many ro
On Wed, Jul 18, 2007 at 01:08:30PM -0400, Steven Flatt wrote:
> We're using Postgres 8.2.4.
>
> I'm trying to decide whether it's worthwhile to implement a process that
> does periodic reindexing. In a few ad hoc tests, where I've tried to set up
> data similar to how our application does it, I'v
On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote:
> EnterpriseDB, a commercially enhanced version of PostgreSQL can do
> query parallelization, but it comes at a cost, and that cost is making
> sure you have enough spindles / I/O bandwidth that you won't be
> actually slowing your syst
On Wed, Jul 18, 2007 at 10:03:00AM +0100, Heikki Linnakangas wrote:
> Ron Mayer wrote:
> > Seems Linux has IO scheduling through a program called ionice.
> >
> > Has anyone here experimented with using it rather than
> > vacuum sleep settings?
>
> I looked at that briefly for smoothing checkpoint
We're using Postgres 8.2.4.
I'm trying to decide whether it's worthwhile to implement a process that
does periodic reindexing. In a few ad hoc tests, where I've tried to set up
data similar to how our application does it, I've noticed decent performance
increases after doing a reindex as well as
On Jul 18, 2007, at 11:30 AM, Michael Dengler wrote:
Hmm..I was hoping to avoid personal insults
Anyway, Nuts or not...what I am attempting is to simply have row
from one table inserted into another servers DB I don't see it as
replication because:
a) The destination table will have
Hmm..I was hoping to avoid personal insults
Anyway, Nuts or not...what I am attempting is to simply have row from one
table inserted into another servers DB I don't see it as replication
because:
a) The destination table will have a trigger that modifies the arriving data
to fit its table sc
On 7/18/07, Benjamin Arai <[EMAIL PROTECTED]> wrote:
Hi,
If I have a query such as:
SELECT * FROM (SELECT * FROM A) UNION ALL (SELECT * FROM B) WHERE
blah='food';
Assuming the table A and B both have the same attributes and the data
between the table is not partitioned in any special way, does
On 7/18/07, Benjamin Arai <[EMAIL PROTECTED]> wrote:
But I want to parrallelize searches if possible to reduce
the perofrmance loss of having multiple tables.
PostgreSQL does not support parallel query. Parallel query on top of
PostgreSQL is provided by ExtenDB and PGPool-II.
--
Jonah H. Harr
Hi,
If I have a query such as:
SELECT * FROM (SELECT * FROM A) UNION ALL (SELECT * FROM B) WHERE
blah='food';
Assuming the table A and B both have the same attributes and the data
between the table is not partitioned in any special way, does Postgresql
execute WHERE blah="food" on both table sim
On Wed, 2007-07-18 at 16:02 +0200, Csaba Nagy wrote:
> On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> > Row X is inserted into TableX in DB1 on server1TableX trigger
> > function fires and contacts DB2 on server2 and inserts the row into
> > TableY on server2.
> This kind of problem is
"Michael Dengler" <[EMAIL PROTECTED]> writes:
> I am trying to find out how to use a trigger function on a table to copy any
> inserted row to a remote PG server.
> ...
> This is not replication, I'm not interested in a full blown trigger based
> replication solution.
To be blunt, you're nuts. Yo
On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> Row X is inserted into TableX in DB1 on server1TableX trigger
> function fires and contacts DB2 on server2 and inserts the row into
> TableY on server2.
This kind of problem is usually solved more robustly by inserting the
"change" into a
Michael Dengler wrote:
> I am trying to find out how to use a trigger function on a table to copy
> any
> inserted row to a remote PG server.
Have a look at contrib/dblink.
You'll have to think what you want to happen in error scenarios. For
example, if the connection is down, or it brakes just a
Hi All,
I am trying to find out how to use a trigger function on a table to copy any
inserted row to a remote PG server.
ie:
Row X is inserted into TableX in DB1 on server1TableX trigger function
fires and contacts DB2 on server2 and inserts the row into TableY on
server2.
I've looked arou
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
No, because it only works on file to db or vice versa not table to table.
I don't understand how the ins
On Tue, Jul 17, 2007 at 10:58:01PM +0200, Thomas Finneid wrote:
I am not sure I understand you correctly here, are you saying that
SELECT INTO in 8.1 disables WAL logging and uses just a single fsync at
the end? in that case it means that I could disable WAL as well and
achieve the same perform
It's the time to parse statements, plan, execute, roundtrips with
the client, context switches, time for your client library to escape
the data and encode it and for postgres to decode it, etc. In a word :
OVERHEAD.
I know there is some overhead, but that much when running it batched.
Ron Mayer wrote:
> Seems Linux has IO scheduling through a program called ionice.
>
> Has anyone here experimented with using it rather than
> vacuum sleep settings?
I looked at that briefly for smoothing checkpoints, but it was
unsuitable for that purpose because it only prioritizes reads, not w
Thomas Finneid wrote:
> During the somes I did I noticed that it does not necessarily seem to be
> true that one needs the fastest disks to have a pg system that is fast.
>
> It seems to me that its more important to:
> - choose the correct methods to use for the operation
> - tune the pg memory s
Tom Lane wrote:
Thus the timing issue (at least as exhibited by this script) has nothing
whatever to do with the time to delete a file, but with the time to
create one. Since the part of DROP being timed has probably got no I/O
involved at all (the tuples being touched are almost surely still i
29 matches
Mail list logo
