Tagged Questions
2
votes
1answer
33 views
postgresql partitioning master table duplicate entries
I ve created a simple partitioning structure as given below:
Master table
CREATE TABLE parent_table
(
id_n numeric(19,0) NOT NULL,
name_v character varying(255),
location_n numeric(19,0),
...
0
votes
2answers
72 views
Partitioning in PostgreSQL when partitioned table is referenced
My PostgreSQL database has table with entities which can be active and inactive - it's determined by isActive column value. Inactive entities are accessed very rarely, and, as database grows, ...
1
vote
0answers
73 views
Automatically insert data into correct partition table
I was wondering if there was any way to configure PostgreSQL to automatically insert data into the correct partition table based on check constraints? My plan is that I would have a parent table with ...
0
votes
0answers
38 views
Postgres: organize partitions structure for faster select by PK when partitioned by “usage”
dear All.
I try to plan my partitions in Postgres SQL Server.
I have a big Table (~50M) that has ~15 columns, but remember 3 of them:
ID:UUID, "Type":string, ChangeDate:Timestamp
Type has low # ...
0
votes
1answer
70 views
adding more indexes to postgres causes “out of shared memory” error
i have a rather complex query that i'm trying to optimize in postgres 9.2 - the explain analyze gives this plan (explain.depesz.com):
Merge Right Join (cost=194965639.35..211592151.26 ...
2
votes
1answer
112 views
Django Save Object based on PK and another field
I am trying to use a partitioned table in postgresql together with a Django installation.
From Googleing the subject, I found out, that Django does not support partitioning by itself, so I did the ...
1
vote
1answer
89 views
Strategy to maintain large table of backup catalogs per project in MySQL or PostgreSQL
I am developing an LTO backup/restore solution based on gnu tar.
We either keep the tapes inhouse, or the customer might buy these backups from us.
Hence the choice of a widely available, and free ...
0
votes
1answer
81 views
function/procedure to update (ALTER) another function/procedure
I have partitioned tables on PostgreSQL 9.2 each and every one with a partition function that looks like this:
CREATE OR REPLACE FUNCTION myPartitionSelectionFunction()
RETURNS trigger AS
$BODY$
...
0
votes
1answer
67 views
postgresql partition check on hstore field
i have a master table that i am partitioning. everything is fine, except that one of the fields is a hstore type and i would like the CHECK on the partition creation to check against that hstore key's ...
0
votes
1answer
187 views
postgreSQL get last ID in partitioned tables /
my question is basically the same as this one, but i couldn't find an answer, its also written "to be solved in the next release" and "easy for min/max scans"
PostgreSQL+table partitioning: ...
0
votes
1answer
226 views
PostgreSQL: number of rows inserted into table partitioned using 'ON INSERT … DO INSTEAD' rules
Looking into http://www.postgresql.org/docs/9.2/static/rules-status.html, I'm under the impression that PostgreSQL will return the correct number of inserted rows if in addition to non-overlapping ...
1
vote
1answer
174 views
Load contents from a CSV file into a PostgreSQL table
Below is a description of the procedure I went through to try and load data from a file into a PostgreSQL 8.0 database running on a Linux RedHat 7.2 host.
Now, my issue is that the FOR EVERY ROW ...
3
votes
2answers
607 views
PostgreSQL partition query by date optimization
We have a table that has approximately one billion records per month. Considering 18 months of history we are talking about 18 billion records.
This table is partitioned weekly by date (so we have ...
2
votes
1answer
488 views
Postgres partition management with triggers. Issue altering tables
SHORT STORY:
I have a partitioned postgres database with a table to track the partitions and triggers. The triggers need to alter the constraints on the partition tables (their valid_date ...
5
votes
1answer
5k views
Optimize Postgres timestamp query range
I have the following table and indices defined:
CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
eid bigint,
created timestamp with time zone NOT NULL ...
0
votes
1answer
166 views
Partitioning and Sequence Increment
I have table call it scdr_buz and have partitioned it on monthly basis, I have created trigger on insert which take care of upsert and create table if not present then upsert. I have sequence ...
1
vote
1answer
180 views
in postgresql 9.2, can hint optimization be used for partitions?
in a simple partitioned table:
-- note: no records stored in base, only inheritors of base
create table base(
base_id bigint,
base_tp char(3) not null,
... );
create table ...
2
votes
2answers
788 views
Postgres partition pruning
I have a large table in Postgres.
The table name is bigtable and the columns are:
integer |timestamp |xxx |xxx |...|xxx
category_id|capture_time|col1|col2|...|colN
I have partitioned the ...
0
votes
1answer
443 views
How to use @SQLInsert with ResultCheckStyle.NONE in Grails to achieve Postgres partitioning
I want to use Grails with Postgres and partition a table.
I know, that Hibernate and Postgres is not the best pair when it comes to partitioning.
I've read here (slide 25) that I can overcome the ...
2
votes
2answers
1k views
PostgreSQL date query performance problems
I have around 5 million rows in a postgres table. I'd like to know how many rows match start_time >= NOW(), but despite having an index on start_time the query is extremely slow (in the order of ...
0
votes
2answers
953 views
get number of partitions in postgresql database
What is the most efficient way to get the number of partitions created in the database?
I am using *postgresq*l APi for c++.
3
votes
1answer
1k views
Choosing the right partitioning rule
I am setting up a new PostgreSQL 9 database that will contains millions (or maybe billions) of rows. So I decided to partition data using PostgreSQL inheritance.
I created a master table like this ...
1
vote
4answers
2k views
Self-managing PostgreSQL partition pables
I am trying to make a self-managing partition table setup with Postgres. It all revolves around this function but I can't seem to get Postgres to accept my table names. Any ideas or examples of ...
0
votes
3answers
1k views
PostgreSQL+table partitioning: inefficient max() and min()
I have a huge partitioned table stored at a PostgreSQL table. Each child table has an index and a check constraint on its id, e.g. (irrelevant deatils removed for clarity):
Master table: points
...
2
votes
1answer
2k views
PostgreSQL: Auto-partition a table
I have a huge database which holds pairs of numbers (A,B), each ranging from 0 to 10,000 and stored as floats.
e.g.,
(1, 9984.4), (2143.44, 124.243), (0.55, 0), ...
Since the PostgreSQL table ...
2
votes
1answer
1k views
Postgresql: split database between different machines
I want to partition a very large PostgreSQL 8.3 database. Quoting the manual,
Partitioning can provide several
benefits:
...
Seldom-used data can be
migrated to cheaper and slower ...
0
votes
1answer
1k views
Postgres: Table inheritance: Enforcing unique constraints across partitions?
I have a table that I would like to partition, but I don't know how to deal with the uniqueness constraints.
Is it possible to create a unique constraint across multiple child tables?
8
votes
3answers
5k views
hibernate insert batch with postgresql
is there a solution for batch insert via hibernate in partitioned postgresql table? currently i'm getting an error like this...
57286 [pool-1-thread-18] ERROR org.hibernate.jdbc.AbstractBatcher - ...
