Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Rather than keeping it a single physical table, have you looked into <a href="http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html" rel="nofollow noreferrer">PostgreSQL partitioning</a>? It's supported as of version 8.1.</p> <p>Partitioning can help you avoid the problem of choosing between fast INSERT vs fast DELETE performance. You can always partition the table by Year/Month, and just drop the partitions that you no longer need. Dropping partitions is extremely fast, and inserting into small partitions is also extremely fast.</p> <p>From the manual:</p> <blockquote> <blockquote> <blockquote> <p>Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:</p> </blockquote> </blockquote> <ul> <li>Query performance can be improved dramatically for certain kinds of queries.</li> <li>Update performance can be improved too, since each piece of the table has indexes smaller than an index on the entire data set would be. When an index no longer fits easily in memory, both read and write operations on the index take progressively more disk accesses.</li> <li>Bulk deletes may be accomplished by simply removing one of the partitions, if that requirement is planned into the partitioning design. DROP TABLE is far faster than a bulk DELETE, to say nothing of the ensuing VACUUM overhead.</li> <li>Seldom-used data can be migrated to cheaper and slower storage media. </li> </ul> <blockquote> <blockquote> <p>The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.</p> <p>Currently, PostgreSQL supports partitioning via table inheritance. Each partition must be created as a child table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see Section 5.8) before attempting to implement partitioning.</p> </blockquote> </blockquote> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. POOptimal database table optimization method
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLBushkin
    1. USLBushkin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CObut partitioning the table by Year/Month optimizes for the delete instead of the insert / select, right ?
      singulars
      1. PO
      1. USPat
    2. CO@Pat: It should optimize for both. Having small partitions improves inserts, and if most queries only need "current" data, those will be optimized as well. As for selects that operate against the entire table (all partitions), they will already be incurring high cost that the physical partitioning should be negligible.
      singulars
      1. PO
      1. USLBushkin
    3. COSo if I understand correctly, the partitioning you suggest will optimize delete and insert, it will not make the select worse because it is already bad. But the requirement is to speed "inserts and selects" instead of speed "deletes and inserts". Or am I missing something in your answer?
      singulars
      1. PO
      1. USPat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload