partition table to
inherit from the parent, commit, then drop the old table. This operation would
be very fast, the users probably won't even notice.
Bob Lunney
On Nov 27, 2012, at 4:04 PM, Mike Blackwell wrote:
> I need to delete about 1.5 million records from a table and reload i
Houman,
Partition by date and revise your processes to create and load a new child
table every day. Since you already know the date append it to the table base
name and go straight to the data you need. Also, the index on T.c won't help
for this query, you're looking at a full table scan eve
Bob
From: Tomas Vondra
To: pgsql-performance@postgresql.org
Sent: Friday, March 30, 2012 8:11 PM
Subject: Re: [PERFORM] database slowdown while a lot of inserts occur
On 29.3.2012 21:27, Bob Lunney wrote:
> Lance,
>
> May small inserts cause frequent fsyncs. Is there any w
Lance,
May small inserts cause frequent fsyncs. Is there any way those small inserts
can be batched into some larger sets of inserts that use copy to perform the
load?
Bob Lunney
From: "Campbell, Lance"
To: "Campbell, Lance"
Possibly. What does
psql > show work_mem;
say?
Bob Lunney
From: Alessandro Gagliardi
To: pgsql-performance@postgresql.org
Sent: Wednesday, February 1, 2012 12:19 PM
Subject: Re: [PERFORM] From Simple to Complex
Final update on this thread: since it
Otto,
Separate the pg_xlog directory onto its own filesystem and retry your tests.
Bob Lunney
From: Havasvölgyi Ottó
To: Marti Raudsepp
Cc: Aidan Van Dyk ; pgsql-performance@postgresql.org
Sent: Thursday, December 8, 2011 9:48 AM
Subject: Re: [PERFORM
f. Listing ony the ones that have changed is sufficient.
Finally, the wiki has some good information on the care and feeding of a
PostgreSQL database:
http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
Bob Lunney
--- On Tue, 4/12/11, Václav Ovsík wrote:
table_version.bde_crs_action_revision
USING btree
(_revision_expired, audit_id);
Bob Lunney
--- On Wed, 3/30/11, Jeremy Palmer wrote:
> From: Jeremy Palmer
> Subject: [PERFORM] Slow deleting tables with foreign keys
> To: "pgsql-performance@postgresql.org"
> Date: Wed
(Just the ones
changed from the default, please.)
8. Which file system are you running for the database files? Mount options?
9. Are the WAL files on the same file system?
Bob Lunney
--- On Wed, 3/30/11, Strange, John W wrote:
> From: Strange, John W
> Subject: [PERFORM] COPY wit
(You don't want to know
what happens if the person isn't killed.)
I don't know what property your admin type is trying to protect, but I'm
inclined to let it burn and live to work through the insurance collection
process.
Oh, and +1 for timed escalation of a shutdown.
Bob
| as.you_can - without end
> http://blog.rubybestpractices.com/
>
> -Inline Attachment Follows-
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
Louis,
Someday, as sure as Codd made little relational databases, someone will put an
incorrect price in that table, and it will have to be changed, and that change
will ripple throughout your system. You have a unique chance here, at the
beginning, to foresee that inevitability and plan for it.
Take a look at
http://en.wikipedia.org/wiki/Temporal_database
and
http://pgfoundry.org/projects/temporal/
and anything Snodgrass ever wrote about temporal databases. Its a fascinating
schema design subject, one that comes in very handy in dealing with
time-influenced data.
Good luck!
Bob Lunney
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
red buffer
cache's state doesn't change significantly between the start of planning and
actual execution time, and the host is dedicated to running the database and
nothing else that would trash the host's file system cache. I admit that I
haven't looked at the code for this yet, so I don't know if I'm on to something
or off in the weeds.
Regards,
Bob Lunney
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Be sure that you are starting PostgreSQL using an account with sufficient
memory limits:
ulimit -m
If the account has memory limit below the server's configuration you may get
the out of memory error.
Bob Lunney
--- On Tue, 11/9/10, Till Kirchner wrote:
> From: Till Kirchner
&
m referring to. You discovered this independently yourself,
according to your anecdote about the overlapping indexes.
Bob Lunney
--- On Fri, 9/24/10, Tobias Brox wrote:
> From: Tobias Brox
> Subject: Re: [PERFORM] Memory usage - indexes
> To: "Bob Lunney"
> Cc: pgsql
n one huge index on the
original table.
Good luck!
Bob Lunney
--- On Thu, 9/23/10, Tobias Brox wrote:
> From: Tobias Brox
> Subject: [PERFORM] Memory usage - indexes
> To: pgsql-performance@postgresql.org
> Date: Thursday, September 23, 2010, 5:50 PM
> We've come to a ti
We need more information than that, like:What version of PostgreSQL?What does the hardware look like?What does the disk and tablespace layout look like?How are your configuration variables set?Other than that, are the statistics up to date on the VehicleMake table?Bob Lunney--- On Thu, 8/26/10
r you automatically.
Bob Lunney
--- On Mon, 8/23/10, DM wrote:
From: DM
Subject: [PERFORM] Triggers or code?
To: pgsql-performance@postgresql.org
Date: Monday, August 23, 2010, 2:42 PM
Hello There,
I have a table x and a history table x_hist, whats the best way to update the
history table.
should i ne
check the undo logs to
determine the state that applies to your transaction. Its just two different
ways to accomplish the same thing.
Bob Lunney
--- On Fri, 6/11/10, Jayadevan M wrote:
> From: Jayadevan M
> Subject: [PERFORM] Query about index usage
> To: pgsql-performance@postgre
s into tuning a PG server for good performance than simply
installing the software, setting a couple of GUCs and running it.
Bob
--- On Thu, 6/10/10, Tom Wilcox wrote:
> From: Tom Wilcox
> Subject: Re: [PERFORM] requested shared memory size overflows size_t
> To: "Bob Lunney&
True, plus there are the other issues of increased checkpoint times and I/O,
bgwriter tuning, etc. It may be better to let the OS cache the files and size
shared_buffers to a smaller value.
Bob Lunney
--- On Wed, 6/9/10, Robert Haas wrote:
> From: Robert Haas
> Subject: Re: [P
enable_seqscan = off for the session, but that is
a Big Hammer for what is probably a smaller problem.
Bob Lunney
--- On Wed, 6/2/10, Jori Jovanovich wrote:
From: Jori Jovanovich
Subject: [PERFORM] SELECT ignoring index even though ORDER BY and LIMIT present
To: pgsql-performance@postgresql.org
Date
riant, which won't have
this problem at all.
Good luck!
Bob Lunney
--- On Wed, 6/2/10, Tom Wilcox wrote:
> From: Tom Wilcox
> Subject: Re: [PERFORM] requested shared memory size overflows size_t
> To: pgsql-performance@postgresql.org
> Date: Wednesday, June 2, 2010, 6:58
time to delete them just
drop the child table.
Of course, if the 1M rows you need to delete is very small compared to the
total overall size of the original table the first two techniques might now buy
you anything, but its worth a try.
Good luck!
Bob Lunney
--- On Sat, 5/8/10, thilo wrote
If you have a multi-processor machine (more than 2) you could look into pigz,
which is a parallelized implementation of gzip. I gotten dramatic reductions in
wall time using it to zip dump files. The compressed file is readable by
ungzip.
Bob Lunney
From: Dave Crooke
Subject: Re: [PERFORM
agmentation and increased seek times.
If PostgreSQL had a mechanism to pre-allocate files prior to restoring the
database that might mitigate the problem.
Then if we could only get parallel index operations ...
Bob Lunney
--- On Wed, 3/17/10, Greg Stark wrote:
> From: Greg Stark
>
Try replacing the 'current_timestamp - interval 8 days' portion with explicit
values (e.g. partitioned_column < '2009-10-21'::date ) and see if that works.
I think the query planner can only use explicit values to determine if it
should go straight to partitioned tables.
Bob
--- On Thu, 10/29
Greg,
Thanks for the mental prod! Yes, the original data is more closely sorted by
the timestamptz column, since they represent events coming into the collection
system in real time. As for the distribution of data values, it goes without
saying the timestamptz value is monotonically increas
27 matches
Mail list logo
