pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitions
8+ messages / 5 participants
[nested] [flat]
* Partitions
@ 2017-11-06 07:19 Günce Kaya <guncekaya14@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Günce Kaya @ 2017-11-06 07:19 UTC (permalink / raw)
To: pgsql-admin
Hello,
We have a large table and we use partition on that table. We keep data as
monthly with partitions so partitions check date fields. I'm confused about
adding second partition on same table but for check different field like
state(character varying). By the way this partitions which check date has
index for state column.
When a query run first check partitions. Sometimes it is really make slow
queries than we expect. We want to get query check first state field during
running that query. In brief we would like to prioritise this.
Can we separate partition structure for instance partitions for date and
partitions for state on same table?
I'm not sure it is reasonable? Is there anyone performed this scenario?
Regards,
Gunce Kaya
Linkedin <https://tr.linkedin.com/in/guncekaya; - Twitter
<https://twitter.com/gguncesi; - Blog
<http://www.guncekaya.blogspot.com.tr/;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Partitions
@ 2017-11-09 23:28 rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-09 23:41 ` Re: Partitions John Scalia <jayknowsunix@gmail.com>
2017-11-10 00:18 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 8+ messages in thread
From: rammohan ganapavarapu @ 2017-11-09 23:28 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a requirement to create monthly partitions in my db, i am trying to
understand what is the best way to do it, i know i can run a cron job to
check if partition exist and create if not at the starting of month. Any
suggestions?
Thanks,
Ram
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2017-11-09 23:41 ` John Scalia <jayknowsunix@gmail.com>
2017-11-10 02:03 ` Re: Partitions Scott Mead <scottm@openscg.com>
1 sibling, 1 reply; 8+ messages in thread
From: John Scalia @ 2017-11-09 23:41 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: pgsql-admin
Set up a trigger on insert to check for the existence of the partition table, then it could do the insert there or create the table if needed and insert. Look online. I know I’ve seen examples of this.
—
Jay
Sent from my iPad
> On Nov 9, 2017, at 6:28 PM, rammohan ganapavarapu <rammohanganap@gmail.com> wrote:
>
> Hi,
>
> I have a requirement to create monthly partitions in my db, i am trying to understand what is the best way to do it, i know i can run a cron job to check if partition exist and create if not at the starting of month. Any suggestions?
>
> Thanks,
> Ram
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-09 23:41 ` Re: Partitions John Scalia <jayknowsunix@gmail.com>
@ 2017-11-10 02:03 ` Scott Mead <scottm@openscg.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Mead @ 2017-11-10 02:03 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: rammohan ganapavarapu <rammohanganap@gmail.com>; pgsql-admin
> On Nov 9, 2017, at 18:41, John Scalia <jayknowsunix@gmail.com> wrote:
>
> Set up a trigger on insert to check for the existence of the partition table, then it could do the insert there or create the table if needed and insert. Look online. I know I’ve seen examples of this.
Be careful here, these solutions generally take a massive performance hit. The best method is the native pg_partman function. It includes a built-in scheduler so that you don’t need to rely on an external tool like cron.
> —
> Jay
>
> Sent from my iPad
>
>> On Nov 9, 2017, at 6:28 PM, rammohan ganapavarapu <rammohanganap@gmail.com> wrote:
>>
>> Hi,
>>
>> I have a requirement to create monthly partitions in my db, i am trying to understand what is the best way to do it, i know i can run a cron job to check if partition exist and create if not at the starting of month. Any suggestions?
>>
>> Thanks,
>> Ram
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2017-11-10 00:18 ` Stephen Frost <sfrost@snowman.net>
2017-11-10 01:37 ` Re: Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2017-11-10 00:18 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: pgsql-admin
Greetings,
* rammohan ganapavarapu (rammohanganap@gmail.com) wrote:
> I have a requirement to create monthly partitions in my db, i am trying to
> understand what is the best way to do it, i know i can run a cron job to
> check if partition exist and create if not at the starting of month. Any
> suggestions?
I'd suggest you take a look at pg_partman.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 00:18 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
@ 2017-11-10 01:37 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 01:50 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 8+ messages in thread
From: rammohan ganapavarapu @ 2017-11-10 01:37 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
I saw pg_parman but it's third party extension right, i am trying to see if
I can use native plugin or extension to the job.
Thanks for your suggestion, i will consider it.
Ram
On Nov 9, 2017 4:18 PM, "Stephen Frost" <sfrost@snowman.net> wrote:
> Greetings,
>
> * rammohan ganapavarapu (rammohanganap@gmail.com) wrote:
> > I have a requirement to create monthly partitions in my db, i am trying
> to
> > understand what is the best way to do it, i know i can run a cron job to
> > check if partition exist and create if not at the starting of month. Any
> > suggestions?
>
> I'd suggest you take a look at pg_partman.
>
> Thanks!
>
> Stephen
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 00:18 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
2017-11-10 01:37 ` Re: Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
@ 2017-11-10 01:50 ` Stephen Frost <sfrost@snowman.net>
2017-11-10 01:59 ` Re: Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2017-11-10 01:50 UTC (permalink / raw)
To: rammohan ganapavarapu <rammohanganap@gmail.com>; +Cc: pgsql-admin
Greetings,
* rammohan ganapavarapu (rammohanganap@gmail.com) wrote:
> I saw pg_parman but it's third party extension right, i am trying to see if
> I can use native plugin or extension to the job.
There isn't anything built-in, which means the options are to build your
own version of something like pg_partman, or to simply use pg_partman.
You're certainly welcome to choose between those, but I can say that I'm
pretty happy to use pg_partman to deal with exactly what you're asking
for.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitions
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 00:18 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
2017-11-10 01:37 ` Re: Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 01:50 ` Re: Partitions Stephen Frost <sfrost@snowman.net>
@ 2017-11-10 01:59 ` rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: rammohan ganapavarapu @ 2017-11-10 01:59 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Thank you, i will take a look.
Ram
On Thu, Nov 9, 2017 at 5:50 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> * rammohan ganapavarapu (rammohanganap@gmail.com) wrote:
> > I saw pg_parman but it's third party extension right, i am trying to see
> if
> > I can use native plugin or extension to the job.
>
> There isn't anything built-in, which means the options are to build your
> own version of something like pg_partman, or to simply use pg_partman.
> You're certainly welcome to choose between those, but I can say that I'm
> pretty happy to use pg_partman to deal with exactly what you're asking
> for.
>
> Thanks!
>
> Stephen
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-11-10 02:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-06 07:19 Partitions Günce Kaya <guncekaya14@gmail.com>
2017-11-09 23:28 Partitions rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-09 23:41 ` John Scalia <jayknowsunix@gmail.com>
2017-11-10 02:03 ` Scott Mead <scottm@openscg.com>
2017-11-10 00:18 ` Stephen Frost <sfrost@snowman.net>
2017-11-10 01:37 ` rammohan ganapavarapu <rammohanganap@gmail.com>
2017-11-10 01:50 ` Stephen Frost <sfrost@snowman.net>
2017-11-10 01:59 ` rammohan ganapavarapu <rammohanganap@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox