Stephen Frost writes:
> * Job (j...@colliniconsulting.it) wrote:
>> We thought to implement one partition for day.
>> Do you think it should be fine?
> Really depends on what you're doing. If you're running very short
> queries that pull out just a record or a few records, then you're going
> to
Greetings,
* Job (j...@colliniconsulting.it) wrote:
> >If this is really what you're mostly doing, having constraint exclusion and
> >an index on 'profile' would probably be enough, if you insist on continuing
> >to have the table partitioned by day (which I continue to argue is a bad
> >idea-
> We thought to implement one partition for day.
That would be 365 partitions in a year.
In our experience INSERTS suffers the most in a partitioned table because
triggers are the only way to route the row to the proper child (inherited)
table.
Question: How is your insert pattern? Do you inse
Hi Stephen,
Thank you for your excellent opinion!
>If this is really what you're mostly doing, having constraint exclusion and an
>index on 'profile' would probably be enough, if you insist on continuing to
>have the table partitioned by day (which I continue to argue is a bad idea-
>based on
On 2/20/2017 5:22 AM, Stephen Frost wrote:
You probably shouldn't be partitioning by day for such a small dataset,
unless you've only got a few days worth of data that make up those 800m
records.
agreed. we do like 6 months retention by weeks, so there's 26 or so
partitions, that is reasonabl
> Unfortunately, that'll require locking each table and scanning it to make
> sure that the CHECK constraint isn't violated.
Actually, CHECK constraints can be added with the NOT VALID clause.
New tuples will be checked immediately, while the validation of existing tuples
can be done later usin
Greetings,
* Job (j...@colliniconsulting.it) wrote:
> here is primary a partitioned table (for 20/2/2017 logs):
> flashstart=# \d webtraffic_archive_day_2017_02_20;
> Table
> "public.webtraffic_archive_day_2017_02_20"
> Column |Type
7;::text)
Heap Blocks: lossy=225509
-> Bitmap Index Scan on
webtraffic_archive_day_2017_02_16_timestamp_date_wbidx (cost=0.00..120.00
rows=1 width=0) (actual time=29.277..29.277 rows=2255360 loops=1)
Index Cond: (("timestamp")::d
Greetings,
* Job (j...@colliniconsulting.it) wrote:
> we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a
> table.
> Table is partitioned by day, with indexes on partitioned table.
You probably shouldn't be partitioning by day for such a small dataset,
unless you've onl
Please share us an explain analyze of your query and \d+ of your table
2017-02-20 13:33 GMT+01:00 Job :
> Hu guys,
>
> we have a test machine with Postgresql 9.6.1 an about 800.000.000 record
> in a table.
> Table is partitioned by day, with indexes on partitioned table.
>
> Also a simple query (
Hu guys,
we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a
table.
Table is partitioned by day, with indexes on partitioned table.
Also a simple query (for example i want to search log occurred in a specific
day), is immediate in tha partitioned table (table_2017_02_1
11 matches
Mail list logo
