473,699 Members | 2,746 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Controlling maximal on-disk size of a table

I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw old entries away to
keep the table from growing.

I understand that in addition to issue a
DELETE FROM log WHERE date < xxx
I also need to issue a
VACUUM log
so that new entries will use the space of deleted entries.

Now I want to reserve a certain amount of disk storage to hold the log table. So I first let the table (plus its index, its toast table and toast index) grow until it is about to reach the maximum size. Then a daemon continually deletes old entries and vacuums the table so the on-disk usage stays more or less constant from now on, at least this is the idea.

Of course I would like to keep as much history as possible, given the available space. Also the log may sometimes be quiet and sometimes quite busy, also the size of the text entries may vary quite a bit.

Now to make a good guess about when to issue the next delete, I need to estimate how much of the on-disk usage is accounted for by deleted entries.

I can of course count the number of entries, estimate the bytes needed for storage by averaging the length of the text column, adding the size of the fixed columns and compare that to the on-disk size to conclude how much space is still available. As for the index I assume it is has a fixed size per row.

But these queries are expensive because the log may easily contain millions of entries with an on disk size in the range of a few GB, and must be repeated quite often to prevent sudden bursts of new entries from overflowing the log.

Is there a better way to get at the current "free space" inside of a table/index?
_______________ _______________ _______________ _____________
Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min.
weltweit telefonieren! http://freephone.web.de/?mc=021201
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
1 3429
What you're looking for sounds like and extended (destructive) version
of what autovacuum does.

So you might try to look at the statistics tables like autovacuum does.
I don't know how it does that, but it seems that that way you'd be able
to incrementally have approximate information about what happens with
the table.

Good luck,

d.
--
David Helgason,
Business Development et al.,
Over the Edge I/S (http://otee.dk)
Direct line +45 2620 0663
Main line +45 3264 5049

On 16. nov 2004, at 13:21, Nils Rennebarth wrote:
I have a table that is essentially a log where new entries are
streaming in continually and from time to time I throw old entries
away to
keep the table from growing.

I understand that in addition to issue a
DELETE FROM log WHERE date < xxx
I also need to issue a
VACUUM log
so that new entries will use the space of deleted entries.

Now I want to reserve a certain amount of disk storage to hold the log
table. So I first let the table (plus its index, its toast table and
toast index) grow until it is about to reach the maximum size. Then a
daemon continually deletes old entries and vacuums the table so the
on-disk usage stays more or less constant from now on, at least this
is the idea.

Of course I would like to keep as much history as possible, given the
available space. Also the log may sometimes be quiet and sometimes
quite busy, also the size of the text entries may vary quite a bit.

Now to make a good guess about when to issue the next delete, I need
to estimate how much of the on-disk usage is accounted for by deleted
entries.

I can of course count the number of entries, estimate the bytes needed
for storage by averaging the length of the text column, adding the
size of the fixed columns and compare that to the on-disk size to
conclude how much space is still available. As for the index I assume
it is has a fixed size per row.

But these queries are expensive because the log may easily contain
millions of entries with an on disk size in the range of a few GB, and
must be repeated quite often to prevent sudden bursts of new entries
from overflowing the log.

Is there a better way to get at the current "free space" inside of a
table/index?
_______________ _______________ _______________ _____________
Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min.
weltweit telefonieren! http://freephone.web.de/?mc=021201
---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1562
by: martin z | last post by:
I'm having a lot of trouble figuring out the details of controlling Python compiling. I can compile and embed just fine, but I want some finer granularity on what gets compiled into Python. I can find little to no documentation of the compiler flags besides the ones essential to operating system compatibility. How does one choose what built-in modules are compiled into Python? I ask because newer versions are accumulating more and...
0
1905
by: Bart Simpson | last post by:
Hi, I would like to create/delete ftp accounts of windows IIS, and to control MS Media Service under windows 2003 by Python program. A few days ago, I found there is iisftp.vbs vb-script that can creat/delete IIS ftp account. But I've suspected the iisftp cannot configure detailed configueration. (ex. It can't configure anonymous-login and home directory
2
1534
by: Skip Montanaro | last post by:
I updated the patch that supports PEP 304, "Controlling Generation of Bytecode Files" to apply cleanly against current CVS. I've tested it on Mac OS X (straight Unix build only). I'd appreciate it if some Linux, Windows and Mac framework folks could apply the patch, rebuild, then run the tests (there is a "testbcb" target in the Makefile that should give Windows people an idea what to do). The patch is attached to ...
1
1506
by: Seda Ozulku | last post by:
I don't have much information about .Net. May be it must be a silly question. I have Windows Application running at the server. I need to Access the program functions ( like at given time program sends mail to customer or you can manually select customer and send it ) from web. Why web? I dont want to install client applications to the all user... And Clients dont have access to server via Terminal and because of th e the Server...
3
2170
by: yadavindrian01 | last post by:
Hi everybody I am a student of computer engg. I have to make a project - controlling devices through computer using parallel port. I know necessary details of the parallel port. Please guide me. also tell me if can i use VB as front end and C++ as the coding language and how. Regards
5
1447
by: Mainard | last post by:
Hi, all First off I know alittle about javascript, but i have never worked with controlling froms with it be for i wonder can some help me? for example:I have this <textarea name"write1">Write Your Message:</textarea> on a form called myform2, and i have another form and textarea on another page. how am i to send the text from one from to the other form?
8
3225
by: Andrey Mosienko | last post by:
We are using PostgreSQL about two years beginning from version 7.0. I have one question about starting postmaster: Is there way to detach it from the controlling terminal under FreeBSD? My situation: I start postmaster manually: su pgsql -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/pgsql.log start" Log:
0
1125
by: RWC | last post by:
Hi! I'm having some trouble controlling how the XP Packaging Wizard installs my applications. In 97, there were options to replace older, never or always, if there was a file conflict. Now with XP, the user is prompted if they want to keep the file or not. I'd like to bypass this prompt and over write certain files, and NEVER overwrite others (the data file for instance). Right now, the user can choose to run the upgrade set up, but...
1
1264
by: Rex | last post by:
Hi, If someone has turned off Java, what is the maximal set of info I can learn about a user, his computer & browser? Also, how can I detect whether Java is disabled? Thanks.
6
21343
by: Lubomir | last post by:
Hi, Where in .NET are definded constants for maximal file name length and maximal file path? Thanks, Lubomir
0
8704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7776
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6540
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.