Search Postgresql Archives

Re: Questions about Partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 04/19/2011 08:56 AM, Phoenix Kiula wrote:
While I fix some bigger DB woes, I have learned a lesson. Huge indexes
and tables are a pain.

Which makes me doubly keen on looking at partitioning.
Before jumping into partitioning it would be useful to know specifically what pain you are having with your current tables and indexes. Maintenance? Performance? Other? Question zero is "What issues are currently causing you pain with large tables?" and after that determining if the partitioning is an appropriate solution. There is pain associated with partitioning, as well, so you need to be sure that you will achieve a net pain reduction.

Carefully read http://www.postgresql.org/docs/current/static/ddl-partitioning.html, it has examples that answer several of your questions. Pay extra attention to 5.9.6 "Caveats".

Some places where partitioning work well:

1. The partition can substitute for an index and the resulting child tables will have somewhat comparable sizes. If you had contact information where state was typically required in queries you might partition the data into tables for each state so a typical query would only touch a smaller data set and the partitioning/child-table constraints substitute for an index on state.

2. You frequently drop data in bulk and can group that data in such a way that you can drop or truncate a child-table. Among the places I've used partitioning is for validation codes. I partition them by like expiration and when the date arrives, I just drop the partition with the expired codes - way faster than delete-from and the necessary follow-up maintenance when deleting millions of codes.

3. The nature of your data is such that it can be partitioned into a small part that is accessed frequently and parts that are relatively rarely accessed.

Most examples I see online are partitioned by date. As in months, or
quarter, and so on. This doesn't work for me as I don't have too much
logic required based on time.

Time-based data often satisfies all of the above (log data you can partition by month, typically only look at the current month and drop data that is a year old, for example) so that's what ends up being in most examples.

The biggest, highest volume SELECT in my database happens through an
"alias" column. This is an alphanumeric column. The second-biggest
SELECT happens through the "userid" column -- because many users check
their account every day.

A rough table definition can be considered as follows:

    CREATE TABLE maintable
           id    SERIAL  primary key
           alias  VARCHAR(42)
           ...
           user_id  VARCHAR(30)
           user_registered   BOOLEAN
           status    VARCHAR(1)


  My questions:

1. Which column should I partition by -- the "alias" because it's the
largest contributor of queries? This should be OK, but my concern is
that when user_id queries are happening, then the data for the same
user will come through many subtables that are partitioned by "alias"
-- will this happen automatically (presuming "constraint exclusion" is
on)? How does partitioning by one column affect queries on others.
Will there be subtable-by-subtable indexes on both alias and

Answer question zero, above, first. But beware - the primary key is not inherited. You run the risk of duplicating the primary key (or other unique identifier) across child tables unless you implement the appropriate constraints on the child tables to prevent this. It's also pointless to have a primary key on the parent table in most situations.

2. How does SERIAL type work with partitions? Will INSERT data go into
the respective partitions and yet maintain an overall sequence -- I
mean, the *same* overall sequence for the parent table distributed
automagically across subtables?
This depends on how you set up your triggers, constraints, child tables etc. but by default a basic "create table thechild () inherits (theparent);" will result in a child table that shares the same sequence as the parent.
3. If I partition using "a%", "b%" etc up to "z%" as the partition
condition, is this an issue -- are about 26 subtables too many
partitions? Mine are static partitions as in they will be the same
forever, unlike data-based partitions. And each partition will
continue to grow. If I include that "alias"es can begin with numbers
and allowed symbols too, then this may be 45 partitions? What's the
limit of partitions -- not only official limit, but practical limit in
terms of performance?

As always, the answer is "depends" but I wouldn't typically see 45 as too many. See primary-key warning above. It's less an absolute number of tables and more whether the design of your tables and queries results in execution efficiency gains that outweigh the additional planner costs.

4. Given that it's a wildcard LIKE condition (with a "%") will this
affect the index and subsequent SELECT speed? Are partition conditions
recommended to be "=" or "<" type operators only or is LIKE ok??
The restriction in your where clause should closely match the constraint definition. I don't know precisely how clever or forgiving the planner is in different situations but have the best results when my where clauses include a condition that exactly matches my constraint definitions. I don't know the details of how the planner decides when to use constraint exclusion but I have observed that a constraint of "the_data ~ '^a'",... does not seem to cause the planner to use constraints but "substr(the_data,1,1)='a'", ... does.
5. Does partitioning need to happen only through one column? Can I
have a condition containing two columns instead?

    CREATE TABLE subtable_a (
	PRIMARY KEY (id)
	CHECK ( user_id LIKE 'a%' and user_registered IS TRUE)
     ) INHERITS (maintable);

    CREATE TABLE subtable_b (
	PRIMARY KEY (id),
	CHECK ( user_id LIKE 'b%' and user_registered IS TRUE)
     ) INHERITS (maintable);

    ......etc....
You can use multiple columns, expressions, etc. For this and the prior question, use "explain" to verify that your design and queries are operating as desired.

7. "Constraint exclusion" - is it recommended to have this in the
pg.conf, or will I need to do this before every SQL? I prefer the
pg.conf way, but want to confirm that there are no downsides for other
regular SQL operations with this setting?
The default, constraint_exclusion = partition, should be fine. The planner will only check constraints in situations where it might be useful.
8. How will JOIN work? I have different tables JOINing with the parent
table now. With partitioned subtables, will constraint exclusion
automatically do what's needed and my SQL does not need to change? Or
will there be triggers required for each and every query I currently
have?

They should "work", just fine. But they will probably not gain in efficiency unless they are of a type where constraint exclusion will be beneficial and are rewritten so the planner will use that exclusion. Note, also, that the planner will not use constraint exclusion on prepared queries.


Eight questions is enough for my first post in this partitioning thread   :)

Thanks much!


Partitioning is a fabulous solution to problems for which it is suited. I'm not convinced that yours is one of them. You may be looking at lots of pain trying to get it to work correctly and it may not even be of benefit. Which returns us to question zero.

Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux